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.