I have a query to retrieve the Top 10 queries based on total logical reads from the dm_exec_query_stats DMV.

    SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
    ((CASE qs.statement_end_offset
    ELSE qs.statement_end_offset
    END - qs.statement_start_offset)/2)+1),
db_name(qt.dbid) as db_name,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
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.

enter image description here

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.

enter image description here

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?

1 Answer 1

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)
  • Very nice, that was what I was looking for. Thank you for the quick assist. Commented Dec 10, 2012 at 7:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.