Page Writes by Query

Overview

The Page Writes by Query report in Database Health Monitor helps identify SQL queries that generate the highest number of page writes within a specific database. This report is essential for performance tuning, as excessive page writes can lead to disk I/O bottlenecks, increased transaction log usage, and slower query execution.

Understanding the Report

1. Pie Chart Visualization

  • The pie chart displays a visual breakdown of queries contributing to page writes.
  • Larger slices indicate queries with higher write activity, potentially highlighting inefficient operations such as excessive DELETEs, INSERTs, UPDATES, or SELECT INTO statements.
  • Each slice is labeled with the total page writes associated with that query.

2. Query Breakdown (Tabular Data)

Below the pie chart, a detailed table lists the queries in descending order of page writes.

  • Columns:
    • Row – The ranking of the query based on total page writes.
    • Total Writes – The total number of page writes generated by the query.
    • Query – The SQL statement responsible for the writes.

Interpreting the Report

High Page Write Queries to Watch For:

  1. DELETE Statements:
    • Large DELETEs (especially without indexing or partitioning) can trigger excessive page writes and escalate transaction log growth.
    • Example in the screenshot: DELETE TOP (1000) h OUTPUT deleted.* INTO quickScanArchive.dbo.quickScanArchive FROM quickScanHistory h
    • Solution: Consider batching DELETEs, using partitioned tables, or archiving old data efficiently.
  2. INSERT Statements:
    • Queries inserting large amounts of data can cause a spike in page writes.
    • Example in the screenshot: INSERT INTO quickScanHistory (id, quickscan) VALUES(@id, @quickScanData)
    • Solution: Optimize by reducing transaction size, using minimal logging, and ensuring indexes are properly maintained.
  3. UPDATE Statements:
    • Updates that modify large datasets or indexed columns can cause excessive page writes.
    • Example in the screenshot: UPDATE quickScanHistoryXml SET [quickscan] = @xml WHERE [id] = @id
    • Solution: Consider narrowing the WHERE clause, using computed columns, or evaluating the necessity of the update.
  4. SELECT INTO:
    • Queries using SELECT INTO #tempTable may result in significant writes to tempdb, especially when used inefficiently.
    • Example in the screenshot: SELECT * INTO #runningQueries FROM master.sysprocesses
    • Solution: Use explicit temporary tables, optimize joins, and avoid unnecessary data duplication.

Optimization Strategies

  • Batch Operations: Instead of large DELETEs/INSERTs, process data in smaller chunks to minimize transaction log impact.
  • Index Optimization: Ensure indexes are maintained and optimized to reduce unnecessary writes.
  • Partitioning: Consider table partitioning for large datasets to manage writes more efficiently.
  • Minimal Logging: Use simple recovery mode (if appropriate) and take advantage of bulk-logged operations where possible.
  • Monitoring tempdb Usage: Reduce unnecessary SELECT INTO queries that heavily use tempdb.

How Stedman Solutions Can Help

If your SQL Server is experiencing high disk I/O, slow query performance, or transaction log bloat, our Managed SQL Server Services can help. We specialize in query tuning, performance optimization, and database maintenance.

Learn More About SQL Server Managed Services

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