There are different ways you can view the details of the statistics objects.For example, as shown in the query below, you can use the DBCC SHOW_STATISTICS command.This default applies only when the auto_create_statistics database option is set to ON, which is the default setting in the model database and thus in all new databases you create. To follow along, first make sure that auto_create_statistics is set to ON in the Adventure Works database, by running the following statement: Next, run the code in Listing 1, which creates the two tables in the database.The examples I’ll employ in my discussion of column statistics use copies of the Person. You can see all statistics that exist on a specified table by using the sp_helpstats system procedure.They’re created at different points and, unless you’re creating the statistics manually yourself, they’re created slightly differently.
SQL Server 2005 generates and automatically maintains statistics for every index you create, a feature that can help you in your query performance-tuning efforts.However, for some tables, such as those subject to significant changes in distribution, or those with skewed values, it’s possible that SQL Server’s automatic statistics update will be inadequate to maintain consistently high levels of query performance.In this article, I’ll describe, briefly, when SQL Server creates statistics and its criteria for performing automatic statistics updates.(See “Making the Most of Automatic Statistics Updating,” October 2007, Instant Doc ID 96767 for more information about automatic updating of index statistics.) In addition to providing statistics for indexes, SQL Server can create statistics on unindexed table columns—called .Along with index statistics, column statistics help the SQL Server optimizer create efficient query plans.How do you find out if statistics are correct, and what can you do if the automatic update of statistics isn't right for the way a table is used?