November 24, 2009 at 9:24 pm
Hi All
What is your personal preference regarding the options on Autogrowth?
I know some DBA's prefer setting a fixed megabyte growth size, let's say 250mb, instead of 15% of 400gb?
Does it actually take longer for SQL to determine the percentage of the totalsize at that specific point in time, before it "grows" the Database?
Are there any ways to measure the difference in duration (performance implications?) ?
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
November 24, 2009 at 10:10 pm
I Haven't seen anywhere if the calculation the % value would take some time for SQL Server. It must be a fraction of a section for SQL Server to calculate such a value.
My personal preference has always been autogrowth in XYZ MB, value depends on the Database size and expected growth.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 25, 2009 at 7:52 am
And I prefer it as a multiple of 64 ( 1 extent which is 8 pages (8*8))
Pavan.
November 25, 2009 at 8:25 am
I think the reason people tend to prefer MB over % is as you said due to percentage sizes.
For example 10% of 1GB is 100MB (no problem); however 10% of 400GB is 40GB (bit of a problem), 10% of 2TB is 200GB (big problem).
Ultimately it comes down to your database size and expected growth patterns. There is no point haveing the growth set so after one extent you have more freespace then you will use in months/years but similarly it is seen as a performance no no to go extending multiple times a day etc.
Generally as long as your initial size is set correctly (for purpose) and you have done your growth analysis, the correct solution for a given db should be self evident.
Adam Zacks-------------------------------------------Be Nice, Or Leave
November 25, 2009 at 8:37 am
Autogrowth in % is bit risky when the database is very large. Adding 10% of 1.5 TB database will take more time if keep the growth as 100 MB or so. The % growth option can cause error when there is not enough disk space on server as well.
% growth consumes more disk space than required.
November 25, 2009 at 8:50 am
Same info as Atul and Schadenfreude-Mei. Time to extend the database in % gets larger as the growth gets larger which can cause performance issues. To better control the growth of the database and performance impact of growing the files - go with MB.
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
November 25, 2009 at 12:31 pm
Atul,Schadenfreude-Mei and Jason are right. The time to grow in % takes more time if the database is huge and it leads to performance issues.We use autogrow MB.
November 25, 2009 at 1:03 pm
VRR (11/25/2009)
Atul,Schadenfreude-Mei and Jason are right. The time to grow in % takes more time if the database is huge and it leads to performance issues.We use autogrow MB.
Hey do you mean that I am wrong in my reply?
Look at the Original post, I replied the the OP's comment
Does it actually take longer for SQL to determine the percentage of the totalsize at that specific point in time
saying I have no idea about calculation taking longer time.
I know the what happens on enabling 10% value (as 400 Gigs DB needs 40 Gigs to autogrow) and that is what I meant when I said I go by a certain MB value.
It is always easy to elaborate on someone's already post..
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 25, 2009 at 1:10 pm
I use MB, but I also have regular monitoring that can help me avoid needing autogrow except in emergencies. Be sure if autogrow does occur, you have some sort of monitor that alerts you to check things out.
November 25, 2009 at 10:48 pm
I never use percentage for many of the reasons stated.
I also never leave the defaults. It's takes and makes 73 disk fragments to grow a database from 1MB to 1GB in 10% growth spurts. As others have pointed out, when the database get's large, the growth spurts by percentage can become quite large. It won't take as long to grow in 2k5 as it did in 2k, but you really never want to let growth on any database take you by surprise.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 26, 2009 at 12:08 am
In most cases, I always MB rather than % and monitor the growth of the database and log.:-)
November 26, 2009 at 12:08 am
In most cases, I always use MB rather than % and monitor the growth of the database and log.:-)
November 26, 2009 at 12:19 am
Thank you for all the insightfull replies.
Seeing that we all agree more or less on the subject, I am putting this in my manual as "Best Practice" 🙂
No disrespect to anyone, but it would have been interesting to see what Gail has to say about this.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
November 26, 2009 at 1:10 am
I can probably tell you what she would say, the same thing as the rest of us. When setting the autogrowth it is best to set it in MB not a %, for all the reasons given by everyone else already.
No disrespect taken. :alien:
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply