IBM Support

"MS SQL Expensive Query Plans" attribute group causing SQL Server agent collector to hang

Technical Blog Post


Abstract

"MS SQL Expensive Query Plans" attribute group causing SQL Server agent collector to hang

Body

Starting from SQL Server agent 6.31 FP11 (for APM v8.1.x infrastructure), a new attribute group is available: MS SQL Expensive Query Plans.
This attribute group provides the top 10 cached query plans according to the performance statistics in the SQL server.
So to make story short, it can be helpful to know the performances for specific SQL queries and possibly optimize them.

Anyway, on a small subset of SQL Server environments we observed it may cause delays or hangs on the ITCAM SQL Server agent collector.
Likely it depends on SQL Server engine workload or something else that prevents the collector from reading the query plan cache in a timely fashion.

The symptoms from user perspective looks like many other similar issues that occur at collection time : basically no one of the agent widgets
display valid data: all of them show: "no data available" or are empty.

The failing agent does not send the expected metrics to APM because it does not receive data from the collector.
The very first record it should send belongs to dataset called KOQADBSU.
If you open the SQL Server agent collector log file (having name <hostname>_OQ_<instance>_col.out, you can find it into <itmhome>\tmaitm6_x64\logs),
you can notice that in a working agent, this is correctly collected and sent, while it is not sent for a failing agent.
Looking at the point where the collector should gather data for it, you seed that the subcursor is not invoked at all, because collector thread is hanging on another subcursor, called KOQEXPQP.

The collector basically invokes cursor KOQEXPQP

CTS0200T (2018-07-17 18:44:29) (18600)started collection for cursor KOQEXPQP

..

but this cursor never exits, never completes.
So the cursors for data that should have been collected later, including KOQADBSU, are never executed.

 
KOQEXPQP is the cursor for attribute group "MS SQL Expensive Query Plans" data set.
If you are facing above symptoms, it is possible that in your SQL Server, this cursor is taking long time to complete and blocking the others cursors.

If this data set is not used, you can exclude it by re-configuring the agent from Template (right click Template entry-->Configure agent), select Edit for the failing instance and put the value KOQEXPQP in field "Extended Parms".


Then click OK and start the agent instance again.

In this way the cursor is disabled and if there are no other cursors causing delays, you will see data in your APM dashboards for this SQL Server node.

If you instead needs data from attribute group "MS SQL Expensive Query Plans", I suggest you to open a case to IBM Support so that they can provide additional assistance to fix
the problem with cursor KOQEXPQP.

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

ibm11085205