Create new Statistics

  • Hi all,

    I am working on the performance and query that I came across, and have added a new index which is making the query perform much better.

    Out of interest I ran the DATABASE ENGINE TUNING WIZARD against my query.

    Alot of the recomendations were to create new statistics!?!?!

    a few questions.

    1 - Exactly what are statistics?

    2 - Is it not something I can run on the whole db?

    3 - How often should you update stats on a server/db/table?

    Thanks

    Chris

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life
  • Stats give the optimiser an idea of the distribution of data in a column or set of columns. In my opinion, it's not all that useful to create statistics, I would rather create an index

    You can run the DTA on a profiler trace - sample workload. Do not run it against a production system, and take its recommendations with a large pinch of salt. It tends to recommend far more indexes and statistics than are actually necessary

    In most cases the automatic update of stats takes care of things (providing you have auto update enabled at a database level). Sometimes on a large table the auto update doesn't kick in often enough and you get bad query plans. That's usually a good hint that you want to manually update statistics.

    Rebuilding indexes updates the statistics.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks Gail 🙂

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

Viewing 3 posts - 1 through 2 (of 2 total)

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