Autogrowth, shrink and database performance

Question

Autogrowth, shrink and database performance

IT researcher

SSCertifiable

Points: 7600
More actions
January 22, 2014 at 2:31 am

#303438

I have already asked some questions about Autogrowth and shrink. But I have some doubts related to it.
1)I have a database which grows about 300 MB every month. So is it best to set Autogrowth of 300 MB to it? Now 10 MB is set.
2)I have so many small databases which really have data of about 50 MB. But it's physical size is about 900 MB.(So 850 is free space). I have not shrinked this database. As if i shrink it then again when data gets added autogrowth occurs. But adding data to this database is rare. So I think it will not gro more than 200 mb for next one year. So should i shrink the database? If I keep it as it is Should that cause any performance problem? Or having more free space(more than 90%) in database will cause any problem. I think most of the people think having large physical size will cause performance problem. So is it true or not?
3) SQL Server 2008 R2 Express Database Size Limit is 10 GB. So is it physical size of both mdf and ldf file together? because I need to consider this if i should shrink database at any point.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 1

IT researcher (1/22/2014)
I have already asked some questions about Autogrowth and shrink. But I have some doubts related to it.
1)I have a database which grows about 300 MB every month. So is it best to set Autogrowth of 300 MB to it? Now 10 MB is set.
2)I have so many small databases which really have data of about 50 MB. But it's physical size is about 900 MB.(So 850 is free space). I have not shrinked this database. As if i shrink it then again when data gets added autogrowth occurs. But adding data to this database is rare. So I think it will not gro more than 200 mb for next one year. So should i shrink the database? If I keep it as it is Should that cause any performance problem? Or having more free space(more than 90%) in database will cause any problem. I think most of the people think having large physical size will cause performance problem. So is it true or not?
3) SQL Server 2008 R2 Express Database Size Limit is 10 GB. So is it physical size of both mdf and ldf file together? because I need to consider this if i should shrink database at any point.

1) Yes, you can autogrow for 300 MB, however it's better to go with smaller sizes, let's say 50-70 MBs.

2) No, you should not shrink. They are all tiny. A database size of 300+ GB may be ranged as "big" database.

3) Yes it's the total limit.

You have other advanced features - compression for e.g.

Regards,

IgorMi

Igor Micev,My blog: www.igormicev.com

HanShi SSC-Dedicated Points: 33507 More actions · Answer 2

Answers:

1. A growth setting of 300MB is most probably acceptable. It depends on your disk performance if it can handle the I/O at that moment. If you assign the "instant file initialisation" policy to the SQL service account, it will initialize the file as a background process, speeding up the growth. You could also monitor the file usage and manually expand the size at a moment when the database is not heavily being used.

2. Shrink a database to such a size that it soon need to grow again isn't good practice. If you don't have storage capacity issues you could leave the size as is. But you could shrink the database to such a size that it will accomodate the usage of the upcoming 6 - 12 months. If you know the size will grow to a specific amount and then remain at that size you'll best set the database to that size at once. This will prevent growing of the file and thus prevent fragmentation of the file on disk.

3. If I recall correct the max size for an Express database is including the LOG file (but I'm not sure).

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 3

I'm pretty sure the express limit is the data file only, independent of the log. Documentation that I could find isn't completely clear though. Several places it says "10gb of storage" which certainly indicates a separation from the log.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

tim_harkin SSCrazy Points: 2399 More actions · Answer 4

IT researcher (1/22/2014)
I have already asked some questions about Autogrowth and shrink. But I have some doubts related to it.
1)I have a database which grows about 300 MB every month. So is it best to set Autogrowth of 300 MB to it? Now 10 MB is set.

If this is a production database (not sure based on the question, the fact that it is on Express, etc.) my preference would be to expand the data file so that it will be big enough to handle at least 6 months, if not 12 months of growth. So I would manually grow the file by 1.8 to 3.6 GB. That way, you allocate all of the storage at once, taking the hit all at once, and you have less disk-level fragmentation.

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 5

I have always heard you should 'size' your database to about the size it should grow to for 1 years worth of data. Autogrowth should be not small chunks. Reason for that is every single time the db autogrows it grabs that space from somewhere on the disk and it is likely to cause fragmentation. The more autogrowths the more your data is spread across the drive fragemented. If you run a select against all of the data in a large table it has to go out and hunt for that data on disk and every fragment of that data slows down the read.

phil.doensen Mr or Mrs. 500 Points: 501 More actions · Answer 6

The 10GB limit for Express is the size of the MDF.

It does not include the LDF. That can actually be as large as it wants to be.

You can also have multiple databases all up to 10GB in size.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

IgorMi (1/22/2014)
1) Yes, you can autogrow for 300 MB, however it's better to go with smaller sizes, let's say 50-70 MBs.
2) No, you should not shrink. They are all tiny. A database size of 300+ GB may be ranged as "big" database.

I have to disagree with that. Smaller growth means more underlying operating system file fragmentation.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Perry Whittle SSC Guru Points: 234013 More actions · Answer 8

IT researcher (1/22/2014)
I have already asked some questions about Autogrowth and shrink. But I have some doubts related to it.
1)I have a database which grows about 300 MB every month. So is it best to set Autogrowth of 300 MB to it? Now 10 MB is set.

No, i would set autogrowth at about 2048 ot 1024M

IT researcher (1/22/2014)
3) SQL Server 2008 R2 Express Database Size Limit is 10 GB. So is it physical size of both mdf and ldf file together? because I need to consider this if i should shrink database at any point.

Per file iirc

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 9

Jeff Moden (1/24/2014)
IgorMi (1/22/2014)
1) Yes, you can autogrow for 300 MB, however it's better to go with smaller sizes, let's say 50-70 MBs.
2) No, you should not shrink. They are all tiny. A database size of 300+ GB may be ranged as "big" database.
I have to disagree with that. Smaller growth means more underlying operating system file fragmentation.

Maybe you're right. For non-busy systems the autogrow size even doesn't matter. For busy OLTP systems going with small sizes is better if we consider a good maintenance which cares about the fragmentation.

I was not fully precise: I meant 50-70 MBs per file. And I considered his databases have usually one file as he claims they are tiny.

Regards,

IgorMi

Igor Micev,My blog: www.igormicev.com

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 10

IgorMi (1/25/2014)
Jeff Moden (1/24/2014)
IgorMi (1/22/2014)
1) Yes, you can autogrow for 300 MB, however it's better to go with smaller sizes, let's say 50-70 MBs.
2) No, you should not shrink. They are all tiny. A database size of 300+ GB may be ranged as "big" database.
I have to disagree with that. Smaller growth means more underlying operating system file fragmentation.
Maybe you're right. For non-busy systems the autogrow size even doesn't matter. For busy OLTP systems going with small sizes is better if we consider a good maintenance which cares about the fragmentation.
I was not fully precise: I meant 50-70 MBs per file. And I considered his databases have usually one file as he claims they are tiny.
Regards,
IgorMi

I strongly disagree. Small sizes of growth is never the right option except for small databases that aren't supposed to grow because of the fragmentation that multiple small growths cause. And, to be honest, letting auto-growth do it's thing is generally a bad plan because auto-growth usually occurs when you're doing something big and important... the same times that you can least afford delays by growth especially small growth. It doesn't take much more time to grow a database by 500MB than it does 50 or 70 but, done correctly, auto-growth should never happen. It should be a planned exercise before you need it to happen and (there are special reasons why you might not want to, of course) it should usually be large enough to get you through the next 3 to 6 months without an auto-growth.

And, to be sure, I'm not talking just about index or database fragmentation. I'm also talking about fragmentation at the OS level.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)