Use SQL Server Dynamic Management Views and Functions (DMV and DMF)

Microsoft SQL 2005/2008 provides access to Dynamic Management Views and Functions (DMV and DMF). We can use this data to diagnose problems and optimize database performance.
Here is a partial list of available information:

• Causes of query execution delays.
• Work with indexes (missing, unused, requiring the most input and output operations, often used).
• Requests with high I / O overhead, high CPU usage.
• Queries running most often.
• Requests suffering from locks.

A full description of all functions can be found in MSDN - Dynamic Administrative Views and Functions (Transact-SQL) . For those who have not yet encountered requests for DMV - I recommend reading the article from the MSDN magazine for January 2008: " Opening hidden data to optimize application performance. " It is quite extensive, contains general information and a large number of ready-made frequently used queries to the DMV.
Next, I want to talk about how you can save and analyze query data to the DMV.

Terms and Definitions
DMV, DMF - Dynamic Management Views and Functions. Return server status data that can be used to monitor server instance health, diagnose problems, and tune performance.
Storage - a database for the accumulation of information obtained by queries to the DMV and DMF for further analysis.

Automatic collection and analysis of DMV and DMF data

I will describe, in my opinion, the biggest drawbacks of working with DMV through simple SQL queries:

1. DMV data is contained in memory and begins to accumulate again after each server restart. In addition, they are zeroed for some objects due to various changes in the structure of tables or indexes.
2. A large information system gives an uneven load on the database. Some of the functionality can be used rarely and therefore some database objects may not be included in the DMV or incorrectly taken into account. For example, it can be a daily download of a large amount of data or a monthly report. In addition, it is often necessary to compare the figures before and after the changes. This is difficult to do without having on hand historical data from DMV and DMF.
3. It is not always possible to work directly with the database, i.e. when you can make arbitrary queries to the DMV.

The following tools can be used to solve these problems.

DMVStats

A SQL Server 2005 Dynamic Management View Performance Data Warehouse
The project's address at CodePlex: http://sqldmvstats.codeplex.com
')
This application collects information from DMV and DMF in its database and provides a set of ready-made SQL Reporting Services reports for analysis. To collect data, use the job (jobs) SQL Server Agent.
List of ready reports:

Analyze Block Info Executing Requests and Waiting Tasks Index Definitions Index Operational Stats Top IO Queries

Top Worker Queries Virtual File Stats Wait stats Drill-through Guidance Alert

Installation and configuration
Instructions for installation, configuration and use: DMVStats.doc . In short, the installation process is automated with two scripts. The InstallDMVStatsDBProcs.cmd script creates the repository database, the necessary sp procedures and installs the SQL Server Agent job. The DeployReports.cmd script is required to add reports to MS SQL Reporting Services.
The application is managed and configured using reports on SQL Reporting Services.


To change the task time, to enable or disable it, there are ready-made scripts in the Util directory of the application archive.

Note (not reflected in the documentation).
If after the installation the “Rules” report is empty (rules for selecting performance counters), then you need to replace the names in the DatabaseScripts / ConfigDMVstatsDB.sql script to insert them into the DMVconfig.include_perf_counters table. For example, instead of 'SQLServer: Locks', you must specify 'MSSQL $ SQL2005: Locks', where SQL2005 is the name of your SQL instance. You can see the full correct name in the sys.dm_os_performance_counters view. In the same place, by analogy, you can add to the DMVconfig.include_perf_counters table the additional performance counters you need from the sys.dm_os_performance_counters view. After that, rerun the corrected SQL script.

General impressions
The project is distributed with documentation, contains convenient scripts for installation and configuration. Using MS SQL Reporting Services allows you to analyze data remotely through the website and use the built-in features of automatic construction and distribution of reports on a schedule. The downside is that not all reports are convenient, some are formed for a very long time. A rather convoluted architecture is used with the integration of a set of snapshots of data into so-called sets (Baselines), while filters on these sets are not installed in the reports.

Sample report "Analyze Block Info".



It is also disappointing that DMVStats was last updated on July 5, 2007. But in spite of all this, it covers a wide range of tasks for automating the storage and analysis of DMV data. Overall, DMVStats is a good tool for monitoring and diagnosing database problems for an administrator or developer.

Miracle SQLSTAT2005

SQL SERVER 2005 performance data warehouse
The project address on CodePlex: http://sqlstat2005.codeplex.com

The SQLSTAT2005 project also has its own database, which is populated with DMV and DMF data on a SQL Server Agent job schedule. For data analysis, reports are used for SQL Management Studio.

Current implementation features:

• Collecting and saving DMV and DMF data in its database, setting up a schedule and the amount of requested data.
• Reports on the execution of individual requests and request packets (information about the request, its text, plan, information on input-output).
• Reports on the use of resources of the processor, memory and disks.
• Reports support drilling (drill-down), i.e. from the batch of requests, you can view all the requests, and for specific requests you can already see information about the execution time, the used plan, I / O operations.
• All versions of SQL Server 2005 are supported, including Express.

Installation and configuration
Installation, Configuration, and Usage Instructions: Miracle SQLSTAT2005.docx
In short, we simply restore the database from the SQLSTAT2005_v1.bak archive, create the SQL Server Agent job with pens, and add the start report Reports / SQLSTAT2005_StartHERE.rdl from the archive to SQL Server Management Studio.

You can add a Custom Report in SQL 2005 version not lower than SP2.

The process of adding reports.


An example of one of the reports "SQLSTAT2005 Performance (Statement)". It shows 30 longest requests.


General impressions
The storage structure is almost one-to-one replicates DMV tables, only a prefix for tables and several service columns (data snapshot ID and number of records captured) are added. This is useful when writing additional queries to this database. Data from DMF, such as texts and query plans, is also stored in the corresponding tables. A simple principle of working with reports. When you run a start report, you must select the starting and ending snapshot of data from the list. After that, all SQLSTAT2005 reports will be available for the selected time period. The reports are installed locally for Management Studio, on the remote server you only need to create a warehouse database and configure the collection task for the SQL Server Agent.

Additional Information

The DMVStats and SQLSTAT2005 projects facilitate the collection and analysis of information presented to SQL Server through DMV and DMF. If to compare them, on the one hand DMVStats - it is easier to install, configure, and contain more ready-made reports, but is not being developed at the present time. On the other hand, SQLSTATS2005 is made easier and is in development. If development and support of SQLSTATS2005 is continued, in the future it will be a more convenient tool for work.

In addition to these tools, you yourself can use the approach used in them. This is the creation of your own tables and a task that, according to a schedule, collects data from DMV and DMF for storage in your tables for further analysis. To create SQL queries, you can use diagrams that show the relationships between system tables and administrative views: SQL Server 2005 System Views Map and SQL Server 2008 System Views Map .

I also want to mention another solution: SQL Server 2005 Performance Dashboard Reports . This is an extension for SQL Server Management Studio that allows you to build reports based on SQL server service data. Reports work directly with system tables, views, and functions.
List of reports from SQL Server 2005 Performance Dashboard Reports:

Performance Dashboard; Blocking; Buffer IO; Buffer Latch; Databases Overview; Expensive Queries; General Waits; Historical IO; Historical Waits; Latch waits;

Missing Indexes; Missing Indexes - XML ​​Showplan; Page Details; Plan Guide; Query Plan; Recent CPU Consumption; Requests Overview; Session Details; Sessions Overview; Traces;

Sample start report "Performance Dashboard" from SQL Server 2005 Performance Dashboard Reports.

Detailed information about Performance Dashboard Reports can be obtained by the links:
SQL Server 2005 Performance Dashboard (Eng) - Part 1 , Part 2 , Part 3 .

In this article, I talked about the existing methods and tools for working with the service data provided by MS SQL Server 2005/2008.

1. The SQL source code for querying the article " Opening hidden data to optimize application performance. "
2. DMVStats - A SQL Server 2005 Dynamic Management View Performance Data Warehouse.
3. Miracle SQLSTAT2005 - SQL SERVER 2005 performance data warehouse.
4. SQL Server 2005 Performance Dashboard Reports