Geek asks and answers

Automatically reduce and restore current indices

I am working on providing a method to bulk update our tables (more than 1M new or updated rows for each update), and was interested in dropping the current indexes and re-creating them after the updates.

I was wondering if anyone has a script to ensure that these operations are freely connected, so if the indexes change over time, the update process does not change.

This seems to be one of those things that the community has probably already decided.

+5
source share
2 answers

I have a script that I use to query system tables to capture all non-clustered indexes and disconnect from this recovery after completion. Below is the standard edition, if you are in the enterprise, I would add a parameter ONLINE.

Disable

DECLARE @sql AS VARCHAR(MAX);
SET @sql = '';
SELECT 
    @sql = @sql + 'ALTER INDEX [' + i.name + '] ON [' + o.name + '] DISABLE; '
FROM sys.indexes AS i
JOIN sys.objects AS o ON i.object_id = o.object_id
WHERE i.type_desc = 'NONCLUSTERED'
AND o.type_desc = 'USER_TABLE'

EXEC (@sql)

Rebuild

DECLARE @sql AS VARCHAR(MAX);
SET @sql = '';
SELECT 
    @sql = @sql + 'ALTER INDEX [' + i.name + '] ON [' + o.name + '] REBUILD WITH (FILLFACTOR = 80); '
FROM sys.indexes AS i
JOIN sys.objects AS o ON i.object_id = o.object_id
WHERE i.type_desc = 'NONCLUSTERED'
AND o.type_desc = 'USER_TABLE'

EXEC (@sql);

I like this method because it is very customizable, since you can exclude / include certain tables based on conditions, and also avoid the cursor. You can also change EXECto PRINTand see the code that will be executed and run manually.

Condition for excluding a table

AND o.name NOT IN ('tblTest','tblTest1');
+10
source 
EXEC sp_MSforEachTable 'ALTER INDEX ALL ON ? DISABLE'

and

EXEC sp_MSforEachTable 'ALTER INDEX ALL ON ? REBUILD'

is all you need if you want to do this for all tables and each index.

+4
source

More articles:


All Articles