As a SQL Server Database Administrator, one of the most enjoyable tasks is troubleshooting performance issues. It requires putting on your detective hat and gathering clues from various sources to pinpoint the root cause of the problem. In this article, we will discuss a checklist that can help you diagnose performance issues at the OS level in your SQL Server instance.

Baseline Your System

Before using this checklist to diagnose performance issues, it is recommended to establish a baseline of your system when it’s performing well. This baseline will serve as a reference point to identify any deviations from normal behavior. For example, if you know that your system usually has 20% disk time and after running through the checklist you observe it running at 50%, you can quickly identify that some process is reading and/or writing more data than usual.

Collecting Performance Metrics

There are several tools available for collecting performance metrics on a Windows system, such as Performance Monitor, Resource Monitor, Task Manager, and PowerShell. In this checklist, we will focus on using PowerShell to gather performance counters as it provides flexibility, scalability, and the ability to schedule and save the output for later analysis.

CPU

The CPU usage on the server is usually the most telling performance metric. Even if the root cause of the performance issue lies elsewhere, the CPU will be affected and show higher than normal usage. To check CPU usage, you can utilize the “% Processor Time” performance counter.

Get-Counter '\Processor(*)\% Processor Time'

Memory

Although SQL Server can be configured to limit its memory usage, it is still important to monitor the system’s memory availability. You can use the following counters to check the available memory, number of page faults per second, and the percentage of the page file used.

Get-Counter '\Memory\Available MBytes'
Get-Counter '\Memory\Page Faults/sec'
Get-Counter '\Paging File(_Total)\% Usage'

IO

Checking the IO on the server is crucial for identifying issues with the storage subsystem or hardware limitations. It can also indicate missing indexes and queries performing full table scans. The following counters can help you monitor IO performance:

Get-Counter '\PhysicalDisk(*)\Current Disk Queue Length'
Get-Counter '\PhysicalDisk(*)\Disk Reads/sec'
Get-Counter '\PhysicalDisk(*)\Disk Writes/sec'
Get-Counter '\PhysicalDisk(*)\Avg. Disk sec/Read'
Get-Counter '\PhysicalDisk(*)\Avg. Disk sec/Write'

Network

Diagnosing network issues is relatively straightforward. By monitoring the bytes sent and received per second, you can identify any abnormal data transfer rates.

Get-Counter '\Network Interface(*)\Bytes Sent/sec'
Get-Counter '\Network Interface(*)\Bytes Received/sec'

Disk Space

Monitoring disk space is essential to prevent issues related to running out of space. By checking the percentage of free disk space for each drive, you can ensure that data files can extend if needed and that temporary space is available for sorting operations.

Get-Counter '\LogicalDisk(*)\% Free Space'

SQL Server

Although not directly related to the OS, these counters provide insights into the health of your SQL Server instance. The buffer cache hit ratio indicates the percentage of reads found in memory, while the page life expectancy represents the duration a page stays in memory without being referenced. The batch requests per second counter measures the workload on your SQL Server instance, and the SQL compilations per second counter indicates the performance of the plan cache.

Get-Counter '\SQLServer:Buffer Manager\Buffer cache hit ratio'
Get-Counter '\SQLServer:Buffer Manager\Page Life Expectancy'
Get-Counter '\SQLServer:SQL Statistics\Batch Requests/sec'
Get-Counter '\SQLServer:SQL Statistics\SQL Compilations/sec'

By running this PowerShell script or a similar one, you can gain a comprehensive overview of your SQL Server instance’s health. Remember to establish a baseline when the server is performing well to compare against when issues arise.