Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2005
    Posts
    91

    Unanswered: How To Defrag SQL Server 2000

    Hi everyone, I am fairly new with SQL Server and need a little bit of help in regards to boosting my SQL servers performance. I have been advised that defraging SQL Server will definitely help solve my issue however I have never performed this function before. Can someone please help guide me through the necessary steps to execute this task. Thanks in advance.

  2. #2
    Join Date
    Jan 2006
    Location
    USA
    Posts
    115
    I have been advised that defraging SQL Server will definitely help solve my issue however I have never performed this function before.
    Only one setting cannot solve your problem, you have to consider lots of things like server memory settings, disk space, indexes, query optimizing etc.

    You can use database maintenance wizard from Enterprise Manager-> Tools-> Database maintenance planner.

    You can fragment your indexes for better performance, syntax is given below.

    Code:
    DBCC INDEXDEFRAG
        ( { database_name | database_id | 0 } 
            , { table_name | table_id | 'view_name' | view_id } 
            , { index_name | index_id } 
        )    [ WITH NO_INFOMSGS ]
    Rajesh Patel

    Everybody says - mistake is the first step of success, but it's not true. The correction of the mistake is the first step of success.

  3. #3
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Quote Originally Posted by rajeshpatel
    Only one setting cannot solve your problem, you have to consider lots of things like server memory settings, disk space, indexes, query optimizing etc.

    You can use database maintenance wizard from Enterprise Manager-> Tools-> Database maintenance planner.

    You can fragment your indexes for better performance, syntax is given below.

    Code:
    DBCC INDEXDEFRAG
        ( { database_name | database_id | 0 } 
            , { table_name | table_id | 'view_name' | view_id } 
            , { index_name | index_id } 
        )    [ WITH NO_INFOMSGS ]
    I am curious; is there any value in doing a backup/restore?

    I have a daily scheduled run of Executive Software's Diskkeeper on all the servers. That keeps the files defragged on the file-system level, but of course doesn't reorder anything within the database.

    As I understand it, the concept of Defragmenetation offers an optimization of physical aspects of the disk drive (rotations, head movements) and the software activities of piecing together the fragments. It follows that having all the bits of an index in order would have a similar effect (as you described above).

    I guess in a database there's also a matter of eliminating all the holes left by prior deletes and of spreading indexes out more intelligently.

    So then; I'm displaying a complete ignorance of "database layer fragmentation". Am I missing a lot of fundamentals in my thinking?

    Question: Would it be a benifit to backup-then-restore a database?

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Backing up and restoring a database has no effect on fragmentation. Database fragmentation that is, the DBA's nerves will become highly fragmented if this sort of thing is implemented. In Oracle, you can export and import tables to remove fragmentation, which may be what you are thinking of. In SQL Server, a backup collects all pages that have data on them, and stashes them away. On a restore, the data pages are simply rewritten in place. without any moving of data around the pages.

    Database fragmentation happens mainly with deletes, sometimes with updates, and somewhat less frequently with inserts (depending on your indexes).

    Suppose you have a data page that originally has 20 entries (rows) in it. When you read in that page, you get 20 rows in memory. Suppose further that 19 of these rows are deleted. Now when you read in the same 8KB page, you only get 1 row of data. The space taken up by the rows that were there is not reclaimed automatically, and depending on insert/update activity and clustered index layout may not ever be reclaimed unless you rebuild the indexes. Rebuilding the indexes has the effect of re-arranging, or regenerating the entries packed closer together making read operations more efficient.

    Here is a link to a decent paper about it. Note, users tend to not notice the difference, until tables get above 10,000 pages or so.
    http://www.microsoft.com/technet/pro.../ss2kidbp.mspx

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by vich
    I am curious; is there any value in doing a backup/restore?
    [thud]
    yes, there is some value
    [/thud]
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jan 2006
    Location
    USA
    Posts
    115
    Check this, a single web page consist of various subjects for SQL Server Maintenance.

    http://www.microsoft.com/technet/pro...n/default.mspx
    Last edited by rajeshpatel; 11-16-06 at 01:31.
    Rajesh Patel

    Everybody says - mistake is the first step of success, but it's not true. The correction of the mistake is the first step of success.

Posting Permissions

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