Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2004
    Posts
    114

    Unanswered: Database fragmentation

    Hello,

    How can I mesure the database fragmentation ? Cause DBCC SHOWCONTIG shows obects fragmentation only. I would like to see the whole database fragmentation.

    Thanks for help

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    Your database is 60% fragmented. What does it say? What do you do?

  3. #3
    Join Date
    Mar 2004
    Posts
    114
    I just found my answer:

    USE pubs
    DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Fragmentation may be on the file system level, which may be corrected by detaching the database, defragging the disk, reattaching the db, and doing the db level defrag which you just discovered.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The file fragmentation on the disk is invisible to SQL Server... It sees a file as a contiguous stream of 8 Kb pages, but it is quite ignorant of how those pages are actually laid out on the disk(s).

    rdjabarov brings up a good point though, in that even a table or database that is contiguous from SQL Server's perspective might be on a Windoze file that has grown many times, so the head still has to hop all over creation to retrieve data. It is a good idea to periodically detach the database and defrag the Windoze disk after the database file(s) grow.

    -PatP

  6. #6
    Join Date
    Feb 2004
    Posts
    492
    So what defragmentation tool is available that is able to defrag a database file considering it's internal structure?

    I've seen several implementations of defragtools; one would only move about the occupied sectors, making it easier for the OS the find free space. Others would do a file approach, selecting the sectors involved and move those around. I've had a defrag tool that did a file approach and moved the first sectors of a file to the beginning, working to the end. It took ages, _and_ the tool somehow mixed up sectors, rendering some files useless.

    With database files it's tricky: indexes, tables, procedures, views.. where to store them? Would a tool consider? Does it speed up performance or does it go down the drain?

    I have to admit it's been a long time since I last looked at defragmentation tools; so I don't know how advanced they are these days and how well they work. SQLServer already claims a contiguos block of data when a db is created, I think adding new parts works the same. If a growth of a db is the only change to the disk, and sqlserver claims the new block right after the db-block, what are the odds a defrag tool doesn't leave it alone and make it worse?

    I'm not an expert on defragmentation, I'm also not an expert on sqlserver, I know from experience a defrag helps when a lot of small files are scattered around the disk, but I wonder the effect on db-files.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There are two kinds of fragmentation that apply here.

    For internal fragementation, the only fix that I know of is to rebuild the index or indicies affected. DBCC REINDEX works well for this.

    For file fragementation, you can either detach the database or stop the SQL Server and use the build in defragmentation tool, or you can buy Diskeeper.

    -PatP

  8. #8
    Join Date
    Mar 2004
    Posts
    114
    But does the command

    DBCC SHRINKDATABASE (pubs,NOTRUNCATE)

    move pages in the beginning of the database file ? Can this contribute to decrease internal fragmentation ?

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    No, DBCC SHRINKDATABASE doesn't move any pages. Even if it did move them, it would change the fragmentation, but rarely decrease it.

    -PatP

  10. #10
    Join Date
    Mar 2004
    Posts
    114
    So, I don't understand the aim of this command (with NOTRUNCATE option).
    Last edited by kuzco; 10-04-04 at 10:23.

  11. #11
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    NOTRUNCATE will not release freed space back to OS.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm sorry, I didn't catch Kuzco's reply earlier. I was thinking in terms of the TRUNCATEONLY option when I wrote that the DBCC SHRINKDATABASE didn't move pages. The NOTRUNCATE option can move pages, but it doesn't always do what you'd expect it to... See BOL on DBCC SHRINKDATABASE or better yet Inside SQL Server 2000 for more detailed descriptions.

    -PatP

  13. #13
    Join Date
    Mar 2004
    Posts
    114

    Smile

    Ok, thanks for your informations

Posting Permissions

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