SQL Server Monitoring – Query statistics

This blog is part of the blog series named SQL Server Monitoring.

Purpose

To get a baseline of the number of logical reads that the server handles, as well as the amount of elapsed and worker time.

Sources

This feature queries the 3 DMV’s sys.dm_exec_query_stats, sys.dm_exec_sql_text and sys.dm_exec_query_plan which returns aggregate performance statistics for cached query plans in the SQL Server and if available also the SQL statement as well as the query plan.

Report

The report has in the upper part a table showing the collected statements per database, and in the lower part there are 2 line charts, the left showing the amount of logical reads, and the right showing the amount of elapsed and work time in mS.

Model

The model for the report uses the facts [Query statistics] and [Query statements], and the dimensions [Date], [Time] and [Query statistic].

Configuration

The default configuration is to collect the top 1000 query statements with regards of TLT (total logical reads).

Scheduling

The default schedule for the feature is every 15 minutes (900 seconds).

Retention

The default retention for the table dbo.fhsmQueryStatement is 4 days and for the table dbo.fhsmQueryStatistics the retention is 30 days.