The latest tip in SQL Server Magazine on OS-Level fragmentation. This really caught my attention. Brian Moran wrote the tip that is someone who knows a lot about how a SQL Server database works it.
One of the points Brian made really caught my attention. To quote the write-up:
Remember that SQL Server can report 0 percent fragmentation even when the on-disk files are horribly fragmented. SQL Server doesn’t know or need to know how the OS physically lays out the bits on disk; it’s the OS’s job to manage physical bits on disk. However, because SQL Server doesn’t know how the bits are laid out on disk, SQL Server has no direct way to report about file fragmentation.
This really caught my attention. You think everything is ok because the database is reporting back o percent fragmentation, when in reality the disk you are relaying on is heavily fragmented.
Anyone who has ever taken the time to defragment the hard drive on their PC knows first hand what an impact disk fragmentation can have on overall performance. A good rule of thumb to use would be 20% overall performance impact. IE: a heavily fragmented disk was causing your overall performance to degrade by 20%.
So Brian’s tip in SQL Server magazine takes you past the typical look at fragmentation using the DBCC SHOWCONTIG command. Which will looks at fragmentation within the SQL Server database to how to look at fragmentation beyond the database and within the OS. Which is also impacting your database performace.
Here is a link to Brians Article
Founder & CEO, Ntirety
My Personal Twitter Account: Michael_Corey
Ntirety Corporate Twitter Account: Ntirety