Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
 Posted - 2009-12-11 : 13:32:12
|
| I just ran the Database Engine Tuning Advisor and it recommended that I create dozens of new statistics on my database. I know that adding too many indexes can have a negative impact (particularly in inserts, updates, and deletes) but what about statistics? Can having too many statistics collected on a table have a negative impact? |
|
|
khermann
Starting Member
10 Posts |
Posted - 2009-12-11 : 17:08:29
|
| There is only upside to having complete, up-to-date statistics. Stats are a significant factor in how the optimizer generates execution plans, so the more accurate they are the higher the chances of a generating the best execution plan.If you find yourself often having to manually update stats you might consider turning on auto-update statistics for the database if it is not already; which I believe is triggered when the engine detects a 10% change in rowcount on a table. If you turn this on in a high volume OLTP database I would also suggest considering turning on auto-update statistics asynchronously as well. |
 |
|
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
Posted - 2009-12-11 : 17:58:19
|
| Thanks for the reply. Those options are enabled on the database. I guess I'm confused because SQL Server seems to create its own statistics as needed (_WA_SYS) but if I run the Database Engine Tuning Advisor it recommends I create many more statistics. I guess it can't hurt to create them.... |
|
|
|
|
|
|
Statistics - downside?