Best Practices on Performance Counters for Microsoft SQL Server

Why is this needed?

When users notice that web pages or other systems are running slow, they start complaining to System or database administrators.

How to know if your database is running fine and is not bottleneck in this situation? Using a built-in Microsoft tool called Performance monitor is one of many possible options to begin an investigation.

How to use Perfmon?

There are several ways to open the Performance Monitor window. The easiest one is to click on the Start button on the taskbar (or push the Windows button on your keyboard), type in “Performance Monitor“ and hit enter.

You should see the default graph of processor time. At this point, it is easy to right-click on this graph, choose Add counters, select them and get instant results.

However, it is worth to collect performance counters data for a longer period and compare non-peak time and days versus peak ones. How to do this? Follow these simple steps:

Firstly, you have to create a Data Collector Set:

  1. On the Performance Monitor window already opened, expand Data Collector Sets. 
  2. Right-click on User Defined, and choose New > Data Collector set.

    Figure 1 – Create a new Data Collector Set.
  1. On the new window, enter a name (example – “SQL Server performance metrics“), choose Create manually (Advanced) and then Next.
  2. On the Create data logs radio button, tick a box on the Performance counter and click Next.
  3. Click Add and select counters you are interested in, and click Add after each selected counter. You can also choose a local computer or remote machine to collect data from. The next paragraph will have a list of recommended counters for the SQL Server instance running on Microsoft Server. Figure 2 shows the example performance counter selected.
    Figure 2 – SQL Compilations performance counter added.

    After all required performance counters were added, a Sample interval can be chosen. Remember that too small intervals may generate a huge amount of data that may be harder to render. A large interval may not have enough data for investigation. The recommendation is to start interval for 1 minute (if capture runs for a day) and later adjust by specific counters selected and personal requirements. Click Next.

  1. On the “Where would you like the data to be saved?“ window, we would recommend choosing the drive which will not be collecting data from (in case any disk counter is used). Click Next.
  2. Now Run as: User might be specified. Otherwise, leave <Default> value, choose Save and close and click 

When a new user-defined template is available, right-click on it and choose Start like it is shown in Figure 3.

Figure 3 – Start collecting data.

Let them run for a while. It can also be scheduled, for example, to start daily. To do this, instead of Start, choose Properties and then Schedule tab.
Our recommendation is to keep collecting running no longer than one day (and then start again if needed) for possible overhead.

Performance counters

The table below contains the recommended performance counter for SQL Server instance:

Object Counter Description
SQLServer: SQL Statistics Recompilations/sec Statement recompiles per second count.
SQLServer: SQL Statistics Compilations/sec SQL compilations per second count.
SQLServer: SQL Statistics Batch Requests/sec T-SQL command batches received per second.
SQLServer: Memory Manager Memory Grants Pending Total number of processes waiting for a memory grant.
SQLServer: General Statistics Processes blocked Currently blocked processes count.
System Processor Queue Length Collects number of threads waiting for processor time.
Memory Available MBytes Collects memory available in Megabytes.
Physical Disk Avg. Disk sec/Read Average time in seconds of read data from the disk.
Physical Disk Avg. Disk sec/Write Average time in seconds of write data from the disk.
Physical Disk Disk Reads/sec Rate of read operations on the disk.
Physical Disk Disk Writes/sec Rate of write operations on the disk.
Processor % Processor Time Percentage of elapsed time processor spends to execute not idle thread.

More details on Performance counters can be found on Microsoft monitor resource usage page.

Analyzing results

When there is any result collected, on Performance Monitor window expand Reports, User Defined, and open report under “ SQL Server performance metrics“ (or another name specified previously) like in Figure 4.

Figure 4 – Analyzing performance counter results.

During the investigation, keep in mind that one counter can be closely related to another one.
For example, suppose SQL Server does not have enough memory dedicated. In that case, it may start writing to disk instead and increase it‘s performance counters of disk activity (even if the root cause is different).

Happy exploring!
Any question, let me know!

Share this post

Share on facebook
Share on google
Share on twitter
Share on linkedin
Share on pinterest
Share on print
Share on email

Leave a Comment

Your email address will not be published. Required fields are marked *

4.8/5

42

TESTIMONIALS

FREE Scripts to Instantly
Improve SQL Server Performance

3 FREE Scripts to Instantly
Improve SQL Server Performance

Learn how to increase Performance of SQL Server INSTANTLY with 3 free scripts.