February 7, 2012 at 7:54 am
Good Morning everyone,
I am about to run out of space on one of my important databases. The initial size was set at 85 Gigs with an autogrowth option of 10% (Yepp..I get that much of data in a day). I read quite a lot about autogrowth being not good if you dont configure it correctly.
My question to you is, what if I change the initial file size (of my .mdf) to 200 Gigs (supposedly). Would this hurt my database in any way? I tried finding the answer to this at a lot of places, but most of them deal with autogrowth being not set right and the initial size being copied from the Model DB.
Any help would be appreciated! Thanks.
February 7, 2012 at 7:57 am
One of my databases space usage goes up by 20GB on a weekend. I always presize it for the week as growth varies slightly but always gets used eventually.
Presizing data files should have no detrimental effect. It could potentially effect the transaction log (VLF's) if you were to do the same.
February 7, 2012 at 8:32 am
Thanks for that answer, I just increased my file size to 110 Gigs.
I was thinking on the lines of adding a secondary file group (.ndf) file but wasn't too sure how it works. My questions for that are
1) If I add a secondary file group and mark it as default, will any of my queries using the primary file group fail?
2) Any best practices for adding file groups?
3) How does the performance get affected if I add new file groups to the database?
Thanks once again.
February 7, 2012 at 9:02 am
The size of your files has no negative effect on performance. File growth has a tremendously negative effect due the fact that SQL Server does not have instant file initialization and no data can be written to the file while it is expanding. Because of this you really never want to set autogrowth to a % especially for large files. If you can't pre-size the file than a good growth size is usually 512MB chunks.
Here is a great article on handling trans log growth. http://www.red-gate.com/products/dba/sql-backup/assets/files/t-log-whitepaper.pdf
Adding additional filegroups will not affect performance though for datafiles you might considered putting the new filegroup on a different array. Since logs are sequential writes it doesn't matter. Your data will still be read from the old datafile and SQL will automatically start using the new log file.
February 7, 2012 at 9:28 am
SQL server does have instant file initialisation. It's just not on by default.
It can be enabled by giving the service account volume tasks permission in the security policy
February 7, 2012 at 9:40 am
rajiv.varma (2/7/2012)
1) If I add a secondary file group and mark it as default, will any of my queries using the primary file group fail?
No, this is transparent for intents and purposes. You could even go so far as to mark the primary file growth as not to autogrow. This is commonly used when you run out of space on one logical drive.
rajiv.varma (2/7/2012)
2) Any best practices for adding file groups?
Not really. Its normally for backup or physical performance purposes and its really down to your environment.
rajiv.varma (2/7/2012)
3) How does the performance get affected if I add new file groups to the database?
You will only notice a performance benefit at the file level by spreading them over mutiple spindles and this depends on your storage. SAN storage is another point to note and that depends on vendor. Filegroups are more about logically grouping files and spreading objects over multiple files. i.e your largest table could go in a filegroup of 4 files and each of these would be on a different spindle. The data itself would be fairly evenly distributed between the drives allowing for better performance. Thats a fairly generic point and not set in stone, here are many factors which will affect performance. The point is its invisible to the query running
February 7, 2012 at 10:21 am
Thanks for your inputs! It helped me a lot:-)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply