- Table of Contents
- Microsoft SQL Server Defined
- Microsoft SQL Server Features
- Microsoft SQL Server Administration
- Microsoft SQL Server Programming
- Performance Tuning
- Practical Applications
- Choosing the Back End
- The DBA's Toolbox, Part 1
- The DBA's Toolbox, Part 2
- Scripting Solutions for SQL Server
- Building a SQL Server Lab
- Using Graphics Files with SQL Server
- Enterprise Resource Planning
- Customer Relationship Management (CRM)
- Building a Reporting Data Server
- Building a Database Documenter, Part 1
- Building a Database Documenter, Part 2
- Data Management Objects
- Data Management Objects: The Server Object
- Data Management Objects: Server Object Methods
- Data Management Objects: Collections and the Database Object
- Data Management Objects: Database Information
- Data Management Objects: Database Control
- Data Management Objects: Database Maintenance
- Data Management Objects: Logging the Process
- Data Management Objects: Running SQL Statements
- Data Management Objects: Multiple Row Returns
- Data Management Objects: Other Database Objects
- Data Management Objects: Security
- Data Management Objects: Scripting
- Powershell and SQL Server - Overview
- PowerShell and SQL Server - Objects and Providers
- Powershell and SQL Server - A Script Framework
- Powershell and SQL Server - Logging the Process
- Powershell and SQL Server - Reading a Control File
- Powershell and SQL Server - SQL Server Access
- Powershell and SQL Server - Web Pages from a SQL Query
- Powershell and SQL Server - Scrubbing the Event Logs
- SQL Server 2008 PowerShell Provider
- SQL Server I/O: Importing and Exporting Data
- SQL Server I/O: XML in Database Terms
- SQL Server I/O: Creating XML Output
- SQL Server I/O: Reading XML Documents
- SQL Server I/O: Using XML Control Mechanisms
- SQL Server I/O: Creating Hierarchies
- SQL Server I/O: Using HTTP with SQL Server XML
- SQL Server I/O: Using HTTP with SQL Server XML Templates
- SQL Server I/O: Remote Queries
- SQL Server I/O: Working with Text Files
- Using Microsoft SQL Server on Handheld Devices
- Front-Ends 101: Microsoft Access
- Comparing Two SQL Server Databases
- English Query - Part 1
- English Query - Part 2
- English Query - Part 3
- English Query - Part 4
- English Query - Part 5
- RSS Feeds from SQL Server
- Using SQL Server Agent to Monitor Backups
- Reporting Services - Creating a Maintenance Report
- SQL Server Chargeback Strategies, Part 1
- SQL Server Chargeback Strategies, Part 2
- SQL Server Replication Example
- Creating a Master Agent and Alert Server
- The SQL Server Central Management System: Definition
- The SQL Server Central Management System: Base Tables
- The SQL Server Central Management System: Execution of Server Information (Part 1)
- The SQL Server Central Management System: Execution of Server Information (Part 2)
- The SQL Server Central Management System: Collecting Performance Metrics
- The SQL Server Central Management System: Centralizing Agent Jobs, Events and Scripts
- The SQL Server Central Management System: Reporting the Data and Project Summary
- Time Tracking for SQL Server Operations
- Migrating Departmental Data Stores to SQL Server
- Migrating Departmental Data Stores to SQL Server: Model the System
- Migrating Departmental Data Stores to SQL Server: Model the System, Continued
- Migrating Departmental Data Stores to SQL Server: Decide on the Destination
- Migrating Departmental Data Stores to SQL Server: Design the ETL
- Migrating Departmental Data Stores to SQL Server: Design the ETL, Continued
- Migrating Departmental Data Stores to SQL Server: Attach the Front End, Test, and Monitor
- Tracking SQL Server Timed Events, Part 1
- Tracking SQL Server Timed Events, Part 2
- Patterns and Practices for the Data Professional
- Managing Vendor Databases
- Consolidation Options
- Connecting to a SQL Azure Database from Microsoft Access
- SharePoint 2007 and SQL Server, Part One
- SharePoint 2007 and SQL Server, Part Two
- SharePoint 2007 and SQL Server, Part Three
- Querying Multiple Data Sources from a Single Location (Distributed Queries)
- Importing and Exporting Data for SQL Azure
- Working on Distributed Teams
- Professional Development
- Application Architecture Assessments
- Business Intelligence
- Tips and Troubleshooting
- Additional Resources
The SQL Server Central Management System: Collecting Performance Metrics
Last updated Jun 26, 2009.
In this tutorial I’m continuing a series on building a system that you can use to monitor and track your SQL Server systems, and in fact, any kind of system you like (The first article in this series is here). You can use the concepts you find here for lots of SQL Server applications, since in essence it uses many of the features and tools in SQL Server, specifically SQL Server 2008.
At the bottom of this article I reference a CodePlex (Microsoft’s open-source software site) where I’m creating a solution called the SQL Central Management System, or SQLCMS. If you’re interested in participating, just post a notice there that you want to join in the solution. We’ll design it together. I’m working through that project in this series of articles.
In the solution I’m building, there are three basic “components”:
This tutorial covers one aspect of the execution component of the project, and in specific using this system as a central repository for your performance information.
Using the Management Data Warehouse (MDW) Feature for Performance Collection for SQL Server 2008 Instances
In a previous tutorial, I explained the new Data Collector and Management Data Warehouse feature in SQL Server 2008. The Data Collector is the part that is configured on each of the systems you want to monitor (which is why it is only available for SQL Server 2008) and it uses a series of SQL Server Agent Jobs and SQL Server Integration Services Packages to collect and store data about the performance of the system. It stores this locally in a file on each system, and then at a defined interval it loads that data up into the Management Data Warehouse, which I’m storing on my SQLCMS server.
The process for this, then, is quite straightforward. You simply set up the MDW database (using the Wizards explained in that previous tutorial) and specify the SQLCMS server as the storage unit. You then run the Wizards again on each SQL Server 2008 Instance (with the exception of SQL Server Express Edition, which doesn't have the Agent or SSIS installed) and point those to the SQLCMS server. The only caveat is that you should open each collection_set*_upload SQL Agent Job on these systems and stagger them by a few minutes or so. That way you won’t have everyone uploading their data to the MDW at the same time, and you’ll have a better performing SQLCMS.
You can also opt to collect data from the SQLCMS server as well, and that’s often a good idea. I normally only enable the disk collections, since that’s all I really care about.
I’ve presented various queries to deal with the MDW database in other places, but I’ll consolidate them here with comments to show you what they do. This example will show you the base tables and also a simple example of joining them to get database sizes and so on.
/* Title: MDW Queries.sql Purpose: Holds the MAPS queries if that database is used. Version: 1.0 Modification History: 04/30/2009 - Buck Woody - Initial Script Creation Requirements: 1. SQL Server 2008 2. The Management Data Warehouse feature from SQL Server 2008 to be installed and run once for each instance you wish to monitor. (Replace the MDW database name if you’re using something else) Optional: */ USE MDW; GO /* Base Information - More is here: http://msdn.microsoft.com/en-us/library/bb677306.aspx */ SELECT * FROM mdw.sys.schemas; GO /* Instances that are tracked */ SELECT * FROM mdw.core.source_info_internal --WHERE instance_name = 'ERP' /* The snapshots that are tracked against an instance */ SELECT * FROM mdw.core.snapshots_internal WHERE source_id = 1; GO /* The type of Collectors that the MDW collects */ SELECT * FROM msdb.dbo.syscollector_collector_types ct INNER JOIN mdw.core.supported_collector_types_internal ti ON ct.collector_type_uid = ti.collector_type_uid; GO /* A particular snapshot - this one is the disk information */ SELECT * FROM mdw.snapshots.disk_usage WHERE snapshot_id = 1 /* Database Sizes and Growths */ SELECT c.instance_name , a.dbsize / 102 AS 'DBSizeMB' , a.logsize , a.ftsize , a.database_name , a.collection_time FROM MDW.snapshots.disk_usage a INNER JOIN MDW.core.snapshots_internal b ON a.snapshot_id = b.snapshot_id INNER JOIN MDW.core.source_info_internal c ON b.source_id = c.source_id ORDER BY c.instance_name , a.database_name , a.collection_time ASC
You can, of course, report on all kinds of information stored in the MDW database using those base tables at the top of the queries. And you can extend the Data Collectors to get your own counters but that’s another tutorial.
The key now is to use the links in the Instance names in the MDW to link back to the SQL Server 2008 Central Management Server feature, which I documented in this series. Using those names, you can link up the MDW tables to show the MAPS data I've explained, or any other data that you collect. Because the Instance Name is the only element that is common across all of the features I’m using, I use it as a natural Primary Key something I rarely do in other applications. It’s not the best practice, but it’s not the end of the world, either. There will only be a relatively small (less than a few thousand) servers that you’ll probably track with this system, so there is a low risk of data corruption due to a natural Primary Key.
Using your own processes for performance collection for SQL Server Instances
Perhaps you don’t want to use the Data Collector for your SQL Server 2008 Instances, or perhaps you still have SQL Server 2005 (or even lower) versions in your organization that you want to monitor.
That’s fine you can still leverage the MDW feature to help you out with those collections. I recommend that you create the MDW database as a storage location for your data, so that in case you want to put your SQL Server 2008 Instances under Data Collection you can. I also advocate that you turn on the standard Data Collectors for the SQLCMS server (which is a SQL Server 2008 Instance), even if you subsequently disable them. If you do that, you’ll have access to the definitions of the data that they collect.
On my system, I’ve done that very thing. Then, I drilled down in SQL Server Management Studio (SSMS) to the Management node, and then expanded the Data Collection and then System Data Collection Sets node. From there I can see the three Data Collection Sets that were created using the Wizard I followed earlier, just as I described in my previous tutorial on the Management Data Warehouse feature.
Here’s where the payoff comes in. I can see what Microsoft is doing to collect data from SQL Server 2008, and I can use that information to collect it against “lower” versions of SQL Server, in particular SQL Server 2005. It’s like accessing the best minds at Microsoft for performance tuning advice.
Here’s how I do that (by the way, reverse engineering is normally frowned upon, but I checked with the team that owns this feature at Microsoft and they said in this case it’s OK):
I selected the Server Activity Data Collection Set, and then right-clicked it. From the menu that appeared, I selected Properties.
Recall that the Data Collection Sets can be made up of three kinds of actions: SQL Queries, Performance Monitor Counters, and SQL Traces. In this case, I see in the top panel that two of these were used: SQL Queries and Performance Monitor Counters.
I simply click on those items in the top panel, and the results are shown in the bottom panel. Now it’s a matter of copying out the data in the bottom and collecting it into the SQLCMS system into the MDW or other database.
So how do I do that? There are lots of options, from using SSIS to the Performance Monitor in Windows and that’s something I’ll explore in a future tutorial. Stay tuned!
InformIT Articles and Sample Chapters
If you’re not familiar with the Windows Performance Monitor, you’ll need some background for the next article in this series. Windows 2000 Performance Tools: Leverage Native Tools for Performance Monitoring and Tuning will get you started.
Books and eBooks
You don’t have to use any of this to have a great solution for managing and monitoring not only SQL Server but all kinds of products. Microsoft has a good solution called System Center, and System Center Operations Manager 2007 Unleashed is a great book on it. (Also available as a downloadable eBook and in Safari Books Online.)