SET NOCOUNT ON DROP TABLE IF EXISTS [#Fragmented_Indexes] DECLARE @SchemaName sysname, @TableName sysname, @IndexName sysname, @AvgFragmentationInPercent INT, @PageCount INT, @Fragmentation INT, @Stmt NVARCHAR(MAX); SET @PageCount = 1000 SET @AvgFragmentationInPercent = 10 SELECT [s].[name] AS [SchemaName], [t].[name] AS [TableName], [i].[name] AS [IndexName], [ps].[avg_fragmentation_in_percent] [Fragmentation] INTO [#Fragmented_Indexes] FROM [sys].[tables] [t] INNER JOIN…
EXEC sp_MSforeachtable @command1 = “print ‘?’ DBCC DBREINDEX (‘?’, ‘ ‘, 50)” GO EXEC sp_updatestats GO
Rebuild all indexes on table
9. 3. 2018
ALTER INDEX ALL ON dbo.SampleTable REBUILD WITH (FILLFACTOR = 70)
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…
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],…
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]…