Indexing SQL Server Queries For Performance: Unpredictable Searches

In this video, I delve into the complexities of indexing for performance when dealing with unpredictable searches in SQL Server. Unpredictable searches can arise from dynamic SQL within stored procedures or ad hoc queries generated by front-end dashboards, where you have no idea what the WHERE clause might look like. I explore why using double wildcard searches and mismatched data types is a recipe for disaster, leading to full table scans and implicit conversions. To address this, I demonstrate how adding an 'OPTION (RECOMPILE)' hint can significantly improve performance by allowing SQL Server to generate a new execution plan each time the query runs, based on actual parameter values. However, I also caution about potential compile-time issues that could arise from frequent recompiles and discuss upcoming optimizer features like "Oppo" in SQL Server 2025, which might help mitigate some of these challenges. CHAPTERS 00:00:00 - Introduction 00:01:05 - Unpredictable Searches 00:02:45 - Option Recompile Hint 00:06:37 - Index Design 00:10:08 - Dynamic Sorting 00:13:56 - Plan Reuse 00:17:09 - Query Execution Analysis 00:19:42 - Rowstore Indexes 00:21:52 - Columnstore Indexes 00:23:08 - Conclusion ━━━━━━━━━━━━━━━━━━━━━━━━━━ 📚 TRAINING & COURSES ━━━━━━━━━━━━━━━━━━━━━━━━━━ Get AI-Ready With Erik https://training.erikdarling.com/get-ai-ready-with-erik?coupon=AIREADY SQL Server Performance Engineering Course https://training.erikdarling.com/sql-server-performance-engineering?coupon=ENGINEERING Learn T-SQL with Erik https://training.erikdarling.com/learn-t-sql-with-erik?coupon=ADVANCEDTSQL Everything Bundle: https://training.erikdarling.com/?coupon=SPRINGCLEANING ━━━━━━━━━━━━━━━━━━━━━━━━━━ 🛠️ CONSULTING & SERVICES ━━━━━━━━━━━━━━━━━━━━━━━━━━ Need SQL Server performance help? https://training.erikdarling.com/sqlconsulting ━━━━━━━━━━━━━━━━━━━━━━━━━━ 💬 CONNECT ━━━━━━━━━━━━━━━━━━━━━━━━━━ Ask questions at Office Hours https://erikdarling.com/officehours/ Become a channel member https://www.youtube.com/@ErikDarlingData/join ━━━━━━━━━━━━━━━━━━━━━━━━━━