E9.2 SQL Server index fragmentation

hittcontact

Member
All
Wanted to check how you keep up with index management for your production SQL databases. Are there any tools available? Any idea how long will it take for a 600gb database where indices are fragmented by over 90%?

Can someone share the script for rebuilding/re-organizing?

TIA
 

schojo44

Well Known Member
You can simply build a maintenance plan via SSMS:
1651480608597.png

With lots of options and schedules, just as you need it:
1651480660533.png

And then let that run supervised in the beginning to get a feeling for it and afterwards schedule it to run at night or on the weekends, depending on your workload etc.
Note of caution: An Index Rebuild will mess with pointers, so if you rebuild an index while a large UBE is running e.g. you might get wonky results!

About your question for how it will take: Not possible to answer with the few information given, but shouldn't be too bad if your hardware isn't the worst.
 

brother_of_karamazov

Legendary Poster
All
Wanted to check how you keep up with index management for your production SQL databases. Are there any tools available? Any idea how long will it take for a 600gb database where indices are fragmented by over 90%?

Can someone share the script for rebuilding/re-organizing?

TIA
Checking index fragmentation manually:

One-time per server create function fn_PhysicalIndexStatistics_Wrapper
======================================================================

SQL:
USE MASTER
GO
IF exists (select * from sys.objects where name = N'fn_PhysicalIndexStatistics_Wrapper')
    DROP FUNCTION fn_PhysicalIndexStatistics_Wrapper;
GO
CREATE FUNCTION fn_PhysicalIndexStatistics_Wrapper 
(@DatabaseID INT, 
@ObjectID INT, 
@IndexID INT, 
@PartitionNumber INT, 
@Mode VARCHAR) 
 
RETURNS @IndexStats TABLE 
([Object ID] INT,
[Index ID] INT,
[Index Description] VARCHAR(30), 
[Avg. Fragmentation %] FLOAT, 
[Fragment Count] BIGINT,
[Page Count] BIGINT,
[Alloc. Unit Type] nvarchar(60))
 
BEGIN     
INSERT INTO @IndexStats 
([Object ID], [Index ID], [Index Description],  [Avg. Fragmentation %], [Fragment Count], [Page Count], [Alloc. Unit Type])

SELECT 
object_id, index_id, index_type_desc, round(avg_fragmentation_in_percent, 0), fragment_count, page_count, alloc_unit_type_desc
FROM sys.dm_db_index_physical_stats 
(@DatabaseID, 
@ObjectID, 
@IndexID, 
@PartitionNumber, 
@Mode)

RETURN 
END

Then execute:
======================================================================
SQL:
use databasename

SELECT cast(DB_NAME() + '.' + sys.schemas.name + '.' + sys.objects.name as char(40)) as 'Object Name',
cast(sys.indexes.name as char(40)) as 'Index Name',
master.dbo.fn_PhysicalIndexStatistics_Wrapper.[Index Description],
master.dbo.fn_PhysicalIndexStatistics_Wrapper.[Avg. Fragmentation %],
master.dbo.fn_PhysicalIndexStatistics_Wrapper.[Fragment Count],
master.dbo.fn_PhysicalIndexStatistics_Wrapper.[Page Count]
FROM sys.objects 
JOIN sys.schemas on sys.objects.schema_id = sys.schemas.schema_id
CROSS APPLY master.dbo.fn_PhysicalIndexStatistics_Wrapper(DB_ID(), object_id, NULL, NULL, NULL) 
JOIN sys.indexes
on master.dbo.fn_PhysicalIndexStatistics_Wrapper.[Object Id]=sys.indexes.object_id
and master.dbo.fn_PhysicalIndexStatistics_Wrapper.[Index Id]=sys.indexes.index_id
WHERE [Index Description] ! = 'HEAP'
--and [Page Count] > 100
--order by [Object Name], [Index Name]
--or
order by [Avg. Fragmentation %] desc
 

brother_of_karamazov

Legendary Poster
All
Wanted to check how you keep up with index management for your production SQL databases. Are there any tools available? Any idea how long will it take for a 600gb database where indices are fragmented by over 90%?

Can someone share the script for rebuilding/re-organizing?

TIA
Checking index fragmentation manually:

One-time per server create function fn_PhysicalIndexStatistics_Wrapper
======================================================================

SQL:
USE MASTER
GO
IF exists (select * from sys.objects where name = N'fn_PhysicalIndexStatistics_Wrapper')
    DROP FUNCTION fn_PhysicalIndexStatistics_Wrapper;
GO
CREATE FUNCTION fn_PhysicalIndexStatistics_Wrapper 
(@DatabaseID INT, 
@ObjectID INT, 
@IndexID INT, 
@PartitionNumber INT, 
@Mode VARCHAR) 
 
RETURNS @IndexStats TABLE 
([Object ID] INT,
[Index ID] INT,
[Index Description] VARCHAR(30), 
[Avg. Fragmentation %] FLOAT, 
[Fragment Count] BIGINT,
[Page Count] BIGINT,
[Alloc. Unit Type] nvarchar(60))
 
BEGIN     
INSERT INTO @IndexStats 
([Object ID], [Index ID], [Index Description],  [Avg. Fragmentation %], [Fragment Count], [Page Count], [Alloc. Unit Type])

SELECT 
object_id, index_id, index_type_desc, round(avg_fragmentation_in_percent, 0), fragment_count, page_count, alloc_unit_type_desc
FROM sys.dm_db_index_physical_stats 
(@DatabaseID, 
@ObjectID, 
@IndexID, 
@PartitionNumber, 
@Mode)

RETURN 
END

Then execute:
======================================================================
SQL:
use databasename

SELECT cast(DB_NAME() + '.' + sys.schemas.name + '.' + sys.objects.name as char(40)) as 'Object Name',
cast(sys.indexes.name as char(40)) as 'Index Name',
master.dbo.fn_PhysicalIndexStatistics_Wrapper.[Index Description],
master.dbo.fn_PhysicalIndexStatistics_Wrapper.[Avg. Fragmentation %],
master.dbo.fn_PhysicalIndexStatistics_Wrapper.[Fragment Count],
master.dbo.fn_PhysicalIndexStatistics_Wrapper.[Page Count]
FROM sys.objects 
JOIN sys.schemas on sys.objects.schema_id = sys.schemas.schema_id
CROSS APPLY master.dbo.fn_PhysicalIndexStatistics_Wrapper(DB_ID(), object_id, NULL, NULL, NULL) 
JOIN sys.indexes
on master.dbo.fn_PhysicalIndexStatistics_Wrapper.[Object Id]=sys.indexes.object_id
and master.dbo.fn_PhysicalIndexStatistics_Wrapper.[Index Id]=sys.indexes.index_id
WHERE [Index Description] ! = 'HEAP'
--and [Page Count] > 100
--order by [Object Name], [Index Name]
--or
order by [Avg. Fragmentation %] desc
 

brother_of_karamazov

Legendary Poster
You can simply build a maintenance plan via SSMS:
View attachment 19327

With lots of options and schedules, just as you need it:
View attachment 19328

And then let that run supervised in the beginning to get a feeling for it and afterwards schedule it to run at night or on the weekends, depending on your workload etc.
Note of caution: An Index Rebuild will mess with pointers, so if you rebuild an index while a large UBE is running e.g. you might get wonky results!

About your question for how it will take: Not possible to answer with the few information given, but shouldn't be too bad if your hardware isn't the worst.

For maintenance, create an Agent job using Ola's scripts from https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html that include both index and statistics maintenance.

Here's the settings I use:

SQL:
--Weekend heavy

EXECUTE [master].[dbo].[IndexOptimize]
@Databases = 'USER_DATABASES',
@LogToTable = 'Y',
@FragmentationLow = NULL,
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_OFFLINE,INDEX_REBUILD_ONLINE',
--or @FragmentationHigh = 'INDEX_REBUILD_ONLINE',
--if on a busy system during index rebuilds
@MinNumberOfPages = '300',
@SortInTempdb = 'Y',
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'


SQL:
--Weekday light

EXECUTE [master].[dbo].[IndexOptimize]
@Databases = 'USER_DATABASES',
@LogToTable = 'Y',
@FragmentationLow = NULL,
@FragmentationLevel1 = 50,
@FragmentationLevel2 = 80,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE',
@MinNumberOfPages = '300',
@SortInTempdb = 'Y'
 

brother_of_karamazov

Legendary Poster
All
Wanted to check how you keep up with index management for your production SQL databases. Are there any tools available? Any idea how long will it take for a 600gb database where indices are fragmented by over 90%?

Can someone share the script for rebuilding/re-organizing?

TIA

Duration of the rebuild is mostly dependent on disk IO performance. My guess is about 1.5 - 2 hours on SSD.
 
Top