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