Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
Specifically I'm most interested in:
E.g. are they floats / decimals? What precision / scale / length do they have?
The motivation for this is that I think I might be seeing rounding errors, so would like to know how precisely we should expect cardinality estimations to match with these stats.
I'd be happy for an answer for any version of SQL Server, but 2012 and 2014 are most relevant to me.
Below are the field names and datatypes of each of the result sets as reported by SQL Server (I tested on SQL Server 2012, 2014, and 2016, though I would expect these to remain consistent across SQL Server versions):
DBCC SHOW_STATISTICS() WITH STAT_HEADER
[Name] NVARCHAR(128)
[Updated] NVARCHAR(20)
[Rows] BIGINT
[Rows Sampled] BIGINT
[Steps] SMALLINT
[Density] REAL
[Average key length] REAL
[String Index] NCHAR(3)
[Filter Expression] NVARCHAR(MAX)
[Unfiltered Rows] BIGINT
DBCC SHOW_STATISTICS() WITH DENSITY_VECTOR
[All density] REAL
[Average Length] REAL
[Columns] NVARCHAR(4000)
DBCC SHOW_STATISTICS() WITH HISTOGRAM
[RANGE_HI_KEY] INT -- Datatype varies based on datatype of first key column
[RANGE_ROWS] REAL
[EQ_ROWS] REAL
[DISTINCT_RANGE_ROWS] BIGINT
[AVG_RANGE_ROWS] REAL
To be clear, these datatypes are the result set datatypes from the DBCC commands; there is no indication of the actual datatypes used to store that info. However, it seems reasonable to assume that the datatypes of the result set are the same as those used the store the info.
Also, for anyone curious as to how I was able to determine these datatypes: I got the info from a SQLCLR stored procedure I created as part of the SQL# library. It is called DB_DescribeResultSets and is similar to sys.dm_exec_describe_first_result_set but handles multiple result sets, temp tables, dynamic SQL, etc (because it runs the query instead of just parsing it). The DB_DescribeResultSets stored procedure is not available in the Free version, though. However, you could do the same thing in any .NET app. It just requires running a query through a SqlDataReader and then using the GetSchemaTable method to get the result set schema.
I ran DB_DescribeResultSets as follows:
EXEC SQL#.DB_DescribeResultSets
@TheQuery = N'DBCC SHOW_STATISTICS(N''msdb.dbo.sysjobs'',
nc1);',
@RowNumberToGetValuesFrom = 1,
@ResultSetNumberToDescribe = 0, -- 0 = all result sets
@ShowHiddenFields = 1,
@ResultSet = ''; -- this is an XML OUTPUT param and can't have a default in SQLCLR
( table_or_indexed_view_name , target ) from the docs?
– alksdjg
Oct 27 '15 at 22:11
EXEC SQL#.DB_DescribeResultSets N'DBCC HOW_STATISTICS([AdventureWorks2012.Sales.SalesOrderDetail], IX_SalesOrderDetail_ProductID);', 1, 0, 1, '';. It is similar to sys.dm_exec_describe_first_result_set but handles multiple result sets, temp tables, dynamic SQL, etc (because it runs the query). It is not in the Free version, though.
– Solomon Rutzky
Oct 27 '15 at 22:35
SqlDataReader and then using the GetSchemaTable method to get the result set schema.
– Solomon Rutzky
Oct 27 '15 at 22:35
There is no right or wrong data type that you should strictly follow. Choose the one that does not give you errors or truncation.
Refer to Kalen's blog on Accessing Distribution Statistics.
So to answer your question :
The 'All density' number in the density vector --> should be numeric(10,8)
Numbers in the histogram should be as
RANGE_HI_KEY sql_variant,
RANGE_ROWS bigint,
EQ_ROWS bigint,
DISTINCT_RANGE_ROWS bigint,
AVG_RANGE_ROWS bigint
Worth mentioning Aaron Bertrand's excellent answer to Where are Statistics physically stored in SQL Server?
smallint or int or numeric or decimal. Some datatypes, you dont have a choice :-)
– Kin Shah
Oct 27 '15 at 21:34
bigint?
– alksdjg
Oct 27 '15 at 21:38
