Yesterday, someone asked a question on SQLServerCentral.com that caught my attention: “What would be the best way for defragging a disk on which database files are also stored?” This poster was concerned that a defrag of the disk would cause fragmentation of the data pages.
This is a very good question about a situation that is often overlooked by DBAs. Most DBAs know about index fragmentation and hard drive fragmentation, but few of us actually look at the two as being inter-related. Fewer DBAs even bother with disk fragmentation. It’s easy to assume that the server admin will deal with that. But the question remains, how does one defrag a SQL Server disk?
For those who don’t know, it’s important to remember the difference between logical and physical fragmentation. Logical fragmentation involves the data pages / extents and the indexes. Physical fragmentation involves the sectors of a hard drive. Database files can be spread across different, nonadjacent sectors without causing logical fragmentation and data pages can be fragmented while still existing on contiguous sectors of the disk drive. How is this possible?
Without getting into a deep-dive explanation of how the engine works, the best description is that pages and extents are virtual constructs that are created when a database is created or a database grows. They are a container for data, but not a physical one. When inserts, deletes, and updates are done, then engine creates new pages, sometimes breaks old pages, and gaps appear between the pages. This causes logical (or virtual) fragmentation within individual tables. The best way to search for this logical fragmentation is to use the new DMV sys.dm_db_index_physical_stats (DBCC SHOWCONTIG is a deprecated feature).
But pages are tiny creatures, and it is entirely possible to have a complete table on one sector, or several contiguous sectors, of a disk drive. So the index may be logically whole, regardless of how fragmented the disk drive is. Even if the pages reside on one sector block, they can still be logically fragmented but not physically fragmented. One state does not necessarily lead to the other. Though with VLDBs and fragmented disks, it’s more likely that physical fragmentation is an indicator of logical fragmentation.
So, do you defrag your disk drive? And if you do, how does it affect page fragmentation?
Answer 1: It depends. Answer 2: It doesn’t. Pages are logical creatures, not physical. The data file itself would be shoved back on to contiguous sectors–though I have my doubts a disk defrag can do that if the database is online given that data files can’t be copied or backed up while online and still be readable–but the pages themselves are unaffected by a disk defrag. But should you defrag your disk?
It depends. What type of disk do you have?
Before you defragment indexes or disks, I do recommend some reading. There are a lot of interesting articles out there about the difference between logical and physical disks that argue if your disk is RAID or SAN, you should absolutely not defrag. Here’s the one I found most intriguing: http://www.las-solanas.com/storage_virtualization/san_volume_defragmentation.php.
Here’s a good article on database fragmentation:http://www.sql-server-performance.com/articles/per/detect_fragmentation_sql2000_sql2005_p1.aspx
And another article on physical fragmentation from MSSQLTips: http://www.mssqltips.com/tip.asp?tip=1481
I don’t post a lot of technical articles, so I’d appreciate any comments or constructive reviews you have about this blog. And links to other blogs or articles on the subject are also welcome.
