SQL Server rebuilding indexes - script

Question

I'm using a script from @Namphibian, but I have some problems there.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

CREATE TABLE #FragmentedIndexes
(
    DatabaseName SYSNAME,
    SchemaName SYSNAME,
    TableName SYSNAME,
    IndexName SYSNAME,
    [Fragmentation%] FLOAT
)

INSERT INTO #FragmentedIndexes
    SELECT
        DB_NAME(DB_ID()) AS DatabaseName,
        ss.name AS SchemaName,
        OBJECT_NAME (s.object_id) AS TableName,
        i.name AS IndexName,
        s.avg_fragmentation_in_percent AS [Fragmentation%]
    FROM 
        sys.dm_db_index_physical_stats(db_id(),NULL, NULL, NULL, 'SAMPLED') s
    INNER JOIN 
        sys.indexes i ON s.[object_id] = i.[object_id]
                      AND s.index_id = i.index_id
    INNER JOIN 
        sys.objects o ON s.object_id = o.object_id
    INNER JOIN 
        sys.schemas ss ON ss.[schema_id] = o.[schema_id]
    WHERE 
        s.database_id = DB_ID()
        AND i.index_id != 0
        AND s.record_count > 0
        AND o.is_ms_shipped = 0

DECLARE @RebuildIndexesSQL NVARCHAR(MAX)

SET @RebuildIndexesSQL = ''
SELECT
 @RebuildIndexesSQL = @RebuildIndexesSQL +
CASE
 WHEN [Fragmentation%] > 30
   THEN CHAR(10) + 'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON '
      + QUOTENAME(SchemaName) + '.'
      + QUOTENAME(TableName) + ' REBUILD;'
 WHEN [Fragmentation%] > 10
    THEN CHAR(10) + 'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON '
    + QUOTENAME(SchemaName) + '.'
    + QUOTENAME(TableName) + ' REORGANIZE;'
END
FROM #FragmentedIndexes
WHERE [Fragmentation%] > 10
DECLARE @StartOffset INT
DECLARE @Length INT
SET @StartOffset = 0
SET @Length = 4000
WHILE (@StartOffset < LEN(@RebuildIndexesSQL))
BEGIN
 PRINT SUBSTRING(@RebuildIndexesSQL, @StartOffset, @Length)
 SET @StartOffset = @StartOffset + @Length
END

PRINT SUBSTRING(@RebuildIndexesSQL, @StartOffset, @Length)
EXECUTE sp_executesql @RebuildIndexesSQL

DROP TABLE #FragmentedIndexes

But instead of 'SAMPLED' I'm using 'DETAILED' but still some indexes are not rebuilt. I found a few indexes with the same value of fragmentation over 30% which still wasn't rebuilded or reorganized yet. That script is running every night last 4 days. My problem is I can't use Maintentance plans for this task.

Any ideas please?

It must be because the tables are too small, check s.page_count for those indexes — Denis Rubashkin, Aug 15 '18 at 06:31
Did you try to rebuild one of those indexes manually? Does this lead to a different result than the script? — Tyron78, Aug 15 '18 at 08:40
Rather than re-invent the wheel, take a look at [Ola Hallengren's Maintenance Solution](https://ola.hallengren.com/). This is highly regarded and widely used in the SQL Server community for database maintenance. — Dan Guzman, Aug 15 '18 at 10:15
Now I'm looking at the number of non-rebuilded indexes and it's about one hundred. I know it's a huge database with lots of indexes. @ Tyron78 I'll try rebuild some that's a good idea thanks, tomorrow I'll post here results. @DanGuzman thanks Dan, but it's too complicated for me I'm not so good at script reading to find something there as I said it's too complicated for me :( I'll just need something simple to take my indexes from one specific database and determine which one is over 30% and just rebuild them. — Roman Schmidt, Aug 15 '18 at 11:58
@RomaneS are you still have problem with this? what happened when you rebuild on of those index manually as tyron78 say? — polzka90, Aug 17 '18 at 14:07

score 1 · Answer 1 · answered Aug 17 '18 at 17:28

According to this answer: https://dba.stackexchange.com/questions/18372/why-index-rebuild-does-not-reduce-index-fragmentatation

You need to consider the number of page of your index to know if you do a rebuild

I will recommend to change your INSERT INTO SELECT to this

    SELECT
    DB_NAME(DB_ID()) AS DatabaseName,
    ss.name AS SchemaName,
    OBJECT_NAME (s.object_id) AS TableName,
    i.name AS IndexName,
    s.avg_fragmentation_in_percent AS [Fragmentation%],
    page_count
FROM 
    sys.dm_db_index_physical_stats(db_id(),NULL, NULL, NULL, 'DETAILED') s
INNER JOIN 
    sys.indexes i ON s.[object_id] = i.[object_id]
                  AND s.index_id = i.index_id
INNER JOIN 
    sys.objects o ON s.object_id = o.object_id
INNER JOIN 
    sys.schemas ss ON ss.[schema_id] = o.[schema_id]
WHERE 
    s.database_id = DB_ID()
    AND i.index_id != 0
    AND s.record_count > 0
    AND o.is_ms_shipped = 0
    AND s.avg_fragmentation_in_percent > 0
    AND page_count > 1000

Hi @polzka90 when I try change that lines it end up with this error: Msg 213, Level 16, State 1, Line 11 Column name or number of supplied values does not match table definition. Any suggestions what I did wrong ? :( — Roman Schmidt, Aug 20 '18 at 15:27
sorry for the delay, can you put some link to see all new query, i test it and it works — polzka90, Aug 22 '18 at 17:10

SQL Server rebuilding indexes - script

1 Answers1