DMF sys.dm_exec_sql_text不显示DBID

本文介绍了DMF sys.dm_exec_sql_text不显示DBID的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我从

SELECT s2.dbid, 
    s1.sql_handle,  
    (SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 , 
      ( (CASE WHEN statement_end_offset = -1 
         THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2) 
         ELSE statement_end_offset END)  - statement_start_offset) / 2+1))  AS sql_statement,
    execution_count, 
    plan_generation_num, 
    last_execution_time,   
    total_worker_time, 
    last_worker_time, 
    min_worker_time, 
    max_worker_time,
    total_physical_reads, 
    last_physical_reads, 
    min_physical_reads,  
    max_physical_reads,  
    total_logical_writes, 
    last_logical_writes, 
    min_logical_writes, 
    max_logical_writes  
FROM sys.dm_exec_query_stats AS s1 
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2  
WHERE s2.objectid is null 
ORDER BY s1.sql_handle, s1.statement_start_offset, s1.statement_end_offset;

当我执行它时，dbid字段返回为空.为什么这样表现呢?我想限制来自一个数据库的查询，但它似乎不起作用.

When i execute it the dbid field is returned as null.Why this behave like this?I want to limit queries from one database but it seems not working.

非常感谢您的回答.

推荐答案

1)此行为在SQL2005-> SQL2008R2中可用.

1) This behavior is available in SQL2005 -> SQL2008R2.

2)为什么sys.dm_exec_sql_text.dbid具有(有时)NULL?

2) Why sys.dm_exec_sql_text.dbid has (sometimes) NULLs ?

在SQL2005中-> SQL2008R2 dbid为NULL，用于临时和准备好的SQL语句"(有关 SQL Server 2008 R2 ).
在SQL 2012中对于即席和准备好的SQL语句，是在其中编译语句的数据库的ID"(请参见 MSDN ).因此，从SQL2012 dbid开始将返回一个非NULL值，包括"ad hoc和prepared SQL语句".

Within SQL2005 -> SQL2008R2 dbid is NULL "for ad hoc and prepared SQL statements" (see MSDN for SQL Server 2008 R2).
Within SQL 2012 "For ad hoc and prepared SQL statements, the ID of the database where the statements were compiled" (see MSDN). So, starting from SQL2012 dbid will return a non-NULL value including "ad hoc and prepared SQL statements".

3)为了解决SQL2008中的此问题-> SQL2008R2，我使用了sys.dm_exec_plan_attributes(请参阅 MSDN )

3) To solve this problem in SQL2008 -> SQL2008R2 I used sys.dm_exec_plan_attributes (see MSDN)

SELECT ..., ISNULL(s2.dbid,CONVERT(SMALLINT,att.value)) AS my_dbid, ...
FROM sys.dm_exec_query_stats AS s1 
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 
CROSS APPLY sys.dm_exec_plan_attributes(s1.plan_handle) att
WHERE att.attribute='dbid

这篇关于DMF sys.dm_exec_sql_text不显示DBID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

dbId