April 26, 2012 at 2:38 pm
okbangas (4/20/2012)
kennethigiri (4/20/2012)
I know one can adjust the fill factor to deal with internal fragmentation, what can one do about external fragmentation?Defragment or even rebuild the index to reduce external fragmentation.
kennethigiri (4/20/2012)
In addition, we use a shared storage area (Compellant) which is supposed to be self tuning. Do we need to re-align? How can this be done in an already existing setup?The SAN may be self-tuning, it will not affect offset and allocation unit issues however. Now, there are more factors to take into consideration as well, most important is the block (aka stripe) size of your SAN. For large blocks, as for instance 4MB, the performance issues related to partition offset are barely noticeable, but for a small block size as for instance 128kB is way worse. But to the point, what can you do about it? To adjust the allocation unit size you'll have to reformat the partition, to adjust partition alignment you'll have to delete and recreate the partition. Thus, My general recommendation in a SAN environment would be to present a new LUN to the server, partition and format it correctly, then move the data files. Yes, it will include downtime, but at least you have to move the data only once. By the way: Partitions CREATED by Windows Server 2008 and later has correct partition alignment (1MB), whereas Windows Server 2003 and earlier creates them with 31.5kB alignment, which is not optimal.
Possibly a dumb question but here goes anway..
Does the fragmentation on the drive that your database’s data file is hosted on go with the database as it is backed up and restored elsewhere? For example lets say on SERVERA I have a 300GB database and the data file is heavily fragmented (thanks to several auto-grow occurrences over time) and so now the total of the space on the drive is not one block but is fragmented. If I were to back it up and then restore to a new drive does the fragmentation it had before move with the database in its backup?
Kindest Regards,
Just say No to Facebook!April 26, 2012 at 2:50 pm
Does the fragmentation on the drive that your database’s data file is hosted on go with the database as it is backed up and restored elsewhere?
Rather simplisticly:
Assuming you are doing a file level backup then no it doesn't - how fragmented the file will be when it is copied/restored to another server will depend on the state of the file system there - if that server's disk is pretty full and the free space itself is fragmented (i.e. there are files all over the disk with gaps in between) and none of the gaps is big enough for the db file you are restoring then it will be fragmented on restore. If the disk is pretty clear it should restore to a single fragment.
If you are doing some form of disk (or block) level backup them it will because the copy is an image of the source so takes with it the layout of files on the disk.
If you are talking about a SAN it gets (potentially) a whole lot hazier as the SAN spreads it's data over loads of disks and depending on SAN make/model/feature set may do all sorts of things like auto optimising most used data to fastest devices etc. etc.
James Horsley
Workflow Consulting Limited
April 26, 2012 at 3:47 pm
James Horsley (4/26/2012)
Does the fragmentation on the drive that your database’s data file is hosted on go with the database as it is backed up and restored elsewhere?
Rather simplisticly:
Assuming you are doing a file level backup then no it doesn't - how fragmented the file will be when it is copied/restored to another server will depend on the state of the file system there - if that server's disk is pretty full and the free space itself is fragmented (i.e. there are files all over the disk with gaps in between) and none of the gaps is big enough for the db file you are restoring then it will be fragmented on restore. If the disk is pretty clear it should restore to a single fragment.
If you are doing some form of disk (or block) level backup them it will because the copy is an image of the source so takes with it the layout of files on the disk.
If you are talking about a SAN it gets (potentially) a whole lot hazier as the SAN spreads it's data over loads of disks and depending on SAN make/model/feature set may do all sorts of things like auto optimising most used data to fastest devices etc. etc.
The backup we use is Microsofts DPM (Data Protection Manager) and I think it is doing the backups (at least the express full backups) at a bit/byte level and not just backing up the file. If thats true then based on what you;ve said restoring the DB to a freshly defragged drive (not a SANS) should resulte in a defraged DB.
Kindest Regards,
Just say No to Facebook!April 27, 2012 at 7:30 am
YSLGuru (4/26/2012)
James Horsley (4/26/2012)
Does the fragmentation on the drive that your database’s data file is hosted on go with the database as it is backed up and restored elsewhere?
Rather simplisticly:
Assuming you are doing a file level backup then no it doesn't - how fragmented the file will be when it is copied/restored to another server will depend on the state of the file system there - if that server's disk is pretty full and the free space itself is fragmented (i.e. there are files all over the disk with gaps in between) and none of the gaps is big enough for the db file you are restoring then it will be fragmented on restore. If the disk is pretty clear it should restore to a single fragment.
If you are doing some form of disk (or block) level backup them it will because the copy is an image of the source so takes with it the layout of files on the disk.
If you are talking about a SAN it gets (potentially) a whole lot hazier as the SAN spreads it's data over loads of disks and depending on SAN make/model/feature set may do all sorts of things like auto optimising most used data to fastest devices etc. etc.
The backup we use is Microsofts DPM (Data Protection Manager) and I think it is doing the backups (at least the express full backups) at a bit/byte level and not just backing up the file. If thats true then based on what you;ve said restoring the DB to a freshly defragged drive (not a SANS) should resulte in a defraged DB.
It's result in defragged DB file from the OS standpoint. SQL Server has its own internal fragmentation that would still copy over. Lots of folks tend to forget the subtle, but important, difference between the two.
=============================================================
/* Backups are worthless, Restores are priceless */
Get your learn on at SQL University!
Follow me on Twitter | Connect on LinkedIn
My blog: http://sqlchicken.com
My book: Pro Server 2008 Policy-Based Management
April 27, 2012 at 10:12 am
SO how best to get rid of the SQL fragmentation?
Kindest Regards,
Just say No to Facebook!April 27, 2012 at 10:38 am
YSLGuru (4/27/2012)
SO how best to get rid of the SQL fragmentation?
Reindex all the clustered indexes on all tables. But make sure your db is big enough to allow this because it uses lots of space doing this as the clustered index is the order of the data so you are basically moving ALL the data around. Can take a long time too depending of course on size of DB and performance of system. Paradox is if this makes your DB grow you may get disk fragmentation again!
James Horsley
Workflow Consulting Limited
April 27, 2012 at 10:59 am
James Horsley (4/27/2012)
YSLGuru (4/27/2012)
SO how best to get rid of the SQL fragmentation?Reindex all the clustered indexes on all tables. But make sure your db is big enough to allow this because it uses lots of space doing this as the clustered index is the order of the data so you are basically moving ALL the data around. Can take a long time too depending of course on size of DB and performance of system. Paradox is if this makes your DB grow you may get disk fragmentation again!
The key is pre-sizing your database files so they don't HAVE to autogrow. Remember autogrow is only there as a fall back in case your capacity planning wasn't correct. Each time your data/log files need to grow you create OS-level fragmentation. Internally (to data file), he's right about clustered indexes. Paul Randal covered this in his post on why you shouldn't shrink your databases. If you have auto-shrink turned on your databases for the love of all that's holy please go shut that off right now!
Also to help with fragmentation on the transaction log side of things, look no further than Kimberly Tripps post on improving transaction log throughput. Getting rid of those VLFs and properly sizing your transaction log will help you out immensely in the long run.
=============================================================
/* Backups are worthless, Restores are priceless */
Get your learn on at SQL University!
Follow me on Twitter | Connect on LinkedIn
My blog: http://sqlchicken.com
My book: Pro Server 2008 Policy-Based Management
January 25, 2015 at 11:26 am
... Thus, My general recommendation in a SAN environment would be to present a new LUN to the server, partition and format it correctly, then move the data files. Yes, it will include downtime, but at least you have to move the data only once. By the way: Partitions CREATED by Windows Server 2008 and later has correct partition alignment (1MB), whereas Windows Server 2003 and earlier creates them with 31.5kB alignment, which is not optimal.
We eventually did this late last year... 😀
Br. Kenneth Igiri
https://kennethigiri.com
All nations come to my light, all kings to the brightness of my rising
January 25, 2015 at 11:29 am
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.
I am sure you know the answer by now but just in case. The server is a VM so the Storage guys had to make sure the disks we assigned to the VM then in the settings of the VM the disk was resized then from Disk management on Windows you can see the new size. To extend it on 2008 you can just right-click and extend but on Windows 2003, you have to use Diskpart.exe. select the volume and the just type "extend volume"
Br. Kenneth Igiri
https://kennethigiri.com
All nations come to my light, all kings to the brightness of my rising
Viewing 9 posts - 46 through 53 (of 53 total)
You must be logged in to reply to this topic. Login to reply