Post reply

Physical Fragmentation and Contig.

  • June 25, 2019 at 11:58 am

    #3657311

    During the process of migrating an old database to a new server, I noticed that the database's Autogrow settings were still set to grow in 1MB intervals.

    The database is currently 23GB so I'm concerned about the level of fragmentation of the physical file.

    I do the normal maintenance operations to defrag indexes on a weekly basis but I've never attempted to defrag the physical database file before.

    I understand that I should use Contig.EXE to defragment the file and that the database must be offline to do so.  I also read than I can do an online analysis to check the fragmentation levels first using the "0--a" switch

    So, my questions are,

    1. How much impact does running Contig.exe -a to analyse a MDF file have on the database performance?
    2. Is there a way to tell how long the actual defrag operation will take or will I need to copy the database and test on that copy first?
    3. Do I even need to worry about the fragmentation caused by autogrowing the DB too frequently?

     

  • June 25, 2019 at 3:27 pm

    #3657415

    Don't believe that tool is necessary anymore, and it was only ever minimally necessary when you mixed very large files on a volume with a large amount of small files of large combined size. Was good for things like page files or exchange databases kept on the same volume as the ESE logs or other files in server 2003. SQL significantly influences physical writes to disk and NTFS does a better job of avoiding extent fragmentation than it did 15 years ago.

  • June 25, 2019 at 6:24 pm

    #3657492

    If you performed a backup/restore - the new file that was created during that process will be minimally fragmented (file system).  This does nothing for internal index fragmentation (of course).

    You can check the fragmentation level of the file to confirm...which won't have a significant impact on the system.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • June 25, 2019 at 10:30 pm

    #3657572

    I’m still on 2008 R2 and what I wrote below may no longer apply to later versions, I don't know, but here is what happened to me a few months ago:

    D:\MyDB.mdf: Operating system error 665(The requested operation could not be completed due to a file system limitation) encountered.

    You can read the article below for details but in short, there is some sort of limitation in the design of NTFS. Every time the data file is grown some values are added to NTFS attribute table if newly acquired space is not adjacent to the prior. The fragmentation was huge - over 2 million fragments in that failed MDF file and 5 to 6 digits number in others.

    https://blogs.msdn.microsoft.com/psssql/2015/06/10/operating-system-error-665-file-system-limitation-not-just-for-dbcc-anymore/

    So, to answer your questions…

    1. It took about 2 sec to analyze 150 MDF files with Contig utility, so no impact. Do it with wildcard. You can also redirect output to a text file for later review.
    2.  It took me about 6 to 7 hours to defragment the disk that was hosting those 150 files using standard Windows Defrag utility. (1.5 TB total; about 1 TB in MDFs and 200GB free). I had to allocate special maintenance window on Sunday and shut down SQL Server. Please note that Contig utility didn’t work for me in defrag mode, it would run relatively fast and return successfully but before and after fragmentation counts were the same.
    3. I think you should check fragmentation occasionally. If fragments count within 2-3 digits, I wouldn’t worry about it.

    --Vadim R.

  • June 26, 2019 at 2:51 pm

    #3657773

    That's two different misconfigurations.

     

    The first is setting the allocation size to something other than 64k. I always format the drives to use 64k allocations when I add a new volume, but I typically wont advocate moving databases around to get them onto a 64k drive anymore if it got configured for 4k

    The second misconfiguration is either storing another folder on the volume that is write intensive, that contains something other than mdf/ndf files, or having many databases on a volume that are constantly autogrowing.

    To create a problem, it requires that both of these misconfigurations be present. Either of them are a non-issue without the other.

    The recommendation in the article to use ReFS is an extremely bad one. Unless you are 100% sure of why you need to use ReFS and understand the logic behind using it, you should only ever use NTFS. Using ReFS would mask those two misconfigurations, but doesn't solve them.

    Reformatting the drive to a 64k allocation unit size may not be practical on a live system, but correctly sizing your databases is something that alone can solve this type of fragmentation and there are multiple other reasons to do it

  • June 27, 2019 at 4:30 pm

    #3658196

    Thanks BrownCoat42, and yes we have both of those: 4k unit size and a folder with a lot of files. Also, this server is on receiving end of transactional replication: almost all databases are subscribers. This is where we generate most of the output in a variety of formats, so yes, it's write intensive.  I'll see what I can do to move those files to a different drive, separate from MDFs. On the other hand we're going to move to a higher version, so maybe just contain the situation until it happens. Thanks again for your insights.

     

    --Vadim R.

  • June 29, 2019 at 6:47 pm

    #3658657

    You may get more bang for your buck by moving the databases to a different volume instead of the files, formatted to use the 64k allocation unit size. You just perfectly hit the doughnut hole for this problem to come into play, with 4k allocation units, write intensive databases and a highly active files directory on one volume. while you normally wont see any performance difference in modern systems with modern versions of NTFS when you go between 4k allocation and 64k allocation, your case may actually be extreme enough that you actually would get a bit of a performance boost, in addition to permanently solving the extent fragmentation problem. If you have the spare fast disk for it anyways.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply