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.
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?
use dbx; select foo from db1.dbo.table join db2.dbo.table on condition where some_function();
This query consumed lots of CPU and requested a large memory grant. In which database? The information you want simply doesn't exist as a concept. As a human with insight knowledge and with hindsight benefit, you probably would be able to explain why 75% of CPU is due to db1 and 15% is due to db2. But ultimately you just can assign queries to a database. The fact that some (ok, most) queries are 100% contained inside a db does not mean that all query resources can be assigned deterministically to a db.
However, for practical means is relatively simple to automate exactly what you did in your post: inspect the plans and identify all physical access operators locations and use this info to assign the query resources to a DB.
with xmlnamespaces (default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') select x.value(N'@NodeId',N'int') as NodeId , x.value(N'@PhysicalOp', N'sysname') as PhysicalOp , x.value(N'@LogicalOp', N'sysname') as LogicalOp , ox.value(N'@Database',N'sysname') as [Database] , ox.value(N'@Schema',N'sysname') as [Schema] , ox.value(N'@Table',N'sysname') as [Table] , ox.value(N'@Index',N'sysname') as [Index] , ox.value(N'@IndexKind',N'sysname') as [IndexKind] , x.value(N'@EstimateRows', N'float') as EstimateRows , x.value(N'@EstimateIO', N'float') as EstimateIO , x.value(N'@EstimateCPU', N'float') as EstimateCPU , x.value(N'@AvgRowSize', N'float') as AvgRowSize , x.value(N'@TableCardinality', N'float') as TableCardinality , x.value(N'@EstimatedTotalSubtreeCost', N'float') as EstimatedTotalSubtreeCost , x.value(N'@Parallel', N'tinyint') as DOP , x.value(N'@EstimateRebinds', N'float') as EstimateRebinds , x.value(N'@EstimateRewinds', N'float') as EstimateRewinds , st.* , pl.query_plan from sys.dm_exec_query_stats as st cross apply sys.dm_exec_query_plan (st.plan_handle) as pl cross apply pl.query_plan.nodes('//RelOp[./*/Object/@Database]') as op(x) cross apply op.x.nodes('./*/Object') as ob(ox)