Siten0308 (4/4/2016)
we ran into an issue were the store procedure that gets used a lot, was causing the SQL server to peak performance, only after our sr DBA ran a manual recompile on the procedure did everything calm down, or so he says anyways, and whats worse he is not the type to share what scripts or anything, so reaching out to you guys to maybe shed light and do my troubleshooting and resolution.
Well the script he ran would likely just have been
EXEC sp_recompile 'procedure that gets used a lot', which isn't really all that useful.
Ideally, in this kind of situation, you want to identify and address the root cause, and not just recompile the procedure and hope the problem will go away (it usually doesn't go away permanently)
This may be of some use: https://www.red-gate.com/community/books/accidental-dba
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability