Index rebuilt/Reorganize script didn't remove fragmentation
-
I have a script which I ran last night on one of my DB which is on the dev server but when I check the fragmentation level after the script ran successfully, it is still showing fragmentation.
Code to check the fragmentation.SELECT s.Name,t.name,
ind.name AS IndexName, indexstats.index_type_desc AS IndexType,
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id
INNER JOIN sys.tables t ON indexstats.object_id = t.object_ID AND ind.index_id = indexstats.index_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE
indexstats.avg_fragmentation_in_percent > 5
AND ind.Name is not null
ORDER BY indexstats.avg_fragmentation_in_percent DESCScript which rebuild/reorganize indexes.
declare @schemaName nvarchar(30)
declare @tableName nvarchar(500)
declare @indexName nvarchar(500)
declare @indexType nvarchar(55)
declare @percentFragment decimal(11,2)declare FragmentedTableList cursor for
SELECT s.Name,t.name,
ind.name AS IndexName, indexstats.index_type_desc AS IndexType,
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id
INNER JOIN sys.tables t ON indexstats.object_id = t.object_ID AND ind.index_id = indexstats.index_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE
indexstats.avg_fragmentation_in_percent > 5
AND ind.Name is not null
ORDER BY indexstats.avg_fragmentation_in_percent DESCOPEN FragmentedTableList
FETCH NEXT FROM FragmentedTableList
INTO @schemaName, @tableName, @indexName, @indexType, @percentFragmentWHILE @@FETCH_STATUS = 0
BEGIN
print 'Processing ' + @indexName + 'on table ' + @tableName + ' which is ' + cast(@percentFragment as nvarchar(50)) + ' fragmented'
if(@percentFragment<= 30)
BEGIN
EXEC( 'ALTER INDEX ' + @indexName + ' ON ' +@schemaName+'.'+@tableName + ' REBUILD; ')
print 'Finished reorganizing ' + @indexName + 'on table ' + @tableName
END
ELSE
BEGIN
EXEC( 'ALTER INDEX ' + @indexName + ' ON '+@schemaName+'.' + @tableName + ' REORGANIZE;')
print 'Finished rebuilding ' + @indexName + 'on table ' + @tableName
END
FETCH NEXT FROM FragmentedTableList
INTO @schemaName, @tableName, @indexName, @indexType, @percentFragment
END
CLOSE FragmentedTableList
DEALLOCATE FragmentedTableList -
Hmm... you seem to have your reorg and rebuilds in the wrong places.
-
If index_id = 0, it is a heap and cannot be rebuilt (except by truncating and reloading all the data). You should create a clustered index on the table if you're worried about fragmentation.
If the index has a very low rowcount (<8 pages), it could be in a mixed extent with pages from other objects. This is too small to defragment. Many automated scripts filter out indexes under 1000 pages because the impact of fragmentation is not great enough to worry about.
A very large table in a database with insufficient free space may not be defragmented because there is not enough room to copy all the index pages to do a rebuild.
It is possible to put too much emphasis on defragmenting, and there are some better solutions for addressing it than writing your own scripts. https://www.brentozar.com/archive/2012/08/sql-server-index-fragmentation/
-
You may want to consider implementing Ola Hallengren's Index and Statistic Maintenance solution and streamline these operations, as is mentioned in the URL Scott posted above.
https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html -
It doesn't matter what you use to do the defrags, if you're rebuilding or reorging indexes that suffer bad page splits (inserts other than at the end of the key order), using the standard rules of >=10% reorg <30% and >30% rebuild will actually cause increased blocking while you patiently wait for the bad splits to occur enough to cause fragmentation to reach those levels ESPECIALLY IF THE FILL FACTOR IS 100%!!!!
If you're not going to determine the correct Fill Factor for each index that could suffer fragmentation and you're not going to do a rebuild (only) at 1%, you're screwing yourself and your system.
Also, fragmentation only affects performance of read-aheads. If you don't have much of a read-ahead problem, then don't bother rebuilding any index unless it get's below 70% AVERAGE PAGE FULLNESS (NOT Average Percent of Fragmentation).
If you think I'm sucking bong water through two straws, then please read the following article, which I'll remind you was written by Paul Randal, the guy that wrote the code for sys.dm_db_index_physical_stats...
Here's the snippet for that article that I really want you to read. The Bold/Underlining is mine.
Fragmentation alone is not a sufficient reason to reorganize or rebuild an index. The main effect of fragmentation is that it slows down page read-ahead throughput during index scans. This causes slower response times. If the query workload on a fragmented table or index does not involve scans, because the workload is primarily singleton lookups, removing fragmentation may have no effect.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Also, as further proof that the bong water I'm drinking through two straws actually tastes pretty good, here's the diagram of supposed "Best Practice" methods when the bad page splits happen. And, no... I've not rebuilt indexes on my production boxes since Monday, January 18th, 2016 (more than 2 years ago.) I do have some tables that I'll need to defrag to get some space back but there are just a couple of those.
The Black line is an "append only" trace and the Brown line with the Yellow "Glow" is with no defragmentation and this was for a GUID clustered index for a simulated 1000 rows per hour, ten hours per day, for 365 days (3.65 million rows of 123 bytes each). The smoother and flatter the line is, the better. Any upswing in page-count on this chart is a page split and you can clearly see why you should never defrag an index that you've left at 100% . The "Best Practice" is not the best practice if you want to avoid either page splits or fragmentation.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Scott Coleman - Thursday, April 5, 2018 11:57 AM
You can actually rebuild a heap in sql server 2008 onwards
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply