Performance Studio – New monitoring tool in Sql Server 2008
“Performance Studio” is a new tool available natively starting Sql Server 2008.
In Sql Server 2005 days, we had a tool (plugin) called “Performance Dashboard” (SP2 and later only) that relies on DMV & DMF to show the performance statistics of Sql Server almost real time. Its reports would query the DMV’s real time and display the data in nice graphs and tables. It was quite a useful, but one feature that I noticed missing and very badly wanted was data retention. It does not retain data for trend-analysis, it focusses only on how the server is performing as of now. But if you wanted to compare today’s statistics with yesterday or a month ago, you are out of luck. There is no built-in way to do that (or I did not know). But overall ‘Performance Dashboard’ is a great tool. If any one needs it for their Sql Server 2005 SP2 environment, it is available for download here
Now, Microsoft made the next version much better. Its not a plugin anymore. It’s part of the product itself and it can monitor multiple server at the same time (and the graphs are much prettier too). All data is stored into a dedicated database called as Management Data Warehouse (MDW), a relational database with three schemas, i.e. Core, Snapshots and Custom_Snapshots.
‘Performance Studio’ is a framework that ties together collection, analysis, troubleshooting and persistence of SQL Server diagnostics information. It has tools for
- Low overheard data collection
- Performance Monitoring, troubleshooting and tuning.
- Persistence of diagnostics data
- Reporting
In simple terms, this tools allows you to capture data from “PerfMon stats”, DMV’s, and other T-SQL (i.e. Top 10 queries by CPU, disk, etc) and store in one central location. The components that perform the gathering of these metrics (BufferCacheHitRatio, Top 10 Queries, etc) are called ‘Collection Sets’. In each collection set you have a bunch of such counters grouped into one. All of these counters are captured together and stored. The central location where all the data is store is called Management Data Warehouse (MDW).
To enable this collection, go to ‘Management’ >> ‘Data Collection’ >> right click >> ‘Configure Management Data Warehouse’. First you want to select ‘Create or upgrade a Management Data Warehouse’, then follow the wizard. Its fairly intuitive.
Performance Studio Setup
Again go to ’Management’ >> ‘Data Collection’ >> right click >> ‘Configure Management Data Warehouse’ and this time select ‘Setup Data Collection’. This is where we set up the source server that you want to monitor. Cache is a temporary location on your disk where all the transient data is stored until it is uploaded into MDW database.
Collection provided by default are:
- Disk Usage
- Query Statistics
- Server Activity
1. Disk Usage Collection Set: Monitors disk usage for each database
- Collects and uploads every 6 hours
- Retains data for 90 days
- Stores the collected data in these tables: ‘Snapshots.disk_usage’ & ‘Snapshots.log_usage’
Query Statistics Collection Set: Records interesting queries
- Collects and uploads every 15 minutes
- Gathers data for the top 3 interesting queries based on highest values for max_worker_time, max_physical_reads, max_logical_reads and many more factors [refer to the Webcast below for more info].
- Gathered data is loaded into ‘shapshots.query_stats’, ‘snapshots.notable_query_text’, ‘snapshots.notable_query_plan’ tables
Server Activity Collection Set: Tracks waits states, memory and performance counters.
- Collects data every 10/60 seconds and uploads that data to the MDW database every 15 minutes.
- Retained for 14 days
- Gathers data from:
- Active Sessions and Requests (every 10 seconds)
- Memory (every 60 seconds)
- Performance Counters (every 60 seconds)
- Wait Statistics (every 60 seconds)
- Schedulers (every 60 seconds)
More points to note:
- You can create your own “Collection Sets” to collect custom data.
- You can have just one “Data Collector” to collect data from several servers.
- Set up this collector (and MDW database) on a dedicated server or a server separate from the servers you are trying to monitor.
- Plan to allow the disk usage to grow approximately at 250 – 350 MB per day.
- Collects only from Sql Server 2008 and beyond (does not collect data from Sql Server 2005 or 2000).
- Cache location is a directory configured during set up. This is where all the data is stored until it is time to upload the data into MDW database.
Reports:
To open reports, you want to go to ‘Management’ >> ‘Data Collection’ >> right click >>’Reports’ >> ‘Management Data Warehouse’ >> select a category.
Open Performance Studio Reports
A Sample report:
Performance Studio Sample Report
I would encourage everyone to watch this Technet webcast on this new feature.