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 only takes a minute to sign up.
Sign up to join this community
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.
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.
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.
