In SQL server 2008 you have a lot of DMV:s to use to tune your database, but SQL Azure is missing a lot of them, and it makes it a lot harder to tune your queries. One of the DMV:s I like a lot is sys.dm_os_wait_stats, but this DMV doesn’t exist in SQL Azure (yet). Today I’ll give you a small T-SQL script to show what you are waiting for right now. It is not a real substitute for the sys.dm_os_wait_stats, but you can at least see what you are waiting for right now. SELECT r.wait_type ,r.wait_time ,SUBSTRING(qt.text,r.statement_start_offset/2,( CASE WHEN r.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE r.statement_end_offset END -r.statement_start_offset)/2) AS query_text ,qt.dbid, dbname=DB_NAME(qt.dbid) ,qt.objectid ,r.sql_handle ,r.plan_handle FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS qt WHERE r.session_id > 50 AND WAIT_TYPE IS NOT NULL
If any query is wating for anything it will be returned and you can see what it’s waiting for. The query returned may be a good candidate for tuning, but the root cause can be a completely different query.