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.
SQL SERVER – Insert Values of Stored Procedure in Table – Use Table Valued Function
How do you insert values of stored procedure output into a table? The traditional temp table way, and an alternate method with a Table Valued Function.