A Little About Filter Operators In SQL Server Query Plans
In this video, I delve into the often overlooked filter operators in query plans and why you shouldn't ignore them. Erik Darling from Darling Data shares his insights on when these operators are expected to appear and when they might indicate potential performance issues. He explains that while some filters can be necessary for certain operations like halving clauses or windowing functions, a high number of filters generally suggests that SQL Server is removing rows too early in the query plan, which can lead to unnecessary work and slower execution times. I also cover specific scenarios where filter operators are common, such as non-sargable predicates, column length checks using functions, bad join decisions, and operations involving MAX data types. Throughout the video, Erik emphasizes the importance of understanding why these filters appear in your query plans and how they can impact performance, encouraging viewers to test different approaches and consider alternative query designs to optimize their SQL Server queries.
CHAPTERS
00:00:00 - Introduction
00:02:12 - Halving Clause Usage
00:03:25 - Example Query Plan
00:04:29 - Batch Mode Operators
00:06:03 - Filter Operator Details
00:07:28 - Common SQL Considerations
00:08:26 - Non-Sargable Predicates
00:09:02 - Sargability Rules
00:10:06 - Index Scan Analysis
00:11:38 - Wildcard Characters
00:12:53 - Bad Join Decisions
00:14:01 - Left Join Testing
00:14:49 - Query Plan Rewrite
00:15:04 - Recompile Hint
00:16:15 - Full Text Indexes
00:17:30 - SQL Server 2019
00:18:06 - Performance Variations
00:18:49 - Query Execution Time
00:19:06 - Row Count Comparison
00:20:14 - Scalar UDF and Schema Binding
00:21:36 - Trace Lag Example
00:22:26 - Loop Predicate Application
00:23:19 - Complexity Considerations
00:24:04 - Filter Operator Impact
00:24:50 - Video Conclusion
━━━━━━━━━━━━━━━━━━━━━━━━━━
📚 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
━━━━━━━━━━━━━━━━━━━━━━━━━━