Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

or

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.

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

share|improve this question

1 Answer 1

up vote 6 down vote accepted
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)
share|improve this answer
    
Very nice, that was what I was looking for. Thank you for the quick assist. –  Craig Efrein Dec 10 '12 at 7:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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