SQL 2008 Management Datawarehouse Overview
Posted by rahmanagoro on April 13, 2011
With the release of SQL 2008, Microsoft has developed a warehouse framework for database administrators to have an idea of what is actually going in within SQL server. A lot of the times during my career, I have heard the phrase “The database was slow between 3AM and 4AM) This is especially true if you work within a global framework whereby the database server is serving requests 24/7. I would have users in the Asia pacific region using the database from around midnight GMT (UK time), the as they finish the UK users start around 8AM, and as they wrap up the US users kick in around 1PM BST. So it’s a never ending circle.
To buttress the point, management data warehouse gives me the high level and detailed view of what is happening on the database server, I like to use the waits and queues methodology to see what SQL server is waiting for, and to also keep tabs on my queues as well. If a process is queing up waiting for resources, then there must be a reason for it and as a database administrator, I want to check and understand why we have the waits and queues on the database server.
This post assumes that management data warehouse has already been installed on the database server, I am still running SQL 2008 RTM SP1 cumulative update 5 on my systems and till date, there is no way to remove management data warehouse once it has been installed. I installed management data warehouse on the dedicated DBA database on the production system, the database schema gets installed on the database as well.
To launch management data warehouse, follow the steps below.
Management data warehouse works by collecting information from a number of DMV’s and using connection points to upload the data onto the database and then store it within data warehouse style tables.
If you click on the memory graph, it drills down to show you the key memory counters. Here you can see things like page life expectancy, SQL server internal memory consumption by type
Reviewing waits, within management data warehouse you can check your wait statistics and further drill into what percentage the composition is.
Memory counters based on page life expentancy.
Disk IO statistics information can also be found below.
On the IO specific information, you can drill down on top queries based on duration, total IO, physical disk reads and also logical writes.