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 bottlenecked 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 collecting performance counters data for a longer period and comparing 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:
- On the Performance Monitor window already opened, expand Data Collector Sets.
- Right-click on User Defined, and choose New > Data Collector Set.
- On the new window, enter a name (example – “SQL Server performance metrics“), choose Create Manually (Advanced) and then Next.
- On the Create Data Logs radio button, tick a box on the Performance Counter and click Next.
- 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.
After all required performance counters were added, a sample interval can be chosen.
Remember that intervals that are too small 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 the interval for 1 minute (if capture runs for a day) and later adjust by specific counters selected and personal requirements. Click Next.
- On the “Where would you like the data to be saved?“ window, we would recommend choosing the drive that will not be collecting data from (in case any disk counter is used). Click Next.
- 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.
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.
Top 12 SQL Server Performance Counters
The table below contains the recommended performance counter for SQL Server instances:
| 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 to 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 the processor spends executing non-idle threads. |
More details on performance counters can be found on Microsoft monitor resource usage page.
Analyzing results
When there is any result collected, on the Performance Monitor window expand Reports, User Defined, and open report under “ SQL Server performance metrics“ (or another name specified previously) like in Figure 4.
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).
