June 9, 2014 at 1:56 am
Hi all,
I am backing up a 3 tb database and it's spawning across multiple threads and at the same time I am running an index creation statement to create index on million row table. I wanted to know whether the process will have any effect on backup or will it slow down my index creation?
Regards,
Faisal
June 9, 2014 at 4:32 am
Yeah, of course. Both processes are going to use CPU and memory and I/O. Therefore there's a very distinct possibility that the two processes may be in contention on those resources. Plus the fact that the index creation is a transaction that will need to be managed within the backup process in order to deal with recovery should you have to restore. They will absolutely impact each other. The degree of impact is not something I can predict for you though. You'd need to monitor both processes to see what they're waiting on, if anything. I'm not suggesting that you don't do them at the same time, but if either has to run faster, possibly not running it at the same time as the other process is one thing that might speed them up.
"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
June 9, 2014 at 4:37 am
Thanks! Grant.
June 9, 2014 at 11:39 am
Okay...what if my backup finishes before my index creation. How much of it will be backed up? and if it's in simple recovery...and it's a full backup.
Regards,
Faisal
June 9, 2014 at 11:56 am
A full backup records everything going on in the system, all the transactions that are running during the backup process, those that finish, those that don't. It all gets written out. So your partial index creation will be recorded (exact details of what exactly gets recorded, I'm not sure, but stuff gets written to the backup). So that will be stored since it's an open transaction. Then, as part of the restore process, if you need one, the recovery process takes care of rolling forward and back transactional changes as needed so that your database will be in a consistent state. The index creation, if it doesn't finish, won't be partially done when the restore completes. It won't have happened. But, that transaction will still be a part of the backup itself.
"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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply