SQL Server 2008 R2 Performance Monitoring

Posted on January 19, 2011 by mssqldude

In case you are not aware, there are several tools that SQL Server 2008 R2 provides for you as a DBA to use that allow you to monitor performance on your databases. I’ve been talking a lot about these to customers recently and will touch on this next week at the South Jersey SQL Server User group: http://bit.ly/gtponF.
Some of these tools are just there and you may not ever open them or you may be on a previous version of SQL Server.
1. Management Data Warehouse
This one has gone through a few revisions and is an excellent part of the SQL Server 2008 R2 toolset that you can configure to run on your SQL Server simply by right-click and selecting “Configure Management Data Warehouse” on the Data Collector sets in your instance “Management” folder. The data collectors are configurable and will collect stats, typically at 15 minute intervals, across your instance databases for queries, server stats and disk stats. The MDW will also manage the archival of data history such that you can configure how much history to maintain online. Since this is a “data warehouse” and is based on data collectors that activate at set intervals, or on requested execution, the MDW is very good at providing history and baselines. In addition, the reports are all based on SSRS and include a rich set of reports out of the box that include drill-down to query text and query plans. Since these are SQL Server Reporting Services based performance reports for SQL Server, you can create your own MDW reports in SSRS as custom reports.

2. Utility Control Point
UCP is brand new in SQL Server 2008 R2 and with a single Data Center edition of the database, you can stand-up a monitoring server using just SQL Server and stay within SQL Server Management Studio. There are interactive dashboards that allow you to monitor SQL Server 2008 R2 databases using the data collectors mentioned above in MDW also within SSMS, making both of these closely related to each other and very easy for a SQL Server DBA because you can stay within your Management Studio environment.
In both the case of MDW reports and UCP, data is collected and pushed into a data warehouse and you should be cognizant of a possible hit to performance as well as data storage with all of these counters that are collected, processed and stored. UCP is a good candidate to stand alone in your environment and manage the servers centrally here.

3. Activity Monitor
Another tool that has gone through many SQL Server revisions, the SQL Server 2008 R2 version of Activity Monitor. Like MDW, you can get performance stats wih query details in the activity monitor. But unlike data collector driven tools like MDW & UCP, Activity Monitor produces real-time results in areas such as waits, processes and expensive queries. And unlike a data warehouse, the data is not persisted. The monitoring begins when you open Activity Monitor and ends when you close it.

Fonte:  mssqldude