Statistics

Statistics, staat default aan in SQL2005, SQL2008:

Database properties --> options --> auto create statistics =  true / false.

Onder elke table in de database vindt je een folder statistics, als de statistics beginnen met _WA_Sys dan zijn het de automatisch gegenereerde statistics, en anders de handmatige gemaakte. SQL maak automatisch statistics aan bij elke query waarin een where gebruikt wordt en waar geen index is voor die collum.

Statistics helpen de optimizer bij het maken van een query-execution plan. --> helpen de optimizer bij het kiezen van de juiste indexes.

Statistics helpen de query optimizer bij het volgende:

- Kiezen van de juiste te gebruiken indexen
- Bepalen van de cardinality, aantal te retourneren rows. Afhankelijk hiervan gaat de Query Optmizer kiezen voor
* index seek operator
* index scan operator

Statistics worden gemaakt op het volgende:
- een index van een table
- een list van columns van een table

Met DBCC SHOW_STATISTICS (table_name, Collum_ or Index_name)
Laat je de statistics zien voor een bepaalde index of collumn 

 Bovenstaande SP retourneert drie result sets:
- StatHeader:
- Density Vector
- Histogram

 

The following table describes the columns returned in the result set when STAT_HEADER is specified.

Column name Description

Name

Name of the statistics object.

Updated

Date and time the statistics were last updated. The STATS_DATE (Transact-SQL) function is an alternate way to retrieve this information.

Rows

Total number of rows in the table or indexed view when the statistics were last updated. If the statistics are filtered or correspond to a filtered index, the number of rows might be less than the number of rows in the table.

Rows Sampled

Total number of rows sampled for statistics calculations. If Rows Sampled < Rows, the displayed histogram and density results are estimates based on the sampled rows.

Steps

Number of steps in the histogram. Each step spans a range of column values followed by an upper bound column value. The histogram steps are defined on the first key column in the statistics. Maximum number of steps is 200.

Density

Calculated as 1 / distinct values for all values in the first key column of the statistics object, excluding the histogram boundary values. This Density value is not used by the query optimizer and is displayed for backward compatibility with versions before SQL Server 2008.

Average Key Length

Average number of bytes per value for all of the key columns in the statistics object.

String Index

Yes indicates the statistics object contains string summary statistics to improve the cardinality estimates for query predicates that use the LIKE operator; for example, WHERE ProductName LIKE '%Bike'. String summary statistics are stored separately from the histogram and are created on the first key column of the statistics object when it is of type char, varchar, nchar, nvarchar, varchar(max), nvarchar(max), text, or ntext..

Filter Expression

Predicate for the subset of table rows included in the statistics object. NULL = non-filtered statistics. For more information, see Filtered Statistics.

Unfiltered Rows

Total number of rows in the table before applying the filter expression. If Filter Expression is NULL, Unfiltered Rows is equal to the Rows header value.

The following table describes the columns returned in the result set when DENSITY_VECTOR is specified.

Column name Description

All Density

Density is 1 / distinct values. Results display density for each prefix of columns in the statistics object, one row per density. A distinct value is a distinct list of the column values per row and per columns prefix. For example, if the statistics object contains key columns (A, B, C), the results report the density of the distinct lists of values in each of these column prefixes: (A), (A,B), and (A, B, C). Using the prefix (A, B, C), each of these lists is a distinct value list: (3, 5, 6), (4, 4, 6), (4, 5, 6), (4, 5, 7). Using the prefix (A, B) the same column values have these distinct value lists: (3, 5), (4, 4), and (4, 5)

Average Length

Average length, in bytes, to store a list of the column values for the column prefix. For example, if the values in the list (3, 5, 6) each require 4 bytes the length is 12 bytes.

Columns

Names of columns in the prefix for which All density and Average length are displayed.

The following table describes the columns returned in the result set when the HISTOGRAM option is specified.

Column name Description

RANGE_HI_KEY

Upper bound column value for a histogram step. The column value is also called a key value.

RANGE_ROWS

Estimated number of rows whose column value falls within a histogram step, excluding the upper bound.

EQ_ROWS

Estimated number of rows whose column value equals the upper bound of the histogram step.

DISTINCT_RANGE_ROWS

Estimated number of rows with a distinct column value within a histogram step, excluding the upper bound.

AVG_RANGE_ROWS

Average number of rows with duplicate column values within a histogram step, excluding the upper bound (RANGE_ROWS / DISTINCT_RANGE_ROWS for DISTINCT_RANGE_ROWS > 0).

 

 

{{opmerking.Naam}}:

{{opmerking.OpmerkingText}}

            

Saving your comment....

Naam is verplicht!
Email is verplicht!
Opmerking is verplicht!