T-SQL Window Function Performance
summary
Shows concrete fixes: adding a covering POC index cuts table logical reads (example scan drops from 689 to 119), using explicit frame ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW forces in‑memory work tables and drives logical reads to zero for running totals and FIRST_VALUE, and enabling batch mode on rowstore in SQL Server 2019 turns expensive table spools into window aggregate operators. Measured impact: a 30M-row workload falls from ~36 minutes with the default RANGE frame to ~3.5 minutes with ROWS (and additional gains when pre-aggregating).
AI-written summary · about
chapters (12)
- 0:00Introduction
- 1:57Demo
- 7:13Results
- 7:55Adding an Index
- 12:35Review
- 13:10Frame
- 14:24Terminology
- 17:37Examples
- 21:38Range
- 28:47Recap
- 29:59Batch Mode
- 41:10Timings
related talks
49mModern T SQL for Better Performance
32mWindowing Functions for Database Analytics
01h 06mPostgres Window Magic
34mCommon Table Expressions and Window Functions simple, maintainable, fast queries
01mWindowing Functions for Database Analytics
44mImproving Performance with Intelligent Query Processing
51mNext Level Scalability on SQL Server 2022
01h 00m7 Tips to Performance Tuning, Optimization, and Everything