SQL Server Performance

Fragmentation of DiskDrives on SQL Server

Discussion in 'SQL Server 2005 Performance Tuning for Hardware' started by bhatrai, Feb 6, 2008.

  1. bhatrai New Member

    SQL Server is running with data and logs on Raid1+0 Disk Drive.I analyzed the harddisk for fragmentation and I see file fragmentation 95%, Total Fragmentation 47%. Running update on a table, after joining tables with over 8 million records shows Average Disk Queue length is over 100. Should I use disk defragmenter Windows 2003 tool to defragment the drive. What is long term and short term solution?
    I have these findings/assumptions: Its not out of RAM because buffer hit cache ratio is 99+%. It is not utilizing lot of CPU because %Processor time is very low. there is no old statistics and internal fragmentation on indexes: because DBCC DBREINDEX builds the index and updates the statistics and I recently ran DBCC DBREINDEX with 90% fill factor.
    The first bullet point after 3rd paragraph also seems to advice that disk deframentation is required before index defragmentation. http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
    wanted some expert opinions before I perform this operation on production server.
  2. gfalk New Member

    This isn't an exhaustive list by any measure, but yes, defrag first before rebuilding the indexes. The second operation is moot if the datafiles the indexes are stored in are scattered all over the disk anyway.
    Long term if this fixes your problem, either schedule defrag to run occasionally, or find a third party program such as Diskeeper to take care of the problem automagically.
    Additionally it could be something simple such as a goofy WHERE clause on your update, or bad indexing on the table (swabo for the WHERE clause or just too many triggering cascaded updates of the associated indexes).
    If all else fails, you're stuck digging into the physical design of the database, which is always a fun bit, especially if your volumes are on a SAN you don't directly manage ;).

  3. satya Moderator

  4. jmoss111 New Member

    You might want to look at contig.exe by Mark Russinovich. It makes a file occupy one contiguous space on the drive.
  5. bertcord New Member

    fragmentation does not matter if your SQLServer is performaing random IO. Think about. If your application is performing random 8KB reads who cares if the database is fragmented.
    Even table scans are most tiems not sequentail since a databse server is a mulit user system. If this is a dedicated machien for one process then fragmentation might be something to look at.
    Bert

Share This Page