Why INSERT/EXEC Causes Weird Blocking In SQL Server
In this video, I delve into a critical aspect of SQL Server programming: the use of 'INSERT EXEC' and its potential pitfalls when used within stored procedures. I demonstrate how executing a stored procedure via 'INSERT EXEC' can inadvertently wrap it in a transaction, leading to locking issues that might cause blocking or even deadlocks, especially if long-running operations like 'WAIT FOR DELAY' are involved. By walking through an example with two stored procedures, I illustrate the unexpected behavior and highlight the importance of being cautious when using 'INSERT EXEC', particularly when performing DML operations inside the executed procedure.
CHAPTERS
00:00:00 - Introduction
00:01:29 - Inner Store Procedure Details
00:04:00 - Outer Store Procedure Setup
00:05:30 - Blocking Pattern Demonstration
00:07:24 - Summary and 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
━━━━━━━━━━━━━━━━━━━━━━━━━━
On this page of the site you can watch the video online Why INSERT/EXEC Causes Weird Blocking In SQL Server with a duration of hours minute second in good quality, which was uploaded by the user Erik Darling (Erik Darling Data) 14 March 2024, share the link with friends and acquaintances, this video has already been watched 887 times on youtube and it was liked by 53 viewers. Enjoy your viewing!
