Post reply

Table Fragmentation

  • July 20, 2006 at 8:37 am

    #153098

    Is it possible to defrag a table that does not contains any indexes? I ran a DBCC SHOWCONTIG and found quite a few tables that does not have any index but it have a very high extent fragmentation over 90%. I would like to lower the extent fragmentation on those table. I'm using SQL server 2000. Any assistance will be helpful.

    Thanks

  • July 20, 2006 at 9:12 am

    #650424

    yup easy, create a clustered index ( on the desired column of the table ) then remove the clustered index.

    I'd seriously advise about tables without integrity constraints - but that's another thread!

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • July 21, 2006 at 12:53 pm

    #650831

    I'd also look at the size of the table. Any table less than 1 Extent (8 - 8k pages) will not be defragmented anyway no matter what you do.

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • July 23, 2006 at 5:47 pm

    #650960

    Develop a weekly maintenance plan... see Books Online for how...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 24, 2006 at 1:57 am

    #650991

    But Jeff,

    Even if he creates a weekly maintenance plan it wouldn't defragment his table if there is no index on and if the table lis smaller the 8 pages (as Rudy and Colin advised earlier)



    Bye
    Gabor

  • July 24, 2006 at 9:45 am

    #651109

    Hello,

    Thanks everybody for your great input. This will definitely help me for future system maintenance. You guys are great.

    Thank you again.

  • July 24, 2006 at 4:41 pm

    #651209

    I believe that if you include "Reorganize data and index pages" in the {Optimizations} tab, fragmented tables are defragmented.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 25, 2006 at 12:48 am

    #651257

    I believe that if you include "Reorganize data and index pages" in the {Optimizations} tab, fragmented tables are defragmented.

    No, they are not. This is mainly due to the internal way of working of SQLServer.

    "Reorganize data and index pages" mainly means basically a DBCC dbreindex and/or dbcc indexdefrag. You cannot reorganize datapages itself unless you have a clustered index on. And in this case you are reorganizing (defragmenting) the clustered index.



    Bye
    Gabor

  • July 25, 2006 at 1:35 am

    #651260

    absolutely, it's an oft overlooked part of database optimisation. Mind you the actual fragmentation is only painful for physical i/o, usually read ahead / scans. Once your data is in cache the impact of fragmentation is far less. One of the reasons I like to get lots of ram on a sql server - helps offset the bad stuff < grin >

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • July 25, 2006 at 6:46 am

    #651312

    Gabor, I admittedly need to do a little more research but considering that the "Reorganize data and index pages" option actually moves data from the end of the physical file to the beginning of the file, I'm thinking that the data get's defragmented index or not.  I'll see what I can find, though (unless you have a URL from a Microsoft website I can look at).

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 25, 2006 at 7:39 am

    #651330

    Jeff,

    I think you are mixing up 2 things reindexing (removing the fragmentation) and file or db shrinking (remiving the unused space from the and of the files

    According to the BOL (have a look on the red lines), but if there is no clustered index on a table then the data pages itself will not be reorganised.

    Removing spaces from the and of the data file will not compact the data pages in the middle of that file. ie if you have a heavilly fragmented table with 50-80% empty pages the dbcc shrinkfile or shrinkdb would not be a big help.:

    Update Data Optimization Information

    Use the Update Data Optimization Information screen to view or specify the following options.

    Options

    Reorganize data and index pages

    Cause the indexes on the tables in the database to be dropped and re-created with a new FILLFACTOR. The FILLFACTOR determines how much empty space to leave on each page in the index, thereby reserving a percentage of free space on each data page of the index to accommodate future expansion. As data is added to the table, the free space fills because the FILLFACTOR is not maintained. Reorganizing data and index pages can reestablish the free space.

    Reorganize pages with the original amount of free space

    Cause the indexes on the tables in the database to be dropped and re-created with the original FILLFACTOR that was specified when the indexes were created.

    Change free space per page percentage to

    Cause the indexes on the tables in the database to be dropped and re-created with a new automatically calculated FILLFACTOR, thereby reserving the specified amount of free space on the index pages. The higher the percentage, the more free space is reserved on the index pages and the larger the index grows. Valid values are from 0 through 100.

    Update statistics used by query optimizer.

    Cause the distribution statistics of each index created on user tables in the database to be resampled. The distribution statistics are used by Microsoft® SQL Server™ to optimize navigation through tables during the processing of Transact-SQL statements. To build the distribution statistics automatically, SQL Server periodically samples a percentage of the data in the corresponding table for each index. This percentage is based on the number of rows in the table and the frequency of data modification. Use this option to perform an additional sampling using the specified percentage of data in the tables.

    Sample % of the database

    Generate distribution statistics by sampling the percentage of data in the tables. The higher the percentage, the more accurate the statistics, but the longer the sampling takes. If the specified value does not generate a sufficient sample, SQL Server determines an adequate sample size automatically. Valid values range from 1 through 100.

    Remove unused space from database files

    Remove any unused space from the database, thereby allowing the size of the data files to be reduced.

    When it grows beyond

    Remove unused space from the database only if the database exceeds the specified size, in megabytes (MB).

    Amount of free space to remain after shrink

    Determine the amount of unused space to remain in the database after the database is shrunk (the larger the percentage, the less the database can shrink). The value is based on the percentage of the actual data in the database. For example, a 100 MB database containing 60 MB of data and 40 MB of free space, with a free space percentage of 50 percent, would result in 60 MB of data and 30 MB of free space (because 50 percent of 60 MB is 30 MB). Only excess space in the database is eliminated. Valid values are from 0 through 100.

    Schedule

    Set the frequency that the data optimization tasks (scheduled using SQL Server Agent) are executed. The default is every Sunday at 1:00 AM.

    Change

    Change the default schedule.



    Bye
    Gabor

  • July 25, 2006 at 5:33 pm

    #651515

    Thanks for the feedback, Gabor.  Yep, I found the same thing and more... I'm not sure what I was thinking of before.  Must'a been some bad coffee of something...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 26, 2006 at 3:22 am

    #651549

    that's why I'm just drinking tee.... (and some good red wine)



    Bye
    Gabor

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply