December 7, 2009 at 9:41 am
Hey guys,
Just looking for a quick bit of advice.
A routine was run in one of our applications which cycles through and tries to synchronise the system configuration against the database - it basically adds, removes, alters columns where applicable...
The upshort of it is that it bombed out half way with an error, but not before it had reserved a massive amount of space within the database to cater for its operations - 200GB+
This has increased the size of the database by this amount (approximately a 65% increase in its overall size - to 520GB).
Now, obviously a shrinkfile or shrink database operation is going to be called for to get rid of the free space sitting in it, but in running this routine am I correct in saying that;
I'll have to rebuild the indexes due to the fragmentation caused by the shrink?
It could actually hamper performance when every table looks to create its own space for growth when new operations run through the system?
Any advice or recommendations for tackling this without having too much impact on performance levels, bearing in mind the size of the database and the time it will take to run maintenance jobs on the files?)
Many thanks in advance 🙂
December 7, 2009 at 12:58 pm
Indexes will not have to be rebuilt. Shrinkfile is only releasing white space that was reserved by the database but not actually used. The performance hit will come when the database needs to grow during normal operations, and that is done for the database files themselves, not per table.
Question though; will this routine be ran again sometime in the future? If so, and the routine grew the database because it will need the space eventually, then it is better to keep the space since it will be utilized in the future. That is better than shrinking the database and then waiting for autogrow to increase the size, because you have no control of when that operation happens (possibly impacting performance) and depending on the autogrow settings could cause more file fragmentation than you would get from just leaving the size as is.
Joie Andrew
"Since 1982"
December 7, 2009 at 11:22 pm
I would avoid the shrink of the database. And certainly would not shrink the database to a point that releases all whitespace. By keeping adequate whitespace in the database, you allow for increased data demands while not forcing autogrowths to occur. Autogrowths are harsh on db performance. If you leave the whitespace and then perform your index maint, the maintenance will likely run faster. Also, for this other routine that you run, it will run substantially faster if the space is already allocated.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 8, 2009 at 2:10 am
Okay, there is no significant difference in the backup times, and there is plenty of diskspace available to handle the extra 200+GB, so it is not a major issue to leave it where it's at...
Is there any difference in performance just from having all this whitespace in the database though?
I was quite comfortable doing a shrinkfile, and have done it a few times with no degradation in performance, but I just read this article:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
And the part that stuck out for me in particular was this section:
"Moving datapages around will fragment your database.
Say you rebuild your indexes (which will requires free space in the database), and then shrink the database. The shrink will essentially undo the index rebuild, leaving you with fragmented indexes. Don't believe me? This is easy to test for yourself.
What if you do it the other way around, shrink first, then rebuild? Well, the rebuld need free space in the database for the largest index that you rebuild, and it is likely you have a large table with a clustered index. A friend of mine had a 4GB used space db, where almost all space was one 4GB table. He did a shrink and then rebuild, where the re-build immediately "bumped up" the db size to 8GB.
(Applies to shrinking of data files.)"
But I guess, since most people are recommending to leave the freespace in the database, that it is the best action to take anyway.
The routine that caused the freespace in the first place, will have to be run probably one time in the future through to completion. It is not a regularly run routine, and I don;t really see it needing to be run more than once, but if the space is already there at least for it to run, then it is probably worth leaving at least for the timebeing, provided the whitespace is causing no significant overhead?
December 8, 2009 at 2:54 am
Joie Andrew (12/7/2009)
Indexes will not have to be rebuilt.
They will. Shrink, with the default options can completely reverse an index, ie increase fragmentation up to almost 100%
See - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/
Shrinkfile is only releasing white space that was reserved by the database but not actually used.
That it does. But if the unused space is scattered around the file and the TRUNCATE_ONLY option is not specified on the shrink, SQL will first move pages as far forward in the file as possible. It does that without consideration of index structure, moving each page individually.
If truncate_only is specified, pages are not moved and only space that's at the end of the data file is released. Using that option does not increase fragmentation.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 8, 2009 at 3:36 am
GilaMonster (12/8/2009)
Joie Andrew (12/7/2009)
Indexes will not have to be rebuilt.They will. Shrink, with the default options can completely reverse an index, ie increase fragmentation up to almost 100%
Why do you want to shrink? Databases tend to grow as more data gets put in them. It's in their nature.
The main reason I am looking to shrink the database, is that it has jumped from 320GB to 540GB.
Obviously this reduces the managability of the files, and my only real concern is in diskspace on the failover copy of the db, which is pushing it close on the drive it is on.
The database forms part of a system which has a shelflife of maybe another 18 months, and it is never going to increase by 220GB in this time so it is a bit overkill to have that much reserved space sitting allocated to it for no good reason.
Like I say though, I will probably have to run the routine that created the whitespace through to completion one time in the next few weeks so it may be worth holding off doing any shrinking if at all, until it has been run.
GilaMonster (12/8/2009)
That it does. But if the unused space is scattered around the file and the TRUNCATE_ONLY option is not specified on the shrink, SQL will first move pages as far forward in the file as possible. It does that without consideration of index structure, moving each page individually.
If truncate_only is specified, pages are not moved and only space that's at the end of the data file is released. Using that option does not increase fragmentation
I will have to check the allocation in the files, but it is highly dependant on whether the whitespace is still sitting at the end of the file, as to whether a truncate only is going to be suitable.
I'd just like to know whether there is any overhead caused by the additional whitespace when it comes to transactions, or if I'll actually be better off in the meantime due to no auto-increase having to take place?
December 8, 2009 at 3:51 am
Ian Ritchie (12/8/2009)
I'd just like to know whether there is any overhead caused by the additional whitespace when it comes to transactions
Nope. No overhead due to empty space inside the data file. Backups don't back up unallocated extents. Integrity checks don't check unallocated space.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 8, 2009 at 3:53 am
Okay thank you 🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply