I search the Internet and did not find clear explanation if I have to try defragmenting physical database files when my DB resides on SAN. I understand that there are tools and procedures inside some SANs that deal with that, but also there are SANs which lack that features.

My question has 3 parts:

  • How to check for physical fragmentation of SQL files?
  • Is it necessary to do this if the files reside on SAN?
  • Is it necessary to create a bigger physical database files to avoid fragmentation?
  • Do you have any evidence that physical fragmentation is causing problems ? – Kin Shah Oct 23 '14 at 12:17
  • No. We use rented virtual server. The database is growing and we have some strange problems. I want to be sure that defragmentation is not an issue. Also I am generally curious. – Bogdan Bogdanov Oct 23 '14 at 13:01
  • Thats a knee jerk approach. I would suggest to look at the wait stats, locking / blocking by running sp_whoisactive or sp_askBrent. Also read on Stop Worrying About SQL Server Fragmentation – Kin Shah Oct 23 '14 at 13:21

To answer your questions in order:

  • You can check for the physical fragmentation of the disk by going to the drive, right clicking on it and selecting tools, then defragment. Or close to that, depending on which version of the OS you are using your specific steps may be different. But you will only be able to see the fragmentation on a volume level, not specific files.

  • For the most part, no, you don't need to worry about physical fragmentation of your files if you are using a SAN (unless the SAN is dedicated to this one purpose). Due to how most SANs are used, your IO is going to be random; which is fine since you should have LOTS of spindles to spread the load out.

  • Larger physical database files would prevent fragmentation, but again, you shouldn't worry about that if you are on a SAN. The more important thing to make sure of is that when the file grows, it grows at a set size (rather than percentage) and that size is sufficient to handle a good amount of growth. You would want to grow the file to what you estimate the required size will be in X days and THEN defragment the volume.

This link - Brent Ozar goes into more detail and should include some more information you can use to either prove or disprove that the SAN is the reason for your bottleneck.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.