Page Reads by Query

The Page Reads by Query report in Database Health Monitor provides a graphical and tabular breakdown of the SQL queries that generate the highest number of page reads within a specific database.

Understanding the Report

  1. Pie Chart Visualization:
    • The pie chart visually represents the distribution of page reads across different queries.
    • Larger slices indicate queries with higher page read counts, which may signal inefficient queries or missing indexing.
    • The numerical labels on each slice indicate the total page reads associated with a particular query.
  2. Query Breakdown (Tabular Data):
    • Below the chart, a detailed table lists queries ordered by Total Reads.
    • The columns include:
      • Row: Rank of the query by page reads.
      • Total Reads: The number of logical page reads generated by the query.
      • Query: The SQL text of the query, often revealing inefficient patterns such as missing indexes, large table scans, or excessive XML parsing.

Key Takeaways from the Report

  • Identifying Performance Bottlenecks: Queries with high page reads may be reading more data than necessary, leading to performance issues.
  • Detecting Inefficient Queries: A query performing a full table scan or expensive XML parsing (as seen in the screenshot) may be a candidate for optimization.
  • Indexing Opportunities: If certain queries appear repeatedly with high page reads, missing or fragmented indexes might be the cause.
  • Optimizing Query Structure: Reviewing these queries helps identify opportunities to rewrite SQL, use indexed views, or partition large tables.

How to Use This Report for SQL Optimization

  • Check Execution Plans: For queries with high page reads, analyze the execution plan to determine whether table scans or index scans are causing the issue.
  • Index Tuning: Use Database Health Monitor’s Index Usage reports to identify missing or unused indexes.
  • Query Optimization: Rewrite queries to use better filtering, JOIN strategies, or temp tables when necessary.

Next Steps

If your SQL Server is suffering from slow queries or high resource consumption, Stedman Solutions’ Managed Services can help with query tuning, performance optimization, and index management. Learn more here:
SQL Server Managed Services

For proactive SQL Server health monitoring, download Database Health Monitor:
Database Health Monitor