All About SQL Server Stored Procedures: Dynamic SQL For Performance
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
━━━━━━━━━━━━━━━━━━━━━━━━━━
On this page of the site you can watch the video online All About SQL Server Stored Procedures: Dynamic SQL For Performance with a duration of hours minute second in good quality, which was uploaded by the user Erik Darling (Erik Darling Data) 12 March 2025, share the link with friends and acquaintances, this video has already been watched 1,084 times on youtube and it was liked by 64 viewers. Enjoy your viewing!
