Rebuild all indexes for all tables and all databases
Rédigé par Sozezzo - - Aucun commentaireMaintain database indexes
You can just create script or run right now the reindex.
Verify the configuration to filter databases or to change the FILLFACTOR.
PRINT '-- ***************************************** --' PRINT '-- Reindex all tables' PRINT '-- on selected databases' PRINT '-- ***************************************** --' PRINT '--' PRINT '/*' PRINT @@servername; PRINT Getdate(); PRINT '*/' SET NOCOUNT ON; -- CONFIGURATION DECLARE @runScriptRightNow INT = 0; DECLARE @fillfactor AS NVARCHAR(10) = '99' -- occuped% -- https://docs.microsoft.com/en-us/sql/relational-databases/indexes/specify-fill-factor-for-an-index BEGIN TRY DROP TABLE #dbs; END TRY BEGIN CATCH END CATCH; SELECT [name] INTO #dbs FROM sys.databases WHERE database_id > 4 AND name NOT IN ('MyDatabaseNoReindex') ORDER BY [name] -- CONFIGURATION USE [MASTER]; DECLARE @dbname nvarchar(200); DECLARE @sql NVARCHAR(MAX); DECLARE @ToKill AS NVARCHAR(MAX); DECLARE @templateSelectTable AS NVARCHAR(MAX) = 'SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' + table_name + '']'' as tableName FROM [$(dbname)].INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE''' DECLARE @templateReindex AS NVARCHAR(MAX) ='ALTER INDEX ALL ON $(tablename) REBUILD WITH (FILLFACTOR = $(fillfactor));' SET @templateReindex = REPLACE(@templateReindex,'$(fillfactor)',@fillfactor) DECLARE @table AS table (TableName nvarchar(256)) DECLARE @script AS table (Idx int identity,SqlScript nvarchar(max)) DECLARE @Idx int; DECLARE @SqlScript nvarchar(max) WHILE (EXISTS(SELECT * FROM #dbs)) BEGIN SELECT TOP 1 @dbname = name FROM #dbs; DELETE FROM #dbs WHERE name = @dbname; SET @sql = REPLACE(@templateSelectTable,'$(dbname)',@dbname); DELETE FROM @table; INSERT INTO @table (TableName) EXEC(@sql) INSERT INTO @script(SqlScript) select REPLACE(@templateReindex,'$(tablename)',TableName) from @table; WHILE (EXISTS(SELECT * FROM @script)) BEGIN SELECT TOP 1 @Idx = Idx, @SqlScript = SqlScript FROM @script t; DELETE FROM @script WHERE Idx = @Idx; IF (@runScriptRightNow = 1) BEGIN RAISERROR (@SqlScript, 10, 1) WITH NOWAIT; EXEC (@SqlScript); END ELSE PRINT @SqlScript END END
Same code with safe mode and try again
GO PRINT '-- ***************************************** --' PRINT '-- Reindex all tables with safe mode' PRINT '-- on selected databases' PRINT '-- ***************************************** --' PRINT '--' PRINT '-- ' +@@servername; PRINT '-- ' +cast(Getdate() as nvarchar(100)); SET NOCOUNT ON; -- CONFIGURATION DECLARE @runScriptRightNow INT = 1; DECLARE @fillfactor AS NVARCHAR(10) = '98' -- occuped% -- https://docs.microsoft.com/en-us/sql/relational-databases/indexes/specify-fill-factor-for-an-index BEGIN TRY DROP TABLE #dbs; END TRY BEGIN CATCH END CATCH; SELECT [name] INTO #dbs FROM sys.databases WHERE database_id > 4 AND name IN ('MyDatabaseNoReindex') ORDER BY [name] -- CONFIGURATION$(ESCAPE_NONE(dbname)) USE [MASTER]; DECLARE @dbname nvarchar(200); DECLARE @sql NVARCHAR(MAX); DECLARE @ToKill AS NVARCHAR(MAX); DECLARE @templateSelectTable AS NVARCHAR(MAX) = 'SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' + table_name + '']'' as tableName FROM [[#dbname]].INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE''' DECLARE @templateReindex AS NVARCHAR(MAX) =' DECLARE @tryAgain INT = 2; WHILE (@tryAgain>0) BEGIN BEGIN TRY PRINT ''-- REINDEX Table : [#tablename]''; ALTER INDEX ALL ON [#tablename] REBUILD WITH (FILLFACTOR = [#fillfactor])); END TRY BEGIN CATCH SET @tryAgain = @tryAgain - 1; WAITFOR DELAY ''00:00:10''; CONTINUE; END CATCH BREAK; END; ' SET @templateReindex = REPLACE(@templateReindex,'[#fillfactor])',@fillfactor) DECLARE @table AS table (TableName nvarchar(256)) DECLARE @script AS table (Idx int identity,SqlScript nvarchar(max)) DECLARE @Idx int; DECLARE @SqlScript nvarchar(max) WHILE (EXISTS(SELECT * FROM #dbs)) BEGIN SELECT TOP 1 @dbname = name FROM #dbs; DELETE FROM #dbs WHERE name = @dbname; SET @sql = REPLACE(@templateSelectTable,'[#dbname]',@dbname); DELETE FROM @table; INSERT INTO @table (TableName) EXEC(@sql) INSERT INTO @script(SqlScript) select REPLACE(@templateReindex,'[#tablename]',TableName) from @table; WHILE (EXISTS(SELECT * FROM @script)) BEGIN SELECT TOP 1 @Idx = Idx, @SqlScript = SqlScript FROM @script t; DELETE FROM @script WHERE Idx = @Idx; IF (@runScriptRightNow = 1) BEGIN RAISERROR (@SqlScript, 10, 1) WITH NOWAIT; RAISERROR ('GO', 10, 1) WITH NOWAIT; EXEC (@SqlScript); END ELSE PRINT @SqlScript END END GO
Sources :
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/specify-fill-factor-for-an-index
https://www.mssqltips.com/sqlservertip/1367/sql-server-script-to-rebuild-all-indexes-for-all-tables-and-all-databases/