Indexing and Cached Execution Plans

Question

Indexing and Cached Execution Plans

paul27uk

SSC Enthusiast

Points: 106
More actions
March 18, 2010 at 6:45 am

#220013

Hi, I have been trying to read up on the above and I'm getting a little lost, so I figured why not post up here!
Basically, I have added a few indexes to out database and was wondering what would happen to the cached execution plan? Does this plan store the index use? If so, does the cache need to be removed for this plan?
Secondly, if the above is correct, and my cache needs clearing, is it easier to do it all as the index could alter a lot of these sp's, and i'm not sure of all of them.
Thanks, and apologies if this didn't make sense! 😀
Update:
Simple Question I think would be as follows:
If I add an Index to a table which would change how a stored procedure executes (changing the plan), would I need to drop the cached plan for this SP or would it update automatically?

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

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

vidya_pande SSCertifiable Points: 5275 More actions · Answer 1

Execution plan is prepared based on indexes on table. It depends on the memory availability whether it will keep in memory or not.

If the plan is cached in memory execution will be faster.

If you want to clear the cache you can do it using

DBCC FREEPROCCACHE

paul27uk SSC Enthusiast Points: 106 More actions · Answer 2

vidya_pande (3/18/2010)
Execution plan is prepared based on indexes on table. It depends on the memory availability whether it will keep in memory or not.
If the plan is cached in memory execution will be faster.
If you want to clear the cache you can do it using
DBCC FREEPROCCACHE

Ok, but if the sp already has a cached exec plan, would adding an index to a table force the cached plan to be changed to use the index?

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

Adding or altering indexes invalidates cached plans based on the base table that the index is defined on. Next time the query runs, the plan will be recompiled.

It's easy enough to test. Create and populate a test table, run a query that filters by a specific column, check the execution plan. Create an index, check the execution plan again.

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