View log cache size

Question

Post reply

View log cache size

RakeshRSingh
SSCarpal Tunnel

Points: 4109
More actions
June 21, 2010 at 2:21 am

#223992

Dear experts,

Can I view the log cache size in SQL Server memory any DMV's which states that.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Answer 1

Log cache?

I know of a data cache and a log buffer.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild : Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Answer 2

RakeshRSingh

SSCarpal Tunnel

Points: 4109

June 21, 2010 at 4:11 am

#1182640

Thanks for your reply Gail.

Can we get the size of log buffer ?

Answer 3

Why? What are you investigating?

The log buffer is pretty small, it's where log records are written before they're hardened to disk

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild : Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Answer 4

RakeshRSingh

SSCarpal Tunnel

Points: 4109

June 21, 2010 at 5:16 am

#1182657

I'm experiencing WRITELOG wait type in SQL Server.

This was the reason I wanted to understand the log buffer size, if we can increase it and can SQL Server perform better with increased log buffer size.

Thanks in advance.

Answer 5

Size of the buffer is not relevant here. Log records MUST be written to disk when a transaction commits, regardless of how full or not the log buffer is.

What kind of system do you have? Lots of small, fast transactions or fewer, larger transactions?

Typically fixing writelog waits involves alleviating IO contention on the log drive. What's your disk layout look like?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild : Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Answer 6

RakeshRSingh

SSCarpal Tunnel

Points: 4109

June 21, 2010 at 10:21 pm

#1183067

Database is handling avg transactions of 2500 /sec. These are lots of small transactions.

we are currently using SAN RAID10 with log file placed on separate disk.

Transactions are waiting for log writing, and at the application end we get pending records.

Answer 7

If there's lots of small transactions, the log buffer size is not going to be a contributing factor. Log records must be flushed to disk when the transaction completes, regardless or how full/empty the log cache is.

Is the log drive a separate physical array?

What's the IO stats on it? (start with avg sec/read and avg sec/write)

How big's the log, what are autogrow settings and how many VLFs do you have?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild : Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Answer 8

Thanks Gail for following the post.

avg disk sec/Write(0.0006) and avg disk sec/Read(0.0004) values are very less.

When we get pending records there is very less utilization of disk counters and CPU counters. What we have measured is the Active Transaction that is showing for the period is between 4 and 7. When this counter shows 0 or less than 3 everything is running fine.

As far as log is concerned it is on different array. we do get an average disk write queue length less than 1 when everything running fine, but less when we face the problem.

I hope I am clear to you.

Answer 9

smitty-1088185

SSCrazy

Points: 2321

June 23, 2010 at 7:35 am

#1183914

Are your disk spindles shared with other applications? Does it happen to be a NetApp SAN?

Answer 10

shaktibangerwa

Grasshopper

Points: 15

September 14, 2015 at 6:27 am

#1826325

Hi Gail,

Could you please share any article or explain log buffer. How transaction and LSN created into it and flushed/written to log file/disk. Can we extend Log buffer size.

Thanks,

Shakti

Answer 11

BLOB EATER

SSChampion

Points: 14609

September 14, 2015 at 8:01 am

#1826368

Paul Randal is your friend.

https://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx#id0060003 "> https://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx#id0060003