Dear SQL DBA Erik Darling and Kendra Little Rate SQL Server Perf Tuning Techniques
May 20, 2024
Join SQL Server experts Erik Darling and Kendra Little as they explore a treasure trove of performance tuning techniques. They rate everything from recompile hints to scalar UDFs, adding humor to the complexities of optimization. Discover their takes on new features like the Query Store and the Resource Governor. They debate the merits of Common Table Expressions and index rebuilding while reflecting on memory management. With insights on filtered indexes and the future of T-SQL, this conversation is a must-listen for anyone passionate about SQL Server performance.
AI Snips
Chapters
Transcript
Episode notes
Filtered Indexes Optimize Skewed Data
- Filtered indexes help optimize queries on skewed or partial data sets via granular statistics.
- Always include filtered columns in the index to maximize use and avoid poor query plans.
Use Join Hints Sparingly
- Use join hints as last resort when optimizer fails, primarily with loop or hash joins.
- Avoid merge joins, which they find problematic and would prefer an explicit 'no merge join' hint.
PSPO Limits With Inequality Predicates
- Parameter Sensitive Plan Optimization works only for equality predicates with limited applicability.
- Many issues arise with inequalities like date ranges, limiting its usefulness.