December 10, 2010 at 6:25 am
Here's a dumb question...
Why do we have a feature in SQL Server that all the guru's, MVP's and experts say "Don't use!!!"? I mean why not add a "Nothing Button" - you know, a button that the user can click that does absolutely nothing.
Remove the darn thing, and add the Nothing Button - much more productive and safe.
December 10, 2010 at 6:31 am
The opinion of a novice-intermediate DBA,
It is all too easy to fall victim to the simple maintenance plans. When I started out, I used many of them before even fully understanding them. I figured its in the maintenance window so it must be standard. With Network Admin’s complaining about space and not familiar with how SQL holds and uses space these things are all the harder to control. This should be added to the Network Admin certifications whatever those might be :).
I say keep the functionality and not a trace flag as I don't what to have to bounce a server to enable. I may not want to bring all my databases down for just one out of hand database or dev and testing environments with half the load. Having it in the maintenance plans is a big joke and should be removed. AutoShrink is yet another lump of dookie and should be removed. It should be available by T-SQL and still in the interface but more warnings and explanations. The shrink functions should be enhanced to defrag or perhaps even a separate background defragger option or maybe just a less intensive defrag than reorganize and rebuild (one that doesn't hinder users while performing). Maybe we can teach the SAN or our storage to maintain the drives reducing or eliminating fragmentation all together O one can dream :-D.
Like many, nothing I am saying is new. I hope in the years to come all these woes can be improved or even eliminated to a point we all tell jokes about fragmentation our kids don't understand.
Thanks,
Matt
December 10, 2010 at 6:33 am
I'll add to the question: Should autoshrink be removed, and then put back in when SSDs become the standard and hard-drive seek time is no longer a problem that needs to be overcome, and file fragmentation is essentially a meaningless concept?
There are a LOT of database design, administration, and tuning concepts that will not be anywhere near as meaningful with storage that doesn't involve heads reading from spinning disks. Non-sequential access to flash storage doesn't have the physical/kinetic issues that make file fragmentation an issue on current storage media. Will that affect how clustered indexes behave? Will it affect the performance issues of page splits and index fragmentation?
Now I need to go get an SSD drive and start playing with this! There goes my Christmas money! 🙂
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 10, 2010 at 6:35 am
For clarification, are we talking about shrinking datafile and logfiles?
Also, what about truncate only?
For instance, I've found myself using shrinkfile truncate only for logfiles once every few months after a large data load to get them back in line. Mostly because our NetApp uses block level snapshots and having a large transaction log requires more block changes due to its 'circular' nature.
December 10, 2010 at 6:41 am
I also agree that there are valid uses for shrink but it should be used judiciously and with extreme caution. In most production environments I see little need for it, but it can be useful in development environments. For example, sometimes we will have a need to process a large dataset in development then remove the data when we're done. Our disk space on development is sparse enough so a shrink on the DB becomes essential in those situations. I'm not sure if the situation calls for a re-engineering of the shrink utility but it certainly should not be a knee-jerk type of operation.
LinkedIn: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com
December 10, 2010 at 6:41 am
pjdiller (12/10/2010)
Also, what about truncate only?
It's only valid when shrinking the data file. If you shrink the log and specify that option, it's ignored. It's the same as doing just a shrink file on the log with no targetsize set.
Again from Books Online:
target_size
Is the size for the file in megabytes, expressed as an integer. If not specified, DBCC SHRINKFILE reduces the size to the default file size. The default size is the size specified when the file was created.
TRUNCATEONLY
Releases all free space at the end of the file to the operating system but does not perform any page movement inside the file. The data file is shrunk only to the last allocated extent.
target_size is ignored if specified with TRUNCATEONLY.
TRUNCATEONLY is applicable only to data files.
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 10, 2010 at 6:44 am
GSquared (12/10/2010)
I'll add to the question: Should autoshrink be removed, and then put back in when SSDs become the standard and hard-drive seek time is no longer a problem that needs to be overcome, and file fragmentation is essentially a meaningless concept?<snip> Non-sequential access to flash storage doesn't have the physical/kinetic issues that make file fragmentation an issue on current storage media. Will that affect how clustered indexes behave? Will it affect the performance issues of page splits and index fragmentation?
Yes and no because shrinking is not only about file fragmentation and index fragmentation/page splits are not only about seek time of the drive.
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 10, 2010 at 6:49 am
GilaMonster (12/10/2010)
GSquared (12/10/2010)
I'll add to the question: Should autoshrink be removed, and then put back in when SSDs become the standard and hard-drive seek time is no longer a problem that needs to be overcome, and file fragmentation is essentially a meaningless concept?<snip> Non-sequential access to flash storage doesn't have the physical/kinetic issues that make file fragmentation an issue on current storage media. Will that affect how clustered indexes behave? Will it affect the performance issues of page splits and index fragmentation?
Yes and no because shrinking is not only about file fragmentation and index fragmentation/page splits are not only about seek time of the drive.
Right, but will the cost/space ratio on SSDs (significantly higher than on HDDs), be a bigger concern than fragmentation? Will performance in a highly fragmented database on an SSD still outweigh the storage cost? Right now, on HDDs, it usually (almost universally) weighs on the side of "let it grow", or even better yet "make it big enough that it doesn't need to grow regularly". Will that still be true on non-sequential storage media with access times more comparable to RAM than to platters?
I don't know the answers to those questions, so I get to geek out over the holidays and see if I can find them out. Should be fun!
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 10, 2010 at 6:57 am
It depends? 😀
I think the answers will differ from app to app, DB to DB. Suspect that there won't be a universal truth showing up any time soon. Even on magnetic media, there are scenarios where fragmentation (index) is completely harmless and doesn't affect performance in the slightest.
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 10, 2010 at 7:07 am
NOOOOOOOO the dreded
It depends?
December 10, 2010 at 7:11 am
I would say that Gail is right on: keep the command, but remove it from Maintenance plans, and remove Autoshrink.
December 10, 2010 at 7:15 am
regarding the need to shrink because of dev or qa reasons... those users can learn to use trace flags. I think it should be made difficult. It is one of several roots of all evil. My favorite are the users that are shrinking to improve performance not realizing they are putting their system in a constant tug-o-war battle.
Disk is cheap. Very cheap. The only shrinking I do is when it is required as a system is ending its supportable life and you need to shift file sizes around for short term growth. Always however grow the files out manually afterwards to put the allocations in there.
The earlier post about needing to shrink to improve performance is completely the opposite as Gila.Monster pointed out. The file growth the next time it runs is causing it to run far longer than it would need. Inserts and selects are cheap. File growht is ugly.
December 10, 2010 at 7:24 am
I agree with the poster who stated that the Model db should be changed to default to SIMPLE rather than FULL recovery mode. We have well over 200 dbs and only 8 require point in time recovery. I know that the default for Model can be changed from FULL to SIMPLE and I've done that, but I would think that the majority of the databases people have only require SIMPLE recovery.
As far as the Shrink option, I think it's necessary and it should be schedulable. We recently discovered that one of our vendor databases was rapidly growing and we traced it to an ErrLog table into which the vendor was writing a vast amount of data. The data more than a few days old was useless, but they never mentioned or even thought of suggesting that the data should be truncated from the table. We setup a purge job to clean out data more than a week old and we were able to reclaim 125GB of space from that one db. This db also gets copied to 7 other instances for DEV, QA, reporting, and dataconversion testing environments, so that became 875GB of space we were able to reclaim. Not to mention the time saved to backup and restore some of those environments.
December 10, 2010 at 7:33 am
Fix or not to fix? I say fix.
Why did Microsoft give us this function and then not fix it. How long has this problem been around? Sure give us BI and SSRS upgraded function but not this. It's too bad that many people had to spend the time to write articles about shrinking a database. If Microsoft would just fix this problem then we can all spend our time doing other things.
A "fixed" shrink database command would be great and help save some disk space. Disk may be cheap but there are many companies out that are even cheaper. They can't afford more disk or want to spend the money on promoting their cause.
So I like to pose this question to Microsoft. Are you to cheap to fix the shrink database command? If you (Microsoft) think you are a world leader in the DB market then fix it. It not, then you are no better than Oracle.
Just my 2 cents worth,
Rudy
Rudy
December 10, 2010 at 7:40 am
RML51 (12/10/2010)
Not to mention the time saved to backup and restore some of those environments.
Shrinking is not going to help backup speed or size. Empty extents are not backed up. It'll only help on restores if the service account doesn't have instant initialisation or if it's the log file that you're shrinking.
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
Viewing 15 posts - 16 through 30 (of 71 total)
You must be logged in to reply to this topic. Login to reply