May 28, 2008 at 8:16 am
Hi folks,
I found a cool script on this page (shown below)
http://msdn.microsoft.com/en-us/library/ms188917.aspx
which queries the fragmentation level of indices and decides whether to do a reorganize or rebuild of the index.
It appears to work on discerning the fragmentation level as I can cross check it with the DBCC SHOWCONTIG ('dbo.tablename') command but the stats don't change after running the script, and no errors are reported.
Any suggestions? Thanks!
Here is the script below:
-- Ensure a USE statement has been executed first.
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);
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * 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';
IF @frag >= 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
IF @partitioncount > 1
SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
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
May 28, 2008 at 11:07 am
reorganize doesn't update stats, only rebuild does
i run a modified version of this and my reorganize command is set to <1
May 28, 2008 at 11:29 am
Thanks for the response.
The rebuild option was done as the index fragmentation level was high. The page count for the index is about 15. I wanted to run it on a small db for testing before I turn it loose on the large ones. Is there a size limit where it won't bother rebuilding on small page counts?
After rebuilding, the frag level is still at 66%
Thanks,
Dan
May 28, 2008 at 11:46 am
Microsoft has as whitepaper on this.
"Generally, you should not be concerned with fragmentation levels of indexes with less than 1,000 pages. In the tests, indexes containing more than 10,000 pages realized performance gains, with the biggest gains on indexes with significantly more pages (greater than 50,000 pages)."
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Kalen Delaney has also written a blog post about this.
http://sqlblog.com/blogs/kalen_delaney/archive/2008/02/28/fragmentation-revisited.aspx
I think that you shouldn't rebuild / reorganize these small indexes. You could just add the page_count to the where clause in Microsoft's script.
I also have a stored procedure that could help you with this.
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
Ola Hallengren
May 28, 2008 at 12:12 pm
Thanks Ola,
Our main production databases are large enough to appreciate the index maintenance.
The page count check, I can do right away.
I'll have to digest your more comprehensive approach when I have a few less fires to put out!
Thanks,
Dan
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply