Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2002
    Posts
    21

    Unanswered: Fragmentation of SQL Data files

    Hi,

    How do you defragement SQL database data files? Running the DBCC DBREINDEX , DBCC INDEXDEFRAG, does that minimize fragmentations of the tables and the indexes files or is it just the index files?
    Thanks in advance

    a

  2. #2
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    the sql server database files are subject to two levels of fragmentation.
    internal and external
    dbcc reindex and indexdefrag are for the internal fragmentation that occurs in the indexes on tables

    external fragmentation is the defrag that you are used to in the os when a file is scattered around the disk.
    if the db is on it's own disk structure then it is not likely that it is very externally fragmented due to dedicated disk space to one file.

    however the internal frag is a performance hog if it gets excessive
    you may want to consider recreating your indexes with an appropriate fill factor to alleviate fragmentation.
    depending on your level of transactions i usually build out all of my indexes with a fill factor of 90 and watch frag from dbcc showcontig.

    then raise or lower the fill factor based on known parameters instead of guesses.

  3. #3
    Join Date
    Aug 2002
    Posts
    21
    Thank you!!

  4. #4
    Join Date
    Aug 2002
    Posts
    21
    Originally posted by Ruprect


    Hi,

    What do you mean by it's own disk structure.


    A

    ******************************
    the sql server database files are subject to two levels of fragmentation.
    internal and external
    dbcc reindex and indexdefrag are for the internal fragmentation that occurs in the indexes on tables

    external fragmentation is the defrag that you are used to in the os when a file is scattered around the disk.
    if the db is on it's own disk structure then it is not likely that it is very externally fragmented due to dedicated disk space to one file.

    however the internal frag is a performance hog if it gets excessive
    you may want to consider recreating your indexes with an appropriate fill factor to alleviate fragmentation.
    depending on your level of transactions i usually build out all of my indexes with a fill factor of 90 and watch frag from dbcc showcontig.

    then raise or lower the fill factor based on known parameters instead of guesses.

  5. #5
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    i meant a dedicated disk array of some type
    raid 0 , 1 , 5, 01, 10, 50 etc
    as opposed to sharing the disks that the db resides with other disk read items (files transaction logs etc)

  6. #6
    Join Date
    Aug 2002
    Posts
    21
    Thanks.

    A

  7. #7
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    You can use DISK DEFRAGMENT tool in Windows to defrag OS files, but to involve SQL data files you must stop the SQL services to consider the files.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •