Monitoring (DW)---a Technical Reference Guide for Designing Mission-Critical DW Solutions

Article
03/05/2012

Want more guides like this one? Go to Technical Reference Guides for Designing Mission-Critical Solutions.

Monitoring can be viewed in a number of different ways: observing performance, auditing change, or confirming compliance of systems to data-center standards. Monitoring components in top-tier implementations is commonly an enterprise-wide initiative. These initiatives can include the deployment of particular monitoring applications or the deployment of multiple scripts that can help in monitoring the environment at finer levels of detail.

Microsoft SQL Server includes several tools to help monitor an environment. Examples include the Activity Monitor, alerting mechanisms, Extended Events, Management Data Warehouse (MDW)/Data Collector, SQL Server Management Studio, SQL Profiler/SQL Trace, dynamic management views, and system views. SQL Server also integrates with Microsoft System Center Operations Manager.

Best Practices

Some pointers to monitoring tools mentioned to provide some context on what each can do:

The Microsoft SQL Server Management Pack1 for Microsoft System Center Operations Manager 2007 provides discovery and monitoring of Microsoft SQL Server 2005, SQL Server 2008, and SQL Server 2008 R2.
For a discussion of Activity Monitor, MDW/data collector and Central Management Servers, see Top 10 SQL Server 2008 Features for the (DBA).2
A high-level pointer to drill deeper on some of the monitoring tools functionality can be found in the article Monitoring (Database Engine)3 in SQL Server 2008 R2 Books Online.
Guidance for monitoring SQL Server performance can be found in Troubleshooting Performance Problems in SQL Server 2008.4
There are a several tools that provide different insights and meet different needs within the data center. Make sure that you evaluate and choose the right tools (or set of tools) for monitoring. Some customers prefer to use third party tools because they might have prior experience and may want to standardize on tools already in use in their installation for other DBMS or tasks.
Tools like SQL Profiler and SQL Trace can have an impact on a high-performance environment. You may need to consider limiting monitoring, and you may need to address how to monitor from multiple facets (for example, some in the database, some via client computers, and some via "polling" solutions, like System Center Operations Manager).
Similarly, just using dynamic management view queries without perfmon/sysmon might not provide as comprehensive a profile as you need to accurately monitor the environment.
Try to develop a strategy for what you are going to collect, how and when it is going to be reviewed, and what you are looking for in terms of the monitoring. Ideally, the monitoring solution will not be intrusive, but will still be productive in terms of collecting what is needed to understand the environment characteristics.

Case Studies and References

The following section describes additional reference and case studies.

The SQL CAT Community Projects & Code Samples5 page on CodePlex also has some shareware for monitoring SQL Server that might work or can be modified to work, depending on the customer and the environment. The scripts and infrastructures from the SQL Server Customer Advisory Team (SQLCAT) include response time and server waits through Extended Events (a less intrusive mechanism) and a Centralized Audit Logging mechanism. The page Project Hosting for Open Source Software6 also includes other SQL Server monitoring tools that have become popular on the site.

For information about monitoring for compliance and for some general auditing tips, see the white paper Reaching Compliance: SQL Server 2008 Compliance Guide.7 The sample scripts and tools are particularly helpful.

A new/future vision for some customers might include Microsoft System Center Advisor.8 While it may not work for some enterprise environments, it is an interesting architecture that helps provide another possible solution to customers for proactive monitoring and troubleshooting.

Questions and Considerations

This section provides questions and issues to consider when working with your customers.

Data warehouse monitoring is significantly different than OLTP. Also, monitoring the performance of data loads is different than monitoring queries. The primary "canaries" to watch include disk I/O and scan rates, TEMPDB operations (sorting on loads or hash-join spilling), fragmentation (creates random I/O), and CPU utilization.
Do you have any enterprise-scoped monitoring software in-house? How does it integrate with SQL Server?
Does your enterprise or solution have any particular service level agreements (SLAs), operational level agreements (OLAs), or other requirements for monitoring performance or compliance? If so, it is best to understand them when picking a solution for monitoring.
Do you have any processes for collecting and reviewing data in terms of monitoring the environment(s)?
Having the ability to monitor the application for compliance and performance is, in many cases, just as important as the application itself; therefore, it is deemed a mission-critical component of the infrastructure.
There are a number of third-party vendors who have developed monitoring applications to work with SQL Server (for example, Idera, RedGate, Quest, and Precise).
Some customers have found the information in the article The SQL Server Management System: Definition9 useful when considering monitoring solutions.