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.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a 38 million row table that I periodically truncate and refill. I disable the indexes (non-clustered) when I do this. But when I included the Execution Plan I noticed SQL Server 2005 is still updating the indexes as I insert the data. How can this be?

Paul White has asked for some documentation. I created a test script to simplify the event. In summary, this is what I found. Consider a table with a clustered index and 2 non-clustered indexes. If the 2 non-clustered indexes are disabled, they are ignored when inserting data as expected. But if the clustered index is also dropped, then the 2 non-clustered indexes ARE updated during the insert. I am only allowed 2 links so I included my DDL script and the execution plan that shows the disabled indexes being updated.

share|improve this question
It is possible that it is showing you the plan based on the previous state where the index wasn't disabled, but when you execute the command it will detect the change and recompile a new relevant plan. Do you see that index if you include the actual execution plan and execute the command? – Matan Yungman Jan 27 '14 at 20:00
Yes, this is the actual execution plan. – Andy Jan 27 '14 at 20:11
What on the "IndexesDisabled" plan are you interpreting as the non clustered indexes being maintained? – Martin Smith Jan 28 '14 at 17:56
The IndexesDisabled plan is fine and shows expected behavior. It's the ClusteredIndexDropped plan that shows the NC indexes being maintained. – Andy Jan 28 '14 at 18:08
I see. Looks like disabling the NCIs then dropping the CI causes the NCIs to become re-enabled! The is_disabled flag in sys.indexes gets reset to 0 – Martin Smith Jan 28 '14 at 18:17

Your Answer


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

Browse other questions tagged or ask your own question.