The culprit? Parameter sniffing. The solution? You guessed it: WITH RECOMPILE. Comprehensive Database Performance Health Check
Logging Agent Jobs That Call Stored Procedures
Improve logging Agent jobs with procedure run records, start and end events, captured errors, step output, and history correlation.
Capturing Stored Procedure Executions with Extended Events in SQL Server
Capturing stored procedure executions with Extended Events: set up a session with module_end, run a sample procedure, and query the captured data.
Given-When-Then for Database Tests
Use Given-When-Then for readable database tests with known input, explicit THROW checks, and transaction rollback after each case.
SQL SERVER – Resolving Deadlock by Accessing Objects in the Same Order
In Understanding SQL Server Deadlocks, I mentioned that one way to avoid or resolve deadlock is by accessing objects in the same order.
Cross-Database Ownership Chaining: Why It Is Off and What to Use
Understand cross-database ownership chaining, inspect its scope, and use signed procedures for narrow access between SQL Server databases.
INSERT EXEC: Capturing a Procedure’s Result Set and Its Limits
Capture rows with INSERT EXEC, match result columns, understand nested and multiple-result limits, and choose supported alternatives.