Power of RunSQL KS from new AppManager SQL server module - Part 1

This is the first post in a series in which I will explain the power of RunSQL KS from the new AppManager for SQL server module.

One of the problems I face is to find what queries are running on my SQL server, which query is time consuming, what is the logical read/write and average elapsed time. One of the fundamental approaches to solve this, use management studio to connect with the SQL server and then look into the "dm_exec_query_stats" view from the master database. This fundamental problem becomes a nightmare everytime I need to login into the SQL server and execute 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 a small SQL query to report what SQL statements are currently executing and how they are performing. The SQL query is attached and simple.

This query reports the execution time for the SQL T-statement and from this I can easily figure out which T-SQL statement is taking time and hence I can call the vendor to correct it. This query can be passed to the RunSQL KS which can schedule to run hourly and I can trigger the email action if the execution time cross for any of the SQL statement.

In my next post, I will bring up how we can use this KS to generate the query execution plan to find out where scanning is taking more time and which index is consuming more time so that I can report problem to the vendor (if needed).


How To-Best Practice
Comment List