Log Writer

Question

Post reply

Log Writer

paul_johnson

Old Hand

Points: 371
More actions
September 1, 2010 at 1:42 am

#239795

Hi, I'm covering a DBA role by default and would really appreciate some help with a third level technical issue.

One of the sql servers I'm managing is encountering performanc issues. When running sp_who2 i note that the log writer is taking up 3011359 instances of CPU time.

Could you tell me what role the log writer performs, and shoud the CPU level be so high.

Many thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Big Data Man SSCrazy Eights Points: 8536 More actions · Answer 1

Big Data Man

SSCrazy Eights

Points: 8536

September 1, 2010 at 3:22 am

#1214804

The Log Writer is responsible for writing to the transaction log file.

Can you provide more details regarding the performance issue? What behavior are users experiencing?

Have you tried monitoring using perfmon counters from a seperate server to check CPU, memory and storage performance?

Thank you,

Phillip Cox

paul_johnson Old Hand Points: 371 More actions · Answer 2

AWE is enabled and there is 8gb of memory of the server. Physical memory usage is continually running at 97%. The server is a busy one with @ 100 users processing transactions. The data and transaction log files are on the same drives. CPU usuage is around 25%.

I am currently capturing %Processor Time, Memory\Pages Sec, Physical disk Disk Time, Queue length,Disk Read Writes.

Big Data Man SSCrazy Eights Points: 8536 More actions · Answer 3

paul_johnson (9/1/2010)

AWE is enabled and there is 8gb of memory of the server. Physical memory usage is continually running at 97%. The server is a busy one with @ 100 users processing transactions. The data and transaction log files are on the same drives. CPU usuage is around 25%.

I am currently capturing %Processor Time, Memory\Pages Sec, Physical disk Disk Time, Queue length,Disk Read Writes.

Hi Paul,

OK, based on this inital information, my first red flag is the co-location of your data and transaction log files on same volume. In terms of AWE, what is your max memory configured, as SQL Server will not page this memory. CPU seems fine, thus other performance counters will help to nail-down possible bottleneck. How is your storage configured (e.g. local or SAN attached) and what are your RAID levels?

How did the performance issue come to light (e.g. user complaints)?

Thanks,

Phillip Cox

paul_johnson Old Hand Points: 371 More actions · Answer 4

AWE is enabled and there is 8gb of memory of the server. Physical memory usage is continually running at 97%. The server is a busy one with @ 100 users processing transactions. The data and transaction log files are on the same drives. CPU usuage is around 25%.

I am currently capturing %Processor Time, Memory\Pages Sec, Physical disk Disk Time, Queue length,Disk Read Writes.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 5

paul_johnson (9/1/2010)

When running sp_who2 i note that the log writer is taking up 3011359 instances of CPU time.

Is that high? How long has the instance been running? That CPU count is cumultive since the process was created (which for system processes is since SQL started)

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