Description
This alert reviews fragmentation details for all objects in the SQL Server instance - the SQL statement used is listed at the bottom of this article. By default it only returns objects that are more than 30% fragmented, but that can be modified as needed by changing the SQL. The thresholds below in this alert definition are set to give warnings if the fragmentation is between 30-50% and a critical message if the fragmentation is above 50%. Use ALTER INDEX REBUILD or equivalent to rebuild them. The list of fragmented objects is in the format DB Name, Table Name, Index Name, Index Type, Page Count as the first column of data, and the 2nd column is fragmentation percentage.
Alert Definition
To create the alert, click on Alerts > Manage Alerts tab and create a Custom Alert of type Custom SQL Alert - Multiple Numeric Return. Configure the alert with values similar to these:
Alert Name: Fragmentation
Execution Interval: >= 1 Day
Notification Text: The following objects are fragmented.
SQL Script:
-- drop and recreate the temp table used to store info from dm_db_index_physical_stats DMO
IF OBJECT_ID('tempdb..#FragInfo') IS NOT NULL
DROP TABLE #FragInfo;
CREATE TABLE #FragInfo ([db_name] SYSNAME, [table_name] SYSNAME, [index_name] SYSNAME NULL,
[index_type_desc] nvarchar(60),[frag_pct] float, [page_count] bigint);
-- grab fragmentation info for every database
declare @cmd1 varchar(500), @MinFragPct varchar(10)
set @MinFragPct = 30;
set @cmd1=';use [?]
SELECT DB_NAME(DB_ID()), OBJECT_NAME(ips.OBJECT_ID), i.name, ips.index_type_desc,
ips.avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL , NULL) ips
INNER JOIN sys.indexes i ON i.object_id = ips.object_id AND i.index_id = ips.index_id
WHERE avg_fragmentation_in_percent >= ' + @MinFragPct;
INSERT INTO #FragInfo EXEC sp_MSforeachdb @command1=@cmd1;
-- show the object in order of worst fragmentation
SELECT COALESCE([db_name],'NULL') + ', ' +
COALESCE([table_name],'NULL') + ', ' +
COALESCE([index_name],'NULL') + ', ' + COALESCE([index_type_desc],'NULL') + ', ' +
COALESCE(CONVERT(varchar,[page_count]),'NULL'),
SUBSTRING(CONVERT(varchar, [frag_pct]),1,4) frag_pct
FROM #FragInfo
ORDER BY frag_pct DESC;
DROP TABLE #FragInfo;
Execute Against: Monitored Instance
Units: Fragmentation Percent
High Threshold: Min 50, Max empty
Medium Threshold: Min 30, Max 50
