patternsqlMinor
Does creating an index and then deleting it return the database to the pre-index creation state?
Viewed 0 times
deletingthecreatingreturndatabaseprestatethendoesand
Problem
I'm testing some indexes and want to do a bit of A-B testing on which indexes perform better (primarily the order of the fields in the index and additional fields specified).
I have a backup of the database I'm testing on, but that backup takes a long time to restore. I'd prefer if I could just create index foo, gather performance metrics, then delete foo to return the DB to the pre-index state and create index bar. Is this how creation of indexes work, or does the creation of the index alter the table in someway that deleting the index can't undo?
I have a backup of the database I'm testing on, but that backup takes a long time to restore. I'd prefer if I could just create index foo, gather performance metrics, then delete foo to return the DB to the pre-index state and create index bar. Is this how creation of indexes work, or does the creation of the index alter the table in someway that deleting the index can't undo?
Solution
Longer
Index changes (creates, rebuilds, drops, etc) on a table do invalidate any relevant plans in the plan cache and cause a new plan to be recompiled the next time the related query is executed.
It is possible for a different plan to be generated than what was originally there, on a database whose data has changed between when the original plan was cached and the new one was recompiled.
Practically speaking, since you're just doing A/B testing on a copy of the database that I'm assuming isn't actively changing otherwise, then yes, Erik is succinctly spot on. It's highly likely that dropping the index will result in the same original execution plan to be cached for your queries on their subsequent runs - meaning your database will effectively be returned to its previous state.
Index changes (creates, rebuilds, drops, etc) on a table do invalidate any relevant plans in the plan cache and cause a new plan to be recompiled the next time the related query is executed.
It is possible for a different plan to be generated than what was originally there, on a database whose data has changed between when the original plan was cached and the new one was recompiled.
Practically speaking, since you're just doing A/B testing on a copy of the database that I'm assuming isn't actively changing otherwise, then yes, Erik is succinctly spot on. It's highly likely that dropping the index will result in the same original execution plan to be cached for your queries on their subsequent runs - meaning your database will effectively be returned to its previous state.
Context
StackExchange Database Administrators Q#324947, answer score: 8
Revisions (0)
No revisions yet.