After a big data change in one table, we wanted fresh plans. Here is how to recompile stored procedure plans for a specific table, with a word of caution.
Finding Dead Code: Stored Procedures Nobody Calls
Investigate dead code with cached procedure statistics, dependency checks and retained snapshots, then quarantine candidates before removal.
Interview Question of the Week #033 – How to Invalidate Procedure Cache of SQL Server?
Clear procedure cache at the narrowest useful scope, remove one plan handle, and compare sp_recompile with statement recompilation.
A Custom db_executor Role for Stored Procedure Access
The app gets a permission error while it works for you as a sysadmin. Here is how a db_executor role fixes it without opening everything.
SQL SERVER – Different Methods to Know Parameters of Stored Procedure
Need to know parameters of stored procedure code quickly? Here are two methods, including sp_help, that list each parameter with its type and order.
SQL SERVER – Is XP_CMDSHELL Enabled on the Server?
Is XP_CMDSHELL enabled on your server? I recommend avoiding it for security. Here are two ways to check and turn it off, starting with T-SQL.
Interview Question of the Week #013 – Stored Procedure and Its Advantages – How to Create Stored Procedure
Learn how to create stored procedure code with parameters and NOCOUNT, then review security, plan reuse, and parameter-sensitive plans.