How to Monitor SQL Server

The venerable Microsoft SQL Server is closing in on its 30th birthday, and yet it still holds as one of the most popular relational databases out there.

Love it or hate it, chances are that at some point in your career you’ll come across a situation where you have to fine-tune an SQL Server. And when you do, it’s highly likely that performing some monitoring beforehand will get you places.

A good SQL Server monitoring plan can help you stay on top of:

• Performance: Decide if a performance increase is necessary or feasible. For instance, by keeping a track record of query completion times you could spot any suboptimal areas and come up with possible improvements. Checking queries as they are executed might reveal issues that need fixing.
• Growth: Observe requirements in terms of users and traffic. Database usage needs tend to evolve faster than expected.
• Security: Ensure adequate security measures have been applied.

What should I monitor?

SQL Server Performance monitoring revolves around 4 key areas (and associated counters):

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.

Windows Monitoring Tools

There are 3 main types of tools out there: i) those that ship with Microsoft Windows, ii) those that ship with Microsoft SQL Server, and iii) all the rest.

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.

By default, counter readings are taken every second, thus showing approximately two minutes of activity on the graph. By accessing Performance Monitor Properties you can change both sample frequency and graph duration to depict a more meaningful time period (e.g. a couple of hours).

For more information on Windows Performance Monitor check out this TechNet guide.

Windows Event Log

The Windows Application Event Log is a comprehensive set of notifications and logs pertaining to important system-wide events including SQL Server.

To view and manage the Windows application log you can use either the Windows Event Viewer or the Log Viewer in SQL Server Management Studio. This guide shows you how.

SQL Server tools

SQL Server Profiler

Microsoft recently announced that SQL Server Profiler for the database engine will be deprecated in future versions. Nevertheless it remains a useful tool.

It comes with MS SQL Server and is very good at identifying things that happen under the hood, such as finding out how SQL statements are dealt with internally, what scripts are running to accomplish T-SQL commands, and other functions. You can store information gathered by SQL Server Profiler either in files or in database tables, so you can later export and use it for analysis in other applications.

SQL Profiler works with Traces. Those are sets of information about events and relevant SQL server activity data. To monitor activity you create traces. They tend to hold enormous amounts of information so you need to narrow it down by applying filters. The huge amount of information made available through traces, along with complaints related to its additional overheads have raised some controversy among developers and DBAs. Like any other utility, details matter, and correct configuration is key.

You can read more about SQL Server Profiler on this MSDN article.

SSMS Activity Monitor

The Activity Monitor utility is a nifty tool that offers a quick overview of SQL Server system performance within the SQL Server Management Studio. You can view information related to Processes and Locks, as well as graphs for Processor Time, Wait Stats, DB I/O’s, Batch Requests, Resource Waits, Data File I/O’s and information about queries.

In many ways, this tool offers similar functionality to Performance Monitor.This article describes Activity Monitor in detail.

SQL Server Error Log

Just like the Windows Application Event Log, certain system and user-defined events are recorded in the SQL Server error log. The information in this log helps with troubleshooting and with ensuring that processes such as backup and restore, batch commands and other scripts work as expected.

The error log can also help detect any potential problems, including automatic recovery messages (particularly if an SQL Server instance has stopped and restarted), kernel messages, or other server-level error messages.

The SQL Server error logs are in the form of text files and can be viewed with any text editor. By default, these files are located at Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ in files ErrorLog and ErrorLog.n.

Every new SQL Server instance comes with a new error log, and SQL Server holds a backup for the last six logs.

This article discusses SQL Server Error Logs in detail.

System Stored Procedures

One of the most effective ways to monitor SQL Server is by using System Stored Procedures. The following stored procedures, built-in to SQL Server, provide a powerful alternative to many monitoring tasks:

Stored procedure	Description
sp_who (Transact-SQL)	Reports snapshot information about current SQL Server users and processes, including the currently executing statement and whether the statement is blocked.
sp_lock (Transact-SQL)	Reports snapshot information about locks, including the object ID, index ID, type of lock, and type or resource to which the lock applies.
sp_spaceused (Transact-SQL)	Displays an estimate of the current amount of disk space used by a table (or a whole database).
sp_monitor (Transact-SQL)	Displays statistics, including CPU usage, I/O usage, and the amount of time idle since sp_monitor was last executed.

Experienced Database Administrators might prefer this method for the flexibility it provides in extracting the exact type of information they want.

Third party tools

SQL Server ships with a rich set of monitoring and configuration tools. 3rd party tools provide extra functionality and flexibility, offering simple yet comprehensive solutions to monitoring challenges and go that extra mile.

You might want to check out Redgate SQL Monitor, Idera SQL Diagnostic Manager, Netwrix Auditor for SQL Server or Apex SQL Monitor that provide in-depth SQL Server metrics and utilities, handle large environments well and offer free trials.

You should also take a look at Server Density (yes, this is where we shamelessly toot our own horn).

Server Density offers a user interface (we like to think it’s very intuitive) that supports tagging, elastic graphs and advanced infrastructure workflows. It plays well with your automation tools and offers mobile apps too.

To monitor SQL Server activity, Server Density works seamlessly with Tether, an open source Windows agent, through dedicated plugins.

So if you don’t have the time to setup and maintain your own on-premise monitoring and you are looking for a hosted and robust monitoring solution that covers SQL Server (and the rest of your infrastructure), you should sign up for a 2-week trial of Server Density.

Server Density is not free but it saves you significant time and effort, which translates to significant savings for your boss.

Summary

The need to monitor Microsoft SQL Server will not go away any time soon.

SQL Server ships with various monitoring utilities but there will be cases when you will want to turn to 3rd party tools for that extra bit of functionality or convenience. As with most things in tech, your mileage may vary (by the way, if you want to learn more about monitoring SQL Server, MSDN might be a good start).

So what about you? How do you monitor SQL Server, and what metrics do you track?