March Madness – SQL Azure – sys.dm_exec_query_plan

Yesterday we saw how to use the sys.dm_exec_sql_text table function to return the statement text for the connections or requests that are hitting our instance of SQL Azure. Today we take a step further along that path by examining the use of the sys.dm_exec_query_plan function. Unlike the sys.dm_exec_sql_text function (which can take either a SQL handle or a plan handle) the sys.dm_exec_query_plan function can only have a plan handle as an input. That means we need to use other system views that have an available plan handle. One such view we discussed was the sys.dm_exec_requests:

SELECT *
FROM sys.dm_exec_requests

Running that we see the column named plan_handle, which we can then use in a CROSS APPLY as follows:

SELECT *
FROM sys.dm_exec_requests er
CROSS APPLY sys.dm_exec_query_plan(er.plan_handle)

And we could get really fancy by combining some of our previous statements into this one:

SELECT *
FROM sys.dm_exec_requests er
INNER JOIN sys.dm_exec_sessions es
 ON er.session_id = es.session_id
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)
CROSS APPLY sys.dm_exec_query_plan(er.plan_handle)

We can use the above statement to quickly see the text of the statements being run and also view the query plans for those statements. We can also see this same information from within the SQL Azure Management Portal. The Management Portal provided near real-time details on the statements currently running against your SQL Azure instance, including details on query plans. If I select one statement in the portal I am taken to a query details screen, and from there I can click on a link that says ‘Query Plan’ and will be shown the following:

I know what you are thinking…you want to know what happens if I click on that metro ‘100% Clustered Index Scan’. Here’s a sneak peek for you: 

From what I can tell the folks on the SQL Azure team are doing their metro-best to get the portal to display the pertinent performance details. Every time I poke around in the Management Portal I seem to find something new and interesting.

So we have two ways to go and get the details for our query plans in SQL Azure. We can use the sys.dm_exec_query_plan function combined with some system views, or we can view the query plans through the Management Portal directly.

Tomorrow we will look at some other interesting details for your SQL Azure queries.