Created
March 23, 2016 11:45
-
-
Save arijusg/99413bc4f0db4ee6954e to your computer and use it in GitHub Desktop.
SqlI Indexes Maintenance
This file contains 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
-- Ensure a USE <databasename> statement has been executed first. | |
USE <databasename> | |
SET NOCOUNT ON | |
-- adapted from "Rebuild or reorganize indexes (with configuration)" from MSDN Books Online | |
-- (http://msdn.microsoft.com/en-us/library/ms188917.aspx) | |
-- ======================================================= | |
-- || Configuration variables: | |
-- || - 10 is an arbitrary decision point at which to | |
-- || reorganize indexes. | |
-- || - 30 is an arbitrary decision point at which to | |
-- || switch from reorganizing, to rebuilding. | |
-- || - 0 is the default fill factor. Set this to a | |
-- || a value from 1 to 99, if needed. | |
-- ======================================================= | |
DECLARE @reorg_frag_thresh float SET @reorg_frag_thresh = 10.0 | |
DECLARE @rebuild_frag_thresh float SET @rebuild_frag_thresh = 30.0 | |
DECLARE @fill_factor tinyint SET @fill_factor = 80 | |
DECLARE @report_only bit SET @report_only = 0 | |
-- added (DS) : page_count_thresh is used to check how many pages the current table uses | |
DECLARE @page_count_thresh smallint SET @page_count_thresh = 1000 | |
-- Variables required for processing. | |
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 @page_count int | |
DECLARE @command nvarchar(4000) | |
DECLARE @intentions nvarchar(4000) | |
DECLARE @table_var TABLE( | |
objectid int, | |
indexid int, | |
partitionnum int, | |
frag float, | |
page_count int | |
) | |
-- Conditionally select tables and indexes from the | |
-- sys.dm_db_index_physical_stats function and | |
-- convert object and index IDs to names. | |
INSERT INTO | |
@table_var | |
SELECT | |
[object_id] AS objectid, | |
[index_id] AS indexid, | |
[partition_number] AS partitionnum, | |
[avg_fragmentation_in_percent] AS frag, | |
[page_count] AS page_count | |
FROM | |
sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') | |
WHERE | |
[avg_fragmentation_in_percent] > @reorg_frag_thresh | |
AND | |
page_count > @page_count_thresh | |
AND | |
index_id > 0 | |
-- Declare the cursor for the list of partitions to be processed. | |
DECLARE partitions CURSOR FOR | |
SELECT * FROM @table_var | |
-- Open the cursor. | |
OPEN partitions | |
-- Loop through the partitions. | |
WHILE (1=1) BEGIN | |
FETCH NEXT | |
FROM partitions | |
INTO @objectid, @indexid, @partitionnum, @frag, @page_count | |
IF @@FETCH_STATUS < 0 BREAK | |
SELECT | |
@objectname = QUOTENAME(o.[name]), | |
@schemaname = QUOTENAME(s.[name]) | |
FROM | |
sys.objects AS o WITH (NOLOCK) | |
JOIN sys.schemas as s WITH (NOLOCK) | |
ON s.[schema_id] = o.[schema_id] | |
WHERE | |
o.[object_id] = @objectid | |
SELECT | |
@indexname = QUOTENAME([name]) | |
FROM | |
sys.indexes WITH (NOLOCK) | |
WHERE | |
[object_id] = @objectid AND | |
[index_id] = @indexid | |
SELECT | |
@partitioncount = count (*) | |
FROM | |
sys.partitions WITH (NOLOCK) | |
WHERE | |
[object_id] = @objectid AND | |
[index_id] = @indexid | |
-- Build the required statement dynamically based on options and index stats. | |
SET @intentions = | |
@schemaname + N'.' + | |
@objectname + N'.' + | |
@indexname + N':' + CHAR(13) + CHAR(10) | |
SET @intentions = | |
REPLACE(SPACE(LEN(@intentions)), ' ', '=') + CHAR(13) + CHAR(10) + | |
@intentions | |
SET @intentions = @intentions + | |
N' FRAGMENTATION: ' + CAST(@frag AS nvarchar) + N'%' + CHAR(13) + CHAR(10) + | |
N' PAGE COUNT: ' + CAST(@page_count AS nvarchar) + CHAR(13) + CHAR(10) | |
IF @frag < @rebuild_frag_thresh BEGIN | |
SET @intentions = @intentions + | |
N' OPERATION: REORGANIZE' + CHAR(13) + CHAR(10) | |
SET @command = | |
N'ALTER INDEX ' + @indexname + | |
N' ON ' + @schemaname + N'.' + @objectname + | |
N' REORGANIZE; ' + | |
N' UPDATE STATISTICS ' + @schemaname + N'.' + @objectname + | |
N' ' + @indexname + ';' | |
END | |
IF @frag >= @rebuild_frag_thresh BEGIN | |
SET @intentions = @intentions + | |
N' OPERATION: REBUILD' + CHAR(13) + CHAR(10) | |
SET @command = | |
N'ALTER INDEX ' + @indexname + | |
N' ON ' + @schemaname + N'.' + @objectname + | |
N' REBUILD' | |
END | |
IF @partitioncount > 1 BEGIN | |
SET @intentions = @intentions + | |
N' PARTITION: ' + CAST(@partitionnum AS nvarchar(10)) + CHAR(13) + CHAR(10) | |
SET @command = @command + | |
N' PARTITION=' + CAST(@partitionnum AS nvarchar(10)) | |
END | |
IF @frag >= @rebuild_frag_thresh AND @fill_factor > 0 AND @fill_factor < 100 BEGIN | |
SET @intentions = @intentions + | |
N' FILL FACTOR: ' + CAST(@fill_factor AS nvarchar) + CHAR(13) + CHAR(10) | |
SET @command = @command + | |
N' WITH (FILLFACTOR = ' + CAST(@fill_factor AS nvarchar) + ')' | |
END | |
-- Execute determined operation, or report intentions | |
IF @report_only = 0 BEGIN | |
SET @intentions = @intentions + N' EXECUTING: ' + @command | |
PRINT @intentions | |
EXEC (@command) | |
END ELSE BEGIN | |
PRINT @intentions | |
END | |
PRINT @command | |
END | |
-- Close and deallocate the cursor. | |
CLOSE partitions | |
DEALLOCATE partitions | |
GO | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment