CHAPTER 28 ■ PERFORMANCE TUNING
Argument [ ALL | COLUMNS | INDEX ]
When ALL is designated, all existing statistics are updated. When COLUMN is designated, only column statistics are updated. When INDEX is designated, only index statistics are updated.
This option designates that once the statistics are created, they should not be updated—even when data changes occur. Again, this option should rarely, if ever, be used. Fresh statistics allow SQL Server to generate good query plans.
This example updates the statistics created in the previous recipe, populating statistics based on the latest data:
UPDATE STATISTICS Sales.Customer Stats_Customer_CustomerType WITH FULLSCAN
How It Works
This example updated the statistics created in the previous recipe, refreshing them with the latest data. The first line of code designated the table name containing the statistics to be updated:
UPDATE STATISTICS Sales.Customer
The second line designated the name of the statistics to be updated: Stats_Customer_CustomerType
The last line of code designated that all rows in the table would be read in order to update the statistics:
Generating and Updating Statistics Across All Tables
You can also automatically generate statistics across all tables in a database for those columns that don’t already have statistics associated to them, by using the system-stored procedure sp_createstats. The syntax is as follows:
sp_createstats [ [ @indexonly = ] 'indexonly' ] [ , [ @fullscan = ] 'fullscan' ] [ , [ @norecompute = ] 'norecompute' ]
The arguments of this command are described in Table 28-3.
Table 28-3. sp_createstats Arguments
When indexonly is designated, only columns used in indexes will be considered for statistics creation.
When fullscan is designated, all rows will be evaluated for the generated statistics.
If not designated, the default behavior is to extract statistics via sampling.
The norecompute option designates that once the statistics are created, they should not be updated—even when data changes occur. Like with CREATE STATISTICS and UPDATE STATISTICS, this option should rarely, if ever, be used. Fresh statistics allow SQL Server to generate good query plans.