Question: When was Stored Procedure Last Compiled?
Answer: Honestly, it is difficult to figure out when was the stored procedure last compiled.
Recent Execution of Stored Procedure – SQL in Sixty Seconds #118
My clients were not sure which SPs are the recent SP and which SP is no longer used. Today we will learn about the recent execution of the stored procedure.
Dynamic SQL Output Parameter: Pass Values In and Out Safely
A dynamic SQL output parameter lets a statement built at run time hand a value back to the caller. This post shows input, output and input/output parameters with sp_executesql, explains the errors you meet, and measures why parameters beat concatenation: one reusable plan instead of ten, and no injection.
Deadlock Victim Selection: Protect One Transaction
Deadlock victim selection decides which of two blocked transactions SQL Server rolls back. This post shows the rules, sets DEADLOCK_PRIORITY on one procedure, reproduces a deadlock with two query windows, and shows what the setting cannot do. It ends with the two fixes that remove the deadlock itself.
ORDER BY a Parameter in SQL Server: CASE, Types and Plans
To ORDER BY a parameter in SQL Server, write one CASE expression for each column and direction. This post builds the procedure, shows the type conversion and DISTINCT errors that trip people up, measures the cost of the sort that CASE forces, and compares OPTION (RECOMPILE) and dynamic SQL.
sp_helpdb in SQL Server: List Databases and Their Files
sp_helpdb in SQL Server lists every database on an instance, or the files of one database, with a single call. This post builds three small databases, reads the output column by column and explains why db_size is text. It also shows how to filter the result and how to find free space.
DISABLE_PARAMETER_SNIFFING Hint: Turn Off Sniffing for One Query
The DISABLE_PARAMETER_SNIFFING hint makes one query ignore the parameter value it was first compiled for. This post compares it with the default, OPTIMIZE FOR UNKNOWN and RECOMPILE on a table where one customer owns 40 percent of the rows. The reads show which variant fits which workload.