Two ways to recompile a stored procedure at run time in SQL Server: add WITH RECOMPILE when you create it, or ask for a one-time recompile.
SQL SERVER – Plan Recompilation and Reduce Recompilation – Performance Tuning
Plan recompilation hurts performance just like a first compile. Learn what causes it, from schema changes to SET options, and how to reduce recompilation.
Stored Procedure Optimization Tips That Still Matter
Stored Procedure Optimization comes down to a short list of habits you can test on your own server. This post builds one badly written procedure and one good one on a small orders table, then measures the difference in logical reads. It also covers parameter sniffing, the sp_ prefix, short transactions and error handling.
SQL SERVER – Size of Index Table – A Puzzle to Find Index Size for Each Index on Table
sp_spaceused shows the total index size of a table. My puzzle for you: write a query that adds the size of each index to the sys.indexes list.
SQL SERVER – Stored Procedure are Compiled on First Run – SP taking Longer to Run First Time
Why does a stored procedure take longer the first time? Stored procedures are not pre-compiled; each one is compiled on first run and cached after that.
Validating JSON Parameters Before a Procedure Uses Them
Learn validating JSON parameters before changing data with ISJSON, JSON_PATH_EXISTS, TRY_CAST, and a single clear list of input errors.
Finding the Slowest Statement Inside a Stored Procedure
Find the slowest statement inside a stored procedure using query stats offsets, CPU and read totals, then confirm with Query Store.