X hits on this document

# A Problem-Solution Approach - page 29 / 48

157 views

0 shares

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:

# 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 )

# 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

_

# 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 views 157 Page views 157 Page last viewed Fri Jan 20 23:01:59 UTC 2017 Pages 48 Paragraphs 987 Words 15822