January 10, 2015 at 1:59 pm
I'm running SQL Server 2008 R2 with latest patch. I'm performing all index maintenance online. Can anyone tell me how long the final phase of the index operation takes? Does the size of the index matter and if any blocking occurs, does the duration increase because the size of indexes is larger? I've been told by management, we can not have any downtime, its my understanding even with online index there's a chance blocking can occur in the final phase of the index operation. My database and index size is over 1.5 tb and the number of transaction per second are in the 100's.
January 11, 2015 at 6:06 am
The amount of time it takes is completely dependent on the size of the index in combination with the ability to get access to resources. At 1.5tb, it's going to take a very long time. You are likely to see some blocking, and yeah, that will extend the amount of time it takes. You may be to the size where you need to look into partitioning or sharding this data into smaller chunks to help with maintenance. Also, at this size, you probably should be looking to upgrade to 2014 soon because it has a lot more capability around maintaining data this size.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 12, 2015 at 8:00 am
Thanks for the reply, that's pretty much inline with what I thought.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply