need a more experienced pair of eyes on this...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

need a more experienced pair of eyes on this...

Author

Topic

mpreissner
Starting Member

21 Posts

Posted - 2011-04-19 : 09:54:01

I've changed the script from MS that conditionally rebuilds or reorganized table indices based on fragmentation level. I unfortunately cannot use the script in its original form due to hardware limitations (i.e. not enough disk space to handle the amount of data file growth experienced during rebuilds). I've eliminated the rebuild clauses, as with the database I'm using this on, we can get away with a reorg once a week to keep from ever having to do a rebuild. Can someone look over the script and let me know if they see any big issues with it? Thanks!

USE IFW

SET NOCOUNT ON;
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 @dbid smallint;
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SET @dbid = DB_ID(N'IFW');
SELECT
[object_id] AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag, page_count
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (@dbid, NULL, NULL , NULL, N'LIMITED')
WHERE avg_fragmentation_in_percent > 5.0 -- Allow limited fragmentation
AND index_id > 0 -- Ignore heaps
AND page_count > 100; -- Ignore small tables

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT objectid,indexid, partitionnum,frag FROM #work_to_do;

-- Open the cursor.
OPEN partitions;

-- Loop through the partitions.
WHILE (1=1)
BEGIN
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;
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;

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
IF @frag < 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';

EXEC (@command);
PRINT N'Executed: ' + @command;
END ;

-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;

-- Drop the temporary table.

DROP TABLE #work_to_do;
GO

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-04-19 : 10:17:59

according to your script if fregmentation is less then 30% then reorganize.AND leave other fregmented indexes which have more then 30% of fregementation and actually need your attention ;) WHY THIS ???

--------------------------
http://connectsql.blogspot.com/

mpreissner
Starting Member

21 Posts

Posted - 2011-04-19 : 11:07:11

I like to issue rebuild statements against indices above 30% fragmentation, but every time I use a script to automate the process, I suffer from excessive data file growth. If I manually address the severely fragmented indices one at a time, I can get things to work, but it does take a while, and affects data availability since I'm only using SQL 2005 Standard, which can't do ONLINE rebuilds. By running a weekly REORGANIZE script, I can keep most tables from ever reaching 30% fragmentation.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources