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. DECLARE @Database NVARCHAR(128) DECLARE @Table NVARCHAR(128) DECLARE @Stmt NVARCHAR(MAX) DECLARE curTable CURSOR FOR SELECT ‘[‘ + sch.name + ‘].[‘ + t.name + ‘]’ as TableName FROM…
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],…

Read more
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]…