Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
/****** Object: StoredProcedure [dbo].[usp_showIndexFragmentation] Script Date: 12/10/2019 3:15:50 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER procedure [dbo].[usp_showIndexFragmentation]
@ReorgPercent int = 10,
@RebuildPercent int = 40
as
set transaction isolation level read uncommitted;
Set nocount on;
SELECT table_name, index_name, fragmentation_percent, index_id,
case when fragmentation_percent between @ReorgPercent and @RebuildPercent then 'Reorganize'
when fragmentation_percent > @RebuildPercent then 'Rebuild' else null end as index_defag,
case when fragmentation_percent between @ReorgPercent and @RebuildPercent then 'ALTER INDEX ['+ index_name +'] ON [dbo].['+ table_name +'] REORGANIZE WITH ( LOB_COMPACTION = ON );'
when fragmentation_percent > @RebuildPercent then 'ALTER INDEX ['+ index_name +'] ON [dbo].['+ table_name +'] REBUILD PARTITION = ALL WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF );'
else null end as script
FROM
(
SELECT (SELECT distinct so.name FROM sys.objects so INNER JOIN sys.indexes ON so.object_id = si.object_id) as table_name,
si.name as index_name,
ps.avg_fragmentation_in_percent as fragmentation_percent, ps.index_id
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes si ON ps.OBJECT_ID = si.OBJECT_ID
AND ps.index_id = si.index_id
WHERE ps.database_id = DB_ID() AND si.name is not null AND
ps.avg_fragmentation_in_percent > @ReorgPercent -- min % to return
) A
ORDER BY fragmentation_percent desc;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment