X hits on this document

102 views

0 shares

0 downloads

0 comments

29 / 48

CHAPTER 28 PERFORMANCE TUNING

Table 28-4. DBCC SHOW_STATISTICS Arguments

Argument

Description

'table name' | 'view name'

_

_

The table or indexed view to evaluate.

target NO_INFOMSGS

The name of the index or named statistics to evaluate.

When designated, NO_INFOMSGS suppresses informational messages.

STAT_HEADER |DENSITY VECTOR | HISTOGRAM [ , n ]

_

Specifying STAT_HEADER, DENSITY_VECTOR, or HISTOGRAM designates which result sets will be returned by the command (you can display one or more). Not designating any of these means that all three result sets will be returned.

Updated

Rows

Rows Sampled

Steps

Density

Average

String

key length

Index

Oct 15 2005

19185

19185

2

0

2

YES

This example demonstrates how to view the statistics information on the Sales.Customer Stats_Customer_CustomerType statistics:

DBCC SHOW_STATISTICS ( 'Sales.Customer'

This returns the following result sets:

, S t a t s _ C u s t o m e r _ C u s t o m e r T y p e )

Name

2:32PM

Stats_Customer CustomerType

_

All density

Average Length

Columns

0.5

2

CustomerType

I

0

18484

0

1

S

0

701

0

1

EQ_ROWS

DISTINCT RANGE ROWS

_

_

AVG RANGE ROWS

_

_

RANGE HI KEY

RANGE ROWS

__

_

How It Works

In the results of this recipe’s example, the All density column indicates the selectivity of a column:

All density

Average Length

Columns

0.5

2

CustomerType

Selectivity refers to the percentage of rows that will be returned given a specific column’s value. A low All density value implies a high selectivity. Columns with high selectivity often make for use- ful indexes (useful to the query optimization process).

In the third result set returned by SHOW_STATISTICS, CustomerType had only two values, I and S (which you can see in the RANGE_HI_KEY in the third result set):

I

0

18484

S

0

701

_ EQ ROWS

RANGE ROWS

_

R

ANGE HI KEY

__

_ DISTINCT RANGE ROWS 0 0 _

_ AVG RANGE ROWS 1 1 _

With such a high density of similar values, and low selectivity (one value is likely to return many rows), you can make an educated assumption that an index on this particular column is unlikely to be very useful to SQL Server in generating a query execution plan.

663

Document info
Document views102
Page views102
Page last viewedTue Dec 06 11:17:21 UTC 2016
Pages48
Paragraphs987
Words15822

Comments