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!