X hits on this document

147 views

0 shares

0 downloads

0 comments

26 / 48

660

CHAPTER 28 PERFORMANCE TUNING

C R E A T E S T A T I S T I C S S t a t s _ C u s t o m e r _ C u s t o m e r T y p ON Sales.Customer (CustomerType) WITH FULLSCAN e

How It Works

This recipe demonstrated manually creating statistics on the Sales.Customer table. The first line of code designated the statistics name:

CREATE STATISTICS Stats_Customer_CustomerType

The second line of code designated the table to create statistics on, followed by the column name used to generate the statistics:

ON Sales.Customer (CustomerType)

The last line of code designated that all rows in the table would be read in order to generate the statistics:

WITH FULLSCAN

Updating Statistics

After you create statistics, if you wish to manually update statistics, you can use the UPDATE STATISTICS command. The syntax is as follows:

UPDATE STATISTICS table | view [ {

{ index | statistics_name } | ( { i n d e x | s t a t i s t i c s _ n a m e } [

,

...n ] )

}

] [

WITH

[

[ | | ] [[, [[ ,

FULLSCAN ] SAMPLE number { PERCENT | ROWS } ] RESAMPLE

] [ ALL | COLUMNS | INDEX ] ] NORECOMPUTE ]

] The arguments of this command are described in Table 28-2.

Table 28-2. UPDATE STATISTICS Arguments

Argument table | view

{ index | ( {index| [ ,...n ]

statistics_name}| statistics_name} )}

FULLSCAN| { PERCENT

SAMPLE number | ROWS } |RESAMPLE

Description The table name or indexed view for which to update statistics.

The name of the index or named statistics to update.

FULLSCAN, when specified, reads all rows when generating the statistics. SAMPLE reads either a defined number of rows or a percentage. RESAMPLE updates statistics based on the original sampling method.

Document info
Document views147
Page views147
Page last viewedWed Jan 18 20:08:09 UTC 2017
Pages48
Paragraphs987
Words15822

Comments