Power of RunSQL KS from new AppManager SQL server module - Part 3 (SQL Jobs monitoring)

Power of RunSQL KS from new AppManager SQL server module - Part 3 (SQL Jobs monitoring)

This is the third post in a series in which I will explain how an SQL job is performing on a daily basis using the power of RunSQL KS from the new AppManager for SQL server module.

One of the problems with my SQL server, I face is to find how the SQL jobs are performing and if any of my SQL jobs are taking longer time than the average time. I can get the time taken by the SQL job but further, if I wanted to know what is the average time, a particular SQL job taking and if the average is exceeded twice then this makes my life bit complex. One of the fundamental approaches to solving this, I can write some queries around "dm_exec_query_stats" and execute it. This fundamental problem becomes a nightmare every time I need to login into the SQL server and executes this view.

With AppManager for SQL server module, there is a KS "RunSQL" which I find helpful to solve the problem and report the metrics on my query. If I can build the SQL query, which can be executed on the discovered SQL servers at a certain interval, then I can trigger the action to report the metrics. What I did is, I created an SQL query to report about SQL jobs which are taking twice the average time to execute. The SQL query is attached and simple.

This query will bring up all the SQL jobs whose average execution time is twice the average, calculated from the history table. This will help me to resolve some of the bottlenecks at the SQL jobs level. You can modify "lastrun.run_duration >= Avgrun_duration *2" to report the jobs as per you need.

I hope with all of my three posts, I've been able to explain how powerful KS is and in the future you can write some complex queries to report any metrics from the SQL database. With this post, I would like to close this thread. Thanks for reading!

Labels (1)


Some content on Community Tips & Information pages is not officially supported by Micro Focus. Please refer to our Terms of Use for more detail.
Top Contributors
Version history
Revision #:
12 of 12
Last update:
‎2020-01-09 18:35
Updated by:
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.