Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Auto growth - intensive? Expand / Collapse
Author
Message
Posted Tuesday, February 26, 2013 9:23 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, November 22, 2014 9:37 AM
Points: 223, Visits: 884
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'
Post #1424142
Posted Tuesday, February 26, 2013 9:38 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, October 31, 2014 10:27 AM
Points: 708, Visits: 3,293
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

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
Post #1424152
Posted Tuesday, February 26, 2013 9:39 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:03 AM
Points: 40,445, Visits: 36,898
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1424154
Posted Tuesday, February 26, 2013 10:27 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, November 22, 2014 9:37 AM
Points: 223, Visits: 884
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'
Post #1424174
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse