Clever Geek Handbook

SQL Server 2008 R2: How to "suspend" a clustered index while writing a server?

I'm wondering if its clustered index can "pause" whenever massive data is written?

The reason is that:

  • Bulk inserts are slow ( 10,000 rows / seconds) if I have a clustered index on "DateTime".
  • Bulk inserts are fast ( 180,000 rows / seconds) if I have an inactive clustered index on "DateTime".

I don't mind if the clustered index is rebuilt overnight, for example. from 1 to 6 in the morning.

+3


source to share


1 answer


You cannot disable a clustered index and use a table.

Since the clustered index is TABLE , which is disabled, it means that you cannot access any data.

From MSDN:

Data rows with a disabled clustered index cannot be accessed except to reset or rebuild the clustered index.



You can...

  • disable any nonclustered indexes and rebuild them overnight. This will help greatly
  • DROP

    all indexes (including clustered) and insert and then CREATE

    overnight. This will render the table unusable in principle.

My preferred solution for this is a little more complex:

  • INSERT

    to a staging table that has the same clustered index key as your target table
  • INSERT

    from stage to target overnight and update indices as needed, then
+2


source






More articles:


All Articles