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