Earlier today I read a blog post detailing how to find your busiest database by querying sys.dm_exec_query_stats.  The blog post suggested you use the dbid column from sys.dm_exec_sql_text.  There is a problem with this.  The dbid is NULL for anything that is not a stored procedure.

This is what was suggested in the blog post (more or less):
select sum(qs.total_elapsed_time) total_elapsed_time,
sum(qs.total_worker_time) total_worker_time,
db_name(st.dbid) as db_name
from
sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) st
group by dbid
order by sum(qs.total_elapsed_time) desc


Here is the result of running this:

Why the NULL db name? Adhoc SQL has its dbid set to NULL in sys.dm_exec_sql_text.

This is because a sql handle is generated by a hash of the sql text for an adhoc query (this differs from stored procedures). As a result a dbid can’t be determined.  Here’s an example where I selected a query hash from sys.dm_exec_query_stats that I knew was for an adhoc query and joined it to sys.dm_exec_sql_text.

select st.* from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) st
where query_hash = 0X5BB668E8DE955D8B

And the output of it:

So how do we determine what the database id associated with an adhoc query is? We use sys.dm_exec_plan_attributes.  The execution plan handle varies by the database it compiled in. As a result the plan_handle can be used to determine where adhoc queries as well as stored procedures executed.

To find the database id associated with a plan handle, we need to look for the attribute name of dbid.  This is how we retrieve the dbid for a given plan handle.

If we take this into account and join against sys.dm_exec_query_stats we get the following:
select sum(qs.total_elapsed_time) total_elapsed_time,
sum(qs.total_worker_time) total_worker_time,
db_name(CONVERT(SMALLINT, dep.value)) db_name, dep.value from
sys.dm_exec_query_stats qs
cross apply sys.dm_exec_plan_attributes(qs.plan_handle) dep
where dep.attribute = N'dbid'
group by dep.value
order by sum(qs.total_elapsed_time) desc

The results of this are below.  The total_elapsed_time and total_worker_time are in microseconds.  Also – total_worker_time is cpu time.  You can modify the order by of the query above if you want to look for the database with the most cpu time.

Caveats

There’s a couple big caveats with this.  As you’re probably aware, sys.dm_exec_query_stats is only as good as the amount of time an entry has been cached in it.  You can see the date/time the query first cached, but you have no idea of when total_elapsed_time or total_worker_time accumulated.

There’s another big caveat this this.  If a query continually times out, it will never register an entry in sys.dm_exec_query_stats.  So if you have a really sick system you might not see the effects of it in sys.dm_exec_query_stats.

Conclusion

Using this approach to find your heaviest queries will work to some extent.  You should just be aware of some of the blind spots involved in this approach.   If performance is critical to you I would suggest a good performance monitoring tool rather than relying on this approach.  As usual beware of the advice you get on the internet.  The blog which posted the inaccurate info is actually fairly widely followed.

 

Watch us diagnose a query timeout with SQLGrease:

See how you can get query level wait events without Query Store:

Free you say?  How can that be?