X hits on this document

150 views

0 shares

0 downloads

0 comments

27 / 48

CHAPTER 28 PERFORMANCE TUNING

Argument [ ALL | COLUMNS | INDEX ]

Description

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.

NORECOMPUTE

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:

WITH FULLSCAN

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

Argument indexonly

Description

When indexonly is designated, only columns used in indexes will be considered for statistics creation.

fullscan

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.

norecompute

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.

661

Document info
Document views150
Page views150
Page last viewedThu Jan 19 16:09:32 UTC 2017
Pages48
Paragraphs987
Words15822

Comments