August 21, 2009 at 12:16 pm
hi,
I'm analyzing index fragmentation & I'm using below query.
select database_id, object_id, index_id, page_count,avg_fragmentation_in_percent from
sys.dm_db_index_physical_stats (null,NULL,NULL,NULL,NULL)
where page_count >1000 AND avg_fragmentation_in_percent >10
after getting the results, I want to rebuild or reorganize the indexes having page count>1000?
is that a right way I'm going?
August 21, 2009 at 12:18 pm
That's definitely the right place to start.
I'd rebuild over 20, and the reorg the rest that're over 10.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 21, 2009 at 12:30 pm
I found the below script from MSDN for index deframentation:
avg_fragmentation_in_percent 30 ---do index rebuild
Can I schedule this to run every day after business hours?
-- 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' 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
thanks
August 22, 2009 at 12:12 am
you can schedule it to run after business hours. Keep in mind that reindex will lock the base tables (users will not be able to run dml operations unless u've enterprise edition and u're doing online rebuild). Generally you can do reorganize on a daily basis and rebuilds on weekends.
August 22, 2009 at 4:59 am
The script is good - I do the same thing myself.
Also, the 30% value you mention...Is this what the msdn article suggests? It could be a good starting place, but you need to select the fragmentation thresholds based on your knowledge of your databases.
I'd also say that although the script is performing a reorganize, no statistics are being updated. You should ensure that the statistics are also updated as part of your maintenance plan.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply