Updated August 29, 2021.
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.
Reports
The report page Query Statistics has 2 line charts, one showing the amount of logical reads, and the other showing the amount of elapsed and work time in mS.
The x-axis for the charts is at the day level.
Also, there is a table showing the logical reads, worker time, execution count and SQL statement, helping to determine if index tuning could help to lower the amount of logical reads processed.
When hovering over the charts tooltips are showing detailed insight by showing the top 5 databases with regards of number of logical reads, and elapsed and worker time in mS.
Logical reads tooltip
Elapsed and worker time tooltip
Model
The model for the report uses the facts [Query statements] and [Query statistics], and the dimensions [Query statistic] and [Date].
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.
