X hits on this document

154 views

0 shares

0 downloads

0 comments

25 / 48

CHAPTER 28 PERFORMANCE TUNING

Statistics

As I discussed in Chapter 22, the AUTO_CREATE_STATISTICS database option enables SQL Server to automatically generate statistical information regarding the distribution of values in a column. The AUTO_UPDATE_STATISTICS database option automatically updates existing statistics on your table or indexed view. Unless you have a very good reason for doing so, these options should never be disabled in your database, as they are critical for good query performance.

Statistics are critical for efficient query processing and performance, allowing SQL Server to choose the correct physical operations when generating an execution plan. Table and indexed view statistics, which can be created manually or generated automatically by SQL Server, collect infor- mation that is used by SQL Server to generate efficient query execution plans.

The next few recipes will demonstrate how to work directly with statistics. When reading these recipes, remember to let SQL Server manage the automatic creation and update of statistics in your databases whenever possible. Save most of these commands for special troubleshooting circum- stances or when you’ve made significant data changes (for example, executing sp_updatestats right after a large data load).

Manually Creating Statistics

SQL Server will usually generate the statistics it needs based on query activity. However, if you still wish to explicitly create statistics on a column or columns, you can use the CREATE STATISTICS

command. The syntax is as follows:

CREATE STATISTICS statistics name ON { table | view } ( column [ ,...n ] ) [ WITH [ [ FULLSCAN | SAMPLE number { PERCENT | ROWS } | STATS_STREAM = stats_stream ] [ [ NORECOMPUTE ] _

,]]

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

Table 28-1. CREATE STATISTICS Arguments

Argument

Description

statistics_name table | view column [ ,...n ]

The name of the new statistics. The table or indexed view which the statistics are based off of. One or more columns used for generating statistics.

FULLSCAN| SAMPLE number { PERCENT | ROWS }

FULLSCAN, when specified, reads all rows when generating the statistics. SAMPLE reads either a defined number of rows or a defined percentage of rows.

STATS_STREAM = stats stream NORECOMPUTE

_

Reserved for Microsoft’s internal use.

This option designates that once the statistics are created, they should not be updated —even when data changes occur afterwards. This option should rarely, if ever, be used. Fresh statistics allow SQL Server to generate good query plans.

In this example, new statistics are created on the Sales.Customer CustomerType column:

659

Document info
Document views154
Page views154
Page last viewedFri Jan 20 16:15:50 UTC 2017
Pages48
Paragraphs987
Words15822

Comments