Microsoft SQL Server has been available on Windows for almost as long as Windows has existed, and the tools to monitor both SQL and Windows have become increasingly complex and varied in the three decades since their initial release. In some respects, monitoring SQL Server can be a more daunting task than monitoring Windows, not necessarily because it is more difficult, but because there are so many different ways to do it.
SQL Server leverages the data collection methods available in Windows (which we outlined in an earlier blog post), and adds its own built in tools. This post will look at tools built in to both SQL Server and Windows that provide the means to monitor SQL's health and performance.
SQL Activity Monitor
SQL Activity Monitor is a great SQL monitoring tool when you need to start with the basics. Launched from within SQL Server Management Studio, Activity Monitor delivers information about the SQL Server processes and their impact on the current SQL Server instance. The display shows 5 panes:
- Overview -% Processor Time, Waiting Tasks, Database I/O, and Batch Requests/sec
- Processes list of currently running SQL processes
- Resource Waits lists – waits for resources
- Data File I/O – lists databases and information about the database files
- Recent Expensive Queries - Lists queries using CPU, memory, disk, and network
- Simple and easy to launch
- Easy to understand
- Quick to identify resource problems and problematic queries
- Real-time display speeds problem diagnosis
- A single server at a time
- No historical context
- A limited number of Windows and SQL performance metrics
- Resource intensive, especially if run on the same server as the SQL Server database engine
SQL Standard Reports
SQL Standard Reports are available on SQL Server 2005 and higher, and are also launched from within SQL Server Management Studio. There are a large number of out-of-box reports that can identify blocking, poorly performing queries, and other areas such as schema change history. Custom reports can also be created although it does take a bit of doing.
- No setup
- Useful pre-packaged reports
- Reports on/from the SQL Server instance
- Reports can be resource intensive, so consider running them during off-peek hours
SQL Data Collector / Management Data Warehouse
SQL Data Collector consists of a set of SQL Agent jobs and is available on SQL Server 2008 and higher. Launched from within SQL Server Management Studio. Data Collector can be configured to collect performance metrics on a regular basis from multiple SQL Servers and house those metrics in the Management Data Warehouse (MDW). The Data Collector automatically collects the most common performance metrics related to disk usage, query statistics, and server activity, and can be programmed to collect custom data.
Data Collection scales across multiple servers and collects more windows and SQL performance metrics than the Activity Monitor, providing both the historical context and cross server monitoring that the Activity Monitor lacks.
- History for baselining
- Capture both OS level and SQL instance level counters at the same time
- Many pre-packaged reports
- Takes time to configure
- Difficult to remove
- The quantity of data uploaded from remote servers can be substantial, and simultaneous uploads can lead to resource issues. Data Collector should run on a dedicated server and the servers resources should be scaled up to meet data retention needs.
The SQL Server Profiler allows you to create a Profiler trace to capture and analyze SQL Server instance activity, providing the ability to audit actions and diagnose problems. Launched from within SQL Server Management Studio or the Windows menu, you can monitor statements running in SQL Server, metrics related to cpu, reads, writes, query duration, where the query is being run from, and more. Profiler can be invaluable in identifying causes of query related performance degradation.
- Easy to navigate Graphical Interface (very popular with DBAs)
- Very good at identifying internal SQL Server problems
- Can be configured to store information in files or in database tables for later export and analysis by other applications
- Needs to be setup ahead of time so it is not helpful in diagnosing immediate problems.
- Collections are resource intensive and should only be used for troubleshooting.
- Can be noisy. Profiler casts a wide net, and troubleshooting a problem can mean paring down to specifics from thousands of captured data points.
- Hasn't been updated since 2008, so it doesn't know how to monitor features in newer versions of SQL Server and Azure SQL
The use of SQL Server Extended Events are a critical component to any successful SQL diagnostics effort, especially when deep dive SQL analytics and tracing are needed and the use of a native SQL Server tool is preferred.
SQL Stored Procedures
SQL Server maintains a set of System Stored Procedures that are useful in carrying out administrative and informational activities. The System Stored Procedures that are most useful for SQL Server monitoring are within the Database Engine Stored Procedures category, with sp_who and sp_who2 being the most widely used.
sp_who - Lists the currently active SQL processes and returns information related to blocking activity, active users, current sessions and processes.
sp_who2 – Returns more detail than sp_who, including the program name as well as cpu and disk io information, but sp_who2 is undocumented and unsupported.
- Provides a good overview of the current SQL Server connections and their state.
- Provides DBAs with data needed to troubleshoot application timeouts, high CPU time, and high disk IO.
- sp_who2 is undocumented and unsupported
- Diagnostics are limited to a small albeit important set of metrics
- Metrics are only for a single server and have no historical context
SQL Dynamic Management Views
SQL Server maintains a wealth of configuration and performance data that can be used to display SQL settings and monitor SQL Server database health. Built-in System Dynamic Management Views (DMV) for SQL Server provide a method to readily extract this information.
Like any other view, SQL System DMVs can be accessed with a SQL query. However, you will first need to determine which view or views target the data you need, and then how to process the data once you have it. Examples of DMVs and the data they return include:
- sys.dm_tran_active_transactions - Returns information about transactions for the instance
- sys.dm_exec_sessions - Shows information about all active user connections and internal tasks
- sys.dm_os_performance_counters – Shows performance monitor counter data
- sys.dm_io_virtual_file_stats – Shows IO statistics for databases and log files
The SQLskills blog is an excellent source for additional information on user created DMV queries..
- Very powerful tool to access SQL Server configuration and its performance data.
- Lightweight data collection.
- You can create scripts that will use data from the DMVs to build a baseline.
- You will need to invest time to determine which metrics you need to access, which DMVs contain the metrics, and how to process the data once you have it.
- Not a central repository for multiple SQL Servers
You can use Perfmon to build your own SQL Monitor by picking and choosing applicable Windows and SQL Server Metrics. Perfmon also lists Average, Minimum, and Maximum values, which are critical to creating a baseline that provides context for observed metrics.
- Quick and easy to set up.
- Built-in data archive and reporting.
- Can be configured to alert.
- Long term data retention and reporting can be clunky.
- Unable to access SQL Server internals (i.e. Stored Procedures or DMVs).
- No advanced reporting options.
SQL Server provides a useful toolbox of built-in monitoring methods and the above list focuses primarily on methods within SQL Server itself. Other Windows tools - such as Typeperf and PowerShell - can also access SQL metrics, as can scripting languages like Python or Perl.
The method you choose will in large part depend on your own knowledge of SQL Server, the problem you’re looking to solve, the server resources you’re willing to dedicate to the monitoring solution, and lastly (and perhaps the most challenging) the time you have to build out a solution.
Regardless of how you collect data to monitor SQL, the key is to use the right tool for the right job. Some tools, such as Profiler and Activity Monitor, are more targeted compared to the broader scopes of the Management Data Warehouse and Dynamic Management Views. Keep in mind: the broader the tool's scope, the more upfront work is required to get the full benefit. The more effort you put in, the more you'll get out.
Want to learn more?
Download our Using Enhanced Application Monitoring to Achieve Peak SQL Server Performance Whitepaper and learn how delivering the performance and availability that end users demand from SQL Server begins with a sound monitoring strategy.