Geeks With Blogs
SharePoint & SQL Thoughts

Am back to SQL Server performance turning after a year of a lot of things, as always I love sharing what I am doing to help someone in need and to create a point of reference to my work.

Below is a SQL script that will allow SQL tables Index Rebuilding or Re-Organization, I added the Online flag to allow my tables to be accessible during the re-indexing process. You can reference BOL to see how the ONLINE flag works and the limitations that it brings along.

Remember for ONLINE feature to work you have to have enterprise, evaluation or the developer editions. For the purpose of my process, I needed to have the online flag enabled.

CREATE PROCEDURE [dbo].[usp_dbmaint_Reindexing_Statistics]
This procedure determines the level of fragmentation on a database, based on fragmentation if your table is below 40%
the process reorganizes the indexes and if it’s over 40% the process rebuilds the indexes. At the end of the process,
I am updating statistics because Re-indexing will only update statistics regarding that specific Index but not the
Stats on the rest of your database.

Created by Leonard Mwangi
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 @command nvarchar(4000);
DECLARE @i int, @n int

SET @i = 0
IF OBJECT_ID('tempdb..#Reindexing','u') IS NOT NULL
drop table #Reindexing

object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
,0 as t_flag
INTO #Reindexing
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 20.0 AND index_id > 0;

SET @n = (SELECT COUNT(*) FROM #Reindexing)
WHILE @i < @n

SET @objectid = (SELECT TOP 1 objectid from #Reindexing where t_flag = 0);
SET @indexid = (SELECT TOP 1 indexid from #Reindexing where t_flag = 0);
SET @frag = (SELECT TOP 1 frag from #Reindexing where t_flag = 0);

SELECT @objectname = QUOTENAME(, @schemaname = QUOTENAME(
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;

IF @frag > 20.0 and @frag < 40.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
IF @frag >= 40.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD WITH (ONLINE = ON)';
EXEC (@command);
PRINT N'Executed: ' + @command;

UPDATE top (1) #Reindexing
SET t_flag = 1
WHERE t_flag = 0
SET @i = @i + 1

Posted on Friday, April 22, 2011 12:29 PM | Back to top

Comments on this post: Performance Turning

Comments are closed.
Comments have been closed on this topic.
Copyright © Leonard Mwangi | Powered by: