May 27, 2010 at 2:04 pm
Does Statement Recompilations decrease the performance???
May 27, 2010 at 2:09 pm
Only if not necessary. That's where the whole question resides.
Can you post the code of the sp(s) that are recompiling?
May 27, 2010 at 2:34 pm
They are not stored procedures.....they are just statements
May 27, 2010 at 7:22 pm
Then chances are you are getting recompiles for "no good reasons". Or for reasons that could be avoided. That being said are you having performance issues on the server?
Recompiles will hurt the server, but not as much as badly written query or missing / wrong indexing.
Why are you asking this question today?
May 28, 2010 at 6:29 am
The thing is, it depends. In short recompiles are taking up processor time that could be spent elsewhere. But how often and how long is that processor time taken up? If you have a statement that runs once a day, but recompiles every time it runs and the recompile time is less than 50ms... who cares. Same daily procedure with a 3-5 minute recompile time (which I've seen) and it might be a HUGE issue. Another procedure that's running hundreds of times a minute and has a statement recompile that takes 5ms... could be a huge problem.
Recompiles, all by themselves, are not bad things. In fact, they're good because they adjust the execution plans to changing data, better statistics, etc. But you can get WAY too much of a good thing.
"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
June 1, 2010 at 3:57 pm
Thanks for the information....My statement recompiles around 6 times a day and it doesn't take too much time(around 2-3 sec)....So i guess i am okay with Statement Recompilations
June 2, 2010 at 6:14 am
Sandy2704 (6/1/2010)
Thanks for the information....My statement recompiles around 6 times a day and it doesn't take too much time(around 2-3 sec)....So i guess i am okay with Statement Recompilations
2-3 seconds is just a tad long. But if it's only a few times a day it might not be an issue. Do you know if it's due to temp tables or changes in statistics?
"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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply