December 7, 2009 at 11:47 am
Hello,
I am using SQL Server 2005. I want to rebuild or reorganise indexes in the databases. now i am using the below script that will create an output in the form of Alter statement. But the output created is not an online operation.
SELECT 'ALTER INDEX [' + ix.name + '] ON [' + s.name + '].[' + t.name + '] ' +
CASE WHEN ps.avg_fragmentation_in_percent > 40 THEN 'REBUILD' ELSE 'REORGANIZE' END +
CASE WHEN pc.partition_count > 1 THEN ' PARTITION = ' + cast(ps.partition_number as nvarchar(max)) ELSE '' END
FROM sys.indexes AS ix INNER JOIN sys.tables t
ON t.object_id = ix.object_id
INNER JOIN sys.schemas s
ON t.schema_id = s.schema_id
INNER JOIN (SELECT object_id, index_id, avg_fragmentation_in_percent, partition_number
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'DETAILED')) ps
ON t.object_id = ps.object_id AND ix.index_id = ps.index_id
INNER JOIN (SELECT object_id, index_id, COUNT(DISTINCT partition_number) AS partition_count
FROM sys.partitions
GROUP BY object_id, index_id) pc
ON t.object_id = pc.object_id AND ix.index_id = pc.index_id
WHERE ps.avg_fragmentation_in_percent > 10 AND
ix.name IS NOT NULL
output cerated is :-
ALTER INDEX [PK_template_variables] ON [dbo].[template_variables] REBUILD
ALTER INDEX [inx_templates] ON [dbo].[templates] REBUILD
ALTER INDEX [inx_triggers1] ON [dbo].[triggers] REORGANIZE
but now i am looking to modisy script that will create alter statements with online=on rebuild/reorganise statements. i want to add an online operations while i do this task. Please help me to modify this script or id someone have the script i will greatly appreciate his help.
Thanks.
December 7, 2009 at 12:07 pm
Why don't you just use this one? Easier than writing and debugging your own.
http://sqlfool.com/2009/06/index-defrag-script-v30/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 7, 2009 at 2:38 pm
thanks a lot.
December 8, 2009 at 8:31 am
Just remember that ONLINE Index Rebuilds is Enterprise Edition only.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply