SQL Server :- sys.dm_db_stats_histogram

Background

As always pressed to grasp something that my very little mind has not been able to understand.

And, that is how to read Statistics Histogram.

SQL

Thankfully Microsoft is always furnishing us with new pathways towards understanding.

sys.dm_db_stats_histogram

Today that way for me is via sys.dm_db_stats_histogram.

The DMV was introduced in v2016 SP1.

Syntax

It takes the ObjectID and Statistics ID and assumes that you are in the contextual database.


select *

from  sys.dm_db_stats_histogram
(
       @objectID
     , @statisticsID
)

Returned Record Set

We will make heavy use of the word range and define it as the preceding row’s range_high_key and our current row’s range_high_key.

Column Meaning Datatype
step_number Step Number from 1 to Maximum of 200 smallint
range_high_key Highest for specific step sql_variant ( Depends on data-type of first stats column)
range_rows Number of records in range real
equal_rows Number of rows that exactly match range_high_key real
distinct_range_rows Number of distinct records in range bigint
average_range_rows Number of records in range / Number of distinct records in range real

Usage


use [DBLab]
go

declare @table sysname
declare @objectID int
declare @index sysname
declare @indexID smallint

set @table = '[dbo].[sales]'
set @index = 'INDX_SalesDate'

set @objectID = object_id(@table)

select

      @indexID = index_id

from  sys.indexes tblSI

where  tblSI.object_id = @objectID

and    tblSI.[name] = @index

DBCC SHOW_STATISTICS
(
       @table
     , @index
)
with histogram

;

select
         [object]
         =
             quotename(tblSS.[name])
           + '.'
           + quotename(tblSO.[name])

       , [stat]
          = tblSS2.[name]

       , tblSH.step_number
       , tblSH.range_high_key
       , tblSH.range_rows
       , tblSH.equal_rows
       , tblSH.distinct_range_rows
       , tblSH.average_range_rows

from   sys.dm_db_stats_histogram
       (
           @objectID
         , @indexID
       ) tblSH

inner join sys.objects tblSO
   on tblSH.object_id = tblSO.object_id

inner join sys.schemas tblSS
   on tblSO.schema_id = tblSS.schema_id

inner join sys.stats tblSS2
   on  tblSH.object_id = tblSS2.object_id
   and tblSH.stats_id = tblSS2.stats_id

 

Output

sysDOTdm_db_stats_histogram_20180530_0642PM

Workshop

Let us pick up on the second row and see if we can understand what each column means.

Image

sysDOTdm_db_stats_histogram_20180530_0647PM

Explanation

  1. Step Number
    • Definition
      • There are at most 200 steps in a SQL Server Statistics Histogram
    • Step Number :- 1
      • Low Bar
    • Step Number :- 200 or last row
      • High Bar
  2. Range High Key
    • Definition
      • This is the maximum value of the current row’s range
    • Step Number :- 2
      • 1900-01-01 16:17:00.000
  3. Range Rows
    • Definition
      • This is the number of rows between the previous row’s max value and the current row’s max value
    • Step Number :- 2
    • 1016
  4. Equal Rows
    • Definition
      • This is the number of rows that exactly match this row
    • Step Number :- 2
    • 5
  5.  Distinct Range Rows
    • Definition
      • This is the number of rows that have distinct values for the current range
    • Step Number :- 2
    • 625
  6. Average Range Rows
    • Definition
      • Number of rows / number of rows that have distinct values for the current range
    • Step Number :- 2
    • 1.6256

Collaborative Query

We will instruct a full update statistics scan against our test table and issue a query against the first column of our statistics.

We will then compare the result set against what is listed in our histogram.

Query


set nocount on;
go

declare @dateRangeBegin datetime
declare @dateRangeEnd   datetime

set @dateRangeBegin = '1900-01-01 00:00:00.000'
set @dateRangeEnd = '1900-01-01 16:17:00.000'

select
         [numberofRows]
            = count(*)

       , [numberofEqualRows]
        = sum(
                case
                    when clock_time = @dateRangeEnd then 1
                    else 0
                    end
             )

       , [numberofDistinctRows]
            = count(distinct [clock_time])

       , [averageDistinctRows]
            = cast
                (
                    (
                          count(*) *1.00
                        / NULLIF
			(
			    count(distinct clock_time) * 1.00
			   , 0
			)
                    )
                    as decimal(20, 6)
                )

from   [dbo].[sales] tblI

where  tblI.clock_time
            between @dateRangeBegin and @dateRangeEnd

Output

sysDOTdm_db_stats_histogram_20180530_0704PM

Explanation

  1. Number of Rows
    select count(*) against table
    
  2. Number of Equal Rows
    sum
        (
           case
              when clock_time = @dateRangeEnd then 1
              else 0
             end
        )
    
  3. Number of Distinct Rows
    count(distinct [clock_time])
    
  4. Average Range Rows
    cast
    (
    	(
    		  count(*) *1.00
    		/ count(distinct clock_time) * 1.00
    	)
    	as decimal(20, 6)
    )
    

References

  1. Docs > SQL >Relational databases > System dynamic management views
    • sys.dm_db_stats_histogram (Transact-SQL)
      Link

SQL Server – Mapping “DBCC Commands” to Dynamic Management Views ( DMV )

Background

Wanted to map DBCC Commands to their corresponding Dynamic Management Views ( DMV ).

Matrix

DBCC Command Dynamic Management Views Usage SQL Version Introduced
DBCC LOGINFO
sys.dm_db_log_info Virtual Log Files v2016/SP2
v2017/RTM
dbcc sqlperf(logspace)
sys.dm_db_log_space_usage Database Transaction Log  Usage v2012
DBCC Memory Status
 sys.dm_os_memory_clerks Types of objects stored in memory  v2008/R2
 sys.dm_os_buffer_descriptors Actual identifiers for the objects stored in the Buffer Pool  v2008/R2
DBCC SHOW CONTIG
 sys.dm_db_index_physical_stats Fragmentation  v2008
DBCC SHOW _STATISTICS
 sys.dm_db_stats_properties Statistics  v2008
sys.dm_db_incremental_stats_properties Incremental Statistics SQL Server 2014 (12.x) Service Pack 2

SQL Server 2016 (13.x) Service Pack 1.

sys.dm_db_stats_histogram Statistics Histogram  v2016

Use

sys.dm_db_log_info

Sample



select  

          [database]
            = tblSD.[name]

        , [file]
            = tblSMF.[name]

        , vLI.[file_id]

        , [seq]
            = RANK() OVER   
                (
                    PARTITION BY 
                          tblSMF.database_id
                        , vLI.[file_id]

                    ORDER BY 
                        vLI.vlf_begin_offset asc
                ) 

        , vLI.vlf_begin_offset

        , vLI.vlf_size_mb

        , vLI.vlf_sequence_number

        , vLI.vlf_active

        , [active]
            = case vLI.vlf_active
                when 0 then 'VLF is not in use'
                when 1 then 'VLF is active'
              end		

        , vLI.vlf_status

        , [status]
            = case vLI.vlf_status
                when 0 then 'VLF is inactive'
                when 1 then 'VLF is initialized but unused'
                when 2 then 'VLF is active'
              end		

        , vLI.vlf_parity

        --, vLI.vlf_first_lsn

        --, vLI.vlf_create_lsn

from   sys.databases tblSD

inner join sys.master_files tblSMF

    on tblSD.[database_id] = tblSMF.database_id

cross apply sys.dm_db_log_info
            (
                tblSMF.database_id
            ) vLI

where tblSMF.[database_id] = vLI.database_id

and   tblSMF.[file_id] = vLI.[file_id]

/*
    Database is online
*/


and  databasepropertyex
            ( 
                  tblSD.[name]
                , 'Collation'
            ) is not null

order by

      tblSD.[name]

    , tblSMF.[name]

    , vLI.vlf_begin_offset



Output

sys.dm_db_log_space_usage

Sample


set nocount on
go

set XACT_ABORT on
go

declare @tblLogSpace TABLE
(
	    
      [dbname]			  sysname

	, [logSizeTotalInMB]  decimal(10,2)

	, [logSizePercentile] decimal(10,2)

	, [logSizeInUseInMB]  
			as 
				(
					cast
					(
						(
							[logSizeTotalInMB] * 1.00
							*
							(
								[logSizePercentile]
								/
								100.000
							)

						)

						as decimal(10, 2)					  	


					)
				)

	, [status]	int

)

insert into @tblLogSpace
(
	  [dbname] 
	, [logSizeTotalInMB] 
	, [logSizePercentile]
	, [status]			 
)
exec('dbcc sqlperf(logspace)')

select *
from   @tblLogSpace tblLS
where  tblLS.dbname = db_name()

select 

	  tblDMLSU.database_id

	, [database]
		= db_name(tblDMLSU.database_id)

	, [dbid] 
		= db_id()

	, tblDMLSU.total_log_size_in_bytes

	, [totalLogSizeInMB]
		= 
		cast
		(

			(
				( tblDMLSU.total_log_size_in_bytes * 1.00 )
				/ 
				( 1000 * 1000 )
			)
			
			as decimal(20, 2)
		)
			  				
	, tblDMLSU.[used_log_space_in_bytes]

	, [usedLogSizeInMB]
		= cast
			(	
					
				(
				tblDMLSU.[used_log_space_in_bytes] * 1.00
				)
				/ 
				( 1000 * 1000 )

				as decimal(20, 2)
			)			

	, tblDMLSU.used_log_space_in_percent

	, tblDMLSU.[log_space_in_bytes_since_last_backup]

	, [usedLogBytesInMBSinceLastBackup]
	  = cast
		(
			(
				( tblDMLSU.log_space_in_bytes_since_last_backup * 1.0 )
				/ 
				( 1000 * 1000 )
			)
			as decimal(20, 2)
		)

from [sys].[dm_db_log_space_usage] tblDMLSU

sys.dm_os_buffer_descriptors

Sample


select 
     [database] 
		= case
			when [database_id] = 32767
				then 'Resource DB'
			else db_name(dmOSBD.[database_id])
			end

	, [bufferedPageCount]
		= count(*)

	, [bufferPoolMB]
		= count_big(*) * 8192 
			/ 
			(1024 * 1024)

from sys.dm_os_buffer_descriptors dmOSBD

GROUP BY 
	      DB_NAME(database_id) 
		, database_id  

ORDER BY 
		count_big(*) DESC

;  


 

sys.dm_os_memory_clerks

Sample


select 
           dmOSMC.[type]

        , [pagesKB]
            = sum(dmOSMC.pages_kb)

        , [pagesMB]
            = sum(dmOSMC.pages_kb)
                / 1000

        , [pagesGB]
            = cast
                (
                    (
                        sum(dmOSMC.pages_kb) * 1.00
                        / ( 1000 * 1000)
                    )
                    as decimal(10, 2)
                )

 from   sys.dm_os_memory_clerks dmOSMC

 group by
        dmOSMC.[type]

 order by
        sum(dmOSMC.pages_kb) desc


;  


Output

sys.dm_db_index_physical_stats

Sample


declare @dbname sysname
declare @dbid   int

declare @objectID int
declare @indexID  int
declare @partitionNumber int
declare @mode            sysname

select 
      [database]
        = db_name(tblIPS.database_id)

    , [object]
        = quoteName
            (
                object_schema_name
                (
                      tblIPS.object_id
                    , tblIPS.database_id	
                )
            )

            + '.'

            + object_name
                (
                      tblIPS.object_id
                    , tblIPS.database_id	
                )

    , tblIPS.[object_id]

    , tblIPS.index_id

    , tblIPS.index_type_desc

    , tblIPS.partition_number

    , tblIPS.avg_fragmentation_in_percent

from sys.dm_db_index_physical_stats 
(
      @dbid
    , @objectID
    , @indexID
    , @partitionNumber
    , @mode

) tblIPS

order by
    tblIPS.avg_fragmentation_in_percent desc

sys.dm_db_stats_properties

Sample


select 

      [database]
        = db_name()

    , [object]
        = quoteName
            (
                object_schema_name
                (
                      tblSO.object_id
                )
            )

          + '.'

          + quotename
            (
                object_name
                (
                    tblSO.object_id
                )
            )

    , [stat]
        = tblSS.[name]

    , [userCreated]
        = case tblSS.user_created
                when 1 then 'Yes'
                else 'No'
          end

    , tblDMSP.[rows]
    
    , tblDMSP.[rows_sampled]

    , [%sampled]	
        = (
            cast
            (
                (
                    tblDMSP.[rows_sampled] * 100.00
                )
                / 
                NULLIF
                (
                      tblDMSP.[rows]
                    , 0
                )

            as decimal(6,2)
          
          )

        )

    , tblDMSP.[steps]
    , tblDMSP.[last_updated]

from  sys.objects tblSO

inner join sys.stats tblSS

    on tblSO.object_id = tblSS.object_id

cross apply sys.dm_db_stats_properties 
(
      tblSS.[object_id]
    , tblSS.[stats_id]

) tblDMSP

where  tblSO.[type] = 'U'

and    tblSO.is_ms_shipped = 0

order by
        tblDMSP.[rows] desc
    , 	tblDMSP.[rows_sampled] desc

Output

sys.dm_db_stats_histogram

Sample



declare @object   sysname
declare @stat     sysname
declare @objectID int

set @object = '[stats].[skewedData]'
set @stat = 'indx_id_measure'

set @objectID = OBJECT_ID(@object)

select 

      [database]
        = db_name()

    , [object]
        = quoteName
            (
                object_schema_name
                (
                      tblSO.object_id
                )
            )

            + '.'

            + quotename
                (
                    object_name
                    (
                      tblSO.object_id
                    )
                )

    , [stat]
        = tblSS.[name]

    , [userCreated]
        = case tblSS.user_created
                when 1 then 'Yes'
                else 'No'
          end

   , tblDMSH.[stats_id] 
   
   , tblDMSH.[step_number]

   , tblDMSH.range_high_key
   
   , tblDMSH.range_rows
   
   , tblDMSH.equal_rows

from  sys.objects tblSO

inner join sys.stats tblSS

    on tblSO.object_id = tblSS.object_id

cross apply sys.dm_db_stats_histogram
(
      tblSS.[object_id]
    , tblSS.[stats_id]

) tblDMSH

where  tblSO.[type] = 'U'

and    tblSO.[is_ms_shipped] = 0

and    tblSS.[object_id] = @objectID

and    tblSS.[name] = @stat

order by
          [object]
        , tblSS.[name]
        , tblDMSH.[stats_id] asc
        , tblDMSH.[step_number] asc

Output

References

  1. Microsoft
    • Docs > SQL > Relational databases > System dynamic management views
      • sys.dm_db_log_info (Transact-SQL)
        • sys.dm_db_log_info
          Link
      • sys.dm_db_log_space_usage
        • sys.dm_db_log_space_usage
          Link
      • sys.dm_os_memory_clerks
        • sys.dm_os_memory_clerks
          Link
      • sys.dm_db_index_physical_stats (Transact-SQL)
        • sys.dm_db_index_physical_stats (Transact-SQL)
          Link
      • sys.dm_db_stats_properties
        • sys.dm_db_stats_properties
          Link
      • sys.dm_db_stats_histogram ( Transact SQL )
        • sys.dm_db_stats_histogram
          Link