Time Trial sys.dm_exec_query_plan vs sys.dm_exec_sql_text

3

November 12, 2012 by Kenneth Fisher

I’ve been working on a way to test performance usage on a server by database. I started out using sys.dm_exec_query_stats since it has every statistic I wanted. Unfortunately it doesn’t contain the database id which makes it rather difficult to split the data out by database. This lead me to cross applying sys.dm_exec_query_plan, which does contain dbid. This worked great! Well, it worked great in test. Imagine my frustration when I pushed everything to production and found that a query that ran in seconds in test, took almost an hour in production. I mean I knew that sys.dm_exec_query_plan was slow, but really. The problem is that my test server only has 4-5000 rows in sys.dm_exec_query_stats, while production has 26000+. This led me to try sys.dm_exec_sql_text. It seemed to work better so I decided to run a time trial to confirm what I was seeing without the rest of my query.

 set statistics io on
set statistics time on

select top 1000 *
from sys.dm_exec_query_stats
cross apply sys.dm_exec_sql_text(sql_handle)

select top 1000 *
from sys.dm_exec_query_stats
cross apply sys.dm_exec_query_plan(plan_handle)

I used the top 1000 because I don’t really have an hour to wait for one of the queries to complete. I’m old enough as it is. 1000 rows seemed like enough of a sample.

Here are the results from two different servers:

Server 1
sys.dm_exec_sql_text

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 3 ms.

(1000 row(s) affected)

SQL Server Execution Times:
CPU time = 218 ms, elapsed time = 83024 ms.

Sys.dm_exec_query_plan

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 2 ms.

(1000 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 2545, physical reads 0, read-ahead reads 0, lob logical reads 1282048, lob physical reads 0, lob read-ahead reads 136372.

SQL Server Execution Times:
CPU time = 70641 ms, elapsed time = 540426 ms.

Server 2
sys.dm_exec_sql_text

 SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

(1000 row(s) affected)

SQL Server Execution Times:
CPU time = 47 ms, elapsed time = 543 ms.

Sys.dm_exec_query_plan

 SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 26 ms.

(1000 row(s) affected)

SQL Server Execution Times:
CPU time = 8391 ms, elapsed time = 11028 ms. 

So that has sys.dm_exec_query_plan at 6-20 times slower. Since the only difference in the output is the sql text vs the query plan and all I want is the dbid I think my solution will have to use sys.dm_exec_sql_text.

About these ads

3 thoughts on “Time Trial sys.dm_exec_query_plan vs sys.dm_exec_sql_text

  1. Ian Stirk says:

    Hi,

    Very nice article.

    You can get the database id from the below query:

    select top 1000 *
    from sys.dm_exec_query_stats qs
    cross apply sys.dm_exec_sql_text(sql_handle)
    OUTER APPLY sys.dm_exec_plan_attributes(qs.plan_handle) pa
    WHERE pa.attribute = ‘dbid’

    Readers can discover a lot more about improving SQL performance via DMVs in this recently published book “SQL Server DMVs in Action” http://www.manning.com/stirk. It contains more than 100 scripts to identify problems, and offers a wide range of solutions. (there are also various test harnesses for doing something similar to what you are doing!)

    Chapters 1 and 3 can be downloaded for free. Chapter 1 includes scripts for:

    A simple monitor

    Finding your slowest queries

    Find your missing indexes

    Identifying what SQL is running now

    Quickly find a cached plan

    Thanks

    Ian

    • Absolutely fabulous DMV. I really appreciate you letting me know about it. It’s not only faster but more accurate than what I was looking at. It’s truly amazing how much information is available using them. I’m going to have to talk to my boss about paying for a copy of your book 

      One question. Is there any reason to use an OUTER APPLY? As best I can tell it looks like there will always be a dbid attribute available let alone any attribute. I’m particularly interested since I wrote an article on outer joins here: Fun with Outer Joins and using a WHERE clause to reference an OUTER joined/applied table tends to stand out to me now.

      Thanks again!
      Kenneth

  2. Ian Stirk says:

    Hi Kenneth,

    I’m glad you liked the code. And yes it really is amazing how much internals info there is available with DMVs.

    You might be right about the OUTER APPLY, I just wanted to ensure I captured everything, even if the dbid is missing.

    Enjoy
    Ian

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 428 other followers

%d bloggers like this: