This is something that's bugged me though has never caused any real issues as I can generally locate this information by other means, but can someone explain why NULL
may be returned in the dbid
, objectid
, and number
columns from the sys.dm_exec_sql_text
DMV, yet yield results in the text
column? I understand why output would return inversely (e.g. all columns but text
would yield data), but I've seen this behavior a number of times across a number of SQL Server versions and the documentation doesn't seem to imply this should be possible, or I'm just reading it wrong.
Here's an example of the query I'm running:
SELECT TOP 100
t.*
, s.*
, c.*
FROM sys.dm_exec_query_stats s
LEFT JOIN sys.dm_exec_connections c
ON c.most_recent_sql_handle = s.sql_handle
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) t
WHERE s.execution_count > 1 AND DATEDIFF (second, creation_time, GETDATE()) > 0
AND t.dbid IS NULL
Here's a sample of one of the results from the text
column which implies this isn't related to temporary objects which is what I would normally guess to be the cause.
select * from [dbo].[Map_ProviderSpecialty]
What situation is occurring where these columns are returning NULL
values?
Best Answer
This is by design as of now. There is a connect item about this problem which is closed as of now.
sys.dm_exec_query_stats DBID column NULL for dynamic SQL - by Theo Ekelmans
But at the end there is a comment:
There is also some workaround in the comment section and also as mentioned by @sp_BiltzErik in the comment above.