IBM Support

Database column for Expensive Queries widget shows "No_Data"

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:

https://feedback.azure.com/forums/908035-sql-server/suggestions/32899729-sys-dm-exec-sql-text-dbid-is-not-always-populated

 

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

 

 

Tutorials Point

 

Subscribe and follow us for all the latest information directly on your social feeds:

 

 

image

 

image

 

image

 

 

  

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


image

[{"Business Unit":{"code":"BU004","label":"Hybrid Cloud"},"Product":{"code":"","label":""},"Component":"","Platform":[{"code":"","label":""}],"Version":"","Edition":""}]

UID

ibm11085241