August 28, 2009 at 1:34 pm
Hi,
I'm maintaing the BizTalk Databases in SQL Sevrer 2005. As per MS, BizTalk databases does not support Index Reorganize. So I want to exclude the Index Reorganize logic from the below script.What are the changes need to be done in order to exclude the index reorganize logic from the below script?
-- 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
Thank You
August 29, 2009 at 7:53 am
IF @frag = 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
You are checking in these lines whether to reindex or reorganize based on the fragmentation limit of 30%. YOu may want to remove the complete syntax and keep the syntax only for rebuilding the index.
Remove these lines
IF @frag < 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
August 29, 2009 at 10:18 am
Interesting. I'd read about BizTalk 2009. I didn't see anything about not being able to reorganize indexes.
I did read that you should not perform any online rebuilds and I would imagine this would extend to reorganize operations whilst BizTalk is processing.
August 29, 2009 at 10:46 am
Clive Strong (8/29/2009)
I did read that you should not perform any online rebuilds and I would imagine this would extend to reorganize operations whilst BizTalk is processing.
I would like to know the logic behind that recommendation. Strange...
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
August 29, 2009 at 11:21 am
I think it was something to do with the column types? I can't recall now...I'll see if I can find the msdn article.
August 29, 2009 at 6:27 pm
Hi Gail,
http://msdn.microsoft.com/en-us/library/ee308910%28BTS.10%29.aspx
From the above Microsoft link:
Default Settings for the Database Index Rebuilds and Defragmentation
BizTalk Server does not support defragmenting indexes. “DBCC INDEXDEFRAG” and “ALTER INDEX … REORGANIZE …” are not supported since they use page locking, which can cause blocking and deadlocks with BizTalk Server. BizTalk Server does support database index rebuilds (“DBCC DBREINDEX” and “ALTER INDEX … REBUILD …”), but they should only be done during maintenance windows when BizTalk Server is not processing data. Index rebuilds while BizTalk Server is processing data are not supported.
So could please tell me what to modify in order to Exclude Reorganize logic & it should work for all the databases in an instance. Currently I'm running manually by connecting to each database.But I want to schedule a sql agent job, to rebuild indexes for all the databases. I do not want to use Rebuild index maintenance task.
Thank You
Thanks
August 30, 2009 at 2:15 am
gmamata7 (8/29/2009)
So could please tell me what to modify in order to Exclude Reorganize logic & it should work for all the databases in an instance.
ps has already told you, a few posts up.
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
August 30, 2009 at 2:23 am
hi,
Its working fine after excluding reorganize logic. But I'm running this script for each database one-by-one manually connecting each db. Could you tell me what & where should I add the logic in the code to work for all the databases in an instance
thank you
August 30, 2009 at 3:45 am
It's not going to be trivial to change that to run over all databases. dm_db_index_physical_stats can easily report on all the indexes on the entire server, but the second part, the part that references sys.objects, sys.indexes, etc won't be as easy to change. It's not a case of editing one or two lines, it'll be a fairly major re-write.
I'd suggest putting this into a job, one step per database. It's the easiest way.
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
August 30, 2009 at 12:55 pm
I'd suggest putting this into a job, one step per database. It's the easiest way.
But, I have 22 databases. Is Having 22 steps for a Index reorganize/rebuild makes sense?
Is there no way to make the script to work for all databases other than having 22 steps in job?
thanks much
August 30, 2009 at 1:07 pm
gmamata7 (8/30/2009)
But, I have 22 databases. Is Having 22 steps for a Index reorganize/rebuild makes sense?
Why not?
Is there no way to make the script to work for all databases other than having 22 steps in job?
Yes there is, but it's a lot of work. The references to sys.objects, sys.indexes, sys.partitions will all have to be database qualified, which means you'll need another loop outside the one that's looping over the partitions. Then the dynamic SQL that does the rebuild will have to be changed to include a USE statement to get to the right database.
If you've got several days to modify and test the rewritten code, you can go that route. If you don't the job with multiple steps is a fair bit quicker and less likely to have bugs.
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply