Uncategorized

Rebuilding all indexes in database (cursor version)

Script to rebuild all indexes in the database. Indexes are iterated using the cursor and so it can be easily extended with other features. Transact-SQL DECLARE @Database NVARCHAR(128) DECLARE @Table NVARCHAR(128) DECLARE @Stmt NVARCHAR(MAX) DECLARE curTable CURSOR FOR SELECT '[' + sch.name + '].[' + t.name + ']' as TableName FROM sys.tables t inner join sys.schemas sch on sch.schema_id = t.schema_id ORDER BY t.name OPEN curTable FETCH NEXT FROM curTable INTO @Table WHILE @@FETCH_STATUS = 0 BEGIN PRINT '-->' + @Table SET @Stmt = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD;' PRINT @Stmt EXECUTE(@Stmt) FETCH NEXT FROM curTable INTO @Table END CLOSE curTable DEALLOCATE curTable GO 12345678910111213141516171819202122232425262728 DECLARE @Database NVARCHAR(128)   DECLARE @Table NVARCHAR(128)  DECLARE @Stmt NVARCHAR(MAX)   DECLARE curTable CURSOR FOR     SELECT '[' + sch.name + '].[' + t.name + ']' as TableName       FROM sys.tables t inner join sys.schemas sch on sch.schema_id = t.schema_id ORDER BY t.name   OPEN curTable   FETCH NEXT FROM curTable INTO @Table   WHILE @@FETCH_STATUS = 0 BEGIN            PRINT '-->' + @Table SET @Stmt = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD;'         PRINT @Stmt EXECUTE(@Stmt)   FETCH NEXT FROM curTable INTO @Table   END   CLOSE curTable  DEALLOCATE curTable  GO Same version as the above on but it also includes the option to set different…
Uncategorized

Generate indexes REBUILD/REORGANIZE script based on fragmentation level

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT CONCAT('/* ', [n].[SchemaName], '.', [n].[TableName], ' (', [d].[record_count], ') [', [d].[page_count] , '] */ ') IndexInfo, CONCAT ('ALTER INDEX ', QUOTENAME([i].[name]), ' ON ', QUOTENAME([n].[SchemaName]), '.', QUOTENAME([n].[TableName]), CASE WHEN [d].[avg_fragmentation_in_percent] > 30 THEN ' REBUILD WITH (ONLINE=ON);' ELSE ' REORGANIZE;' END), ISNULL( [i].[name], '{HEAP}' ) [IndexName], [d].[avg_fragmentation_in_percent] [AvgFragmentationPercent], [d].[page_count] [PageCount], [d].[record_count] [RowCount] FROM [sys].[dm_db_index_physical_stats](DB_ID(), NULL, NULL, NULL, 'SAMPLED') [d] INNER JOIN [sys].[objects] [o] ON [o].[object_id] = [d].[object_id] INNER JOIN [sys].[indexes] [i] ON [i].[object_id] = [o].[object_id] AND [i].[index_id] = [d].[index_id] OUTER APPLY ( SELECT OBJECT_SCHEMA_NAME([d].[object_id], [d].[database_id]) [SchemaName], OBJECT_NAME([d].[object_id], [d].[database_id]) [TableName] ) [n] WHERE [d].[database_id] = DB_ID() AND [d].[page_count] > 64 AND [d].[avg_fragmentation_in_percent] > 5 ORDER BY OBJECT_SCHEMA_NAME([d].[object_id], [d].[database_id]), OBJECT_NAME([d].[object_id], [d].[database_id]) 12345678910111213141516171819202122232425 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT     CONCAT('/* ', [n].[SchemaName], '.', [n].[TableName], ' (', [d].[record_count], ') [', [d].[page_count] , '] */ ') IndexInfo,    CONCAT ('ALTER INDEX ', QUOTENAME([i].[name]), ' ON ', QUOTENAME([n].[SchemaName]), '.', QUOTENAME([n].[TableName]), CASE                 WHEN [d].[avg_fragmentation_in_percent] > 30 THEN ' REBUILD WITH (ONLINE=ON);'                ELSE ' REORGANIZE;'            END),               ISNULL( [i].[name], '{HEAP}' ) [IndexName],    [d].[avg_fragmentation_in_percent] [AvgFragmentationPercent],     [d].[page_count] [PageCount],         [d].[record_count] [RowCount]FROM [sys].[dm_db_index_physical_stats](DB_ID(), NULL, NULL, NULL, 'SAMPLED') [d] INNER JOIN [sys].[objects] [o] ON [o].[object_id] = [d].[object_id] INNER JOIN [sys].[indexes] [i] ON [i].[object_id] = [o].[object_id] AND [i].[index_id] = [d].[index_id] OUTER APPLY ( SELECT OBJECT_SCHEMA_NAME([d].[object_id], [d].[database_id]) [SchemaName], OBJECT_NAME([d].[object_id], [d].[database_id]) [TableName] ) [n]WHERE [d].[database_id] = DB_ID() AND [d].[page_count] > 64 AND [d].[avg_fragmentation_in_percent] > 5ORDER BY OBJECT_SCHEMA_NAME([d].[object_id], [d].[database_id]), OBJECT_NAME([d].[object_id], [d].[database_id])
Uncategorized

Missing indexes by average estimated impact

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED GO SELECT [migs].[avg_user_impact] * ( [migs].[user_seeks] + [migs].[user_scans] ) [avg_estimated_impact], [user_scans], [user_seeks], [migs].[last_user_seek] [last_user_seek], OBJECT_NAME([mid].[object_id], [mid].[database_id]) [table_name], 'CREATE INDEX [IX_' + OBJECT_NAME([mid].[object_id], [mid].[database_id]) + '_' + REPLACE(REPLACE(REPLACE(ISNULL([mid].[equality_columns], ''), ', ', '_'), '[', ''), ']', '') + CASE WHEN [mid].[equality_columns] IS NOT NULL AND [mid].[inequality_columns] IS NOT NULL THEN '_' ELSE '' END + REPLACE(REPLACE(REPLACE(ISNULL([mid].[inequality_columns], ''), ', ', '_'), '[', ''), ']', '') + ']' + ' ON ' + [mid].[statement] + ' (' + ISNULL([mid].[equality_columns], '') + CASE WHEN [mid].[equality_columns] IS NOT NULL AND [mid].[inequality_columns] IS NOT NULL THEN ',' ELSE '' END + ISNULL([mid].[inequality_columns], '') + ')' + ISNULL(' INCLUDE (' + [mid].[included_columns] + ')', '') [create_statement] FROM [sys].[dm_db_missing_index_groups] [mig] INNER JOIN [sys].[dm_db_missing_index_group_stats] [migs] ON [migs].[group_handle] = [mig].[index_group_handle] INNER JOIN [sys].[dm_db_missing_index_details] [mid] ON [mig].[index_handle] = [mid].[index_handle] WHERE [mid].[database_id] = DB_ID() ORDER BY [avg_estimated_impact] DESC GO 1234567891011121314151617181920 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDGO SELECT [migs].[avg_user_impact] * ( [migs].[user_seeks] + [migs].[user_scans] ) [avg_estimated_impact], [user_scans], [user_seeks], [migs].[last_user_seek] [last_user_seek], OBJECT_NAME([mid].[object_id], [mid].[database_id]) [table_name], 'CREATE INDEX [IX_' + OBJECT_NAME([mid].[object_id], [mid].[database_id]) + '_' + REPLACE(REPLACE(REPLACE(ISNULL([mid].[equality_columns], ''), ', ', '_'), '[', ''), ']', '')  + CASE WHEN [mid].[equality_columns] IS NOT NULL AND [mid].[inequality_columns] IS NOT NULL THEN '_'  ELSE '' END + REPLACE(REPLACE(REPLACE(ISNULL([mid].[inequality_columns], ''), ', ', '_'), '[', ''), ']', '') + ']' + ' ON ' + [mid].[statement] + ' (' + ISNULL([mid].[equality_columns], '') + CASE WHEN [mid].[equality_columns] IS NOT NULL AND [mid].[inequality_columns] IS NOT NULL THEN ',' ELSE '' END + ISNULL([mid].[inequality_columns], '') + ')' + ISNULL(' INCLUDE (' + [mid].[included_columns] + ')', '') [create_statement]FROM    [sys].[dm_db_missing_index_groups] [mig] INNER JOIN [sys].[dm_db_missing_index_group_stats] [migs] ON [migs].[group_handle] = [mig].[index_group_handle] INNER JOIN [sys].[dm_db_missing_index_details] [mid] ON [mig].[index_handle] = [mid].[index_handle]WHERE [mid].[database_id] = DB_ID()ORDER BY [avg_estimated_impact] DESCGO