March 21, 2014 at 7:28 am
I now you can flush and clear the cache but is there an option to ignore it?
Say rather than clearing the cache of a db/server you wanted to run a query but have it not look for any cache plans, is this possible?
Cheers
March 21, 2014 at 9:56 am
EXECUTE <procedure name> WITH RECOMPILE will generate a new plan just for this execution, the generated plan won't be cached and the cached plan won't be affected.
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
March 21, 2014 at 10:07 am
Other than what Gail outlines, which is a good way to get a new plan every time, why are you trying to "ignore the cache"?
"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
March 21, 2014 at 11:23 am
GilaMonster (3/21/2014)
EXECUTE <procedure name> WITH RECOMPILE will generate a new plan just for this execution, the generated plan won't be cached and the cached plan won't be affected.
Thanks, and adding OPTION (RECOMPILE) to the end of a insert/update statement will fore that not to use a previously cached plan?
was looking to run a quick test to see if there was a significant time decrease in a change to a few methods currently used and unable to clear the cache.
March 21, 2014 at 11:30 am
roblew 15918 (3/21/2014)
GilaMonster (3/21/2014)
EXECUTE <procedure name> WITH RECOMPILE will generate a new plan just for this execution, the generated plan won't be cached and the cached plan won't be affected.Thanks, and adding OPTION (RECOMPILE) to the end of a insert/update statement will fore that not to use a previously cached plan?
was looking to run a quick test to see if there was a significant time decrease in a change to a few methods currently used and unable to clear the cache.
Not only will it not use a previously cached plan, it won't cache plans at all for that statement.
"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
March 24, 2014 at 2:51 am
Grant Fritchey (3/21/2014)
roblew 15918 (3/21/2014)
GilaMonster (3/21/2014)
EXECUTE <procedure name> WITH RECOMPILE will generate a new plan just for this execution, the generated plan won't be cached and the cached plan won't be affected.Thanks, and adding OPTION (RECOMPILE) to the end of a insert/update statement will fore that not to use a previously cached plan?
was looking to run a quick test to see if there was a significant time decrease in a change to a few methods currently used and unable to clear the cache.
Not only will it not use a previously cached plan, it won't cache plans at all for that statement.
That's what i was hoping for thanks 🙂
March 24, 2014 at 2:56 am
Just bear in mind that OPTION(RECOMPILE) has other effects as well. It allows 'parameter sniffing' on variables, it can result in a row estimate for table variables which is not 1, it can result in an optimal plan for a catch-all query. It's not just ignoring the cached plan, there's a few more changes
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply