E9.2 SQL Server index fragmentation

[hittcontact]

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

 

[MFreitag]

You can simply build a maintenance plan via SSMS:


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


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]

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
======================================================================

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:
======================================================================

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]

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
======================================================================

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:
======================================================================

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]

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:

--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'

--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]

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.

 

[Larry_Jones]

Best Free Tools for SQL Server (Better than SSMS Maintenance Plans) is Ola Hallengren's SQL Server Maintenance toolset. https://ola.hallengren.com/
Very easy to install, setup, go and forget.
Handles Index maintenance, simple or sophisticated backups, Statistics maintenance, etc better than MS SSMS.

By the way don't fret to much about index fragmentation. For one thing most indexes that are < 1,000 pages aren't worth rebuilding per MS. For another many shops reorg/rebuild indexes far too often - causing excessive page splits during transaction processing. That's another thing Hallengren's solution is very good at - identifying only those index that should be reorganized or rebuilt.