none
Why DTA suggests to create statistics?

    Question

  • Hi,
    Database Engine Tuning Advisor suggests to create Statistics(see below picture).  I am under an impression that statistics are updated automatically(autostatistics update = true for DB).

    What does this mean? Should I really be creating seperate objects with script to create statistics?



    Friday, December 07, 2012 1:12 PM

Answers

  • I am curious to know if manually created multi-column stats will add any overhead to database resulting a slow query execution for other quesries( same as too many index is not good thing ).

    Updating statistics incurs overhead but the benefits of better plans often outweighs the associated costs.  You can turn on the AUTO_UPDATE_STATISTICS_ASYNC database option to avoid queries waiting for stats to be updated.  This applies to both single and multi-column stats.

    Also, If I want to create Multi-column stats without talking advice form DTA, what would be my approach? I mean, seeing a query how would I know what stats I should create? Any example would be helpful.

    In addition to the normal histogram and string statistics for the leftmost column, multi-column stats provide density information on the remaining columns.  This information is useful when there is a correlation between column values so that the optimizer can better estimate row counts in absence of a omposite index on those columns.  Accurate row counts allow the optimizer to perform joins in the best order and use indexes, or not, accordingly.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    • Proposed as answer by Prashant Jha Monday, December 10, 2012 5:30 AM
    • Marked as answer by Tech_consultant Monday, December 17, 2012 10:59 AM
    Saturday, December 08, 2012 9:53 PM
    Moderator

All replies

  • from MS website http://msdn.microsoft.com/en-us/library/ms173494.aspx

    The advisor can also recommend what statistics should be collected to back up physical design structures. The physical design structures include clustered indexes, nonclustered indexes, indexed views, and partitioning. Database Engine Tuning Advisor recommends a set of physical design structures that reduces the query optimizers estimated cost of the workload.

    hope this helps

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Friday, December 07, 2012 1:20 PM
  • Database Engine Tuning Advisor suggests to create Statistics(see below picture).  I am under an impression that statistics are updated automatically(autostatistics update = true for DB).

    What does this mean? Should I really be creating seperate objects with script to create statistics?

    It is true that stats SQL Server can create statistics on single columns but multi-columns ststs can be more useful when queries contain complex predicates.  An explicit CREATE STATISTICS is needed to create multi-column statistics.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Friday, December 07, 2012 1:34 PM
    Moderator
  • @Dan,

    Thank you for answering my question.

    I am curious to know if manually created multi-column stats will add any overhead to database resulting a slow query execution for other quesries( same as too many index is not good thing ).


    Also, If I want to create Multi-column stats without talking advice form DTA, what would be my approach? I mean, seeing a query how would I know what stats I should create? Any example would be helpful.
    • Edited by Tech_consultant Friday, December 07, 2012 1:56 PM more question
    Friday, December 07, 2012 1:54 PM
  • I am curious to know if manually created multi-column stats will add any overhead to database resulting a slow query execution for other quesries( same as too many index is not good thing )

    --Not 100% or it depends. if joining is there on multiple columns, multiple columns statistics better option

    Also, If I want to create Multi-column stats without talking advice form DTA, what would be my approach? I mean, seeing a query how would I know what stats I should create? Any example would be helpful.

    --auto create stats always create stats on 1 column while multi columns stats are created manually.

    check where clause | join On columns==> from left to right.

    Here check auto created single column stats doesn't overlap with auto stats.(else performance may decrease)


    Manish

    Saturday, December 08, 2012 2:33 PM
  • I am curious to know if manually created multi-column stats will add any overhead to database resulting a slow query execution for other quesries( same as too many index is not good thing ).

    Updating statistics incurs overhead but the benefits of better plans often outweighs the associated costs.  You can turn on the AUTO_UPDATE_STATISTICS_ASYNC database option to avoid queries waiting for stats to be updated.  This applies to both single and multi-column stats.

    Also, If I want to create Multi-column stats without talking advice form DTA, what would be my approach? I mean, seeing a query how would I know what stats I should create? Any example would be helpful.

    In addition to the normal histogram and string statistics for the leftmost column, multi-column stats provide density information on the remaining columns.  This information is useful when there is a correlation between column values so that the optimizer can better estimate row counts in absence of a omposite index on those columns.  Accurate row counts allow the optimizer to perform joins in the best order and use indexes, or not, accordingly.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    • Proposed as answer by Prashant Jha Monday, December 10, 2012 5:30 AM
    • Marked as answer by Tech_consultant Monday, December 17, 2012 10:59 AM
    Saturday, December 08, 2012 9:53 PM
    Moderator