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.
