January 12, 2004 at 11:34 am
In SQL 7.0, I loaded a new table with data, then added a clustered composite primary key with nocheck, then added several nonclustered indexes on single fields. Autocreate statistics was turned on. When I run sp_helpindex, I see that each field having a nonclustered index also has a statistics index created by SQL Server. How much overhead and space do these redundant indexes incur? Any idea why they were created? They were not created when I ran this in QA, but they do exist now in my production evironment. The same db_options ans scripts were used in both environments.
January 12, 2004 at 12:08 pm
Distribution statistics may also be maintained for unindexed columns. These can be defined manually using the CREATE STATISTICS statement or created automatically by the query optimizer. Statistics on unindexed columns count against the limit of 249 nonclustered indexes allowed on a table.
The statistics generated for a column can be deleted if you no longer want to retain and maintain them. Statistics created on columns by SQL Server (when the AUTO_CREATE_STATISTICS database option is set to ON) are aged and dropped automatically.
The cost of this automatic statistical update is minimized by sampling the data. Under some circumstances, statistical sampling will not be able to accurately characterize the data in a table. You can control the amount of data that is sampled during manual statistics updates on a table-by-table basis by using the SAMPLE and FULLSCAN clauses of the UPDATE STATISTICS statement.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply