2

We use SQL Server 2012 Standard Edition. Is there a way to rebuild the index, trying not to cause data table locking, and reducing the impact on performance?

When we rebuild the index, it causes the program accessing the database to time out. I am hoping to avoid or reduce the performance impact.

1
  • Do you need to have reading available or also writing? Mar 23, 2023 at 10:07

2 Answers 2

5

First, ask yourself why you rebuild the index in the first place. Defragmenting the index will likely not make a measurable performance difference. And if it is new statistics you are after, just update the statistics instead of rebuilding the whole index. More info on my blog here: https://sqlblog.karaszi.com/index-fragmentation-revisited/

ALTER INDEX REBUILD is an offline operation on Standard Edition in the sense that it requires locks. What you might want to do, if you still insist on defragmenting your indexes, is to do ALTER INDEX REORGANIZE instead. It is a more online-ish compared to rebuild.

1

Assuming you only need to keep reading available: you can do the little-known partition/table switching trick.

First create an exact copy of the table definition: the column definitions must all be the same (apart from IDENTITY), the clustered and non-clustered indexes must be the same, the filegroups must be the same, all primary, unique, foreign and check constraints must be the same.

Then do the following:

SET XACT_ABORT ON;

BEGIN TRAN;

INSERT NewTable (ColumnsHere)
SELECT ColumnsHere
FROM OldTable WITH (TABLOCK);

TRUNCATE TABLE OldTable;

ALTER TABLE NewTable SWITCH TO OldTable;

COMMIT;

Finally drop the new table.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.