So you have created an maintenance plan for your databases which will rebuild indexes? Nice.
Is it taking a long time? Hmmm... Not so nice.
IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[db_ddladmin].[spGenerateIndexOptimisationDDL]') AND TYPE IN (N'P', N'PC'))
DROP PROCEDURE [db_ddladmin].[spGenerateIndexOptimisationDDL]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Joe
-- Create date: 16/06/2014
-- Description: Build appropriate DDL to optimise indexes
-- =============================================
CREATE PROCEDURE [db_ddladmin].[spGenerateIndexOptimisationDDL]
@forceRebuild BIT = 0,
@forceReorganise BIT = 0,
@defaultFillFactor TINYINT = 90,
@statsMode nvarchar(20) = 'LIMITED' --(least accuracy, least time) LIMITED | SAMPLED | DETAILED (most accuray, most time)
AS
BEGIN
SET NOCOUNT ON;
if(@forceRebuild = 1)
begin
SELECT
'ALTER INDEX [' + i.name + '] ON [' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + '] REBUILD WITH (FILLFACTOR = ' + CAST(@defaultFillFactor AS VARCHAR(3)) + ', SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);'
FROM sys.indexes AS i
INNER JOIN sys.tables AS t ON t.object_id = i.object_id
WHERE i.name IS NOT NULL;
end
else if(@forceReorganise = 1)
begin
SELECT
'ALTER INDEX [' + i.name + '] ON [' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + '] REORGANIZE;'
FROM sys.indexes AS i
INNER JOIN sys.tables AS t ON t.object_id = i.object_id
WHERE i.name IS NOT NULL;
end
else
begin
SELECT
CASE
WHEN s.avg_fragmentation_in_percent > 5.0 AND s.avg_fragmentation_in_percent <= 30.0 THEN 'ALTER INDEX [' + i.name + '] ON [' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + '] REORGANIZE;'
WHEN s.avg_fragmentation_in_percent > 30.0 THEN 'ALTER INDEX [' + i.name + '] ON [' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + '] REBUILD WITH (FILLFACTOR = ' + CAST(@defaultFillFactor AS VARCHAR(3)) + ', SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);'
ELSE NULL
END
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, @statsMode) AS s
INNER JOIN sys.indexes AS i ON s.object_id = i.object_id AND s.index_id = i.index_id
INNER JOIN sys.tables AS t ON t.object_id = i.object_id
WHERE s.avg_fragmentation_in_percent > 5
AND i.name IS NOT NULL;
end
END;
GO
/*
-- TESTING
EXEC [db_ddladmin].[spGenerateIndexOptimisationDDL]
@forceRebuild = 0,
@forceReorganise = 0,
@defaultFillFactor = 90,
@statsMode = 'SAMPLED' --(least accuracy, least time) LIMITED | SAMPLED | DETAILED (most accuray, most time)
*/
How can you improve this? Firstly, sometimes you can and should avoid messing with your indexes. Blindly rebuilding all indexes all of the time is a bit 'brute force' - you can however just get away with reorganising the index, and in other cases the index might not need any attention whatsoever. This is a more intelligent and optimised approach which could get you valuable time during a maintenance plan.
The sql code snippet below will 'intelligently' generate index optimising DDL.
The sql code snippet below will 'intelligently' generate index optimising DDL.
Remarks:
- Assumes that you know the difference between an index rebuild and an index reorganise.
- Avoid using DBCC DBREINDEX. It is obsolete and will be removed from a future version. (although still present in sql server 2014). Instead use ALTER INDEX.
- Using the Microsoft suggested fragmentation thresholds i.e.
- <= 5% - dont do anything. The performance gains will be negligible compared relative to the cost of rebuilding or reorganising.
- > 5% and <= 30% - perform a reorganise
- > 30% - perform a rebuild
- Assumes you are optimising a single database - i.e. the database where this sp will execute.
- Allows accuracy to be tweaked via @statsMode parameter.
- Assumes that you want to optimise all tables
- Assumes you have a sysadmin role on the server.
- Assumes you have dbo or db_ddladmin role on database.
- Assumes a constant fill factor for all tables e.g. 90%. Its a good starting point default but is it really the best setting for every index in your database? Probably not! Some tables will grow very fast and some wont grow at all. Meaning 90% will not be enough or be overkill respectively. Experiment by seeing how often you need to defrag each of your indexes. This suggests a further improvement for this sql - how to automatically decide good fill factors based on past fragmentation. This assumes you are regularly optimising indexes e.g. every day or every week and the database is growing at a fairly constant rate, then you could use the fragmentation percentage as a guide to 'predicting' what the fill factor should be during the next index rebuild. (you cant set fill factor during a reorganisation)
- In real life, I run the generated DDL statements script during my overnight maintenance plan.
IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[db_ddladmin].[spGenerateIndexOptimisationDDL]') AND TYPE IN (N'P', N'PC'))
DROP PROCEDURE [db_ddladmin].[spGenerateIndexOptimisationDDL]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Joe
-- Create date: 16/06/2014
-- Description: Build appropriate DDL to optimise indexes
-- =============================================
CREATE PROCEDURE [db_ddladmin].[spGenerateIndexOptimisationDDL]
@forceRebuild BIT = 0,
@forceReorganise BIT = 0,
@defaultFillFactor TINYINT = 90,
@statsMode nvarchar(20) = 'LIMITED' --(least accuracy, least time) LIMITED | SAMPLED | DETAILED (most accuray, most time)
AS
BEGIN
SET NOCOUNT ON;
if(@forceRebuild = 1)
begin
SELECT
'ALTER INDEX [' + i.name + '] ON [' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + '] REBUILD WITH (FILLFACTOR = ' + CAST(@defaultFillFactor AS VARCHAR(3)) + ', SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);'
FROM sys.indexes AS i
INNER JOIN sys.tables AS t ON t.object_id = i.object_id
WHERE i.name IS NOT NULL;
end
else if(@forceReorganise = 1)
begin
SELECT
'ALTER INDEX [' + i.name + '] ON [' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + '] REORGANIZE;'
FROM sys.indexes AS i
INNER JOIN sys.tables AS t ON t.object_id = i.object_id
WHERE i.name IS NOT NULL;
end
else
begin
SELECT
CASE
WHEN s.avg_fragmentation_in_percent > 5.0 AND s.avg_fragmentation_in_percent <= 30.0 THEN 'ALTER INDEX [' + i.name + '] ON [' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + '] REORGANIZE;'
WHEN s.avg_fragmentation_in_percent > 30.0 THEN 'ALTER INDEX [' + i.name + '] ON [' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + '] REBUILD WITH (FILLFACTOR = ' + CAST(@defaultFillFactor AS VARCHAR(3)) + ', SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);'
ELSE NULL
END
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, @statsMode) AS s
INNER JOIN sys.indexes AS i ON s.object_id = i.object_id AND s.index_id = i.index_id
INNER JOIN sys.tables AS t ON t.object_id = i.object_id
WHERE s.avg_fragmentation_in_percent > 5
AND i.name IS NOT NULL;
end
END;
GO
/*
-- TESTING
EXEC [db_ddladmin].[spGenerateIndexOptimisationDDL]
@forceRebuild = 0,
@forceReorganise = 0,
@defaultFillFactor = 90,
@statsMode = 'SAMPLED' --(least accuracy, least time) LIMITED | SAMPLED | DETAILED (most accuray, most time)
*/
http://msdn.microsoft.com/en-gb/library/ms189858.aspx
