CODEWITHSUNDEEP
sql-serverindexingsql-server-2012table-rename
Piotr Kuc-Dzierżawski
Piotr Kuc-Dzierżawski

Reputation: 11

Does rename of the table cause indexes to rebuild?

question is already in the title, but I'll try to provide some background here.

I have a table myTable that is being queried via nHibernate from a web application. It contains 500K rows. Every now and then (let's say every 15 min.) I need to refresh this table content, i.e. remove everything and insert another 500K rows (possibly different).

DISCLAIMER: Yes, I know this is not the right architecture :-) I need to understand the behaviour anyway, though.

As insert takes around 60 sec., this is the way I'm doing it:

  1. Insert 500K rows into myTable_backup
  2. Rename myTable to myTable_temp
  3. Rename myTable_backup to myTable
  4. Rename myTable_temp to myTable_backup

Points 2-4 are intended at swaping tables quickly, so that myTable is almost always available.

Despite my best intentions, I'm getting SQL timeouts when trying to access myTable - this happens more or less around the time that "rename" is executed.

My question is: why?

Can it be, that myTable is not available because indexes are still being rebuilt on it as a result of 500K insert? Although it already managed to change a name, there is still background re-indexing going on?

If so, could rebuilding indexes on myTable_backup explicitly executed between points 1 and 2, help?

But then another question pops-up, which is my official question for this article: does point 3) rename myTable_backup to myTable cause indexes to rebuild? That would seem a weird idea to me, however it would explain my timeouts (indexes rebuild take around 10 sec.).

Could you help? If you don't know the answer, perhaps you can suggest how to find out?

Thanks, PKD

Upvotes: 1

Views: 8175

Answers (1)

Martin Smith
Martin Smith

Reputation: 453298

No, rename of the table does not cause indexes to rebuild.

But it will acquire a SCH-M (schema modification) lock and for that it will need to wait for any existing readers to release their locks.

In the meantime any new readers will have to queue up behind the SCH-M lock request rather than jumping the queue and potentially starving it. (This is a change in behavior from the previous version).

You can examine sys.dm_os_waiting_tasks to see the wait types that are happening during the timeouts.

Upvotes: 2

Related Questions