January 9, 2020 at 5:37 pm
I understand file level fragmentation, it mainly occurs when 10% autogrowth is enabled. We can solve this type of fragmentation by backing up and restoring that targeted database.
When it comes to volume level fragmentation or disk level fragmentation, I would like to know:
January 10, 2020 at 2:19 pm
This looks like a crosspost over to Stack exchange for those curious:
But I am going to provide an answer as well to the best of my knowledge. Starting with definitions:
File level fragmentation is fragmentation inside of a file such as index fragmentation
Volume level fragmentation is fragmentation of the files on the volume such as readme.txt
File level fragmentation in terms of a SQL Server database occurs when the object inside the database grows and no longer fits in the page it is in and now that page has a section of it that is empty in the middle. Hence fragmentation. Having the database grow by 10% shouldn't cause any additional fragmentation inside the database, but may cause volume level fragmentation.
Volume level fragmentation is the same concept, except at the disk volume level. Your file on disk grows and needs another block but the next physical block on disk has data in it already (may or may not be full), so it needs to go to a different block on disk. disk fragmentation may occur with any file growth or new file creation as long as the new file is larger than one block. That should answer question 1. Your next question is likely "how big is a block?" and that is "it depends". It depends on how you configured the disk when you formatted it as that is a configurable option while formatting a disk.
For question 2, you differentiate based on the tool you use. file level fragmentation is detected and handled within the tool that manages that file. Volume level fragmentation is detected and managed at the OS level.
For question 3, anyone with a Hard Disk Drive in their local machine, not an SSD, should have done a defrag at some point. If I remember right, Windows 98 SE had a default scheduled task upon install of the OS to defrag the disk monthly. With SSD's or SAN's you shouldn't do a defrag of the volume. SSD's have no benefit and the block order requested by the OS when defragging may not make sense to the SSD which has its own logic for data management. But that is a bit longer of a discussion than what should be in a forum post. SAN's shouldn't be defragged as the OS doesn't know the physical structure of the disk so defragging it MAY result in worse performance. AND, depending on the SAN, the exact disks you have today may not be the same as the ones you have tomorrow.
A good read on disk (technically volume) level fragmentation is here. I say technically volume level fragmentation rather than disk because a disk without a volume on it is unusable AND a disk can have multiple volumes on it, although it is often not recommended to do this without understanding the impacts.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply