Windows Performance Monitor Counters for SQL Server


By:   |   Updated: 2021-08-20   |   Comments   |   Related: More > Monitoring


Monitor the performance of Azure SQL Database, Amazon RDS for SQL Server, and SQL Server

Free MSSQLTips Webinar: Monitor the performance of Azure SQL Database, Amazon RDS for SQL Server, and SQL Server

Attend this webinar to learn how to manage the performance of SQL Server not only on-premises but also in the cloud for Azure SQL Database, Amazon RDS for SQL Server as well as SQL Server using a single tool.


Problem

How do I obtain data and interpret the results of some commonly used SQL Server related Performance Monitor Counters?

Solution

We've seen how to create and run a Perfmon Collector Set to capture real-time performance information. Now, we'll look at some output, metrics and values to look for when troubleshooting.

Objects / Counters

The following table has a list of Perfmon Objects / Performance Counters commonly used to review SQL Server performance along with their descriptions and a guide of expected values.

Perfmon Object Perfmon Counter Counter Description What to Look For
Memory Available MBytes Available MBytes is the amount of physical memory, in Megabytes, immediately available for allocation to a process or for system use. Should remain fairly constant and probably not go below 4096 MB or so. May indicate 'max server memory' left at default, non-SQL Server applications running on server
Processor % Processor Time % Processor Time is the percentage of elapsed time that the processor (CPU) spends to execute a non-Idle thread. % Processor Time will vary quite a bit on an active server. Spikes to 100% aren't uncommon but if it's over 70% or so for any length of time your users are probably experiencing performance issues. Missing or no indexes on a table being queried could be the culprit.
SQLServer:Access Methods Forwarded Records/sec Number of records fetched through forwarded record pointers Ideally close to 0.
SQLServer:Access Methods Full scans/sec Number of unrestricted full scans. These can either be base table or full index scans Less than 1 preferred.
SQLServer:Access Methods Page Splits / Sec Number of page splits per second that occur as a result of overflowing index pages Should be less than 20% of Batch Requests/Sec.
SQLServer:Buffer Manager Buffer Cache hit ratio Percentage of pages that were found in the buffer pool without having to incur a read from disk You would like to see this steady at or close to 100%. Less could indicate not enough memory on server or allocated to SQL Server.
SQLServer:Buffer Manager Checkpoint Pages / Sec Number of pages flushed by checkpoint or other operations that require all dirty pages to be flushed Benchmark for comparison.
SQLServer:Buffer Manager Page life expectancy Number of seconds a page will stay in the buffer pool without references Rule of thumb is this should not go below 300ms.
SQLServer:General Statistics User Connections Number of users connected to the system. Used as a benchmark value to see if the average number increases over time indicating more activity.
SQLServer:Locks Average Wait Time (ms) The average amount of wait time (milliseconds) for each lock request that resulted in a wait Ideally should be 0.
SQLServer:Locks Lock Waits / Sec Number of lock requests that could not be satisfied immediately and required the caller to wait before being granted the lock Ideally should be 0.
SQLServer:Memory Manager Memory Grants Pending Current number of processes waiting for a workspace memory grant Ideally should be 0.
SQLServer:Memory Manager Target Server Memory (KB) Ideal amount of memory the server is willing to consume Total Server Memory (KB) / Target Server Memory (KB) should be approximately 1.
SQLServer:Memory Manager Total Server Memory (KB) Total amount of dynamic memory the server is currently consuming Total Server Memory (KB) / Target Server Memory (KB) should be approximately 1.
SQLServer:SQL Statistics Batch Requests/Sec Number of SQL batch requests received by server Benchmark of server activity.
SQLServer:SQL Statistics SQL Compilations/Sec Number of SQL compilations Should be at 10% or less than total Batch Requests/sec.
SQLServer:SQL Statistics SQL Re-Compilations/Sec Number of SQL re-compilations Should be at 10% or less than total Batch Requests/sec.

View Performance Data

Go to our previously created and run Collector Set.

  1. Right click
  2. Latest Report
Display Report

Here we see data collected on a particular day from 11:45AM to 12:45PM. The top part of the screen is a graphical representation of each Counter. Right below that are the values for the selected Counter. And the bottom is where we select Counters.

Perfmon Report

It's time to look at two examples.

Example #1

Here I've unchecked all the Show boxes except for % Processor Time to show a cleaner graphical representation. Looking at the numbers on the bottom we see that that the minimum was 0%, maximum was 55.746% and the average was 0.289%. Looking at the graph confirms the numbers and as we see there was a very short spike around 12:35PM, but it was otherwise low.

% Processor Time

Example #2

Refer to the Perfmon Object / Counter table for the next example and we see that in order to figure out if the SQL Compilations/Sec and SQL Re-Compilations/Sec are below the threshold that we need to obtain the value of Batch Requests/sec.

  1. Uncheck all but the 3 Counters in question
  2. Right click in Counters area
  3. Select each of the 3 Counters individually
  4. Select scale that fits the line within the screen area in dropdown for Batch Requests/sec and match it for SQL Compilations/Sec and SQL Re-Compilations/Sec
  5. OK
Batch Requests / Sec - SQL Compilations/Sec - SQL Re-Compilations/Sec

Looking at the graph we see Batch Requests/Sec is the only Counter easily visible. SQL Compilations/Sec and SQL Re-Compilations/Sec are barely visible the bottom. By eye that indicates they're both well below the 10% threshold.

Batch Requests / Sec

To confirm, select Average Batch Requests/sec and it's is 0.065. 10% of 0.065 is 0.0065. Select SQL Compilations/Sec and the average is 0.001. Select SQL Re-Compilations/Sec and the average is 0.00. Both of these values are less than 0.0065 so we're within range.

SQL Compilations/Sec
SQL Re-Compilations/Sec

Export Perfmon Data to a .csv File

You may prefer to view and analyze the Perfmon data in a spreadsheet. Perfmon gives us the ability to export it to a .csv file.

  1. Right click anywhere
  2. Save Data As…
Export Perfmon Data to .csv file
  1. Choose location
  2. Name file
  3. Select 'Text File (Comma delimited)(.csv)' in dropdown
  4. Save
save perfom data as

After some formatting to make it a little more readable and saving to a .xlsx it's ready for some slicing and dicing.

Perfmon Data Spreadsheet
Next Steps

We've seen how to view Perfmon data and configure some options to make viewing it a bit easier for some general SQL Server related Counters.

Stay tuned for a tip on looking at Perfmon data in some more specific areas.

Here are some links with further information.






get scripts

next tip button



About the author
MSSQLTips author Joe Gavin Joe Gavin is from Greater Boston. He has held many roles in IT and is currently a SQL Server Database Administrator.

View all my tips


Article Last Updated: 2021-08-20

Comments For This Article





download














get free sql tips
agree to terms