Indexing SQL Server Queries For Performance: Windowing Functions
In this video, I dive into improving the performance of windowing functions in SQL Server, focusing primarily on how effective indexing can be. I share my experience with using cross-apply and drive join methods to optimize queries involving windowing functions, highlighting the importance of aligning indexes closely with the query's requirements. Additionally, I discuss the nuances between these two approaches, emphasizing that while a cross-apply might seem more straightforward, a derived join often provides better performance due to its ability to leverage different join types and reduce memory grants. Throughout the video, I also touch on the significance of achieving batch mode for optimal windowing function performance, especially in Standard Edition environments where parallelism is limited.
CHAPTERS
00:00:00 - Introduction to Windowing Functions and Performance Tuning
00:04:58 - Using Row Number for Finding Maximum Values
00:06:29 - Applying Windowing Functions with Cross Apply
00:10:37 - Optimizing Joins for Better Performance
00:14:02 - Using Hash Join for Efficient Query Execution
00:15:45 - Alternative Query Forms and Their Performance Implications
00:16:44 - Conclusion and Advice on Query Tuning
━━━━━━━━━━━━━━━━━━━━━━━━━━
📚 TRAINING & COURSES
━━━━━━━━━━━━━━━━━━━━━━━━━━
Get AI-Ready With Erik
https://training.erikdarling.com/get-...
SQL Server Performance Engineering Course
https://training.erikdarling.com/sql-...
Learn T-SQL with Erik
https://training.erikdarling.com/lear...
Everything Bundle:
https://training.erikdarling.com/?cou...
━━━━━━━━━━━━━━━━━━━━━━━━━━
🛠️ CONSULTING & SERVICES
━━━━━━━━━━━━━━━━━━━━━━━━━━
Need SQL Server performance help?
https://training.erikdarling.com/sqlc...
━━━━━━━━━━━━━━━━━━━━━━━━━━
💬 CONNECT
━━━━━━━━━━━━━━━━━━━━━━━━━━
Ask questions at Office Hours
https://erikdarling.com/officehours/
Become a channel member
/ @erikdarlingdata
━━━━━━━━━━━━━━━━━━━━━━━━━━