Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2003
    Posts
    73

    Unanswered: Database Optimization (Increase Speed)

    Well good morning/afternoon to everyone.
    It's been a while sinse I've posted here and it seems that the site is a lot faster now. Good to see.

    Anyways, I'm working a current problem here at work with our database being quite slow. I've done some research already and will continue to do so but i wanted to get some of your opinions.

    Right now, I've run the 'DBCC SHOWCONTIG' command and it is telling the following in the first 3 system tables:

    DBCC SHOWCONTIG scanning 'sysobjects' table...
    Table: 'sysobjects' (1); index ID: 1, database ID: 6
    TABLE level scan performed.
    - Pages Scanned................................: 34
    - Extents Scanned..............................: 12
    - Extent Switches..............................: 33
    - Avg. Pages per Extent........................: 2.8
    - Scan Density [Best Count:Actual Count].......: 14.71% [5:34]
    - Logical Scan Fragmentation ..................: 41.18%
    - Extent Scan Fragmentation ...................: 83.33%
    - Avg. Bytes Free per Page.....................: 2303.6
    - Avg. Page Density (full).....................: 71.54%
    DBCC SHOWCONTIG scanning 'sysindexes' table...
    Table: 'sysindexes' (2); index ID: 1, database ID: 6
    TABLE level scan performed.
    - Pages Scanned................................: 72
    - Extents Scanned..............................: 16
    - Extent Switches..............................: 59
    - Avg. Pages per Extent........................: 4.5
    - Scan Density [Best Count:Actual Count].......: 15.00% [9:60]
    - Logical Scan Fragmentation ..................: 50.00%
    - Extent Scan Fragmentation ...................: 81.25%
    - Avg. Bytes Free per Page.....................: 4184.9
    - Avg. Page Density (full).....................: 48.30%
    DBCC SHOWCONTIG scanning 'syscolumns' table...
    Table: 'syscolumns' (3); index ID: 1, database ID: 6
    TABLE level scan performed.
    - Pages Scanned................................: 323
    - Extents Scanned..............................: 50
    - Extent Switches..............................: 299
    - Avg. Pages per Extent........................: 6.5
    - Scan Density [Best Count:Actual Count].......: 13.67% [41:300]
    - Logical Scan Fragmentation ..................: 48.61%
    - Extent Scan Fragmentation ...................: 96.00%
    - Avg. Bytes Free per Page.....................: 4527.0
    - Avg. Page Density (full).....................: 44.07%
    DBCC SHOWCONTIG scanning 'systypes' table...
    Table: 'systypes' (4); index ID: 1, database ID: 6
    TABLE level scan performed.
    - Pages Scanned................................: 1
    - Extents Scanned..............................: 1
    - Extent Switches..............................: 0
    - Avg. Pages per Extent........................: 1.0
    - Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
    - Logical Scan Fragmentation ..................: 100.00%
    - Extent Scan Fragmentation ...................: 0.00%
    - Avg. Bytes Free per Page.....................: 6712.0
    - Avg. Page Density (full).....................: 17.07%

    According to the DBCC SHOWCONTIG command documentation, there should be no fragmentation at all.

    Some questions:
    1. would system performance be severly negatively reduced with the above fragmentation (logical and extent)?
    2. can the 'DBCC INDEXDEFRAG(dbname, tablename, indexname)' command be issued against those system tables without consequences?
    3. is there some other command that can defrag the entire database without having to specify which tables?

    Also, I have also used the index tuning wizard after a profile trace but that failed with some unknown error.

    Thats it for now, please let me know if you have some info I could use to help speed up my database.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Microsoft put out a white paper about defragging. It is actually pretty good:

    http://www.microsoft.com/technet/pro.../ss2kidbp.mspx

    so, based on that paper, my answers are:

    1) Not even noticeably.
    2) Nope.
    Code:
    Server: Msg 2566, Level 14, State 1, Line 1
    DBCC DBREINDEX cannot be used on system tables.
    3) If you discount maintenance plans, no. But a google search should come up with a bunch of scripts that will reindex all of your tables for you. Just be careful about space useage, and transaction log size. As always, try it on your test system first, before...yadday...yadda...yadda.

  3. #3
    Join Date
    Oct 2003
    Posts
    73
    thx for the quick response! That article is great. I'm going through it right now. I'll let you know how it goes.

    thx again.

  4. #4
    Join Date
    Oct 2003
    Posts
    73
    Would the command 'DBCC SHRINKDATABASE' need to be run to increase performance if I were to clear out a LOT of old (unnecessary) data? Or does that not relate to overall db performance?

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    dbcc shrinkdatabase can actually cause fragmentation
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    I have not heard of a case where shrinking any of the datafiles will help performance. Potentially, it could hurt performance, if you expect your database wll be growing as most do.

  7. #7
    Join Date
    Oct 2003
    Posts
    73
    Alright, that sounds logical enough. The reason I was considering a database shrink was because I'm testing this database optimization on my local copy of msde. I quickly realized that because my database is already 2 gigs big, I cannot do anything to increase speed (such as the dbcc reindex command) because there is not enough space. It's too bad that MSDE has this limitation of 2 gigs. I'm currently looking into using a 2003 Server Install with SQL Server on it for testing.

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    For a laptop, I could see the size of the file impacting your pagefile (if you run out of room entirely), but outside of that, you are better off not shrinking.

  9. #9
    Join Date
    Oct 2003
    Posts
    73
    I ran a script here that deleted all the old product data that was no longer needed and then our sql server maintenace plan brought the logical fragmintation down to 0% on the key tables (down from 99% fragmentation).
    This brought about great increases in performance. However, what is questionable is that under the "Optimization" tab in the maintenance plan, "reorganize index and data pages" was selected, but it never seemed to make a significant difference until the old data was removed from the database. I suppose it was making a difference, but the fragmentation was not reduced as much as it is now and the fragmentation occurred again during the week.

Posting Permissions

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