SQL Azure Query Tuning

SQL Azure is still SQL Server at the end of the day. This means it is entirely possible to write queries against SQL Azure that really… what’s a good word… stink. So what do you do? It’s all in the cloud. You couldn’t possibly tune the queries, right? Wrong. Many of the same tools that you have available to you, such as execution plans and dynamic management objects, are still available in SQL Azure.

Let’s talk DMOs for a second. First off, don’t make the mistake I did of trying to run these outside the context of a specific database on SQL Azure. You’ll get extremely inconsistent results, trust me on this. Anyway, I did a quick run-down on some of the most used DMOs for performance tuning, the sys.dm_exec_* set. Here’s a complete listing of those procs and whether or not they’re available to you in SQL Azure:

SELECT  *
FROM    sys.dm_exec_requests AS der
--available

SELECT  *
FROM    sys.dm_exec_requests AS der
        CROSS APPLY sys.dm_exec_query_plan(der.plan_handle)
--available

SELECT  *
FROM    sys.dm_exec_requests der
        CROSS APPLY sys.dm_exec_sql_text(der.sql_handle)
--available

SELECT  *
FROM    sys.dm_exec_query_stats AS deqs
--available

SELECT  *
FROM    sys.dm_exec_cached_plans AS decp
--invalid object

SELECT  *
FROM    sys.dm_exec_connections AS dec
--available

SELECT  *
FROM    sys.dm_exec_cursors AS dec
--invalid object

SELECT  *
FROM    sys.dm_exec_requests AS der
        CROSS APPLY sys.dm_exec_plan_attributes(der.plan_handle) AS depa
--invalid object

SELECT  *
FROM    sys.dm_exec_procedure_stats AS deps
--invalid object

SELECT  *
FROM    sys.dm_exec_query_memory_grants AS deqmg
--invalid object

SELECT  *
FROM    sys.dm_exec_query_optimizer_info AS deqoi
--invalid object

SELECT  *
FROM    sys.dm_exec_query_resource_semaphores AS deqrs
--invalid object

SELECT  *
FROM    sys.dm_exec_sessions AS des
--available

SELECT  *
FROM    sys.dm_exec_requests AS der
        CROSS APPLY sys.dm_exec_text_query_plan(der.plan_handle, 0, -1) AS detqp
--available

SELECT  *
FROM    sys.dm_exec_trigger_stats AS dets
--invalid object

SELECT  *
FROM    sys.dm_exec_xml_handles(@@SPID)
--invalid object

The good news, most everything you need is available so you’re really going to be able to go to town on using DMOs as part of your query tuning. The bad news, “most everything” doesn’t include sys.dm_exec_query_optimizer_info is not on the list. This does take away a tool. It’s not a vital tool, but it’s one that allows you to understand some of what’s happening on the system. I’m not going to cry and rend my clothing because I don’t have it, but I will be somewhat disappointed.

This is great news! The tools you’re learning and using (and you are using DMOs, right?) will continue to be available in SQL Azure.