3

Specifically I'm most interested in:

  • The 'All density' number in the density vector
  • Numbers in the histogram

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.

3

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
|improve this answer|||||
  • Thanks, this looks like what I'm after! How did you get the data types? And how are you running show_statistics with no parameters, looks like it requires ( table_or_indexed_view_name , target ) from the docs? – alksdjg Oct 27 '15 at 22:11
  • @alksdjg I didn't run DBCC with no params, I am just showing a generic form of the command. I got the info from a SQLCLR stored proc I created as part of the SQL# library. It is called DB_DescribeResultSets and I ran it as follows: 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
  • @alksdjg (cont.) You could do the same thing in any .NET app, though. It just requires running a query through a SqlDataReader and then using the GetSchemaTable method to get the result set schema. – Solomon Rutzky Oct 27 '15 at 22:35
  • 1
    Ahhhh ok, makes sense. Cheers. – alksdjg Oct 27 '15 at 22:39
  • 1
    @alksdjg Just to be clear, these datatypes are the result set datatypes from the DBCC commands; there is no indication of the datatypes used to store that info. However, it is reasonable to assume that the datatypes of the result set are the same as those used the store the info. – Solomon Rutzky Oct 27 '15 at 23:37
0

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?

|improve this answer|||||
  • "Choose the one..." - I don't get a choice, do I? Isn't this a property of SQL Server? – alksdjg Oct 27 '15 at 21:33
  • @alksdjg I just meant that you can use smallint or int or numeric or decimal. Some datatypes, you dont have a choice :-) – Kin Shah Oct 27 '15 at 21:34
  • I'm not asking how I should read these values in, I'm asking how they are stored in SQL Server, which I don't believe I have a choice over, is that wrong? – alksdjg Oct 27 '15 at 21:37
  • Also, I can see many example of values in the histogram having decimal places, wouldn't that conflict with bigint? – alksdjg Oct 27 '15 at 21:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.