April 3, 2013 at 7:09 am
I have a database that was set with an initial size of close to 10 gigs. I am down to 2% free space left with autogrowth settings of 50 Megs and unrestricted. The database grows approximately 50 megs per day. I know that a database can become fragmented the more the autogrowth occurs so I'm wondering if it's better to just change the Intial (MB) size to something larger (say 11 gigs).
I'm really just trying to figure out the difference between changing the Initial (MB) setting for the database file size vs allowing autogrowth to happen. Does changing the Initial (MB) move the entire database file to a contigous physical space if it's been fragmented with autogrowth already? Does autogrowth add a contiguous space if it's available? How can I see if my database file is physically fragmented?
Thank you in advance.
April 3, 2013 at 7:19 am
rob.phillips 38602 (4/3/2013)
Does changing the Initial (MB) move the entire database file to a contigous physical space if it's been fragmented with autogrowth already? Does autogrowth add a contiguous space if it's available? How can I see if my database file is physically fragmented?Thank you in advance.
When SQL Server needs more space it will simply request it from the OS. It's really down to the OS what space to give on the disk. File fragmentation shouldn't cause you too much problems although I have seen it affect performance of a web application about 1 year ago.
You can use contig.exe to see the fragmentation of a database file and you can also use it to defragment individual files if you need.
---------------------------------------------------------
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
April 3, 2013 at 7:43 am
Thank you for the reply.
We don't have a heavy load on the server, and there's plenty of free disk space, so I don't think an autogrow event once or twice a day is causing any issues, but in terms of best practice I wonder if it would benefit from moving to 512 MB autogrowth vs 50 MB. Also, what's the difference between chaging the Initial (MB) file size versus allowing autogrowth to happen?
Thanks again.
April 3, 2013 at 7:44 am
Rather grow the file manually, not because of file fragmentation (which may or may not be of any concern depending on the IO subsystem and a bunch or other factors) but because you pick the time to do it.
If autogrow kicks in during peak usage time, the users may well notice a degradation in performance. If you grow the file manually at a quiet time, they won't.
As for how much, how fast does the DB grow and how often do you want to be growing the file manually? Bearing in mind that free space in a data file does not degrade performance.
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
April 3, 2013 at 7:46 am
rob.phillips 38602 (4/3/2013)
Also, what's the difference between chaging the Initial (MB) file size versus allowing autogrowth to happen?
One is done manually by you, one is done automatically by SQL. That's about it.
Autogrow once or twice a day is way too often, if I see that in a client's database it often hints that there's no capacity planning or management happening.
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
April 3, 2013 at 7:47 am
GilaMonster (4/3/2013)
If autogrow kicks in during peak usage time, the users may well notice a degradation in performance. If you grow the file manually at a quiet time, they won't.
Even with Instant File Initialisation?
---------------------------------------------------------
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
April 3, 2013 at 7:48 am
Abu Dina (4/3/2013)
GilaMonster (4/3/2013)
If autogrow kicks in during peak usage time, the users may well notice a degradation in performance. If you grow the file manually at a quiet time, they won't.Even with Instant File Initialisation?
Depends which file is growing, and there's nothing in the OP to suggest that instant init is on.
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
April 3, 2013 at 7:53 am
Can you elaborate a bit on how you "manually" grow the database file? After the autogrow hits the Inital (MB) size of the database file reflects the growth. Does that suggest the file is contiguous?
April 3, 2013 at 7:57 am
GilaMonster (4/3/2013)
Abu Dina (4/3/2013)
GilaMonster (4/3/2013)
If autogrow kicks in during peak usage time, the users may well notice a degradation in performance. If you grow the file manually at a quiet time, they won't.Even with Instant File Initialisation?
Depends which file is growing, and there's nothing in the OP to suggest that instant init is on.
LOL... I'm just picking your brains here. So if we assume 1) IFI is on AND 2) it's the data files then in theory autogrowth shouldn't be an issue? It's just that I have some DBs with autogrowth and I want to be able to justofy it 🙂
---------------------------------------------------------
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
April 3, 2013 at 8:00 am
rob.phillips 38602 (4/3/2013)
Can you elaborate a bit on how you "manually" grow the database file?
The way you mentioned in your initial post.
Does that suggest the file is contiguous?
Not necessarily. File layout is up to the OS, how the file shows up in explorer or any other interface does not take how and where the file is actually stored into account.
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
April 3, 2013 at 8:30 am
So the only difference between manually growing the file and letting autogrowth settings happen is that I can control it on my own timeframe (non-peak hours) to limit the performance hit. That's understandable in large scale OLTP systems, but there are plenty of small installs out there. Either manual or autogrow will both result in some fragmentation over time.
April 3, 2013 at 11:28 am
I would like to thank you for your help. I have messed around with these settings in a test database on a test server and was wondering if you could help me understand the following:
1) When growing the Initial (MB) size for the database an extra 1 gigs, or 5 gigs, or 10 gigs it happened almost instantly. Two to three seconds tops.
2) When growing the Initial (MB) size for the database log file an extra 1 gig, or 5 gigs, or 10 gigs it took quite a bit more time. 2-3 minutes, perhaps.
Would would cause the mdf file to expand much faster than the ldf file? Instant file initialization is not turned on for the sql service account since everything was installed as the default. I'm just trying to get a better grasp on how SQL manages these tasks before I update the production database to prevent autogrowth in the future.
Thanks again.
April 3, 2013 at 12:40 pm
Probably instant file initialisation.
How do you know it's not on? Checked the local security policy setting? If SQL's running as admin, could be that someone gave the administrators group that permission, you wouldn't have noticed.
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
April 3, 2013 at 1:00 pm
The test box is a Windows 7 Home machine (insert chuckle here). I don't believe there's even the ability to set local security policy. Even if so, and instant file initialization were turned on shouldn't it be just as quick for the log file as the database file?
April 3, 2013 at 1:11 pm
Nope, IFI only affects the MDF / NDF, not the Transaction log.
A quote from Brad McGehee[/url]:
Instant file initialization only affects MDF and NDF files, not LDF files. In other words, transaction log files can’t take advantage of instant file initialization. This is because log files are circular in nature and must be zeroed out, as random data in transaction log pages can be problematic.
Read more: http://www.bradmcgehee.com/2010/07/instant-file-initialization-speeds-sql-server/#ixzz2PQfFwNcg
Under Creative Commons License: Attribution
Jason
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply