Created
December 10, 2019 20:42
-
-
Save bcawrse/0eda3e980f5bf5c099e0055fa8a06ded to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/****** 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