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 |
|
Sarat
Constraint Violating Yak Guru
265 Posts |
 Posted - 2003-11-03 : 14:36:14
|
| Can I assume that if I didn't create a statistic on a table by doing a Create Stats command and if there are any stats on a table, they are created by the system since 'auto create stats' is set 'ON'? Thanks,Sarat.**To be intoxicated is to feel sophisticated, but not be able to say it.** |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-11-03 : 15:36:08
|
| Yes. I would guess that most statistics would be created this way.Tara |
 |
|
|
Sarat
Constraint Violating Yak Guru
265 Posts |
Posted - 2003-11-03 : 16:41:09
|
| Q1:Is it possible that the sql server's auto create/update stats job feature can also drop stats if optimizer feels the stats are not required?Thanks,Sarat**To be intoxicated is to feel sophisticated, but not be able to say it.** |
|
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2003-11-03 : 16:54:12
|
| To avoid long term maintenance of unused statistics, SQL Server 2000 ages the automatically created statistics (only those that are not a byproduct of the index creation). After several automatic updates the column statistics are dropped rather than updated. If they are needed in the future, they may be created again. There is no substantial cost difference between statistics that are updated and created. The aging does not affect user-created statisticsFor more info [url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/statquery.asp[/url] |
|
|
|
Sarat
Constraint Violating Yak Guru
265 Posts |
Posted - 2003-11-03 : 18:52:44
|
| Thank You Jasper.Though I am not clear on this statement in the article:--CREATE INDEX generates the declared index in the first place, and then as a byproduct creates one set of statistics for the column combination constituting the index. CREATE STATISTICS only generates the statistics for a given column or combination of columns. ---- 1Does this mean if an index is made up of Col1, Col2, Col3 - Create Index will generate one stat for Col1, Col2 and COl3 combined and not 3 stats for each column constituting the index?--2If # 1 is true, I don't see a stat containing all 3 cols when I do helpstats. If #1 is not true, I see stats for only few columns in the result.--What am I missing?Thanks,Sarat**To be intoxicated is to feel sophisticated, but not be able to say it.** |
|
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2003-11-04 : 02:47:25
|
| The statistic for the index will have the density's for the combinations of colums i.e. Col1,Col1+Col2,Col1+Col2+Col3 plus the distribution of values for the leading column in the index (upto 200 steps).If it's a non clustered index you will also see the clustered index showing up in the combination density's (this is why statistics for NC indexes have a higher keycount than the number of columns in the index if a clustered index also exists) |
|
|
|
|
|
|
|
|
Auto Create Stats ON