February 6, 2009 at 8:54 am
ALTER PROCEDURE [dbo].[RebuildReOrg Indexes]
WITH EXECUTE AS 'dbo'
AS
BEGIN
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;
END
I have seen many scripts for rebuilding indexes out of them i have chosen the above to run in my environment.
I am running this on a DB of size 900GB and the tables and indexes are partitioned in such a way that each month has seperate partition.
But the bad thing is on this particular database rebuild is runnnig from 2 days, how can i cut the time to run and if am using this can the users still will be able to access those tables as usual?
February 6, 2009 at 10:47 am
Script just processes any index which avg_fragmentation_in_percent is greater than 10%, isn't it?
Question here is... Why?
Those scripts and policies are good for small size databases, not for a 900 Gig one -not that that's a big one but you have to start taking size into consideration when they reach 1 Terabyte or so.
Some indexes just like to be fragmented, I've seen indexes that no matter what you do go back to some threshold... some 20%, some 30%... they stay there and perform just Okay so... why bother in trying to get them better?
You have to check how top 10 or top 20 fragmented indexes behave then - IF NEEDED - setup a specific job to maintain them. 😎
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 6, 2009 at 1:17 pm
I am runnning this procedure on the same database, it is still running and now how cud i find out which indexes are rebuild and which are yet to be build?
Does this code build indexes though there was no updates on the table? I undestand fragmentation occurs only when there is a change in the data, right?
Also I would like to know what happens if i stop this procedure before completion? if the index was dropped and in between i stop the prcoess then do i miss that index on the table?
February 6, 2009 at 3:18 pm
Mike Levan (2/6/2009)
I am runnning this procedure on the same database, it is still running and now how cud i find out which indexes are rebuild and which are yet to be build?
when this script is executed you get messages like one given below which tells you the last operated index...
Executed:Alter index indexname on tablename rebuild;
from above u can make out which indexes are rebuild and which are left to be build.
Does this code build indexes though there was no updates on the table? I undestand fragmentation occurs only when there is a change in the data, right?
Yes..this code rebuilds/reorganizes all indexes with avg_frag > 10% rebuild/reorganize depends on avg_frag.
Also I would like to know what happens if i stop this procedure before completion? if the index was dropped and in between i stop the prcoess then do i miss that index on the table?
No, u will not miss any index on the table.The query will terminate with the message "query canceled by the user" .. u can verify this from the message "Executed:...." that u get (as per the code) when an index has been rebuild.
U can use temporary table to loop through the table #work_to_do, which may decrease the query processing time.....
also with 900GB of database..u can also identify the most used indexes using DMVs and rebuild them in order to avoid rebuilding every index....
Regards,
[font="Verdana"]Sqlfrenzy[/font]
February 6, 2009 at 3:39 pm
If you are running Enterprise Edition - you have the option of rebuilding indexes ONLINE. As it is currently written, those indexes being rebuilt will not be available - and if the index is the clustered index the table will not be available.
You also should look at the ALTER INDEX syntax in Books Online. There are additional options available to rebuild indexes only on certain partitions (i.e. the current partition, since the previous partitions are probably not modified anymore).
Beware that if the table contains any LOB data types (e.g. text, ntext, varchar(max), etc...) the clustered index cannot be rebuilt online - and if any of those columns are used in the non-clustered index then that index cannot be rebuilt online either.
FWIW - the logic I use is:
1) Any tables larger than 1000 pages (if less, don't care)
2) With average fragmentation > 10.0 percent
3) Rebuild if average fragmentation > 30.0 percent
a) and index does not contain any LOB data
4) Reorganize if average fragmentation < 30.0 percent
a) or index contains LOB data
If running Enterprise Edition - rebuild index online instead of reorganize (if no LOB data).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 6, 2009 at 3:42 pm
Executed:Alter index indexname on tablename rebuild;
Yes I get this message when i run it as query but not as job. since the job got failed i re ran it as query. but where am i confused is after running 3 indexes on 50 partitions now isee the executed message for only 1 index from the last 8 hrs.
Yes..this code rebuilds/reorganizes all indexes with avg_frag > 10% rebuild/reorganize depends on avg_frag.
I understand that but how can the indexes get fragmented on partiotions of data from the year 1999-2002 which we dont even update them.
February 6, 2009 at 3:46 pm
Jeffery
I am haveing Enterprise on one of my server and would like to do ONLINE indexes and ofcourse i like your logic.
1) Any tables larger than 1000 pages (if less, don't care)
2) With average fragmentation > 10.0 percent
3) Rebuild if average fragmentation > 30.0 percent
a) and index does not contain any LOB data
4) Reorganize if average fragmentation < 30.0 percent
a) or index contains LOB data
But how do i do this in my code, please help.
February 6, 2009 at 3:49 pm
Yes I get this message when i run it as query but not as job. since the job got failed i re ran it as query. but where am i confused is after running 3 indexes on 50 partitions now isee the executed message for only 1 index from the last 8 hrs.
restart the operation on test server using profiler and check which query is taking time ....
Regards,
[font="Verdana"]Sqlfrenzy[/font]
February 6, 2009 at 4:31 pm
To only affect those indexes that have more than 1000 pages:
Selectobject_id As ObjectId
,index_id As IndexId
,partition_number As PartitionNumber
,avg_fragmentation_in_percent As AvgFrag
,page_count As PageCount
,index_type_desc As IndexType
Into #work_to_do
From sys.dm_db_index_physical_stats(@databaseId, Null, Null, Null, 'Sampled')
Whereindex_id > 0
Andpage_count >= @pageCountLimit
Andavg_fragmentation_in_percent >= @avgFragLimit;
Declare the variable @pageCountLimit and set it to 1000 (or any other value you want).
To check the version of SQL Server, use SERVERPROPERTY. You can find the exact parameters in Books Online.
To determine whether or not an index/table has LOB data - you can use the column 'lob_data_space_id' in the table sys.tables. This is not necessarily conclusive - so I recommend testing it thoroughly before implementing in live.
One other note, if rebuilding and you have a large enough tempdb you can also set the option to sort in tempdb. This might help improve the process - but again, you need to test it out.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 6, 2009 at 4:42 pm
read this
http://www.sqlservercentral.com/scripts/index/64728/ [/URL]
to get index usage stats....
Regards,
[font="Verdana"]Sqlfrenzy[/font]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply