SQL Health and History Tool

The SQL Health and History tool (SQLH2) started in 2004 as the brainchild of Terrence Nevins (who at the time was the Microsoft SQL Server architect and program manager for the SQL Server Scalability team) and Grigory Polgusky (who acted as the lead designer and software developer for the product). At the time SQLH2 was developed, the Scalability team needed to record the activity and behavior of users and software while dramatically increasing SQL Server utilization levels. The team wanted to learn not only what factors contributed to OS and database service slowdown, problems, and outages, but also what particular events precipitated them. Of course, these capabilities are of keen interest to SQL Server customers, too.When you use SQLH2 in your environment, you can use the information the tool provides to manage database changes and track performance trends so that you can proactively head off potential performance problems, even as your utilization increases.

Functionality

SQLH2 polls one or more SQL Servers and records a variety of information about the servers in a central repository. The tool collects the data in snapshots at regular intervals (by default, twice per week) so that you can compare and forecast your system's behavior. SQLH2 collects four main types of information:

• Feature Usage—Tells you which services and features are installed and running and the level of workload on each service
• Configuration Settings—Reports machine, OS, and SQL Server configuration settings and SQL Server instance and database metadata
• Uptime—Tells you the amount of time the SQL Server service has been running
• Performance Counters (optional)—Provide a variety of information for determining performance trends

SQLH2 documents all changes to your SQL Server 2005 and 2000 servers, including patches, database growth, changes in database settings, and performance-monitor counters.The utility tracks more information about SQL Server 2005 than SQL Server 2000, including data about triggers, assemblies, and backups. And the tool collects all of this information without requiring you to install any components on the target server and at an overhead of less than 1 percent. Therefore, SQLH2 is a great tool for both change control and trending for a multi?SQL Server environment.

Figure 1 shows an example SQLH2 configuration, in which server A collects performance and change-history information from target servers B, C, and D.

The simple architecture includes four basic components:

• Collector—Collects and stores performance and historic data
• Repository—SQL Server database repository for SQLH2 data
• Performance Collector—Optional, standalone Windows service that collects and stores performance counter data from the target servers
• Reports—Optional set of reports that provide multiple views of the data collected by SQLH2

These components let you look back in time to see configuration data and examine whether your servers are meeting your baseline performance expectations. When you detect that a server isn't meeting the baseline, you can drill down for root-cause analysis. Conversely, SQLH2 is good at revealing under-utilized servers that might be ripe for more databases or a greater user load.

Despite the fact that SQLH2 collects performance-counter information, you shouldn't consider it a real-time monitoring tool or an alerting tool.And although it can help you identify performance bottlenecks, it really isn't intended to be a hard-core diagnostic tool. Instead, think of SQLH2 as a method of reducing hundreds of mouse-clicks into a clean, concise trending and change-management tool. Also note that SQLH2 doesn't work on clustered servers or on SQL Server versions before SQL Server 2000. However, you can use SQLH2 with SQL Server Express to reduce your licensing costs.

Reporting and Documentation

The best thing about SQLH2 is that it ships with 13 ready-made SQL Server Reporting Services reports that make viewing the collected information a snap.You can use these reports to check on one server or to compare one server to another. Table 1 shows some of the reports available for SQLH2. Note that some reports are specific to SQL Server 2005 and,as such,don't provide any data about SQL Server 2000 target servers.

SQLH2 also includes nice documentation, including a comprehensive installation guide, FAQ, and data dictionary in case you want to write custom reports based on the data in the central repository. Figure 2 shows a SQLH2 database diagram, which you can use to build custom reports.

On top of the great documentation, SQLH2 is supported through various newsgroups as well as through the [email protected] email alias, where you can send questions, comments, and feedback.