Procedure Cache issue ?

Question

Post reply

Procedure Cache issue ?

sql_butterfly

Ten Centuries

Points: 1264
More actions
March 7, 2011 at 7:45 am

#246579

Again some strange issue. I am running a stored procedure with parameters and its taking almost five mins to complete. However when I run the entire body of the same sproc [not as exec proc @p1, @p2 etc but with entore body] with the same set of parametes, it completes in just 1 or 2 seconds. Can it be because of proc cache ?

Viewing 10 posts - 1 through 9 (of 9 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 1

Could be a bad plan, maybe some parameter sniffing, or even something else.

Did you read this? http://www.sqlservercentral.com/redirect/articles/72634/

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 2

Absolutely sounds like parameter sniffing gone wrong.

"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

sql_butterfly Ten Centuries Points: 1264 More actions · Answer 3

Thanks Steve, the document was wonderful.

Grant, Even i think its because of parameter snuffing. How can I get rid of it ?

vamshikrishnaeee SSCrazy Points: 2519 More actions · Answer 4

@sql_butterfly,

1. You can run sp_recompile 'spname'

This will recompile the stored procedure every time is called.

2. Other way is to recreate the SP with recompile option.

Both of these options will recompile the storeprocedure.

@Grant ,

Are there any disadvantages for sp_recompile?

Thanks,

Vamsy

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

http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/

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

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 6

Yep, do what Gail says. Actually, that's a safe bet in general.

"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

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 7

I agree with Grant, follow Gail's advice.

If that's it, please let us know.

sql_butterfly Ten Centuries Points: 1264 More actions · Answer 8

Sure, I'll check that way and let you know. Thanks Gail.

sql_butterfly Ten Centuries Points: 1264 More actions · Answer 9

Thanks Gail. Just for the information to all, I executed the sproc with "WITH RECOMPILE" option and thereafter it started working fine [back to normal time].