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