Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

I have an SQL Server 2008 R2 server. I open "Activity Monitor", and inside the "Resource Waits" category, I see that the "Logging" category is taking up more than 1400 ms/sec.

That doesn't seem to be normal, what does that mean and how do I diagnose the cause?

UPDATE:

I ran this query, suggested by Shark: 

select * from sys.dm_os_wait_stats order by wait_time_ms desc

and got these results:

wait_type                         waiting_tasks_count  wait_time_ms         max_wait_time_ms     signal_wait_time_ms
--------------------------------- ------------------- -------------------- --------------------- ---------------- 
LCK_M_IX                          3662                 102060386            31410                233
LAZYWRITER_SLEEP                  741527               63315760             305163               28805
WRITELOG                          3531635              42147142             4142                 309360
REQUEST_FOR_DEADLOCK_SEARCH       6327                 31676553             5038                 31676553
share|improve this question
2  
To get a more granular picture of your waits, run this query: select * from sys.dm_os_wait_stats order by wait_time_ms desc. Each of those wait types have a direct meaning: compare them to this BOL reference (msdn.microsoft.com/en-us/library/ms179984.aspx). – Thomas Stringer Mar 19 '12 at 18:17
1  
Is it in line with what you have seen before (or is this the first time)? At the risk of stating the obvious normal/abnormal are comparatives (usually) against an established baseline. – jl01 Mar 19 '12 at 18:34
@Shark: I ran the query you suggested, you can see the top results in the original question. According to the documentation, this lock "Occurs when a task is waiting to acquire an Intent Exclusive (IX) lock". – Dor Rotman Mar 20 '12 at 12:31
You may want to run perfmon to analyze the physical disk queue length. It looks like you may be bottlenecking when writing from the log cache to disk (the WRITELOG wait type). – Thomas Stringer Mar 20 '12 at 12:32
@jl01: This is first time my system is running under load, and I'm trying to understand if there is an SQL bottleneck. – Dor Rotman Mar 20 '12 at 12:32
show 5 more comments

1 Answer

up vote 2 down vote

I would check disk health / performance for volumes holding your transaction logs. You are seeing a delay due to the durability requirement, logging is backed up.

Check for I/O delays on tlog files in activity monitor as well.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.