February 26, 2013 at 9:23 am
Just wanted to ask for confirmation really I read regarding a databases auto growth. Say if it is set to 10% and you experience a fair aount of growth during the day does the actual auto grow process have a noticable effect on system performance.
Reason I ask is that I read once that it is not uncommon for admins to configue the size of a database to cover the growth and then shrink the db's out of wroking hours afterwards?
'Only he who wanders finds new paths'
February 26, 2013 at 9:38 am
Depends really....
If Instant File Initialisation is turned off then yes, auto growth on a large DB can affect performance temporarily.
Say you have a DB of 200GB size, when auto growth kicks in, imagine how long it's going to take SQL Server to zero out 20GB worth of disk space? A couple of minutes at least....
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
February 26, 2013 at 9:39 am
david.alcock (2/26/2013)
Just wanted to ask for confirmation really I read regarding a databases auto growth. Say if it is set to 10% and you experience a fair aount of growth during the day does the actual auto grow process have a noticable effect on system performance.
It can do, especially if the IO subsystem is overloaded and instant init is not on
Reason I ask is that I read once that it is not uncommon for admins to configue the size of a database to cover the growth and then shrink the db's out of wroking hours afterwards?
I hope that's not common, that's a horrid practice. Why shrink overnight (fragmenting indexes) just to grow the next day (probably getting file system fragmentation too). That's a major waste of resources and time.
Grow the DB to a sensible size, monitor free space, when free space drops below a comfortable threshold grow the DB again.
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
February 26, 2013 at 10:27 am
Thanks both for the replies as always.
Perhaps I recall wrong Gail, I come to have more and more mistrust in my memory each day! It didnt seem a good idea hence my post!
Thanks again
'Only he who wanders finds new paths'
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply