1

From reading, I can see the Work_Queue wait can safely be ignored, but I don't find much about logcapture_wait. This is from BOL, "Waiting for log records to become available. Can occur either when waiting for new log records to be generated by connections or for I/O completion when reading log not in the cache. This is an expected wait if the log scan is caught up to the end of log or is reading from disk."

Average disk sec/write is basically 0 for both SQL Servers so I'm guessing this wait type can safely be ignored?

Here are the top 10 waits from the primary:

wait_type   pct running_pct
HADR_LOGCAPTURE_WAIT    45.98   45.98
HADR_WORK_QUEUE 44.89   90.87
HADR_NOTIFICATION_DEQUEUE   1.53    92.40
BROKER_TRANSMITTER  1.53    93.93
CXPACKET    1.42    95.35
REDO_THREAD_PENDING_WORK    1.36    96.71
HADR_CLUSAPI_CALL   0.78    97.49
HADR_TIMER_TASK 0.77    98.26
PAGEIOLATCH_SH  0.66    98.92
OLEDB   0.53    99.45

Here are the top 10 waits from the secondary:

wait_type   pct running_pct
REDO_THREAD_PENDING_WORK    66.43   66.43
HADR_WORK_QUEUE 31.06   97.49
BROKER_TRANSMITTER  0.79    98.28
HADR_NOTIFICATION_DEQUEUE   0.79    99.07
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Skeeboe
  • 21
  • 1
  • 3

2 Answers2

2

Don't troubleshoot problems on your server by looking at total waits. If you want to troubleshoot what is causing you problems, then you need to look at current waits. You can do that by either querying sys.dm_os_waiting_tasks or by grabbing all waits (like you did above), waiting for 1 minute, grabbing all waits again, and subtracting them to see what waits actually occurred over that minute.

See the webcast I did for more info: Troubleshooting with DMVs

That aside, HADR_LOGCAPTURE_WAIT is a background wait type and does not affect any running queries. You can ignore it.

Robert L Davis
  • 476
  • 3
  • 7
  • We use quest foglight for sql to monitor performance. We had just hooked it to our new AlwaysOn 2012 servers. In a recent update they excluded HADR_Work_Queue and others but not the log wait one. I was pretty sure it could be ignored, just not positive and not sure why they didn't exclude it as well. – Skeeboe Aug 04 '13 at 13:27
  • I also just noticed where you work, maybe I should have tried ignite free, huh? – Skeeboe Aug 04 '13 at 13:30
0

No, you can't just simply ignore " HADR_LOGCAPTURE_WAIT". this wait type happens when SQL is either waiting for some new log data to be generated or when there are some latency while trying to read data from the log file. internal and external fragmentation on the log file or slow storage could contribute to this wait type as well.

Pooyan
  • 1