All About SQL Server Stored Procedures: Dynamic SQL For Performance

Publicado em: 12 Março 2025
no canal de: Erik Darling (Erik Darling Data)

1,084

In this video, I delve into using dynamic SQL to address performance issues in stored procedures, specifically focusing on the challenges of parameter sniffing and plan compilation within if branches. I demonstrate how dynamic SQL can help mitigate cardinality estimation errors by ensuring that each branch of an if statement generates a unique query plan, thus avoiding the pitfalls of parameter sensitivity across executions. The video walks through creating a procedure that uses dynamic SQL to inject specific conditions based on input parameters, effectively solving the initial performance issues while still leaving room for further optimization. I also highlight the importance of considering frequency distributions of input values and how they can influence plan selection, providing a practical approach to handling these scenarios in real-world database tuning situations.

CHAPTERS

00:00:00 - Introduction
00:02:31 - SQL Saturday Announcement
00:04:35 - SQL Prompt and Dark Mode
00:06:01 - Nested Stored Procedures
00:07:23 - Filtered Indexes in Dynamic SQL
00:08:48 - Statement Level Recompile Hint
00:11:14 - Parameter Sniffing Overview
00:15:06 - Query Hashing with Dynamic SQL
00:17:08 - Exist Check for Parameter Sensitivity
00:22:59 - Unmatched Index Warning Solution

━━━━━━━━━━━━━━━━━━━━━━━━━━
📚 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
━━━━━━━━━━━━━━━━━━━━━━━━━━

Nesta página do site você pode assistir ao vídeo on-line All About SQL Server Stored Procedures: Dynamic SQL For Performance duração hora minuto segundo em boa qualidade , que foi baixado pelo usuário Erik Darling (Erik Darling Data) 12 Março 2025, compartilhe o link com seus amigos e conhecidos, no youtube este vídeo já foi visto 1,084 vezes e gostou 64 espectadores. Boa visualização!