Server 2008 R2 DMV问题

Question

我有一个查询可以根据dm_exec_query_stats DMV的全部逻辑读取来检索前10位查询。

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

查询返回所请求的所有信息，但查询来自或指向的数据库名称除外。无论我使用dm_exec_sql_text还是dm_exec_query_plan，结果都是一样的。

db_name(qt.dbid)为db_name dm_exec_sql_text

或

db_name(qp.dbid)为db_name dm_exec_query_plan

两者都返回NULL或tempdb作为数据库名。

在按平均IO选择Reports -> Performance查询时也会出现同样的问题。

数据库名称为空。

但是，如果我将查询计划添加到查询中，然后在SSMS中打开查询计划，则可以在各种索引查找、扫描或删除查找时看到原始数据库的名称。

我注意到查询计划中引用了几个数据库，例如mssqlsystemresource资源以及trackit数据库

如果查询计划能够在前10位列表中显示受查询影响的数据库的名称，那么我应该能够使用DMV获得这些数据库的名称，这是合理的。

如何修改前10个查询以检索每个查询的数据库名称？

或者，是否有更好的方法来获取CPU/IO/内存使用量前10位的查询，并为前10位的每个查询获取数据库名或名称？

Answer 1

use dbx;
select foo
from db1.dbo.table
join db2.dbo.table on condition
where some_function();

这个查询消耗了大量的CPU，并请求提供大量内存。在哪个数据库里？你想要的信息根本不作为一个概念存在。作为一个有洞察力和后知后觉的人，你可能能够解释为什么75%的CPU是db1的，15%的是db2的。但最终您只需将查询分配给数据库。某些查询(好的，大多数)是100%包含在db中这一事实并不意味着所有查询资源都可以确定地分配给db。

然而，对于实际的方法来说，完全自动化您在文章中所做的工作是相对简单的:检查计划和标识所有物理接入运营商的位置，并使用这些信息将查询资源分配给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)