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