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.