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.
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.
Finding Queries That Spill With total_spills in Query Stats
Rank total_spills and recent counters, inspect plans, compare Query Store tempdb use, and investigate estimates before increasing grants.
Session State in SQL Server With Memory-Optimized Tables
Store transient session state in memory-optimized SQL Server tables with native reads and writes, expiry checks, and clear durability rules.
Detecting Memory Pressure – SQL in Sixty Seconds #186
After my video on CPU running 100%, many of you asked for one on memory. Here is my SQL in Sixty Seconds video on detecting memory pressure.