WikiDBA

It’s a very common and sometime frustrating/irritating task for a DBA to trouble shoot SQL Server performance issues. With SQL server 2008 a very useful concept i.e. Management Data Warehouse – MDW introduced for continuous performance monitoring. MDW has an API that allows recording of performance counters from components outside of SQL Server parallel to the recording of built-in SQL Server and Operating System performance counters. MDW captures every standard performance counter traditionally used for performance analysis. MDW captures key Windows Performance Monitor (PerfMon) counters and internal SQL Server counters automatically into a SQL Server database. The collected data is being stored in SQL server Database. The following is the illustration of MDW.

Server installation installs MDW automatically. Let’s activating MDW step-by-step in SQL server 2008.

1. In SQL Server Management Studio (SSMS), open Management, Data Collection and then right-click and select Configure Management Data Warehouse.
   
2. It will show a welcome screen, Select [Next], Now “Select configuration task” dialog will be appear, Select “Create or upgrade a management data warehouse” and then click [Next].
   

    

3. On the Configure Management Data Warehouse Storage screen, click [New].
   

    

4. When the [New Database] dialog box appears, enter DB Name and set your prefer location of this DB. Click [OK].

    

5. The [Configure Management Data Warehouse Storage] dialog will re-appear, click [Next].
   

    

6. The [Map Login and Users] dialog will appear. If you are an administrator, there is no need to add a mapping, otherwise add assign a login/user. Click [Next].
   

    

7. On the [Complete the Wizard] dialog, click [Finish].
   

    

8. Next, the [Configure Data Collection Wizard Progress] dialog. When it completes, click [Close].
   

    

9. Now a new database (as per given name) has been created. In SSMS it will be shown.
   

    

10. Now we have created a MDW database, which will be used to store other server’s data for monitoring purpose, Let we have 3 server named as Server A, Server B and Server C as per above MDW illustration, we want to capture data from all these 3 servers, for same we have to follow these steps on each server from point 10 to 12.

    Now connect Server A, in SSMS, go to Management, Data Collection and then right-click and “select Configure Management Data Warehouse menu” item. On welcome screen, select [Next], This time, however, “select Set up data collection” and click [Next].
    

     

11. Now on “Configure MDW Storage”, Select your server name and DB name that is the host for MDW.

     

12. Click on [Next], then click [Finish], then Close.

    The same thing should be repeated on all servers which needs to be monitor.

     

    Note: Its observation, if all servers are in a domain, there should be an AD account to start SQL agent services on each server which are being monitored.

     

    Go to your MDW database , right click on it, Select “Reports”, then “Management Data Warehouse” then “Management Data Warehouse overview”, all servers will be displayed and now we will able to monitor server’s “Server Activity”, “Query Statistics” and “Disk Usage” will drill down reporting facilities.