I have a very large SQL Server 2008 R2 database (1.5TB) and will be copying some data from column to column within the same table. I've been told that the schema has a large number of indexes and was wondering if there is a default query or script that will rebuild all the indexes. Have also been advised to update the statistics at the same time?
Each of the 30 tables has one clustered index and 13x non-clustered indexes
Thanks.
Try the following script:
Exec sp_msforeachtable 'SET QUOTED_IDENTIFIER ON; ALTER INDEX ALL ON ? REBUILD'
GO
Also
I prefer(After a long search) to use the following script, it contains @fillfactor determines how much percentage of the space on each leaf-level page is filled with data.
DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80
DECLARE TableCursor CURSOR FOR
SELECT QUOTENAME(OBJECT_SCHEMA_NAME([object_id]))+'.' + QUOTENAME(name) AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO
for more info, check the following link:
and if you want to Check Index Fragmentation on Indexes in a Database, try the following script:
SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID() AND dbtables.[name] like '%%'
ORDER BY indexstats.avg_fragmentation_in_percent desc
For more information, Check the following link:
PRINT @sql after the SET @sql = ... line is helpful to give the user some indication of what indexes it is rebuilding and how far it has progressed. –
Micelle Here's another script to add to the list. The key difference with the other ones is that this applies general recommended threshold of rebuild if over 30% fragmentation and reorganize of over 5%. If nothing is over either, nothing happens. This allow you run this script as often as you'd like:
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
DECLARE @TableName varchar(255);
DECLARE @IndexName varchar(255);
DECLARE @Fragmentation FLOAT;
DECLARE @IndexScript varchar(255);
DECLARE @Schema varchar(255);
SELECT
dbtables.[name],
dbindexes.[name],
indexstats.avg_fragmentation_in_percent,
indexstats.page_count [pages]
FROM
sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables
on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas
on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes
ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE
indexstats.database_id = DB_ID()
AND indexstats.avg_fragmentation_in_percent >= 5.0
AND indexstats.page_count > 10
ORDER BY
indexstats.page_count ASC,
indexstats.avg_fragmentation_in_percent ASC
DECLARE TableCursor CURSOR FOR
SELECT
dbtables.[name],
dbindexes.[name],
indexstats.avg_fragmentation_in_percent,
dbschemas.[name]
FROM
sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables
on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas
on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes
ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE
indexstats.database_id = DB_ID()
AND indexstats.avg_fragmentation_in_percent >= 5.0
AND indexstats.page_count > 10
ORDER BY
indexstats.page_count ASC,
indexstats.avg_fragmentation_in_percent ASC;
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO
@TableName,
@IndexName,
@Fragmentation,
@Schema
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@Fragmentation >= 30.0)
SET @IndexScript = 'ALTER INDEX [' + @IndexName + '] ON ' + @Schema + '.' + @TableName + ' REBUILD';
ELSE IF (@Fragmentation >= 5.0)
SET @IndexScript = 'ALTER INDEX [' + @IndexName + '] ON ' + @Schema + '.' + @TableName + ' REORGANIZE';
ELSE
SET @IndexScript = NULL;
IF (@IndexScript IS NOT NULL)
BEGIN
--SELECT @IndexScript
RAISERROR (@IndexScript, 10, 0) WITH NOWAIT
WAITFOR DELAY '00:00:01';
EXEC(@IndexScript);
END
FETCH NEXT FROM TableCursor INTO
@TableName,
@IndexName,
@Fragmentation,
@Schema;
END
CLOSE TableCursor;
DEALLOCATE TableCursor;
I've been using this for a couple years. The script will output a table as well as messages, which means if you're feeling like it's taking too long you can see what it's doing in semi-realtime, (if you run it in SQL Management Studio).
Replace the "YOUR DATABASE NAME" in the query below.
DECLARE @Database NVARCHAR(255)
DECLARE @Table NVARCHAR(255)
DECLARE @cmd NVARCHAR(1000)
DECLARE DatabaseCursor CURSOR READ_ONLY FOR
SELECT name FROM master.sys.databases
WHERE name IN ('YOUR DATABASE NAME') -- databases
AND state = 0 -- database is online
AND is_in_standby = 0 -- database is not read only for log shipping
ORDER BY 1
OPEN DatabaseCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'DECLARE TableCursor CURSOR READ_ONLY FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' +
table_name + '']'' as tableName FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'''
-- create table cursor
EXEC (@cmd)
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD'
PRINT @cmd -- uncomment if you want to see commands
EXEC (@cmd)
END TRY
BEGIN CATCH
PRINT '---'
PRINT @cmd
PRINT ERROR_MESSAGE()
PRINT '---'
END CATCH
FETCH NEXT FROM TableCursor INTO @Table
END
CLOSE TableCursor
DEALLOCATE TableCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
Also a good script, although my laptop ran out of memory, but this was on a very large table
https://basitaalishan.com/2014/02/23/rebuild-all-indexes-on-all-tables-in-the-sql-server-database/
USE [<mydatabasename>]
Go
--/* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--Arguments Data Type Description
-------------- ------------ ------------
--@FillFactor [int] Specifies a percentage that indicates how full the Database Engine should make the leaf level
-- of each index page during index creation or alteration. The valid inputs for this parameter
-- must be an integer value from 1 to 100 The default is 0.
-- For more information, see http://technet.microsoft.com/en-us/library/ms177459.aspx.
--@PadIndex [varchar](3) Specifies index padding. The PAD_INDEX option is useful only when FILLFACTOR is specified,
-- because PAD_INDEX uses the percentage specified by FILLFACTOR. If the percentage specified
-- for FILLFACTOR is not large enough to allow for one row, the Database Engine internally
-- overrides the percentage to allow for the minimum. The number of rows on an intermediate
-- index page is never less than two, regardless of how low the value of fillfactor. The valid
-- inputs for this parameter are ON or OFF. The default is OFF.
-- For more information, see http://technet.microsoft.com/en-us/library/ms188783.aspx.
--@SortInTempDB [varchar](3) Specifies whether to store temporary sort results in tempdb. The valid inputs for this
-- parameter are ON or OFF. The default is OFF.
-- For more information, see http://technet.microsoft.com/en-us/library/ms188281.aspx.
--@OnlineRebuild [varchar](3) Specifies whether underlying tables and associated indexes are available for queries and data
-- modification during the index operation. The valid inputs for this parameter are ON or OFF.
-- The default is OFF.
-- Note: Online index operations are only available in Enterprise edition of Microsoft
-- SQL Server 2005 and above.
-- For more information, see http://technet.microsoft.com/en-us/library/ms191261.aspx.
--@DataCompression [varchar](4) Specifies the data compression option for the specified index, partition number, or range of
-- partitions. The options for this parameter are as follows:
-- > NONE - Index or specified partitions are not compressed.
-- > ROW - Index or specified partitions are compressed by using row compression.
-- > PAGE - Index or specified partitions are compressed by using page compression.
-- The default is NONE.
-- Note: Data compression feature is only available in Enterprise edition of Microsoft
-- SQL Server 2005 and above.
-- For more information about compression, see http://technet.microsoft.com/en-us/library/cc280449.aspx.
--@MaxDOP [int] Overrides the max degree of parallelism configuration option for the duration of the index
-- operation. The valid input for this parameter can be between 0 and 64, but should not exceed
-- number of processors available to SQL Server.
-- For more information, see http://technet.microsoft.com/en-us/library/ms189094.aspx.
--- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -*/
-- Ensure a USE <databasename> statement has been executed first.
SET NOCOUNT ON;
DECLARE @Version [numeric] (18, 10)
,@SQLStatementID [int]
,@CurrentTSQLToExecute [nvarchar](max)
,@FillFactor [int] = 100 -- Change if needed
,@PadIndex [varchar](3) = N'OFF' -- Change if needed
,@SortInTempDB [varchar](3) = N'OFF' -- Change if needed
,@OnlineRebuild [varchar](3) = N'OFF' -- Change if needed
,@LOBCompaction [varchar](3) = N'ON' -- Change if needed
,@DataCompression [varchar](4) = N'NONE' -- Change if needed
,@MaxDOP [int] = NULL -- Change if needed
,@IncludeDataCompressionArgument [char](1);
IF OBJECT_ID(N'TempDb.dbo.#Work_To_Do') IS NOT NULL
DROP TABLE #Work_To_Do
CREATE TABLE #Work_To_Do
(
[sql_id] [int] IDENTITY(1, 1)
PRIMARY KEY ,
[tsql_text] [varchar](1024) ,
[completed] [bit]
)
SET @Version = CAST(LEFT(CAST(SERVERPROPERTY(N'ProductVersion') AS [nvarchar](128)), CHARINDEX('.', CAST(SERVERPROPERTY(N'ProductVersion') AS [nvarchar](128))) - 1) + N'.' + REPLACE(RIGHT(CAST(SERVERPROPERTY(N'ProductVersion') AS [nvarchar](128)), LEN(CAST(SERVERPROPERTY(N'ProductVersion') AS [nvarchar](128))) - CHARINDEX('.', CAST(SERVERPROPERTY(N'ProductVersion') AS [nvarchar](128)))), N'.', N'') AS [numeric](18, 10))
IF @DataCompression IN (N'PAGE', N'ROW', N'NONE')
AND (
@Version >= 10.0
AND SERVERPROPERTY(N'EngineEdition') = 3
)
BEGIN
SET @IncludeDataCompressionArgument = N'Y'
END
IF @IncludeDataCompressionArgument IS NULL
BEGIN
SET @IncludeDataCompressionArgument = N'N'
END
INSERT INTO #Work_To_Do ([tsql_text], [completed])
SELECT 'ALTER INDEX [' + i.[name] + '] ON' + SPACE(1) + QUOTENAME(t2.[TABLE_CATALOG]) + '.' + QUOTENAME(t2.[TABLE_SCHEMA]) + '.' + QUOTENAME(t2.[TABLE_NAME]) + SPACE(1) + 'REBUILD WITH (' + SPACE(1) + + CASE
WHEN @PadIndex IS NULL
THEN 'PAD_INDEX =' + SPACE(1) + CASE i.[is_padded]
WHEN 1
THEN 'ON'
WHEN 0
THEN 'OFF'
END
ELSE 'PAD_INDEX =' + SPACE(1) + @PadIndex
END + CASE
WHEN @FillFactor IS NULL
THEN ', FILLFACTOR =' + SPACE(1) + CONVERT([varchar](3), REPLACE(i.[fill_factor], 0, 100))
ELSE ', FILLFACTOR =' + SPACE(1) + CONVERT([varchar](3), @FillFactor)
END + CASE
WHEN @SortInTempDB IS NULL
THEN ''
ELSE ', SORT_IN_TEMPDB =' + SPACE(1) + @SortInTempDB
END + CASE
WHEN @OnlineRebuild IS NULL
THEN ''
ELSE ', ONLINE =' + SPACE(1) + @OnlineRebuild
END + ', STATISTICS_NORECOMPUTE =' + SPACE(1) + CASE st.[no_recompute]
WHEN 0
THEN 'OFF'
WHEN 1
THEN 'ON'
END + ', ALLOW_ROW_LOCKS =' + SPACE(1) + CASE i.[allow_row_locks]
WHEN 0
THEN 'OFF'
WHEN 1
THEN 'ON'
END + ', ALLOW_PAGE_LOCKS =' + SPACE(1) + CASE i.[allow_page_locks]
WHEN 0
THEN 'OFF'
WHEN 1
THEN 'ON'
END + CASE
WHEN @IncludeDataCompressionArgument = N'Y'
THEN CASE
WHEN @DataCompression IS NULL
THEN ''
ELSE ', DATA_COMPRESSION =' + SPACE(1) + @DataCompression
END
ELSE ''
END + CASE
WHEN @MaxDop IS NULL
THEN ''
ELSE ', MAXDOP =' + SPACE(1) + CONVERT([varchar](2), @MaxDOP)
END + SPACE(1) + ')'
,0
FROM [sys].[tables] t1
INNER JOIN [sys].[indexes] i ON t1.[object_id] = i.[object_id]
AND i.[index_id] > 0
AND i.[type] IN (1, 2)
INNER JOIN [INFORMATION_SCHEMA].[TABLES] t2 ON t1.[name] = t2.[TABLE_NAME]
AND t2.[TABLE_TYPE] = 'BASE TABLE'
INNER JOIN [sys].[stats] AS st WITH (NOLOCK) ON st.[object_id] = t1.[object_id]
AND st.[name] = i.[name]
SELECT @SQLStatementID = MIN([sql_id])
FROM #Work_To_Do
WHERE [completed] = 0
WHILE @SQLStatementID IS NOT NULL
BEGIN
SELECT @CurrentTSQLToExecute = [tsql_text]
FROM #Work_To_Do
WHERE [sql_id] = @SQLStatementID
PRINT @CurrentTSQLToExecute
EXEC [sys].[sp_executesql] @CurrentTSQLToExecute
UPDATE #Work_To_Do
SET [completed] = 1
WHERE [sql_id] = @SQLStatementID
SELECT @SQLStatementID = MIN([sql_id])
FROM #Work_To_Do
WHERE [completed] = 0
END
DECLARE @Database NVARCHAR(255)
DECLARE @Table NVARCHAR(255)
DECLARE @cmd NVARCHAR(1000)
DECLARE DatabaseCursor CURSOR READ_ONLY FOR
SELECT name FROM master.sys.databases
WHERE name NOT IN ('master','msdb','tempdb','model','distribution') -- databases to exclude
--WHERE name IN ('DB1', 'DB2') -- use this to select specific databases and comment out line above
AND state = 0 -- database is online
AND is_in_standby = 0 -- database is not read only for log shipping
ORDER BY 1
OPEN DatabaseCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'DECLARE TableCursor CURSOR READ_ONLY FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' +
table_name + '']'' as tableName FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'''
-- create table cursor
EXEC (@cmd)
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD'
--PRINT @cmd -- uncomment if you want to see commands
EXEC (@cmd)
END TRY
BEGIN CATCH
PRINT '---'
PRINT @cmd
PRINT ERROR_MESSAGE()
PRINT '---'
END CATCH
FETCH NEXT FROM TableCursor INTO @Table
END
CLOSE TableCursor
DEALLOCATE TableCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
Daniel's script appears to be a good all encompassing solution, but even he admitted that his laptop ran out of memory. Here is an option I came up with. I based my procedure off of Mohammad Nizamuddin's post on TechNet. I added an initial cursor loop that pulls all the database names into a temporary table and then uses that to pull all the base table names from each of those databases.
You can optionally pass the fill factor you would prefer and specify a target database if you do not want to re-index all databases.
--===============================================================
-- Name: sp_RebuildAllIndexes
-- Arguments: [Fill Factor], [Target Database name]
-- Purpose: Loop through all the databases on a server and
-- compile a list of all the table within them.
-- This list is then used to rebuild indexes for
-- all the tables in all the database. Optionally,
-- you may pass a specific database name if you only
-- want to reindex that target database.
--================================================================
CREATE PROCEDURE sp_RebuildAllIndexes(
@FillFactor INT = 90,
@TargetDatabase NVARCHAR(100) = NULL)
AS
BEGIN
DECLARE @TablesToReIndex TABLE (
TableName VARCHAR(200)
);
DECLARE @DbName VARCHAR(50);
DECLARE @TableSelect VARCHAR(MAX);
DECLARE @DatabasesToIndex CURSOR;
IF ISNULL( @TargetDatabase, '' ) = ''
SET @DatabasesToIndex = CURSOR
FOR SELECT NAME
FROM master..sysdatabases
ELSE
SET @DatabasesToIndex = CURSOR
FOR SELECT NAME
FROM master..sysdatabases
WHERE NAME = @TargetDatabase
OPEN DatabasesToIndex
FETCH NEXT FROM DatabasesToIndex INTO @DbName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @TableSelect = 'INSERT INTO @TablesToReIndex SELECT CONCAT(TABLE_CATALOG, ''.'', TABLE_SCHEMA, ''.'', TABLE_NAME) AS TableName FROM '
+ @DbName
+ '.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''base table''';
EXEC sp_executesql
@TableSelect;
FETCH NEXT FROM DatabasesToIndex INTO @DbName
END
CLOSE DatabasesToIndex
DEALLOCATE DatabasesToIndex
DECLARE @TableName VARCHAR(255)
DECLARE TableCursor CURSOR FOR
SELECT TableName
FROM @TablesToReIndex
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName, ' ', @FillFactor)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
END
DECLARE @String NVARCHAR(MAX);
USE Databse Name;
SELECT @String
=
(
SELECT 'ALTER INDEX [' + dbindexes.[name] + '] ON [' + db.name + '].[' + dbschemas.[name] + '].[' + dbtables.[name]
+ '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);' + CHAR(10) AS [text()]
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables
ON dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas
ON dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes
ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
INNER JOIN sys.databases AS db
ON db.database_id = indexstats.database_id
WHERE dbindexes.name IS NOT NULL
AND indexstats.database_id = DB_ID()
AND indexstats.avg_fragmentation_in_percent >= 10
ORDER BY indexstats.page_count DESC
FOR XML PATH('')
);
EXEC (@String);
Quick and dirty MS SQL:
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?')"
EXEC sp_MSforeachtable @command1="print '?' UPDATE STATISTICS ?"
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?',' ',80)"
This is taking @ShortFuse answer and modifying if the tables are not in the default schema:
use Stat
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
DECLARE @TableName varchar(255);
DECLARE @IndexName varchar(255);
DECLARE @SchemaName varchar(255);
DECLARE @Fragmentation FLOAT;
DECLARE @IndexScript varchar(255);
SELECT
dbtables.[name],
dbindexes.[name],
indexstats.avg_fragmentation_in_percent,
indexstats.page_count [pages]
FROM
sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables
on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas
on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes
ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE
indexstats.database_id = DB_ID()
AND indexstats.avg_fragmentation_in_percent >= 5.0
AND indexstats.page_count > 10
ORDER BY
indexstats.page_count ASC,
indexstats.avg_fragmentation_in_percent ASC
DECLARE TableCursor CURSOR FOR
SELECT
dbtables.[name],
dbindexes.[name],
dbschemas.[name],
indexstats.avg_fragmentation_in_percent
FROM
sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables
on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas
on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes
ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE
indexstats.database_id = DB_ID()
AND indexstats.avg_fragmentation_in_percent >= 5.0
AND indexstats.page_count > 10
ORDER BY
dbschemas.[name],
dbtables.[name];
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO
@TableName,
@IndexName,
@SchemaName,
@Fragmentation
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@Fragmentation >= 30.0)
SET @IndexScript = 'ALTER INDEX ' + @IndexName + ' ON ' + @SchemaName + '.' + @TableName + ' REBUILD';
ELSE IF (@Fragmentation >= 5.0)
SET @IndexScript = 'ALTER INDEX ' + @IndexName + ' ON ' + @SchemaName + '.' + @TableName + ' REORGANIZE';
ELSE
SET @IndexScript = NULL;
IF (@IndexScript IS NOT NULL)
BEGIN
RAISERROR (@IndexScript, 10, 0) WITH NOWAIT
WAITFOR DELAY '00:00:01';
EXEC(@IndexScript);
END
FETCH NEXT FROM TableCursor INTO
@TableName,
@IndexName,
@SchemaName,
@Fragmentation;
END
CLOSE TableCursor;
DEALLOCATE TableCursor;
Oh man, I wouldn't suggest just rebuilding all your indexes on all databases/tables without seeing how fragmented they are. You are taking huge performance hits for no gains. Make sure you check the amount of fragmentation per index and act on it accordingly. Usually anything over 15% is worth a reorganize until 30% where you would opt for rebuild instead. This also depends on your page count, size of index and activity on your tables. Some tables will always have high fragmentation and may not be worth touching at all if all it does is add/delete constantly unless you're seeing a performance issue on those tables.
© 2022 - 2024 — McMap. All rights reserved.