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?

Answer 1

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

Answer 2

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/

Answer 3

@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.

Answer 4

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

Answer 5

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/