none
Management Data Warehouse RRS feed

  • Question

  • Hi,

    I am planning to create a GUI for monitoring the SQL performance other than the available activity monitor to get a much clear picture of whats running on the server. I came across the Management Data Warehouse set up in SQL server 2008 R2. Can anyone tell me the pros and cons of configuring it. My main concern is to capture the CPU, IO, Blocks, locks, memory issues in  MDW. Please advise.

    Thanks,

    Preetha

    Tuesday, April 22, 2014 5:30 AM

Answers

  • Hi Preetha, great question. The MDW is one of SQL Servers most underrated features.

    The MDW is a great feature for collecting SQL Server Performance data and analysis! See my post: SQL Server Management Data Warehouse: Why it Rocks

    What do you mean with regard to Pros and Cons of the MDWs configuration?

    You will need to make architecture/deployment design decisions dependant on the scale of the environment you wish to monitor. To give you an idea of the potential for the MDW, I use the MDW (four instances to be exact) successfully to monitor a customers environment that has thousands of SQL Server instances.

    With regard to the metrics you wish to monitor, all of these can be collected and reported on via the MDW.


    John Sansom | SQL Server MCM

    Blog | Twitter | LinkedIn | SQL Consulting

    Tuesday, April 22, 2014 6:17 AM
  • Hi,

    Check the Link

    http://technet.microsoft.com/en-us/library/dd939169(v=sql.100).aspx

                                                                                                      

    Best Practices and Caveats for Management Data Warehouse

    Here are some best practices to keep in mind:

    • Use a centralized server for the MDW database. This allows you to use a single point for viewing reports for multiple instances. You’ll need to be within the same domain for this to work.
    • When creating a trace collection set, be sure to set filters to collect just the data you need to diagnose the problem. For example, create a trace for a set of stored procedures that only collect data if the duration of the query exceeds a threshold. This way you don’t flood the MDW database with meaningless data.
    • Before adding a collection item with performance counters, make sure you thoroughly understand what the default Server Activity collection set is already collecting, so that you do not end up collecting the same data through multiple collection sets.

    Performance Considerations

    The costs associated with the MDW are mainly the costs of data collection and each collection set has its own process (an instance of DCEXEC.exe) performing the collection. To minimize the overhead of the collection processes, keep the following points in mind:

    • If you are collecting the results from multiple queries that have the same collection frequency, combine the queries into a single collection item. The XML parameters for a single T-SQL collection item can have multiple <Query> elements. Doing this will minimize the amount of memory that DCEXEC.exe uses during collection. Similarly, combine multiple performance counter collection items into a single collection item wherever possible.
    • Combine collection items into a single collection set whenever possible. The only reason for separate collection sets is if you need separate data retention periods or different collection schedules. For example, suppose you wanted to capture the output of 6 DMV queries. You want to keep the data from the first two queries for 6 months, but it is acceptable to purge the data from the remaining queries after just two weeks. You should not create a separate collection set for each query; two collection sets will capture this data with much less overhead. 
    • A collection set running in cached collection mode will always have a collection process running. If you collect data very frequently, this is more efficient than starting and stopping a new process every time new data must be collected. In contrast, a collection set in non-cached mode will not have a collection process running most of the time. A new process will be started at the scheduled collection and upload time; it will collect and upload data in a single step, then exit. If you are collecting data infrequently, non-cached mode is more efficient than leaving a generally-idle process running all of the time. As a general rule of thumb, if you will be capturing data once every 5 minutes or more frequently than this, consider using a collection set that runs in cached collection mode. If it will suffice to collect data less frequently than every five minutes, consider using non-cached collection mode, instead.
    • Collection frequency for cached collection sets can be as high as one execution every five seconds. You should be aware that more frequent collection has correspondingly high overhead. Always choose the lowest collection frequency that will meet your needs. 
    • One DCEXEC.exe instance can have a memory working set of 30 – 100 MB, depending on the items in the collection set.
    • Starting a new DCEXEC.exe process for non-cached collection or for data upload can take 10 seconds or more of wall clock time, much of it CPU-bound. In most scenarios this wouldn’t be noticed, but it could be have a more measureable impact if you have very little RAM (1GB or less), if you have many collection sets, or if you don’t follow the best practices listed above.

    Caveats and Limitations in the Current Version

    In the current version, removing data collection once it has been configured is not supported. In addition, you should not drop or change the name of the MDW database, as all of the jobs are based on the original database name.

    Summary and Conclusion

    The Management Data Warehouse in SQL Server 2008 is a powerful collection of components which provide an easy way to create a repository of performance-related information. We encourage you to start exploring the data collected using MDW, even if you are not an experienced SQL Server tuner. 

    The Management Data Warehouse consists of three components:

    • A set of custom data collectors, which are based on SQL Server Agent jobs that collect and store performance data
    • A database (called MDW, by default) for holding the data repository
    • A set of graphical drilldown reports for viewing the data collected

    Custom collectors can be added and custom reports can be developed and displayed to capture and present the data that is important to you and your troubleshooting efforts.

    You are welcome submit community comments about how you are using the data collected and what you would like to see in the product, as this can drive future development.


    Regards, PS


    Tuesday, April 22, 2014 9:04 AM
  • Hi,

    Other than MDW, is there any GUI to monitor blocks, memory, cpu, io in sql server 2008 r2?

    You may want to consider looking at 3rd party tools for monitoring.  Idera, Confio, and Redgate all have very highly rated products. The third party tools are all very robust, and in my opinion are generally worth the investment.  The nice thing about the MDW solution is that it's included with all the Microsoft SQL products (cost savings). 

    If you are just getting into monitoring metrics on your servers, I'd recommend trying MDW and seeing what additional features you require in your environment.

    Tuesday, April 22, 2014 10:33 AM

All replies

  • Hi Preetha, great question. The MDW is one of SQL Servers most underrated features.

    The MDW is a great feature for collecting SQL Server Performance data and analysis! See my post: SQL Server Management Data Warehouse: Why it Rocks

    What do you mean with regard to Pros and Cons of the MDWs configuration?

    You will need to make architecture/deployment design decisions dependant on the scale of the environment you wish to monitor. To give you an idea of the potential for the MDW, I use the MDW (four instances to be exact) successfully to monitor a customers environment that has thousands of SQL Server instances.

    With regard to the metrics you wish to monitor, all of these can be collected and reported on via the MDW.


    John Sansom | SQL Server MCM

    Blog | Twitter | LinkedIn | SQL Consulting

    Tuesday, April 22, 2014 6:17 AM
  • Thanks for the information John.By pros and cons, I meant the performance overhead by configuring MDW.

    Is there any other effective tool to see memory, cpu,io, locks of sql server 2008R2 instances in gui mode?

    Tuesday, April 22, 2014 6:20 AM
  • You should be able to collect the metrics for what you're after.  The MDW will help look at historic metrics, not real time.  Depending on query activity, your MDW database could grow very rapidly.  You'll likely need to fine-tune the collection sets after performing the initial setup to reduce down to just what you want/need.

    This YouTube video might help a little: Management Data Warehouse in SQL Server 2008 R2

    Tuesday, April 22, 2014 6:32 AM
  • You should be able to collect the metrics for what you're after.  The MDW will help look at historic metrics, not real time.  Depending on query activity, your MDW database could grow very rapidly.  You'll likely need to fine-tune the collection sets after performing the initial setup to reduce down to just what you want/need.

    Good points for consideration.

    It's worth highlighting that the MDW is a Data Warehouse, it figures then that it is going to need a reasonable amount of physical storage in order to be used effectively, specifically by retaining enough historical data for the needs of the environment. For some that might be 24 hours for others, days or weeks.

    My larger customer environments typically keep several days worth of historical performance data in the MDW itself and then use a custom archive of the data/snapshot of metrics for longer term historical performance reporting.

    Folks can often mistakenly interpret a storage requirements as a caveat rather than what it is, a design consideration of the overall solution deployment.


    John Sansom | SQL Server MCM

    Blog | Twitter | LinkedIn | SQL Consulting

    Tuesday, April 22, 2014 7:11 AM
  • Hi,

    Other than MDW, is there any GUI to monitor blocks, memory, cpu, io in sql server 2008 r2?

    Tuesday, April 22, 2014 7:32 AM
  • Hi,

    Check the Link

    http://technet.microsoft.com/en-us/library/dd939169(v=sql.100).aspx

                                                                                                      

    Best Practices and Caveats for Management Data Warehouse

    Here are some best practices to keep in mind:

    • Use a centralized server for the MDW database. This allows you to use a single point for viewing reports for multiple instances. You’ll need to be within the same domain for this to work.
    • When creating a trace collection set, be sure to set filters to collect just the data you need to diagnose the problem. For example, create a trace for a set of stored procedures that only collect data if the duration of the query exceeds a threshold. This way you don’t flood the MDW database with meaningless data.
    • Before adding a collection item with performance counters, make sure you thoroughly understand what the default Server Activity collection set is already collecting, so that you do not end up collecting the same data through multiple collection sets.

    Performance Considerations

    The costs associated with the MDW are mainly the costs of data collection and each collection set has its own process (an instance of DCEXEC.exe) performing the collection. To minimize the overhead of the collection processes, keep the following points in mind:

    • If you are collecting the results from multiple queries that have the same collection frequency, combine the queries into a single collection item. The XML parameters for a single T-SQL collection item can have multiple <Query> elements. Doing this will minimize the amount of memory that DCEXEC.exe uses during collection. Similarly, combine multiple performance counter collection items into a single collection item wherever possible.
    • Combine collection items into a single collection set whenever possible. The only reason for separate collection sets is if you need separate data retention periods or different collection schedules. For example, suppose you wanted to capture the output of 6 DMV queries. You want to keep the data from the first two queries for 6 months, but it is acceptable to purge the data from the remaining queries after just two weeks. You should not create a separate collection set for each query; two collection sets will capture this data with much less overhead. 
    • A collection set running in cached collection mode will always have a collection process running. If you collect data very frequently, this is more efficient than starting and stopping a new process every time new data must be collected. In contrast, a collection set in non-cached mode will not have a collection process running most of the time. A new process will be started at the scheduled collection and upload time; it will collect and upload data in a single step, then exit. If you are collecting data infrequently, non-cached mode is more efficient than leaving a generally-idle process running all of the time. As a general rule of thumb, if you will be capturing data once every 5 minutes or more frequently than this, consider using a collection set that runs in cached collection mode. If it will suffice to collect data less frequently than every five minutes, consider using non-cached collection mode, instead.
    • Collection frequency for cached collection sets can be as high as one execution every five seconds. You should be aware that more frequent collection has correspondingly high overhead. Always choose the lowest collection frequency that will meet your needs. 
    • One DCEXEC.exe instance can have a memory working set of 30 – 100 MB, depending on the items in the collection set.
    • Starting a new DCEXEC.exe process for non-cached collection or for data upload can take 10 seconds or more of wall clock time, much of it CPU-bound. In most scenarios this wouldn’t be noticed, but it could be have a more measureable impact if you have very little RAM (1GB or less), if you have many collection sets, or if you don’t follow the best practices listed above.

    Caveats and Limitations in the Current Version

    In the current version, removing data collection once it has been configured is not supported. In addition, you should not drop or change the name of the MDW database, as all of the jobs are based on the original database name.

    Summary and Conclusion

    The Management Data Warehouse in SQL Server 2008 is a powerful collection of components which provide an easy way to create a repository of performance-related information. We encourage you to start exploring the data collected using MDW, even if you are not an experienced SQL Server tuner. 

    The Management Data Warehouse consists of three components:

    • A set of custom data collectors, which are based on SQL Server Agent jobs that collect and store performance data
    • A database (called MDW, by default) for holding the data repository
    • A set of graphical drilldown reports for viewing the data collected

    Custom collectors can be added and custom reports can be developed and displayed to capture and present the data that is important to you and your troubleshooting efforts.

    You are welcome submit community comments about how you are using the data collected and what you would like to see in the product, as this can drive future development.


    Regards, PS


    Tuesday, April 22, 2014 9:04 AM
  • Hi,

    Other than MDW, is there any GUI to monitor blocks, memory, cpu, io in sql server 2008 r2?

    You may want to consider looking at 3rd party tools for monitoring.  Idera, Confio, and Redgate all have very highly rated products. The third party tools are all very robust, and in my opinion are generally worth the investment.  The nice thing about the MDW solution is that it's included with all the Microsoft SQL products (cost savings). 

    If you are just getting into monitoring metrics on your servers, I'd recommend trying MDW and seeing what additional features you require in your environment.

    Tuesday, April 22, 2014 10:33 AM