0

I have a script which iterates through all database and underlying tables. With this script i want to rebuild/reorganize the indexes and i am testing this on AdventureWorks2017 database. So after executing the script, lot of indexes have the same results (before 50%, after 50%) and i don't know why? Can someone explain to me why indexes are not fragmented or the script is not good? Thanks

WHILE @@FETCH_STATUS = 0       
BEGIN    
-- > 30 rebuils
IF @avg_fragmentation_percent > 30      
BEGIN       
     -- > 30 rebuils
     SET @SQLString = 'USE  ' +'['+ @Database_name +']'+ CHAR (13 ) +            
     'ALTER INDEX ' + LTRIM(RTRIM(@IndexName)) + ' ON '+LTRIM(RTRIM(@SchemaName)) +'.' + LTRIM(RTRIM(@TableName)) + ' REBUILD PARTITION = ALL'    
     PRINT @SQLString        
     EXEC (@SQLString)  
END       
ELSE  
BEGIN      
 --<30 reorganize
       SET @SQLString = 'USE  ' +'['+ @Database_name +']'+ CHAR (13 ) +            
      'ALTER INDEX ' + LTRIM(RTRIM(@IndexName))+ ' ON '+LTRIM(RTRIM(@SchemaName)) +'.'+ LTRIM(RTRIM(@TableName)) + ' REORGANIZE  PARTITION = ALL'    
       PRINT @SQLString            
       EXEC (@SQLString)  

       SET @SQLString = 'USE  ' +'['+ @Database_name  +']'+ Char (13 ) +     
       'UPDATE STATISTICS  ' +LTRIM(RTRIM(@SchemaName)) +'.'+ Ltrim(Rtrim(@TableName)) + ' WITH FULLSCAN'       
       PRINT @SQLString    
       EXEC (@SQLString)       
   END
FETCH NEXT FROM dbmaint INTO @TableName, @Database_name, @IndexName, @IndexID,@avg_fragmentation_percent ,@SchemaName       
END
1

I am not sure from where you have got this script however it seems you are trying to rebuild indexes if the fragmentation level is more than 30 and reorganize if the same is less than 30. Your script is actually command to issue reorg and rebuild statement based on other part of code which is not copied here.

You may try below piece of code for inquiring the fragmentation level within a given database:

1st Query:

SELECT dbschemas.[name] as 'Schema', 
dbtables.[name] as 'Table', 
dbindexes.[name] as 'Index',
indexstats.alloc_unit_type_desc,
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 indexstats.page_count >=1000 and
dbindexes.name is not null
ORDER BY indexstats.avg_fragmentation_in_percent desc

2nd Query:

DECLARE @DatabaseID int

SET @DatabaseID = DB_ID()

SELECT DB_NAME(@DatabaseID) AS DatabaseName,
       schemas.[name] AS SchemaName,
       objects.[name] AS ObjectName,
       indexes.[name] AS IndexName,
       objects.type_desc AS ObjectType,
       indexes.type_desc AS IndexType,
       dm_db_index_physical_stats.partition_number AS PartitionNumber,
       dm_db_index_physical_stats.page_count AS [PageCount],
       dm_db_index_physical_stats.avg_fragmentation_in_percent AS AvgFragmentationInPercent
FROM sys.dm_db_index_physical_stats (@DatabaseID, NULL, NULL, NULL, 'LIMITED') dm_db_index_physical_stats
INNER JOIN sys.indexes indexes ON dm_db_index_physical_stats.[object_id] = indexes.[object_id] AND dm_db_index_physical_stats.index_id = indexes.index_id
INNER JOIN sys.objects objects ON indexes.[object_id] = objects.[object_id]
INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id]
WHERE objects.[type] IN('U','V')
AND objects.is_ms_shipped = 0
AND indexes.[type] IN(1,2,3,4)
AND indexes.is_disabled = 0
AND indexes.is_hypothetical = 0
AND dm_db_index_physical_stats.alloc_unit_type_desc = 'IN_ROW_DATA'
AND dm_db_index_physical_stats.index_level = 0
AND dm_db_index_physical_stats.page_count >= 1000

Kindly note that, page count dictates how big is the table and contains how many pages. For performing reorg or rebuild, you may check wonderful script from Mr. Ola Hallengren and use them for your maintenance job, here also you can customize the fragmentation level on which you want to perform reorg or rebuild, it also has option of offline and online, you may read more details at this link.

https://ola.hallengren.com/

You may also check the best practice and possible impacts on website of Mr. Brent Ozar and can be referred as below:

https://www.brentozar.com/archive/2017/12/index-maintenance-madness/

If fragmentation is not showing showing any sign of improvement, that could mean its a very small table having page count much lesser than 1000. Standard practice is to consider only those tables which is at least 1000 pages or more.

Kindly refer below links explaining same:

Why is my database still fragmented after I rebuilt and reindexed everything?

SQL Server 2012, rebuild not lowering avg fragmentation

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/c419f809-c4c6-4c7f-9f36-c9a56d21d37a/index-still-fragmented-after-rebuild?forum=sqldatabaseengine

In case, you have bigger tables however doesn't have clustered index meaning its a heap then you should go for table rebuild with below command:

Alter table SchemaName.TableName rebuild

You can read more details about that on this link.

The most important aspect is to see "How to detect whether index fragmentation affects SQL Server performance". please check more details about that here.

Hope above helps.

  • and why some indexes are not fragmented after Ola script and also if i try manually on the index. Is it depend from page count ? – disaster Jul 18 at 10:00
  • @disaster updated my answer, please check. – Learning_DBAdmin Jul 18 at 11:35
  • 1
    +1 Great answer. Op: Dont waste your time defragging tiny tables unless you can see that it is actually causing performance problems. I highly reccomend Ola Hallengrens scripts. Only rebuild indexes when they really need it.Use query store and learn to read execution plans, tuning queries and indexes will give you better results than pointless index shuffling. – Sir Swears-a-lot Jul 20 at 11:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.