April 20, 2012 at 11:54 am
What settings have you got for growth of database files. Sounds to me that they grow by too little each time. Gain control of the growth of your databases, and you will have much less problems with fragmentation.
Wow! I definitely did not know that had an impact on fragmentation. So I set it based on the volume of activity on the database? How do I determine optimal settings?
Br. Kenneth Igiri
https://kennethigiri.com
All nations come to my light, all kings to the brightness of my rising
April 20, 2012 at 11:55 am
I recommend PerfectDisk - I remember we bought it to use on the SQL Server based data warehouse because the staging environment did a lot of updates and deletes and got fragmented a lot. (this was several jobs and versions ago). The price for a standalone server was more than reasonable in the corporate scheme of things and it worked wonders. There was a plugin if you wanted to use it with SQL Server but I don't know about the current version. I'm sure their sales staff can tell you all about it. Just make sure you get the for servers version and not the home PC version.
April 20, 2012 at 11:59 am
magarity kerns (4/20/2012)
I recommend PerfectDisk - I remember we bought it to use on the SQL Server based data warehouse because the staging environment did a lot of updates and deletes and got fragmented a lot. (this was several jobs and versions ago). The price for a standalone server was more than reasonable in the corporate scheme of things and it worked wonders. There was a plugin if you wanted to use it with SQL Server but I don't know about the current version. I'm sure their sales staff can tell you all about it. Just make sure you get the for servers version and not the home PC version.
Thanks. Will check it out. In addition, whil reseaching on this exercise, I read somewhere that SANs may not need defragmentation. What is you experience in this area?
Br. Kenneth Igiri
https://kennethigiri.com
All nations come to my light, all kings to the brightness of my rising
April 20, 2012 at 12:16 pm
kennethigiri (4/20/2012)
What settings have you got for growth of database files. Sounds to me that they grow by too little each time. Gain control of the growth of your databases, and you will have much less problems with fragmentation.
Wow! I definitely did not know that had an impact on fragmentation. So I set it based on the volume of activity on the database? How do I determine optimal settings?
Each time the database file grow, you may potentially get a new fragment, as SQL Server will have to allocate more space for its file. If there is not enough space available where the file physically ends, the new space will have to be allocated elsewhere on the disk, thus creating fragmentation. So for instance, if you expect the database to grow to 40GB and can afford to do so, allocate the 40GBs in one go. If it possibly grows to lets say 60 GB, you may chose it to grow in 1GB chuncks, thus as maximum creating 21 fragments (as long as there is large enough continous free space for 1GB). If you go for this, I would definitely evaluate enabling instant file initialization as described here[/url].
April 20, 2012 at 12:20 pm
Each time the database file grow, you may potentially get a new fragment, as SQL Server will have to allocate more space for its file. If there is not enough space available where the file physically ends, the new space will have to be allocated elsewhere on the disk, thus creating fragmentation. So for instance, if you expect the database to grow to 40GB and can afford to do so, allocate the 40GBs in one go. If it possibly grows to lets say 60 GB, you may chose it to grow in 1GB chuncks, thus as maximum creating 21 fragments (as long as there is large enough continous free space for 1GB). If you go for this, I would definitely evaluate enabling instant file initialization as described here[/url].
Definitely makes sense now.
Br. Kenneth Igiri
https://kennethigiri.com
All nations come to my light, all kings to the brightness of my rising
April 20, 2012 at 12:38 pm
Remember to check both user databases and system databases. TempDB re-initializes after server restart and goes back to the initial size setting. When you do a restart on the service, TempDB goes to whatever setting you have - flushed out and brought anew. Good to have the file initialization thing in place as linked to above by Okbangas. If TempDB is not on a volume by itself and there are several restarts with the initial size set to 1 MB and small growths, you can fragment a drive quickly. MSDB is another one to size up, especially is using dbmail or backups for log shipping or other heavily logged activity.
April 20, 2012 at 12:44 pm
Curious if this instance might be running in VMware or MS virtual machine ?
April 20, 2012 at 12:52 pm
I'm just a novice, and probably shouldn't even post to this site. But, I wanted to ask what is likely a rediculous question. How did you 'extend the drive to 900GB'? Was this a partition that you that you enlarged? What if you hadn't had additional space to give it? I work supporting a software that is in the SQL format, so I know just enough to really get in trouble. I'm so glad you wrote this article poo-pooing the idea of shrinking because that would have been my first solution attempt.
April 20, 2012 at 12:56 pm
Also will dump a screen capture here just in case it is of help - off topic relates to PerfectDisk. I ran a lot of testing on PerfectDisk but we never deployed it here based on initial size settings and the assumption of new hardware to set files correctly when transferring. Physical fragmentation can often be avoided with predictions in file growth needs. If you set up a collector to check available space once a week you can get a general idea of growth - logged to a database that you can take the query into excel and give that projection to Net people or Management to determine what storage needs you will have in the future, before it becomes a problem.
Anyway, screen capture attached. Sorry for all the black marks. Not really need for security but once a pictures gets on the internet... meh. Could not do much with the SMART capabilities when testing against a testing server setup with 2 raid 0's and one raid with a previously failed drive (on purpose).
The screen capture shows the online capability that was tested, though I still recommend your own tests since I was on a physical machine with DAS and not VM with SAN.
April 20, 2012 at 1:00 pm
I also want to thank you for writing an article even I could understand. This seems to be a great site for getting information appropriate for all 'age groups'. 😀
April 20, 2012 at 1:19 pm
npcrwill8 (4/20/2012)
Curious if this instance might be running in VMware or MS virtual machine ?
It's Vmware.
Br. Kenneth Igiri
https://kennethigiri.com
All nations come to my light, all kings to the brightness of my rising
April 20, 2012 at 1:20 pm
matt.newman (4/20/2012)
Remember to check both user databases and system databases. TempDB re-initializes after server restart and goes back to the initial size setting. When you do a restart on the service, TempDB goes to whatever setting you have - flushed out and brought anew. Good to have the file initialization thing in place as linked to above by Okbangas. If TempDB is not on a volume by itself and there are several restarts with the initial size set to 1 MB and small growths, you can fragment a drive quickly. MSDB is another one to size up, especially is using dbmail or backups for log shipping or other heavily logged activity.
Wow. Very informative.
Br. Kenneth Igiri
https://kennethigiri.com
All nations come to my light, all kings to the brightness of my rising
April 20, 2012 at 1:21 pm
kdburger (4/20/2012)
I'm just a novice, and probably shouldn't even post to this site. But, I wanted to ask what is likely a rediculous question. How did you 'extend the drive to 900GB'? Was this a partition that you that you enlarged? What if you hadn't had additional space to give it? I work supporting a software that is in the SQL format, so I know just enough to really get in trouble. I'm so glad you wrote this article poo-pooing the idea of shrinking because that would have been my first solution attempt.
Teh volume is presented from SAN so as long as there is space in the SAN, it can be extended. If you use direct attached storage, you are limited.
Br. Kenneth Igiri
https://kennethigiri.com
All nations come to my light, all kings to the brightness of my rising
April 20, 2012 at 1:24 pm
matt.newman (4/20/2012)
Also will dump a screen capture here just in case it is of help - off topic relates to PerfectDisk. I ran a lot of testing on PerfectDisk but we never deployed it here based on initial size settings and the assumption of new hardware to set files correctly when transferring. Physical fragmentation can often be avoided with predictions in file growth needs. ....
Great! thanks.
Br. Kenneth Igiri
https://kennethigiri.com
All nations come to my light, all kings to the brightness of my rising
April 20, 2012 at 3:28 pm
Hi,
I would like to make use of defragmentation because it sounds like it will help, but my question at this point is, is it really as simple as running Disk Defragmenter in Windows to find out whether a SQL Server needs defragmentation? Or are third-party tools better?
In any event, event, I'm wondering if someone can provide me with a general idea of how to analyze disk fragmentation on a SQL Server and how to determine whether it is in fact causing performance problems. Even pointers to general site summaries of the process would be a great help.
Thanks for any help,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Viewing 15 posts - 16 through 30 (of 53 total)
You must be logged in to reply to this topic. Login to reply