March 31, 2004 at 11:20 am
Hi Folks -
From everything I've read one should ALWAYS turn on the Auto Create Statistics option for SS2K databases. When this option is set, I understand that the query optimizer will automatically create stats on columns that don't have them (un-indexed) if the optimizer would like to have them. But then what happens??
How do these auto-created stats help the optimizer build a better execution plan?? How does SS sample the table in order to create these stats?? Once these stats are created, how do they get refreshed (re-created)?? If I do an UPDATE STATISTICS on a table will it update auto-created stats as well??
Call me a skeptic but I just don't believe you get something for nothin'.
Thanks boys and girls.
-Mike
Mike at the Mill
April 4, 2004 at 10:10 pm
Hi Mike,
You should take a look at 'Statistical Information' in BOL (Books Online). This may answer some of your questions.
With regard to having auto create statistics turned on, Microsoft recommends it be turned on, but I prefer to have them turned off and then have a job scheduled to create/update them so that SQL Server doesn't create statistics when users are on the system. Creating and updating stats is a performance hit and its the last thing you need if your system is already under load. I like to run the jobs when there is very little activity happening.
If you run Update Stats on a table it while you have auto create/update stats turned on, it will still work.
Hope this helps,
Angela
April 5, 2004 at 2:42 pm
Thanks Angela
I've looked at BOL. And after digging around I do see that at least SS will not automatically update stats that it has also automatically created (I think that's a good thing). And there is syntax in UPDATE STATISTICS that let you update auto-created stats. BOL calls these 'collections'. It assumes you know the name of the collection - which you can get by running sp_helpstats.
What I'm still confused about is how/when does the optimizer ever use these auto-created stats. In QA if I display the execution plan of a query that uses the column from one of these auto-created stat collections (i.e., not in any index), it just shows a table scan.
What am I missing??
Mike at the Mill
April 5, 2004 at 8:12 pm
Hi Mike,
SQL Server uses these stats to determine the best strategy for executing a query. If the stats are out of date, SQL Server will use these stats even though the data may have changed significantly since the stats were last updated. The choice SQL Server makes in the Execution Plan may not be the best choice if it's relying on old statistics.
You may still see a table scan on a table with indexes and updated statistics if it is a relatively small table and SQL Server determines it is more efficent to do a full table scan instead of an index lookup.
I would update all your stats and see if you execution plan changes.
BTW, As far as I know SQL Server WILL update statistics that are automatically created as long as you have this specified (ie, Auto_Update_Statisticts is ON - This is the default) and did not use the NORECOMPUTE clause if you created them manually.
From BOL: The frequency at which the statistical information is updated is determined by the volume of data in the column or index and the amount of changing data. For example, the statistics for a table containing 10,000 rows may need updating when 1,000 index values have changed because 1,000 values may represent a significant percentage of the table. However, for a table containing 10 million index entries, 1,000 changing index values is less significant, and so the statistics may not be automatically updated. SQL Server, however, always ensures that a minimum number of rows are sampled; tables that are smaller than 8 megabytes (MB) are always fully scanned to gather statistics.
Cheers,
Angela
April 7, 2004 at 3:35 pm
Angela
Thanks for staying with me as I dig this out of BOL. The stored procedure sp_autostats proves you right on the money. If you just follow sp_autostats with the table name, it will show you the current setting for AUTOSTATS for all stats on the table. The familiar pattern '_WA_Sys' tells you the stats were auto-created.
I'm happy (or as happy as I ever get). Still don't see how the optimizer uses these.
Thank you!
-Mike
Mike at the Mill
October 27, 2004 at 4:58 am
Is there a way by which we can know which all Indexes need to be updated so that I can update only those Indexes rather than UPDATing all the Indexes present.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply