Stored completion recompile and number of rows in underlying table

Question

Stored completion recompile and number of rows in underlying table

Tom John-342103

SSCrazy

Points: 2694
More actions
August 16, 2017 at 1:16 pm

#332291

Under what circumstances will a stored procedure be recompiled in SQL2012? Specifically, will it recompile when:
1. The row count in a permanent table changes significantly (e.g., goes from say, 0 to several million rows)?
2. The stored procedure creates and loads #temp tables?
I am looking at a stored procedure that intermittently exhibits poor performance and I am trying to figure out what might be causing this to happen. The nature of the data being processed is pretty constant.

Viewing 3 posts - 1 through 2 (of 2 total)

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

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

http://www.sqlinthewild.co.za/index.php/2011/08/16/compiles-and-recompiles/

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

Avi1 SSCrazy Points: 2323 More actions · Answer 2

GilaMonster - Wednesday, August 16, 2017 1:27 PM
http://www.sqlinthewild.co.za/index.php/2011/08/16/compiles-and-recompiles/

Nice article.
For that reason, we run stats on some of the tables nightly to get better performance