System Performances impact on SQL Server Agent Database Detail collection

Body

ITCAM for Microsoft Application SQL Server Agent uses a set of "cursors", generally associated to specific
attribute groups, to perform data collections.
These cursors can be triggered on demand by TEMA requests or can be interval-driven;
depending on the attribute group, there can be different methods to gather the needed data.
Into the file koqcoll.ctl you can find SELECT statements from specific tables, and invocation of binary files like koqsql.

Attribute group "Database Detail" is collected by the cursor koqdbd, that invokes binary koqsql using flag 6.

It has been observed that in some cases, "Database Detail" does not return the expected rows (one for each monitored db),just a singlerows showing "nodata".

If the problem occurs sporadically, then it may depend on machine workload, it is not caused by a problem with data collection code.

This is how the KOQDBD data collection does work:

An autonomous thread collects fresh data every 180 seconds, saving it into a DBD_PREV file.  
When a request for attribute group "Database Details" arrives from the agent, it actually gets data from this file, in case the modified date of the file is not older than 225 seconds.  
Otherwise it returns "nodata".  
This can happen if the DBD collector thread was not able to complete its task, for example because of temporary performance  problems or for connection problems with database.  

Let's consider a scenario like this:

we have four requests for KOQDBD/KOQDBS, and the collector logs, once the traces for KOQDBD cursor are enables, shows the following differences
between requests and DBD_PREV file modified time:

a. Difference in milliseconds : 47593.978516 --> 47.59397851600001  seconds.  
b. 57844.978516 --> 57.844978516 seconds.  
c. 87195.978516 --> 87.195978516 seconds.  
d. 176511.978516 --> 176.511978516  

 
All four values are lower than 225 seconds which means data in DBD_PREV  file is fresh enough to be displayed in TEP portal.  
In this time frame you will be able to see data on TEP for Database Detail attribute group.  
   

Now consider the following scenario instead, we have three requests for KOQDBD/KOQDBS:

a. Difference in milliseconds : 296516.978516 --> 296.516978516 seconds.  
b. 311053.978516 --> 311.053978516 seconds.  
c. 345737.978516 --> 345.737978516 seconds.  

   
All the three values are greater than 225 seconds, which means data in DBD_PREV is too old to be displayed on portal, so in that case the collector returns 'nodata'  

As previously said this can occur if the collector thread for database detail, that runs every 180 seconds,
actually did not complete its task in the expected time and has not refreshed the DBD_PREV file.
This can happen if the system performance are impacted and the koqcoll process runs slowly than expected.

So, in case you notice sporadic problems with Database Detail data collection, investigate on the system resources available to this system.
It is possible that one or more processes are using high CPU, impacting the other processes, or that the system is not properly sized to cope with peaks of workload, with CPU or memory showed constantly near 100%.
In this case you may want to scale up (vertical scaling) the system by adding the resources (CPU and Memory) available for it.
It will help to have your system working on a more effective way and will also fix your issue with Database Detail view showing "nodata".

Thanks for reading

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