1

I've appended the following information in a previous post but I asked the 'why' instead of the 'how'.

So here's the question...

SQL Server 2014, CU6. 4.5 TB database, Simple recovery. The most important/accessed table is 1.6 TB, located on the primary filegroup. It's currently about 95% fragmented and so most all queries need to do a full scan of its 600M+ rows. Not surprisingly, it's an IO bottleneck and I'm unable to defrag in any acceptable window of time. My goal is to move this table out of the .mdf and cluster/partition onto its own filegroup (ONLINE=ON & SORT_IN_TEMPDB=ON). This new filegroup would be physically isolated on a dedicated disk array.

But I'm very concerned about any issue that may cause (or necessitate) a rollback (things such as a power failure, deadlocking, corruption, unacceptably slow performance during the operation, etc.).

Further, given the length of time this clustering/partitioning/moving process may take, any backups taken during that time would be useless. They'd immediately go into recovery after being restored and, again, rollback/rollfoward time would be unacceptable. Same deal if I were to turn on full logging and use transactional replication or log shipping -- worthless secondary or subscriber.

How would you handle this process?

I've thought about making a copy of the table, triggers on the source to keep the copy up-to-date.

I've got large buffers of space on all volumes involved. I want to pull the trigger. Boss says 'not yet'.

Thanks for reading...

-Greg

  • 3
    "It's currently about 95% fragmented and so most all queries need to do a full scan of its 600M+ rows" - The QP doesn't know where the leaf items are stored, only the storage engine does... so it being fragmented isn't causing your table scans. Queries that aren't selective enough are causing your table scans. – Sean Gallardy Mar 7 '15 at 0:24
  • @SeanGallardy Thanks for clarifying. Clustered index fragmentation relates to I/O performance issues. Issues with selectivity, out of date stats, SARGability, etc. can cause the Query Optimizer to choose a full table scan when a seek would've been more appropriate. Again, thanks for setting me straight. – GregSD Mar 10 '15 at 19:15
1

I've done this a few times. My servers are only 2008R2, however. I've moved tables like this and 'partitioned' data both because data files were reaching the max size (16tb) and identity columns were reaching the integer maximum.

Index rebuilds on big tables can take some time to roll back. Reorganization does not, however it is much slower (single threaded). You can kill it immediately and it will just stop where it is, so you could maybe do it over the course of several nights if need be. I had to do this with some quite unwieldy full text catalogs.

Rebuilding clustered indexes to new file groups is not an online operation if you have a (MAX) data type in your table -- see comments below. That was something that surprised me when I was working on my process. Also, be careful with sorting in tempdb. I once attempted a full statistics scan on a 3tb table and my 500gb tempdb LUN ran out of space overnight.

Let me know if you have any questions.

Thanks

  • I'm pretty sure that rebuilding to a new FG is online. You can specify ONLINE = ON and there does not seem to be a technical reason it must be offline. Where the pages are written does not matter to the engine. – usr Mar 8 '15 at 14:49
  • 1
    You know what, now that I think about it, I ran into a problem rebuilding it online because my table had a (MAX) column in the table. ----- An online operation cannot be performed for index 'Blah' because the index contains column 'Etc' of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max), xml, or large CLR type. For a non-clustered index, the column could be an include column of the index. For a clustered index, the column could be any column of the table. If DROP_EXISTING is used, the column could be part of a new or old index. The operation must be performed offline. – Erik Darling Mar 8 '15 at 15:18
  • @sqldriver Beginning with 2012, you can perform online indexing operations with heaps/tables that contain varchar(max) & nvarchar(max) data types. I upgraded to 2014 to overcome this exact limitation -- my table contains three varchar(max) columns. – GregSD Mar 10 '15 at 19:16
0

Cancelling an index build (be it online or offline) is a nearly instantaneous operation. The rollback work is very little. My guess is that some metadata changes must be rolled back and the in-build index pages must be deallocated.

I just tested this on a database I had at hand.

Should this procedure fail (even due to power loss) the index rebuild will be cancelled and rollback will be very quick.

Index builds generate tons of log records (online builds generate even more than offline). You might saturate the log transfer network or the secondary. This is a valid concern but it applies to any index build. Chances are this is not a problem for you.

  • @GregSD CI operations are no different from NCI operations in this regard (why would they be?). I tested on a CI. It also makes sense: Index builds and rebuilds create a fresh b-tree. The old one stays unmodified. I suggest that you test this. I'd be surprised if you found something different. – usr Mar 8 '15 at 10:46
  • @GregSD I just tested it with just those WITH options on a 100GB CI. 50GB into the build (according to the allocated size in the target filegroup) I cancelled. Took 10sec. I had a lock monitor open while doing that. During those 10sec I could see the index build PAGE locks go down to zero. My guess: Cancelling the build only takes as much work as deallocating the index pages takes. So you are right that it is not instant but it is very fast. Is 5GB deallocation speed per second too slow for you? – usr Mar 10 '15 at 19:16
  • Also note that on EE (which you have) the database becomes available after the REDO phase. It might be worth a test to see whether the rollback actually blocks concurrent access at all. – usr Mar 10 '15 at 19:17
  • Thanks for correcting me. I tried to edit the comment (possible?) you're referring to but deleted it instead. Sorry. What I was ultimately trying to convey was that I'm not concerned with non-clustered operations failing. The table will remain available. But I respectfully disagree when if comes to clustering. If you've been clustering a large table for any length of time, killing it can take a lifetime to rollback. And far worse, the table will be inaccessible. That's my concern. My 1.6 TB table is mission-critical and the business would stop should it be inaccessible for some time. – GregSD Mar 10 '15 at 19:19
  • In my efforts to figure out editing/deleting, I've taken all our comments and responses out of order. My bad. I've got it figured out now. Anyway, I am running EE. I'll look into the rest soon. Thanks! – GregSD Mar 10 '15 at 19:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.