ballardchalmers - home

Contact us 01342 410223

Blog

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.

Monitoring Queries With The Management Data Warehouse 1

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: Server Management with Utility Control Points

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.

 

Monitoring Queries With The Management Data Warehouse 2

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:

 

Monitoring Queries With The Management Data Warehouse 3

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:

Monitoring Queries With The Management Data Warehouse 4

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

Monitoring Queries With The Management Data Warehouse 5

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

Monitoring Queries With The Management Data Warehouse 6

 The Disk Usage reports show disk usage per database:

Monitoring Queries With The Management Data Warehouse 7

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

Monitoring Queries With The Management Data Warehouse 8

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

Power View Business Inteligence For Power Users 9

 

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

Monitoring Queries With The Management Data Warehouse 10

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

By Geoff Ballard, Chief Technical Officer

Have a comment? Let us know below, or send an email to enquiries@ballardchalmers.com

Comment on this article

About

Ballard Chalmers is one of the UK’s leading enterprise software engineering companies, specialising in development for the Microsoft Enterprise Application Platform. We are principally dedicated to software development and integration for the Microsoft Cloud, as well as on-premises and hybrid systems. Our expertise is in Microsoft .NET, SQL Server, BizTalk Server and SharePoint Server.

Let's talk

  01342 410223

Find out more

Thank you for contacting us.

We will be in touch with you shortly.