SQL SERVER – Querying Performance Counters from SQL Server

Troubleshooting SQL Server is something almost every single DBA has to go through in their lifespan. It sounds simple and often we are clueless on where to start. Here is a scenario, you have been told that SQL Server is slow or unresponsive. Now you need to troubleshoot and figure out what is happening. Sounds simple right? What would you do in this situation?

When I asked this scenario to one of my DBA friend, their answer was simple. “Pinal, the first thing is to log onto the server and open Task Manager. Next I will see the basic parameters of how the CPU, Memory and IO are doing. Based on what I see at a macro level; we will troubleshoot accordingly.”

This seemed to be a simple yet a logical way to see things. On further query, they did mention about using PerfMon counters as an ideal way to capture some of the current problems running inside SQL Server. Capture the numbers and then troubleshoot later with other tools like Extended Events, Profiler, Activity Monitor etc. This conversation interested me big time because I wanted to see how people query performance counters.

The basic thing is to initialize Perfmon.exe and look at the various counters. We can also create a Data Collection template and go ahead with using the same. I am sure most of you are aware about doing the same.

Let me take an unconventional approach to a simple requirement to query Memory Grants pending on a SQL Server box using other techniques.

SQL to Query Performance Counter

In the below query, we use two methods. One to query to the Performance Counters and other via the DMV.

-- Get memory grants pending (perfmon counter)
SELECT cntr_value AS memory_grants_pending
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE '%Memory%Grants%Pending%';
-- Get memory grants pending (DMV)
SELECT COUNT(*) AS memory_grants_pending
FROM sys.dm_exec_query_memory_grants
WHERE wait_time_ms IS NOT NULL;

This metric defines the total amount of memory grants that are waiting for workspace memory, and if this value is consistently above zero it could be a sign of memory pressure, and it could be negatively impacting the querying process. It is recommended that reevaluation of memory allocations and/or available physical memory is taken into account in order to relieve memory pressure for the current SQL Server instance where this query is being run.

If there are memory related errors, then we are likely to see 8645 errors in our SQL Server Errorlog. How to troubleshoot SQL Server error 8645 is explained in the KB.

Query Counter Using PowerShell

As we have done in various blog posts, we can also use PowerShell to query counters too. A simple script I wrote to query looks like below:

Get-Counter -Counter "\SQLServer:Memory Manager\Memory Grants Pending"| SELECT-Object @{Name = "Memory Grants Pending" Expression = {$_.CounterSamples.CookedValue}}

Please let me know if you have every used any of these techniques in your environment to automate querying values from Performance Counters. How do you use these counter values to proactively mitigate problems in your environments? What are some of the techniques that you have been using?

Reference: Pinal Dave (https://blog.sqlauthority.com)