SQL Server 2005 introduced dynamic management views (DMV) and functions (DMF), a mechanism to allow you to look at the internal workings of SQL Server using TSQL. They allow you an easy method for monitoring what SQL Server is doing and how it is performing. They replace the need to query the system tables or using other awkward methods of retrieving system information that you had to use with SQL Server 2000.
To retrieve information from DMVs and DMFs, you have plenty of choices:
To learn about DMVs and DMFs, you can refer to:
If you're not familiar with T-SLQ or you're looking for a nice user-interface to browse the information provided by the DMVs, you have two report-based solution ready to be used:
As Standard Reports, they retrieve information mainly from DMVs and DMFs, but they allow you to navigate and drill down through the information provided, from an initial Dashboard that provides a high-level overview on how your system is performing.
To try them out, visit: http://www.microsoft.com/downloads/details.aspx?FamilyId=1D3A4A0D-7E0C-4730-8204-E419218C1EFC&displaylang=en
Well, so now, what to add on top of that?
Well, what happens is that DMVs and DMFs retrieve real time information, which then is stored in memory. As long as you'll restart your SQL Server instance, you will be going to loose all the information which you have collected, therefore you're going to loose the information that you can visualise through Standard Reports and Performance Dashboard Reports.
You can still save the information you're collecting periodically into a table, and then modify the Performance Dashboard Reports through SQL Server 2005 Business Intelligence Development Studio... But why to reinvent the wheel?
The Microsoft SQL Server Development Customer Advisory Team has developed a tool, DMV Stats, which collects performance oriented DMVs into a data warehouse, and provides a methodology called 'Waits' and 'Queues' to identify and track down performance issues. Drill-through analysis is provided by reporting services reports.
Well, something to try out...
For more information visit: http://blogs.msdn.com/sqlcat/archive/2007/07/13/performance-analysis-tool-dmvstats.aspx
- Beatrice Nicolini -