rebuild indexes with statistics

gistfile1.sql

# this still needs some work 

--save PRE stats on index fragmentation
IF OBJECT_ID('tempdb..#tmp_PRE_reindex', 'U') IS NOT NULL  DROP TABLE #tmp_PRE_reindex
SELECT 
  ROW_NUMBER() over (order by sDips.index_id) as 'rowid', 
	OBJECT_NAME(sDips.OBJECT_ID) as 'objid', 
	si.name,
	sDips.index_id,
	sDips.index_type_desc,index_level,
	sDips.avg_fragmentation_in_percent,
	sDips.avg_page_space_used_in_percent,
	sDips.page_count,
	(select sum(sDdps.used_page_count) from sys.dm_db_partition_stats sDdps where sDdps.object_id = sObj.object_id) as 'used_page_count',
	(select sum(sDdps.reserved_page_count) from sys.dm_db_partition_stats sDdps where sDdps.object_id = sObj.object_id) as 'reserved_page_count',
	(select top 1 sDdps.row_count from sys.dm_db_partition_stats sDdps where sDdps.object_id = sObj.object_id) as 'row_count'
INTO #tmp_PRE_reindex 
FROM sys.indexes as si
full join sys.dm_db_index_physical_stats (DB_ID(N'asdf'), NULL, NULL, NULL , 'Detailed') sDips
	on si.object_id = sDips.object_id
join sys.objects sObj
	on si.object_id = sObj.object_id
where si.name is not null
ORDER BY avg_fragmentation_in_percent DESC


 ----rebuild all indexes
 SET QUOTED_IDENTIFIER ON
 EXEC [sp_MSforeachtable] @command1="RAISERROR('DBCC DBREINDEX(''?'') ...',10,1) WITH NOWAIT DBCC DBREINDEX('?')"


--save POST stats on index fragmentation
IF OBJECT_ID('tempdb..#tmp_POST_reindex', 'U') IS NOT NULL  DROP TABLE #tmp_POST_reindex
SELECT 
	ROW_NUMBER() over (order by sDips.index_id) as 'rowid', 
	OBJECT_NAME(sDips.OBJECT_ID) as 'objid', 
	si.name,
	sDips.index_id,
	sDips.index_type_desc,index_level,
	sDips.avg_fragmentation_in_percent,
	sDips.avg_page_space_used_in_percent,
	sDips.page_count,
	(select sum(sDdps.used_page_count) from sys.dm_db_partition_stats sDdps where sDdps.object_id = sObj.object_id) as 'used_page_count',
	(select sum(sDdps.reserved_page_count) from sys.dm_db_partition_stats sDdps where sDdps.object_id = sObj.object_id) as 'reserved_page_count',
	(select top 1 sDdps.row_count from sys.dm_db_partition_stats sDdps where sDdps.object_id = sObj.object_id) as 'row_count'
INTO #tmp_POST_reindex 
FROM sys.indexes as si
full join sys.dm_db_index_physical_stats (DB_ID(N'asdf'), NULL, NULL, NULL , 'Detailed') sDips
	on si.object_id = sDips.object_id
join sys.objects sObj
	on si.object_id = sObj.object_id
where si.name is not null
ORDER BY avg_fragmentation_in_percent DESC


--display useful comaprison results
select  
		tpre.rowid,
		tpre.objid,
		tpre.name,
		tpre.index_type_desc,
		cast(tpre.avg_fragmentation_in_percent as decimal(5,2)) as 'Pre fragmentation (%)',
		cast(tpost.avg_fragmentation_in_percent as decimal(5,2)) as 'Post fragmentation (%)',
		cast(tpre.avg_page_space_used_in_percent as decimal(5,2)) as 'Pre page space used (%)',
		cast(tpost.avg_page_space_used_in_percent as decimal(5,2)) as 'Post page space used (%)',
		(tpre.page_count/128) as 'Pre index size (MB)',
		(tpost.page_count/128) as 'Post index size (MB)',
		(tpost.used_page_count/128) as 'table used (MB)',
		(tpost.reserved_page_count/128) as 'table reserved (MB)',
		tpre.page_count as 'pre page count',
		tpost.page_count as 'post page count',
		tpost.row_count as 'table row count'
from #tmp_PRE_reindex tpre
join #tmp_POST_reindex tpost
	on tpre.rowid = tpost.rowid
where tpre.objid is not null
order by tpre.page_count, tpre.name desc