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

Gail Shaw SSC Guru Points: 1004504 More actions · 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

RakeshRSingh SSCarpal Tunnel Points: 4109 More actions · Answer 2

Thanks for your reply Gail.

Can we get the size of log buffer ?

Gail Shaw SSC Guru Points: 1004504 More actions · 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

RakeshRSingh SSCarpal Tunnel Points: 4109 More actions · Answer 4

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.

Gail Shaw SSC Guru Points: 1004504 More actions · 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

RakeshRSingh SSCarpal Tunnel Points: 4109 More actions · Answer 6

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.

Gail Shaw SSC Guru Points: 1004504 More actions · 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

RakeshRSingh SSCarpal Tunnel Points: 4109 More actions · 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.

smitty-1088185 SSCrazy Points: 2321 More actions · Answer 9

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

shaktibangerwa Grasshopper Points: 15 More actions · Answer 10

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

BLOB EATER SSChampion Points: 14609 More actions · Answer 11

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