Before we can address fragmentation in your database, we have to understand what causes fragmentation, how you can measure fragmentation, and how your can address fragmentation. After database record insert, update and delete operations there will be empty spaces on pages, the basic storage object. There is nothing wrong empty spaces, since the situation is regular enough, but there is a slight problem with empty spaces. Your database size can be far larger than required, and with additional pages to store data also comes the potential for your queries to take longer as the database engine must access additional pages to collect your data.
To prevent fragmentation, SQL Server provides commands to reorganize and rebuild indexes. Rebuilding an index involves deletion of the old one and creation of a new instance of the index, in which data on pages is neatly compressed and organized as continuously as possible. It is important to note that the index rebuilding operation is rather costly and should be avoided when possible. When fragmentation is insignificant, it is preferable to reorganize the existing index. The reorganizing operation requires less system resources than rebuilding indexes. In addition, reorganization compresses index pages to help save disk space.
The index fragmentation level can be learned from a dynamic system view – sys.dm_db_index_physical_stats:
SELECT
*
FROM
sys.dm_db_index_physical_stats(DB_ID(),
NULL
,
NULL
,
NULL
,
NULL
)
WHERE
avg_fragmentation_in_percent > 0
You can change the “avg_fragmentation_in_percent” value to match the value at which you want to take action, which depends on your environment and database. You might want to only address those indexes that are more than 20 or 30 percent fragmented. Most evaluations will show that defragmentation of indexes with a low level of fragmentation or with a small number of pages does not bring any major improvements, so it probably won’t be worth the effort when working with these types of fragmentation issues.
This script will help identify those indexes with fragmentation and it will generate a script you can execute to resolve the fragmentation issue if the fragmentation (avg_fragmentation_in_percent) is above 30%:
DECLARE
@IsDetailedScan
BIT
SELECT
@IsDetailedScan = 1
DECLARE
@SQL NVARCHAR(
MAX
)
SELECT
@SQL = (
SELECT
'
ALTER INDEX ['
+ i.
name
+ N
'] ON ['
+ SCHEMA_NAME(o.[schema_id]) +
'].['
+ o.
name
+
'] '
+
CASE
WHEN
s.avg_fragmentation_in_percent > 30
THEN
'REBUILD WITH (SORT_IN_TEMPDB = ON'
-- Enterprise, Developer+
CASE
WHEN
SERVERPROPERTY(
'EditionID'
)
IN
(1804890536, -2117995310)
THEN
', ONLINE = ON'
ELSE
''
END
+
')'
ELSE
'REORGANIZE'
END
+
';
'FROM
(
SELECT s.[object_id] , s.index_id, avg_fragmentation_in_percent =
MAX
(s.avg_fragmentation_in_percent)
FROM
sys.dm_db_index_physical_stats(DB_ID(),
NULL
,
NULL
,
NULL
,
CASE
WHEN
@IsDetailedScan = 1
THEN
'DETAILED'
ELSE
'LIMITED'
END
) s
WHERE
s.page_count > 128
-- > 1 MB
AND
s.index_id > 0
-- <> HEAP
AND
s.avg_fragmentation_in_percent > 5
GROUP
BY
s.[object_id], s.index_id
) sJOIN
sys.indexes i
WITH
(NOLOCK)
ON
s.[object_id] = i.[object_id]
AND
s.index_id = i.index_id
JOIN
sys.objects o
WITH
(NOLOCK)
ON
o.[object_id] = s.[object_id]
FOR
XML PATH(
''
), TYPE).value(
'.'
,
'NVARCHAR(MAX)'
)
OPTION
(OPTIMIZE
FOR
(@IsDetailedScan = 1))
--PRINT @SQLEXEC
sys.sp_executesql @SQL