Technical Blog Post
Abstract
Database column for Expensive Queries widget shows "No_Data"
Body
SQL Server Agent delivered with APM 8.1.4 introduced a new attribute group called "Expensive Query Plan.
The metrics for this attribute group are showed in the widget "Expensive Queries".
If you used this widget at least once, you may have noticed that for most of the rows, the column "Database" shows "No_Data".
This is not a problem with agent data collection.
The value nodata is returned directly by the execution of the binary "koqsql 59" for cursor koqexpqp.
The query that the binary runs to extract the wanted information is similar to this one (it is not exactly the same, but the function used are the same).
--------------------
SELECT TOP 5 query_stats.query_hash AS "Query Hash",
SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",
MIN(query_stats.statement_text) AS "Statement Text",
MAX(query_stats.sql_handle)AS "SQL Handle"
FROM
(SELECT QS.*,
SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(ST.text)
ELSE QS.statement_end_offset END
- QS.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats
GROUP BY query_stats.query_hash
ORDER BY 2 DESC;
--------------------
The above query is a sample taken from Microsoft manual that describes function sys.dm_exec_query_stats.
From the output, copy one of the "SQL Handle" and use it with the query below:
select st.dbid, DB_NAME(st.dbid) from sys.dm_exec_sql_text(<sql handle>) as st
where <sql_handle> is one of the Query handles I found from the previous output.
You will see that both dbid and db_name are null for all the query handles.
You may get valid values (DBID and DB NAME) only for queries belonging to database MSDB, if any.
The issue is not with agent code.
This is a limit of SQL Server itself, it is also documented here:
Basically, for adHoc queries, SQL Server does not return dbid because for ad hoc queries, the SQL handles are hash values
based on the SQL text being submitted to the server, and can originate from any database.
For database objects such as stored procedures, triggers or functions, the SQL handles are derived from the
database ID, object ID, and object number.
So basically the query used by cursor KOQEXPQP would populate dbid and dbname only in case the sql query comes from a static stored procedure.
This is working as designed from agent perspective as the limitation is on SQL Server side.
Hope it helps
Subscribe and follow us for all the latest information directly on your social feeds:
|
|
|
Check out all our other posts and updates: | |
Academy Blogs: | https://goo.gl/U7cYYY |
Academy Videos: | https://goo.gl/TLfMoF |
Academy Google+: | https://goo.gl/HnTs0w |
Academy Twitter : | https://goo.gl/AhR8CL |
UID
ibm11085241