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

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).
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 #:
9 of 9
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.