Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2003
    Location
    Melbourne, Australia
    Posts
    53

    Unanswered: SQL Server Re-org

    Can anyone suggest me any document on the process of doing a reorg of a SQL Server database

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Are you talking about defragging/rebuilding indexes?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Apr 2003
    Location
    Melbourne, Australia
    Posts
    53
    "reorg" is used to describe dropping the database, unloading an entire table data to a flat file or by taking a backup and re-creating the DB, re-loading the table having first cleared (by a delete/create of the table) the contents of the table.


    net effect of this is to consolidate out any gaps in the 'storage' of the data...ie page splits or records which got deleted and the storage never re-used....to one end of the tablespace, from where it can be released back to the operating system easily.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    What you just described is not "reorg", but rather a "I-have-so-much-time-on-my-hands-and-so-little-understanding-of-how-SQL-server-works-that-I-am-gonna-create-a-mess-and-see-what-happens" type of thing. If you want to really reorg you need to analyze if such a thing is really needed. You need to know exactly how much disk space is needed for each table and how much is actually used. You need to calculate the data storage accounting for all data types in the table, identifying what's the record size and how many records fit on a page (remember, it's 8060 usable space per page, the rest is overhead.) Then you need to see how many pages are actually used by the table. Also remember that even though page splits are possible, they usually affect indexes, both clustered and nonclustered. Data pages are allocated not on a page-by-page level, but rather by extent (8x8K). If you suspect that the fragmentation is on the OS level, you need to stop the service and defrag the disk itself.

    Usually what you describe is required for a database that did not experience any db maintenance work done against it for quite a while. If this is the case, my advice is not to start with the mess that you propose but rather understand you database and get to know it a little better (dim the lights, pour some champaign, see maybe you'll get lucky tonight )
    "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
    Quote Originally Posted by rupee2003
    Can anyone suggest me any document on the process of doing a reorg of a SQL Server database
    A reorg is a very "Oracle-like" thing to do. It isn't well documented in MS-SQL because it is never done on a database that is well maintained (which is what rdjabarov was hinting at).

    I've dealt with hundreds of SQL Servers, and thousands of databases. I've seen three databases that were candidates for a reorg, all of which were fixed using less drastic measures. While it is possible that a database could be such a mess that a reorg was needed, I've never seen one that had gone that bad.

    -PatP

Posting Permissions

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