March Madness – SQL Azure – Wait Events

Yesterday we discussed one way to improve query performance in SQL Azure by making sure your statistics are kept up to date. In today’s penultimate post we look at another way to get information about your queries in SQL Azure: wait events.

One of the first things I noticed that was missing from SQL Azure was the presences of a similar view for sys.dm_os_waiting_tasks. This was disappointing at first but then I remembered that I could find wait details in system views that we have already seen. That means I can use the following query to gather details about the currently requests and what they are waiting for:

SELECT SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
((CASE QS.statement_end_offset
WHEN -1 THEN DATALENGTH(ST.text)
ELSE QS.statement_end_offset
END - QS.statement_start_offset)/2) + 1) AS statement_text, QS.*
FROM sys.dm_exec_requests QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) ST
WHERE QS.session_id > 50
AND QS.session_id <> @@SPID

I decided to see what results I would get for wait types while running an batch of insert statements into my SQL Azure database. I created a small table to store the results and ran the above statement once a second to track the different wait events. Most of the events seemed familiar like a PAGEIOLATCH_XX, or WRITELOG. But the following three wait events I found to be interesting:

• SE_REPL_COMMIT_ACK
• ASYNC_TRANSPORT_STREAM
• CMEMTHREAD

The CMEMTHREAD wait event is found in on-premise versions of SQL Server, but it is not very well documented. The short answer here is that you are waiting on memory, and one of the reasons why is explained here by Gail Shaw (blog | @SQLintheWild). The SE_REPL_COMMIT_ACK wait is not documented at all and the only reference I found to it was in this MSDN forum. Essentially it is a SQL Azure specific wait event that has to do with ensuring you have one primary and two secondary copies of your data. The ASYNC_TRANSPORT_STREAM does not seem to be documented at all.

Here is why this information is useful to you. If the wait events were all common ones, say LATCH_XX, PAGEIOLATCH_XX, or even WRITELOG then you are likely to be able to take action on reducing contention by making some different choices regarding database design, or indexes, or even the way you have written your queries. Some wait events you are likely not going to be able to fix yourself, such as SE_REPL_COMMIT_ACK.

I like knowing what my queries are waiting for, because it also helps me to understand who is likely to be responsible for making things go faster.

Tomorrow we wrap up this series.