Power of RunSQL KS from new AppManager SQL server module - Part 2 (CPU and MEM usage by database)

Power of RunSQL KS from new AppManager SQL server module - Part 2 (CPU and MEM usage by database)

This is the second post in a series in which I will explain how memory and CPU are utilized at the individual database level using the power of RunSQL KS from the new AppManager for SQL server module. Read Part 1 here.

One of the problems with my SQL server, I face is to find how the CPU and memory are utilized by the individual database. I can get the overall CPU usage for the SQL server but if further I wanted to have a break of my CPU and memory usage at individual database level then I can not find using counters. 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 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 an SQL query to report CPU and memory utilization. The SQL query is attached and simple.

This query will rank up the database based on its CPU usage and from this I can easily figure out which of top 3 database who keeps my CPU busy and eating up my resources. This will help me to resolve some of the bottlenecks at the individual database level. You can uncomment "WHERE TBLMem.MemoryUsedinMB / [TotalMem] *100.0  >= 50.0" to report the memory usage in case if cross the threshold of 50% of total memory allocated to SQL server.

In my next post, I will bring up how we can use this KS to get the longer running SQL jobs based on the average from the history and raises an alert if SQL jobs are taking twice the average time to execute.
Labels (1)

DISCLAIMER:

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