Split Sql Server Index Rebuild job into several parts - Divide load of Rebuild Index job
-
http://www.sqlservercentral.com/Forums/Topic653547-357-1.aspx#bm656714
I want to continue the discussion made on second comment (by Stamey) on this above forum. Since this was a completely new topic I decided to open a new topic for this discussion.
The discussion is about splitting the load of a SQL rebuild indexes job into fewer smaller chunks which would take away the big I/O load from the server, minimizing various locks and blocking occurred for extended period during INDEX REBUILD job.
I have a 24X7 Server with various databases in use all the time. One of the most used database is around 899GB. I have scheduled a job which uses ALTER INDEX script to defrag indexes from all tables on the database. The script has been retrieved from Mr.Greg Robidoux's post. The job runs every Sunday Morning 12AM ; which I believe is the best time for these kind of operations where there are least amount of activities in the server.
However, my problem here is that it runs for around 3.5 hours and many page blocks are seen for extended period.
Only if there were any ideas to split this whole 3.5 hours process into several 20-30 minutes group every night for 3-5 nights. Is this something that can be done? Is it even possible ?
Appreciate all comment and suggestions.
-
You could partition the table; if you're lucky, the older data will not even need rebuiilt, and you can greatly reduce rebuild time and overhead.
Even if it all still needs rebuild, you can do each partition separately, and each partition will of course rebuild more quickly than the original giant partition.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
Very good Alternative Scott.
But don't want to go with Partitioning yet. I will definitely keep that on mind though.
If I can figure a way out to split the job into 3-4 days , I will have two choices to request to my manager.
thank you for you thought....
Any other ideas on Splitting the job ??
-
So are you doing a REORG or a REBUILD?
Is it on Enterprise Edition (2008)?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
Are you running Enterprise Edition?
If so, you could specify ONLINE = ON.
-
I'm doing REBUILDs . I'm on Enterprise 2008 R2.
Cannot do ONLINE=ON . We have BLOBs datatypes.
-
sdpages (2/7/2013)
I'm doing REBUILDs . I'm on Enterprise 2008 R2.Cannot do ONLINE=ON . We have BLOBs datatypes.
Unless your non-clustered indexes actually include the LOB columns, they can be rebuilt online. The clustered index would not be able to be rebuilt online because of the LOB objects, of course - but the others can.
I am not familiar with the script you are using - does it select indexes to be rebuilt/reorganized based on fragmentation? If not, you should find one of the many available scripts that does that and use it instead.
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 -
You could move the BLOB(s) to a different table; use views if you need to, to combine the original table columns and the BLOB(s), to keep current apps "happy".
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
I'm using this script
IF EXISTS(SELECT * FROM sys.all_objects ao WHERE ao.name LIKE 'tbl_rebuild%')
DROP TABLE tbl_rebuild
CREATE TABLE tbl_rebuild (
schemaname varchar(100)
,tablename VARCHAR(100)
,indexname VARCHAR(100)
,fragmentation FLOAT
,page_count INT)
GO
INSERT INTO dbo.tbl_rebuild( Schemaname,tablename,Indexname,fragmentation,page_count)
SELECT s.name,o.name,i.name,ddips.avg_fragmentation_in_percent,ddips.page_count FROM sys.objects o LEFT OUTER JOIN sys.schemas s
ON o.schema_id=s.schema_id LEFT OUTER JOIN sys.indexes i
ON o.object_id=i.object_id LEFT OUTER JOIN sys.dm_db_index_physical_stats(db_id(),NULL,NULL,NULL,NULL) ddips
ON i.object_id=ddips.object_id AND i.index_id=ddips.index_id
WHERE o.type='U'
AND i.index_id>0
AND ddips.avg_fragmentation_in_percent > 30
ORDER BY ddips.page_count desc
GO
DECLARE @cmd varchar(1000)
DECLARE @indxname VARCHAR(150)
DECLARE @jname VARCHAR(150)
DECLARE @schemaname VARCHAR(150)
DECLARE @tblname VARCHAR(150)
DECLARE @pgcount int
DECLARE db_index CURSOR FOR
SELECT r.indexname, r.schemaname,r.tablename, r.page_count FROM dbo.tbl_rebuild r
OPEN db_index
FETCH NEXT FROM db_index INTO @indxname,@schemaname,@tblname,@pgcount
WHILE @@fetch_status=0
BEGIN
SET @jname=@schemaname + '.' + @tblname
SET @cmd = 'ALTER INDEX ' + @indxname + ' ON ' + @jname + ' REBUILD WITH (FILLFACTOR = 90)
PRINT @cmd --Exec @cmd
FETCH NEXT FROM db_index INTO @indxname,@schemaname,@tblname,@pgcount
END
CLOSE db_index
DEALLOCATE db_index
GO
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply