This is a handy SQL Template to get information about the indexes fragmentation on a particular user database. It can be used to rebuild all the indexes based on a rule (ex. index fragmentation > 30% ). or just to get a report of the current index fragmentation status
the script has below characteristics:
- the variable @EXECUTE is to decide whether you want to get the current index fragmentation for all the tables or proceed with the full index rebuild
- Since it is a template, you have to specify the database name and an e-mail address (to get a report) (see the screenshot)
- I never rebuild indexes on a database when the recovery model is FULL so the script checks for that
- If the SQL Edition is Enterprise, then it uses ONLINE= ON option
- It uses MAXDOP = 0
- It generates a report with the previous and current index fragmentation. This is a HTML report that you should receive by e-mail
This is how you specify the variables in the sql template
Note that @EXECUTE variable is currently 0 so the script will just report the index fragmentation. Remember to change it to 1 when you really want to start the index rebuild process
USE <Database,sysname,model>
GO
SET NOCOUNT ON
GO
DECLARE @EXECUTE bit = 0 -- // 0 = PRINT , 1
= EXECUTE
IF @EXECUTE = 1 SELECT 'REINDEX TABLES'
ELSE SELECT 'PRINTING STATEMENTS / REPORT'
SELECT @@servername,serverproperty('Edition'),getdate(),db_name() as Database_Name,
recovery_model_desc as [Recovery Model]
FROM sys.databases where name
= ''
--ALTER DATABASE
SET RECOVERY SIMPLE
--ALTER DATABASE
SET RECOVERY FULL
IF (SELECT recovery_model_desc
FROM sys.databases where name = '') = 'FULL' AND @EXECUTE = 1
BEGIN
SELECT 'The
database is in FULL recovery model, Reindex cannot be executed'
GOTO FINAL
END
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE
@partitioncount bigint;
DECLARE
@schemaname nvarchar(130);
DECLARE
@objectname nvarchar(130);
DECLARE @indexname
nvarchar(130);
DECLARE
@partitionnum bigint;
DECLARE
@partitions bigint;
DECLARE @frag float;
DECLARE @rows int;
DECLARE @prtime datetime;
DECLARE @command varchar(max);
DECLARE @Counter int = 1;
DECLARE @Total int = 0;
DECLARE
@Initial_Time datetime;
DECLARE
@tableHTML_Fragmentation varchar(max);
select
@Initial_Time = getdate()
IF (object_id( 'tempdb..#tablesrows'
) IS NOT NULL) DROP TABLE ..#tablesrows ;
SELECT object_id,OBJECT_NAME(object_id)
TableName,SUM(Rows) Rows
INTO
#tablesrows
FROM sys.partitions WHERE
index_id < 2 GROUP
BY object_id
IF (object_id( 'tempdb..#TMP_DBA_IDX_FRAG'
) IS NOT NULL) DROP TABLE ..#TMP_DBA_IDX_FRAG ;
CREATE TABLE
#TMP_DBA_IDX_FRAG (
objectid int,
indexid int,
partitionnumber int,
avg_fragmentation_in_percent float,
new_avg_fragmentation_in_percent float,
[Rows] bigint)
INSERT INTO #TMP_DBA_IDX_FRAG
SELECT
IDX.object_id AS
objectid,
IDX.index_id
AS indexid,
IDX.partition_number
AS partitionnum,
IDX.avg_fragmentation_in_percent
AS frag,
0 as
new_avg_fragmentation_in_percent,
RC.Rows
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') as IDX
INNER JOIN #tablesrows RC ON
IDX.object_id=RC.object_id
WHERE index_id > 0 and
avg_fragmentation_in_percent >10;
SELECT @Total = count(*) FROM
#TMP_DBA_IDX_FRAG;
IF @EXECUTE = 1 PRINT 'REINDEX TABLES' ELSE PRINT 'PRINTING STATEMENTS'
PRINT '======================================================='
PRINT ''
-- Declare the
cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT objectid,indexid,partitionnumber,avg_fragmentation_in_percent,[Rows]
FROM #TMP_DBA_IDX_FRAG
order by rows desc,objectid,indexid;
-- Open the
cursor.
OPEN partitions;
-- Loop through
the partitions.
WHILE (1=1)
BEGIN;
FETCH NEXT
FROM
partitions
INTO
@objectid, @indexid,
@partitionnum, @frag,@rows;
IF @@FETCH_STATUS <
0 BREAK;
SELECT
@objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid;
SELECT
@indexname = QUOTENAME(name)
FROM sys.indexes
WHERE object_id = @objectid AND
index_id = @indexid;
SELECT
@partitioncount = count (*)
FROM sys.partitions
WHERE object_id =
@objectid AND index_id =
@indexid;
SET @prtime = getdate()
SET @command = NULL
--IF @frag
< 30.0 and @frag > 15.0
-- SET @command = N'ALTER INDEX ' + @indexname
+ N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE; --('+cast(@frag
as varchar)+'%) // '+cast(@rows as varchar) +' rows';
IF
@frag >= 30.0
BEGIN
SELECT
@command = N'ALTER
INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD WITH (MAXDOP =0'+CASE serverproperty('EngineEdition') WHEN 3 THEN ',ONLINE =ON' END+');--('+cast(@frag as varchar)+'%) // '+cast(@rows as varchar) +' rows';
END
IF
@partitioncount > 1
SET
@command = @command +
N' PARTITION=' +
CAST(@partitionnum
AS nvarchar(10));
BEGIN TRY
IF @command is not null IF @EXECUTE = 1 EXEC (@command) ELSE PRINT (@command);
END TRY
BEGIN CATCH
PRINT cast(ERROR_NUMBER() as varchar(10))+'//'+ ERROR_MESSAGE();
IF ERROR_NUMBER() = 2725 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD WITH (MAXDOP =0);--('+cast(@frag as varchar)+'%) // '+cast(@rows as varchar) +' rows (OFFLINE)'
IF @EXECUTE = 1 EXEC (@command) ELSE PRINT (@command)
END CATCH
PRINT N'Executed ('+ cast(@Counter as varchar)+'/'+cast(@Total as varchar)+'):' + @command;
PRINT N'Execution Time: ' +CONVERT ( varchar(30) , getdate() ,120)+' ('+SUBSTRING(CONVERT ( varchar(30) , getdate()-@prtime ,120),12,1000)+')'
PRINT N'-----------'
SET @Counter=@Counter+1
END;
-- Close and
deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
UPDATE
#TMP_DBA_IDX_FRAG
SET
new_avg_fragmentation_in_percent = IDX.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') as IDX
WHERE objectid = IDX.object_id and
indexid=IDX.index_id
select
t.name
,i.name
,round(avg_fragmentation_in_percent,2) as avg_fragmentation_in_percent
,round(new_avg_fragmentation_in_percent,2) as new_avg_fragmentation_in_percent
,[Rows]
from
#TMP_DBA_IDX_FRAG M (nolock)
inner join sys.tables t on M.objectid =t.object_id
inner join sys.indexes i on M.indexid =i.index_id and M.objectid =i.object_id
order by 1
select @@servername as
Server_Name,getdate() as [Now],avg(avg_fragmentation_in_percent)
as Previous_avg_frag,avg(new_avg_fragmentation_in_percent) as New_avg_frag
from
#TMP_DBA_IDX_FRAG (nolock)
where rows > 4000
DECLARE
@avg_fragmentation_in_percent float
DECLARE
@new_avg_fragmentation_in_percent float
select @avg_fragmentation_in_percent=avg(avg_fragmentation_in_percent),
@new_avg_fragmentation_in_percent=avg(new_avg_fragmentation_in_percent)
from #TMP_DBA_IDX_FRAG (nolock)
where rows
> 4000
SET @tableHTML_Fragmentation =
N'
Previous AVG Fragmentation ('+cast(@avg_fragmentation_in_percent as varchar)+') - New AVG Fragmentation ('+cast(@new_avg_fragmentation_in_percent as varchar)+')
'+
N'
'+
N'
'+
N'
'+
CAST ( ( SELECT td = t.name, '',
td = i.name, '',
td = cast(round(avg_fragmentation_in_percent,2) as varchar(10)), '',
td = cast(round(new_avg_fragmentation_in_percent,2) as varchar(10)), '',
td = cast([Rows] as varchar(10))
FROM #TMP_DBA_IDX_FRAG M
inner join sys.tables t on M.objectid =t.object_id
inner join sys.indexes i on M.indexid =i.index_id and M.objectid =i.object_id
ORDER BY 1
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'
Table Name
Index Name
AVG Fragmentation
NEW AVG Fragmentation
Rows
' ;
DECLARE @MailText varchar(max);
DECLARE @Subj varchar(250)
Set @Subj='DBREINDEX on ('+rtrim(@@servername)+') Database
() HAS FINISHED'
SELECT @MailText = 'FULL DATABASE REINDEX ON '+rtrim(@@servername)+' for
STARTED AT '+CONVERT(varchar(30), @MailText, 120)+char(13)+' ,FINISHED AT ' +CONVERT(varchar(30), getdate(), 120)
IF
@avg_fragmentation_in_percent IS NOT NULL AND @EXECUTE = 1
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@subject =@Subj
, @body =@tableHTML_Fragmentation
, @recipients =''
,
@execute_query_database = ''
, @body_format = 'HTML' ;
END
----------------------------------
-- Drop the
temporary table.
IF (object_id( 'tempdb..#tablesrows'
) IS NOT NULL) DROP TABLE ..#tablesrows ;
IF (object_id( 'tempdb..#TMP_DBA_IDX_FRAG'
) IS NOT NULL) DROP TABLE ..#TMP_DBA_IDX_FRAG ;
FINAL:
SELECT name,compatibility_level,recovery_model_desc from
sys.databases
GO
