SQL Server scans the buffer pool during startup, backup, restore and DBCC, and large memory servers feel it most. An extended event records the scans, shows which commands start one and how many tasks each uses. The last sections explain what SQL Server 2022 changed.
SQL Server 2022 – Thread Management for Performance Improvement
I had a great discussion about Thread Management for Performance Improvement with my client, who recently hired me for Comprehensive Database Performance Health Check.
SQL Server 2022 Performance – Buffer Pool Scan
One aspect that can significantly impact performance is scanning the buffer pool in Microsoft SQL Server. This blog explores buffer pool scan.
PSPO Plan Variants: One Query, Several Plans in SQL Server
PSPO plan variants let SQL Server cache up to three plans for one parameterized query when the data is very skewed. This post builds a skewed table, shows two variants with different reads for three calls, compares the same calls with the feature off, and explains when PSPO does not apply.
Chatty Applications: Finding Many Tiny Queries per Second
Diagnose chatty applications with batch rates, cached executions, bounded session event counts, and measured round trips per user action.
Performance Tuning Mistakes in SQL Server: 11 Habits to Fix
Performance tuning mistakes are habits, and each one has a check. Eleven sit in one table, and five are measured on a demo database: a missing index, stale statistics, a row by row loop, a giant delete and a type that cannot be indexed.
Cold Cache vs Warm Cache: Testing Query Speed Fairly
Compare cold cache and warm cache query tests using isolated buffer clearing, logical and physical reads, and controlled measurements.







