finding store proces that need to be recompiled

Question

finding store proces that need to be recompiled

Siten0308

SSCarpal Tunnel

Points: 4404
More actions
April 4, 2016 at 1:59 pm

#315055

Hello,
I am looking around for a script or table with columns to look out for that will show me which store procedures that need to be recompile, or recommend to be recompiled for a tonight maintenance, anyone have that or idea what table/columns to look at?
thanks in advanced

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Eric M Russell SSC Guru Points: 125522 More actions · Answer 1

What problem are you trying to solve? The execution plan for a stored procedure will be cycled out the plan cache when it hasn't been called for a long period of time (relative to access count for other objects) or when statistics for tables referenced by procedure have changed.

Maybe what you really need as part of your nightly maintenance plan is to update statistics on tables where statistics are out-dated.

https://gallery.technet.microsoft.com/scriptcenter/9aad569c-2d33-4d13-8242-160671260b5f

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Lowell SSC Guru Points: 323504 More actions · Answer 2

if we are talking about checking if an object is no longer valid because columns or tables were renamed , dropped, etc,

i've got this script i posted here on SSC multiple times that seems to do a nice job for me;

for Invalid Objects, it's not like oracle, where there is a column to query to test whether something was compiled successfully.

note this doesn't give you a list of what is broken NOW, but rather tries to recompile everything, and reports what fails to compile.

sp_InvalidObjects.txt

also note it's expecting a coding convention of a single space between CREATE PROC/VIEW/FUNCTION

--expected

CREATE PROC

--fails

CREATE PROC

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Siten0308 SSCarpal Tunnel Points: 4404 More actions · Answer 3

Thanks for the reply Eric,

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.

thanks in advanced

Grant Fritchey SSC Guru Points: 398694 More actions · Answer 4

That sounds like an issue with statistics or possibly a bad parameter sniffing issue (or both in combination). Recompiling the procedure causes it to create a new plan based on parameter values. If the stats are out of date, but not updating, that could explain why the recompile was necessary. Are the stats set to auto update? Do you have statistics maintenance? I'd get that set. Then, assuming that doesn't fix the problem, it might be an issue with bad parameter sniffing. Get a copy of the execution plan when the procedure is running slow and one when its fast. Compare the two and figure out which of the mechanisms (OPTIMIZE FOR, OPTIMIZE FOR UNKNOWN, RECOMPILE) for fixing bad parameter sniffing is best in your situation and implement it.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass