Share via

SQL 2005 Standard Reports, SQL 2005 Performance Dashboard Reports and... DMVStats

  • Article

 

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:

 

  • T-SQL

 

To learn about DMVs and DMFs, you can refer to:

https://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx

 

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:

 

  • SQL Server 2005 Standard Reports: In SQL Server 2005 there is a set of pre-made reports in Management Studio that can help database administrators gather information in many common areas. There are reports for CPU, memory, disk, sessions, queries, transactions, and more baked right into Management Studio. 

 

  • If you have SQL Server 2005 CTP2, you can download and install SQL Server 2005 Performance Dashboard: a custom set of reports (custom reports are a new feature of Service Pack 2) for Management Studio.

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: https://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: https://blogs.msdn.com/sqlcat/archive/2007/07/13/performance-analysis-tool-dmvstats.aspx

 

- Beatrice Nicolini -