I have a query to retrieve the Top 10 queries based on total logical reads from the dm_exec_query_stats DMV.
SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.TEXT) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1), db_name(qt.dbid) as db_name, qs.execution_count, qs.total_logical_reads, qs.total_worker_time, qs.total_elapsed_time/1000000 total_elapsed_time_in_S, SUBSTRING(CONVERT(varchar(19),qs.last_execution_time),1,19) FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp ORDER BY qs.total_logical_reads DESC
The query returns all of the information requested, except for the name of the database that the query came from or was directed at. Whether I use dm_exec_sql_text or dm_exec_query_plan the result is the same.
db_name(qt.dbid) as db_name dm_exec_sql_text
db_name(qp.dbid) as db_name dm_exec_query_plan
Both return NULL or tempdb as the database name.
The same problem occurs when selecting Reports -> Performance Top Queries by Average IO.
The database name is empty.
If I add the query plan to the query however, and then open up the query plan in SSMS, I can see the name of the originating database by hovering over the various Index Seeks, Scans or RID lookups.
I have noticed that there are several databases referenced in the query plan, such as mssqlsystemresource along with the trackit database
If the query plan was able to display the name of the database or databases affected by the query in my top 10 list, then it stands to reason that I should be able to obtain the names of those databases using a DMV.
My Question is: How can I modify the top 10 query to retrieve the name of the database for each query.
Or is there a better way to go about getting the top 10 queries by CPU/IO/Memory usage and get the database name or names for each of the top 10