By Haidong Ji
The New Performance Monitoring and Troubleshooting Using Management Studio webcast gives you a great walkthrough of the new features of SQL Server 2008. SQL Server 2005 introduced Dynamic Management Views (DMVs), which greatly enhanced DBA’s understanding of the internal working of the database engine. Unfortunately, this data will be lost every time SQL Server 2005 instance is bounced. This does not change in SQL Server 2008. However, SQL Server 2008 provides the capabilities to store this data in a database. It also provides various reports to analyze this data for better monitoring and troubleshooting. This webcast really did a good job of demonstrating and educating to end users this great feature.
The webcast starts off with a clear explanation of the overall performance data collection architecture. I always like to know how different pieces fit together, so the presenter really gives us a good overview here.
Going one step further, the presenter talks about various databases and processes involved in gathering performance data.
The demo is terrific. SQL Server 2005 has great server status reports. SQL Server 2008 really kicks it up a notch. I like how you can collapse and expand, or drill down to details when you like. The demo gives a pretty good understanding of how the reports are related to each other.
Pay attention to the demo where Query Plan details were displayed. I know from experience this will be very helpful for performance tuning.
TIP: Use Query Plan Details to get more information on a specific query type, such as times executed, plan changes, etc.
I highly recommend the New Performance Monitoring and Troubleshooting Using Management Studio webcast to anybody who is interested in performance tuning and monitoring aspects of SQL Server 2008.