Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Scary DBA

I have twenty+ years experience in IT. That time was spent in technical support, development and database administration. I work forRed Gate Software as a Product Evangelist. I write articles for publication at SQL Server Central, Simple-Talk, PASS Book Reviews and SQL Server Standard. I have published two books, ”Understanding SQL Server Execution Plans” and “SQL Server 2008 Query Performance Tuning Distilled.” I’m one of the founding officers of the Southern New England SQL Server Users Group and its current president. I also work on part-time, short-term, off-site consulting contracts. In 2009 and 2010 I was awarded as a Microsoft SQL Server MVP. In the past I’ve been called rough, intimidating and scary. To which I usually reply, “Good.” You can contact me through grant -at- scarydba dot kom (unobfuscate as necessary).

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:

[sourcecode language="sql"]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[/sourcecode]

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.

Comments

Posted by Steve Jones on 23 May 2011

Looking forward to seeing you tune a few queries on Azure.

Leave a Comment

Please register or log in to leave a comment.