How to Monitor SQL Server
Disk Activity
▪ Physical Disk: % Disk Time: This counter monitors the portion of time the disk is busy
with read/write activity. If the Physical Disk: % Disk Time counter is close to or over 90%,
it indicates that too many system requests are waiting for disk access (check this via the
Physical Disk: Current Disk Queue Length counter). The number of pending I/O requests
should be sustained at no more than 1.5 to 2 times the number of spindles of the
physical disk.
▪ Physical Disk: Average Disk Queue Length: number of I/O operations waiting (again,
over 1.5 or 2 times the number of disk spindles is bad)
▪ SQL Server Buffer Manager – Page reads/sec and page writes/sec. If this counter rises
above your baseline, it may indicate the need for more hardware power
Processor Utilization
▪ Processor: % Processor time: A consistent 80-90% is too high. Multiprocessor systems
have a separate instance for each CPU.
▪ Processor: % Privileged time: indicates the time spent on Windows kernel commands
(SQL Server I/O requests). If both this and Physical Disk counters are high, there might
be a need for a faster disk or lower load for this server.
▪ Processor: % user time: the percentage of time the CPU spends on user processes
(SQL Server)
▪ Processor: Queue Length: the number of threads waiting for processor time. A high
number may indicate the need for faster or more processors.
Memory
▪ Memory: Available MBs: indicates how much memory is available for new processes
▪ Memory: Pages/sec: this counter indicates how many times the virtual memory is getting
accessed. A rule of thumb says that it should be lower than 20. Higher numbers might
mean excessive paging. Using Memory: Page Faults/sec can further indicate whether
SQL Server or some other process is causing it.
Monitor SQL Server
SQL Server works with objects and counters, with each object comprising one or more
counters. For example, the SQL Server Locks object has counters called Number of
Deadlocks/sec or Lock Timeouts/sec.
▪ Access Methods – Full scans/sec: higher numbers (> 1 or 2) may mean you are not
using indexes and resorting to table scans instead.
▪ Buffer Manager – Buffer Cache hit ratio: This is the percentage of requests serviced by
data cache. When cache is properly used, this should be over 90%. The counter can be
improved by adding more RAM.
▪ Memory Manager – Target Server Memory (KB): indicates how much memory SQL
Server “wants”. If this is the same as the SQL Server: Memory Manager — Total Server
Memory (KB) counter, then you know SQL Server has all the memory it needs.
▪ Memory Manager — Total Server Memory (KB): much memory SQL Server is actually
using. If this is the same as SQL Server: Memory Manager — Target Server Memory
(KB), then SQL Server has all the memory it wants. If smaller, then SQL Server could
benefit from more memory.
▪ Locks – Average Wait Time: This counter shows the average time needed to acquire a
lock. This value needs to be as low as possible. If unusually high, you may need to look
for processes blocking other processes. You may also need to examine your users’ T-
SQL statements, and check for any other I/O bottlenecks.
Performance Monitor
Performance Monitor (or, in some older windows versions, System Monitor) is a handy real -
time graphical monitoring tool. It can monitor several, different metric sources and it offers
features such as data export, remote monitoring, and more.
SQL Server Profiler
SQL Server provides a great tool that allows you to see what commends are running on your
SQL Server as well as gathering the useful analyzing information such as duration, number of
reads, number of writes, the machine that ran the query and so on.
▪ The Profiler tool can be launched in one of these ways:
In SSMS, select Tools > SQL Server Profiler from the menu.
▪ It can also launch Profiler from the Windows menu.
▪ Connect to the SQL Server instance you want to trace
▪ A Trace Properties window will open and you can edit filter to Run.
