View Statistics Properties
Applies to: 
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
You can display current query optimization statistics for a table or indexed view in SQL Server by using SQL Server Management Studio or Transact-SQL. Statistics objects include a header with metadata about the statistics, a histogram with the distribution of values in the first key column of the statistics object, and a density vector to measure cross-column correlation. For more information about histograms and density vectors, see DBCC SHOW_STATISTICS (Transact-SQL)
In This Topic
Before you begin:
To view statistics properties, using:
Before You Begin
Security
Permissions
In order to view the statistics object, the user must own the table or the user must be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.
Using SQL Server Management Studio
To view statistics properties
In Object Explorer, click the plus sign to expand the database in which you want to create a new statistic.
Click the plus sign to expand the Tables folder.
Click the plus sign to expand the table in which you want to view the statistic's properties.
Click the plus sign to expand the Statistics folder.
Right-click the Statistics object of which you want to view the properties and select Properties.
In the Statistics Properties - statistics_name dialog box, in the Select a page pane, select Details.
The following properties show on the Details page in the Statistics Properties - statistics_name dialog box.
Table Name
Displays the name of the table described by the statistics.Statistics Name
Displays the name of the database object where the statistics are stored.Statistics for INDEXstatistics_name
This text box shows the properties returned from the statistics object. This properties are divided into three sections: Stats Header, Density Vector, and Histogram.The following information describes the columns returned in the result set for the Stats Header.
Name
Name of the statistics object.Updated
Date and time the statistics were last updated.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. The 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.Unfiltered Rows
Total number of rows in the table before applying the filter expression. If Filter Expression is NULL, Unfiltered Rows is equal to Rows.The following information describes the columns returned in the result set for the Density Vector.
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 information describes the columns returned in the result set for the Histogram.
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).Click OK.
Using Transact-SQL
To view statistics properties
In Object Explorer, connect to an instance of Database Engine.
On the Standard bar, click New Query.
Copy and paste the following example into the query window and click Execute.
USE AdventureWorks2022; GO -- The following example displays all statistics information for the AK_Address_rowguid index of the Person.Address table. DBCC SHOW_STATISTICS ("Person.Address", AK_Address_rowguid); GO
For more information, see DBCC SHOW_STATISTICS (Transact-SQL).
To find all of the statistics on a table or view
In Object Explorer, connect to an instance of Database Engine.
On the Standard bar, click New Query.
Copy and paste the following example into the query window and click Execute.
USE AdventureWorks2022; GO /*Gets the following information: name and ID of the statistics, whether the statistics were created automatically or by the user, whether the statistics were created with the NORECOMPUTE option, and whether the statistics have a filter and, if so, what that filter is. */ SELECT name AS statistics_name ,stats_id ,auto_created ,user_created ,no_recompute ,has_filter ,filter_definition -- using the sys.stats catalog view FROM sys.stats -- for the Sales.SpecialOffer table WHERE object_id = OBJECT_ID('Sales.SpecialOffer'); GO
For more information, see sys.stats (Transact-SQL).
Feedback
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback.
Submit and view feedback for