Post reply

DBCC DBREINDEX vs. UPDATE STATISTICS

  • November 20, 2002 at 2:54 pm

    #58687

    I am using SQL Server 7.0 and want to optimize some of my queries. I have AUTO UPDATE STATISTICS = ON for my DATABASE

    What is the difference between the commands

    DBCC DBREINDEX

    and

    UPDATE STATISTICS

    When should I use these commands?

    Thanks,

    Steve

    Steven R. Morrow


    Steven R. Morrow

  • November 20, 2002 at 3:01 pm

    #441891

    The one (dbcc reindex) actually reindexes the table, i.e physically moves the data around so that it is ordered correctly, a sort of de fragmenation.

    Update stats does just that updates the distribution statistics for the index, information on uniquness, numbers of rows, etc.

    The first is better if can afford the time.

    Simon Sabin

    Co-author of SQL Server 2000 XML Distilled

    http://www.amazon.co.uk/exec/obidos/ASIN/1904347088


    Simon Sabin
    SQL Server MVP

    http://sqlblogcasts.com/blogs/simons

  • November 20, 2002 at 6:51 pm

    #441926

    But reindex should not be used too often or you need to reevaluate your fill factor for your index. Also, even with AUTO UPDATE STATS turn on I suggest unless run sp_updatestats on large databases as it requires a percentage of change before AUOT UPDATE actually occurrs. I do this once to keep things up as stats affect index choices. The more uniqueness in an index the better the stats number should be and the higher the likelyhood it will be used in a query.

  • November 21, 2002 at 9:58 am

    #441993

    My database is about 120 GIG.

    How often would you suggest running sp_updatestats?

    Thanks for your help.

    Steven R. Morrow


    Steven R. Morrow

  • November 21, 2002 at 12:34 pm

    #442012

    How is your data modifed i.e 10% inserts, 20% updates, 5% deletes?

    How big are your tables, do you have fillfactors set?

    Have you monitored for pagesplits, high values indicate you need rebuild the indexes with a higher fillfactor. Are your indexes clusetered, is the data inserted in order i.e an identity or the current date, rather than date of birth or name.

    How wide are your tables?

    These are all things that should feed into how your indexes are defined and maintained.

    Simon Sabin

    Co-author of SQL Server 2000 XML Distilled

    http://www.amazon.co.uk/exec/obidos/ASIN/1904347088


    Simon Sabin
    SQL Server MVP

    http://sqlblogcasts.com/blogs/simons

Viewing 5 posts - 1 through 4 (of 4 total)

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