Monitoring Queries with the Management Data Warehouse

The Management Data Warehouse (MDW) was introduced in SQL Server 2008 and is available in later versions.

The architecture is simple but very effective. There is a central data warehouse database, and data collectors on each server that is being monitored. The data collectors collect information about the server, and the queries that are executed on it, and store them in the data warehouse. The process has been designed to be lean with results cached locally and then bulk loaded to the database server. A series of reports are then provided for the data warehouse, these show charts of resource usage, such as disk and CPU, but more importantly they show information about locks and queries over time. Most impressive is that you can even see the query plan of a query that ran a week ago.

The techniques involved in locating and tuning queries is not changed by the MDW but it is made a lot more efficient, because we have history of events provided for us.

To enable the DMW right click on Data Collection and select the Option to Configure Management Data Warehouse. These lets you select a server and either select, or create a new, database. Note the database exists on one server.

Then start one or more data collectors on each server. There are 4 of these as standard:

·         Disk Usage: Tracks disk space usage

·         Query Statistics: Tracks queries

·         Server Activity: Tracks resources such as CPU

·         Utility Information: Don’t start this as it is used by Utility Control Points (for more details see: http://sqlserver-ballardchalmers.blogspot.co.uk/2012/06/server-management-with-utility-control.html)

You can create your own data collectors as well if you wish.

To start a collector right click it and select Start Data Collection Set.

Each set has its own schedule, which is 15 minutes for the Query Activity. If you wish you force a data load immediately using the Collect and Upload Now menu.

The data collectors create SQL Agent jobs that execute a program called DCEXEC, which uses SQL Server Integration Services to load the data into the database:

Once some data has been loaded the reports are available for use. Right click on the MDW database and select the Overview report to gain access to the reports for all servers:

Alternatively right click on Data Collection to gain access to reports for just the one server:

The Server Activity reports are as expected and show changes in CPU usage over time etc:

The Disk Usage reports show disk usage per database:

The Query Statistics reports are particularly interesting. At the top level they show the worst performing queries:

Clicking a particular query drills through to the SQL plus the query statistics:

Clicking through further you eventually end up with the query plan for the query:

And all this is tracked for the worst performing queries over the last 14 days (configurable).