March 17, 2008 at 5:34 pm
Comments posted to this topic are about the item Get Rid of the Indexes
March 18, 2008 at 2:36 am
Sounds like an awesome idea to me however the Connect proposal's status is "Closed (Won't Fix)" - does that mean MS have reviewed the suggestion and rejected it?
Malcolm
DB Ghost - Build, compare and synchronize from source control = Database Change Management for SQL Server
www.dbghost.com
March 18, 2008 at 2:54 am
It will increase the performance of the database but at the same increase the burden (decrease the performance) of the DBA.
😀
March 18, 2008 at 3:24 am
Hi,
This sounds like an awesome idea and as suggested by author, it would help lower the operational costing aspects of doing backups, thus a "green" idea. The only draw-back would be, if a backup needs restored, indexes would need to be created, thus we are possibly just moving the power consumption issue to another moment in time, but resource cost would still be similar.
Overall, a brillant idea!
Thanks,
Phillip Cox
MCITP - DBAdmin
March 18, 2008 at 4:47 am
In terms of power consumption the power would be saved every time a backup runs - hopefully restores, and therefore index rebuilds, are a less frequent activity! 😉
Malcolm
DB Ghost - Build, compare and synchronize from source control = Database Change Management for SQL Server
www.dbghost.com
March 18, 2008 at 5:47 am
I'm not sure the power savings is real - most servers spin their drives at full speed whether needed or not, and it's not like we're going to wear them out faster by using them (flash drives a different story!). Space savings is ok I guess, and the restore is interesting if you can restore without indexes and then only apply the ones you need to find the data you need, not rebuild all the indexes automatically.
Posting suggestions on Connect is more transparent than sending them to sqlwish in the old days, but I'm not sure that works out to a totally good thing. I don't have time to visit Connect daily/weekly to see what interesting (or not) ideas have been posted by someone with; a good idea, a bad idea they are proud of, or someone that just wants to be seen as driving product change. I don't want to discourage suggestions, but I think when we start lobbying for them it becomes a game more than process.
A minor tweak would be to continue accepting suggestions, let MS consolidate similar ones, rule out any it just won't do for whatever reason, and then get a good pro/con discussion posted. Then put out a once a month call for votes, distribute that via the communities, email, and RSS, and let us vote on all the open ideas at once. Then win or lose, done with them and on to the next interval.
March 18, 2008 at 6:06 am
On the IBM AS/400 - iSeries when you back up a library which contains your files it backs up the physical files, logical flles (also known as alternate indexes or views) but stores the key information for the file not the actual data. When you are restoring files the system restores the layout for the physical file, the layout for all logical files and then it restores the data in the physical file which then builds the logical files as a separate running job (a submitted job to an IBM subsystem).
Microsoft's method of storing the complete index is because they count the offset from the beginning of the file in the index. This way when the index is restored it knows exactly what is where in the file. Now if the master file is changed (deleted records or somehow when restored the order was changed) the index becomes corrupted. The AS/400 method makes it more difficult to create a corrupted index. For example you restore the physical file only, make changes to the layout (drop key fields) and then restore the indexes then problems will occur.
When backing up an AS/400 with 5 or 6 Gigabytes of information the system will store all this on a 4 Gigabyte tape without using data compression without any probelms because it only backs up the method (access path) of creating the index/view.
Now before anyone says it is an old machine it is younger than the PC's. As well the system allows at least a 32 way (truly independent) processors so a different kind of thinking for running multiple jobs at the same time must be done.
March 18, 2008 at 8:24 am
I think the power savings would come from less hardware time from tape drives or less drives needed if you used disk backup. For any particular database it would not necessarily be a huge savings, but in aggregate it might add up.
7 years ago we backed up a quarter TB of database backups alone. If we reduced that in half, it would have been significant savings, especially it would have delayed purchasing more tape media/hardware for backup.
March 18, 2008 at 1:02 pm
Couldn't you effectively do this by putting data and indexes on separate filegroups and only restore the data filegroup? Then just drop/recreate indexes?
Take care,
Bert
"Speculations? I know nothing about speculations. I'm resting on certainties. I know that my Redeemer lives, and because He lives, I shall live also." - Michael Faraday
March 18, 2008 at 1:22 pm
Microsoft has their reply in the comment section:
This is an interesting suggestion, however it will not work with the implementation of backup that SQL uses. SQL backups are purely physical in nature. We do no interpretation of pages other than validating checksums if that option is selected. If an extent is allocated, we copy it. Restore is the reverse process.
Posted by Microsoft on 3/5/2008 at 10:17 AM
From me:
It seems like you could do something similar with filegroups, one for data and one for indexes.
March 18, 2008 at 2:38 pm
Also, if you are restoring a backup of a production database, you are possibly in some sort of DR situation, and in those cases you want the database back as soon as possible (management usually do!). So lengthening the process by having to rebuild indexes afterwards could be a problem.
Its an interesting idea and would have its uses (space saving) but it would have to be an option and not one I would use in most cases.
---------------------------------------------------------------------
March 19, 2008 at 1:49 pm
I think you're missing the point and trying to be trendy by waving the green flag.! The main purpose of a backup is to enable yourself to recover from a number of damaging scenarios, malicious damage, DR, BCP, failures, corruption etc. As pointed out you could take secondary indexes to seperate filegroups and use partitioning to minimise the backups - the last thing i want to do is start rebuilding indexes on 400 million row tables after a restore.
The other critical point is that dropping the indexes will enlarge the transaction log which will also need backing up , and you'd need to do a backup then restore then drop indexes then backup - can't start dropping indexes on 7 x 24 systems. Redgate and Quest have compressed backup software - much better way. I have to say this is probably one of least well thought out suggestions and not really offering anything valid.
If you really want to backup data only use bcp and a zip - but of course putting it all back would be tricky.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
March 19, 2008 at 2:36 pm
I think this would need to be an option, both on backup (keeping v skip indexes) and restore (rebuild or not), but I don't see where there's a problem with it.
No one is asking to drop indexes. It would be an option to not include them in the backup, meaning just send less data to disk. No changes at all to the database being backed up.
there are plenty of places that would probably live with the need to rebuild indexes in a DR situation to save on backup costs every single day.
March 19, 2008 at 2:41 pm
In my sitituation, at least in DEV and QA, the preponderance of "disasters" involve an errant query. In this case, I restore to a new database and copy the munged data over. Having a smaller backup/decreased restore time would be a big help in these scenarios.
I certainly don't believe this proposed feature should be used in ALL cases, but it certainly would help in some.
Take care,
Bert
"Speculations? I know nothing about speculations. I'm resting on certainties. I know that my Redeemer lives, and because He lives, I shall live also." - Michael Faraday
April 3, 2008 at 8:29 am
Thanks for posting this Steve & also for the discussion everyone.
I have re-opened the connect suggestion because I believe MS missed something significant in their response. Their recent response claims the implementation would be difficult due to excessive accounting requirements during backup, but no mention was made of the option to include a page level flag which would allow the backup process to identify NCIX pages without accounting. If such a page flag were included, this feature would be very efficient & relatively easy to implement. Hence, I've opened the connect suggestion for another attempt..
So please continue to vote (for or against)!
Re some of the other comments in this thread:
(a) Restore time would not necessarily be "longer" with this feature. Strictly speaking, the database would be available once the non-index data is restored, which means the restore would complete FASTER under this option, even though the indexes aren't rebuilt at that stage. Options could be provided allowing DBAs to delay recovery until after indexes are rebuilt (slower) or bring the database online immediately after the non-index data has beeen restoreed (faster). Indexes could be recreated online from there if the customer has Ent Edn or offline with Stdn Edn under the second option.
(b) There certainly would be energy savings from this. Whilst disk spindles do still spin, the heads don't move unless there is activity & backups do cause significant head movement. I'm no energy analyst, but I'm sure that the massive number of database backups that occur around the world every day are presently wasting a lot of energy on this.
(c) There are other major objectives of this idea other than just energy savings too - disk space savings are hugely important to many people / businesses & time savings are also a big factor (shortening maintenance activity is always a good thing)
(d) Someone mentioned that tlog sizes would grow as a result of the index removal. This isn't true - nothing would be recorded in the log at all, just some small meta data in the backup file to allow the restore process to know that NCIX pages aren't in the backup.
Cheers,
Greg Linwood
Regards,
Greg Linwood
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply