Why INSERT/EXEC Causes Weird Blocking In SQL Server

Pubblicato il: 14 marzo 2024
sul canale di: Erik Darling (Erik Darling Data)

887

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

In questa pagina del sito puoi guardare il video online Why INSERT/EXEC Causes Weird Blocking In SQL Server della durata di ore minuti seconda in buona qualità , che l'utente ha caricato Erik Darling (Erik Darling Data) 14 marzo 2024, condividi il link con amici e conoscenti, su youtube questo video è già stato visto 887 volte e gli è piaciuto 53 spettatori. Buona visione!

3,427

103