Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 6 down vote favorite

Pretty much as the title describes. I have just checked a SQL Server and noticed there is a lot of query plan bloat that can be fixed by proper parametrisation.

After making the changes to the code, is it recommended that we clear the query plan cache and let it rebuild itself? Or will it clear itself up in time?

NB: Out of the top 50 biggest queries, 47 of them were the same but with different parameters.

share|improve this question

1 Answer

up vote 6 down vote accepted

I would recommend against this. You have done optimization on probably a small subset of queries, and over time you will see the improvement and performance gain. But if you just do a clean refresh of the query plan cache you will blow all of the execution plans out of there, affecting more than just your optimizations.

I'd be willing to say the immediate performance degradation isn't worth the smaller gain of using your optimized plans right away. But again, this is another "it depends".

share|improve this answer
Not sure if you saw the edit I put on as you were writing but most of the top queries are like SELECT blah FROM t WHERE param = 1 then param = 2 and so on... Does that affect your answer? Sorry if it does, you were too quick :P – Stuart Blackler May 22 '12 at 14:22
Ah, ok. I see your note now. What % of the queries in your plan cache have you optimized? Not by space, but by usecounts. – Thomas Stringer May 22 '12 at 15:12
Probably no more than 15% for this one query. Don't have access to the server at the moment to check – Stuart Blackler May 22 '12 at 16:28
1  
@StuartBlackler In that case, I would still recommend not to clear the plan cache as a whole. You can call DBCC FREEPROCCACHE (msdn.microsoft.com/en-us/library/ms174283.aspx) and specify the plan handle as a parameter. This will remove a single plan from the cache. Writing a little clever T-SQL you could loop through all the plans you want to clear to get them out of there. The benefit of this is you won't affect the other 85% of the plan cache. Like most things in production, though, if you decide to go that route definitely test it out in an isolated environment. – Thomas Stringer May 22 '12 at 17:19
If 47/50 queries are going to be properly parameterized (OP says code is being fixed), I don't see why you wouldn't flush the plan cache after the code is updated. I'd also update stats if you aren't already doing so regularly. – Eric Higgins May 22 '12 at 20:16
show 4 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.