Rebuild / reorganize those indexes

Lately I brought back to life a script that I would like to briefly talk about. It is made of dynamic TSQL code which I find pretty neat and really useful if it preceeds the OLAP processing tasks ( ideally together in a SSIS package with a consecutive statistics update task using sp_updatestats ) .

I am not going to share the whole thing, but I will give some hints how to build this code for yourself.

In the declaration part, you set the variables with the fragmentation thresholds which determine if the index is to be rebuild or just reorganized and the minimal index page count. Just a hint here, we have this currently setup as 14 percent threshold to reorganize, 35 to rebuild and 500 as the minimal page count.

Next step is to create a temp table which will hold the index names and the dynamically generated rebuild / reorganize statements. After this table is filled, you iterate through the table and execute the statement per each row.

The main magic however happens using this query:

SELECT
b.name as key_name
,a.avg_fragmentation_in_percent
,a.page_count, c.name as Tab_name
,d.name as Schema_name
,a.partition_number as Partition_Number
,'ALTER INDEX [' + b.name + '] ON [MyDB].[' + d.name + '].[' + c.name + '] '
+ CASE
WHEN a.avg_fragmentation_in_percent BETWEEN @reorg_pct AND @rebuild_pct
THEN 'REORGANIZE'
WHEN a.avg_fragmentation_in_percent > @rebuild_pct
THEN 'REBUILD'
END
+ CASE
WHEN a.partition_number > 1
THEN ' PARTITION = ' + CAST(a.partition_number AS NVARCHAR)
ELSE ''
END
AS index_maintenance
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) a
JOIN sys.indexes b ON b.object_id = a.object_id AND b.index_id = a.index_id
JOIN sys.objects c ON c.object_id = b.object_id
JOIN sys.schemas d ON d.schema_id = c.schema_id
WHERE a.index_id > 0 AND c.name NOT LIKE 'sys%' AND a.page_count > @pagecnt
AND a.avg_fragmentation_in_percent > @reorg_pct
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s