SQL Server – Clone Database – Schema & Statistics

Background

Working with my best friend to troubleshoot a non-performant query and so here we go.

Scenario

The database is too big and contains data that should be kept private and so what to do.

DBCC Clone

Outline

  1. Source SQL Instance
    • dbcc  clone
    • Backup resulting database
  2. Destination SQL Instance
    • Restore database
  3. Compare Queries

Source SQL Instance

DBCC Clone

SQL

DBCC CLONEDATABASE 

	(
		  [WideWorldImportersDW]
		, [WideWorldImportersDW_DBCCClone]
	);   

GO
GO

Database Backup

SQL

exec master..xp_create_subdir 'E:\temp'
go

backup database [WideWorldImportersDW_DBCCClone]
to disk = 'E:\temp\WideWorldImportersDW_DBCCClone.bak'
with init, format, stats=1 

GO

Destination SQL Instance

Database Restore

SQL

USE [master]
go

exec master..xp_create_subdir 'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone'
go

exec master..xp_create_subdir 'C:\Microsoft\SQLServer\Logfiles\WideWorldImportersDW_DBCCClone'
go

RESTORE DATABASE [WideWorldImportersDW_DBCCClone] 

FROM  DISK = N'C:\Temp\WideWorldImportersDW_DBCCClone.bak' WITH  FILE = 1

	,  MOVE N'WWI_Primary' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_167133806.mdf'
	,  MOVE N'WWI_UserData' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_UserData_1341343279.ndf'
	,  MOVE N'fg_Partition_Year_Base__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_Base_file_001_4162930605.ndf'
	,  MOVE N'fg_Partition_Year_2010__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_2010_file_001_3028494415.ndf'
	,  MOVE N'fg_Partition_Year_2011__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_2011_file_001_474007300.ndf'
	,  MOVE N'fg_Partition_Year_2012__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_2012_file_001_3961387451.ndf'
	,  MOVE N'fg_Partition_Year_2013__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_2013_file_001_2554884830.ndf'
	,  MOVE N'fg_Partition_Year_2014__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_2014_file_001_3195214954.ndf'
	,  MOVE N'fg_Partition_Year_2015__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_2015_file_001_2796606126.ndf'
	,  MOVE N'fg_Partition_Year_2016__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_2016_file_001_2922730419.ndf'
	,  MOVE N'fg_Partition_Year_2017__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_2017_file_001_89564842.ndf'
	,  MOVE N'fg_Partition_Year_2018__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_2018_file_001_2594545631.ndf'
	,  MOVE N'fg_Partition_Year_2019__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_2019_file_001_102789485.ndf'
	,  MOVE N'fg_Partition_Year_2020__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_2020_file_001_2559250543.ndf'
	,  MOVE N'fg_Partition_Year_Null__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_Null_file_001_3677708393.ndf'
	,  MOVE N'fg_Partition_Year_Next__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_Next_file_001_2414564952.ndf'
	,  MOVE N'WWI_Log' TO N'C:\Microsoft\SQLServer\Logfiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_3476128794.ldf'
	,  NOUNLOAD
	,  STATS = 5

GO

Compare Queries

Outline

  1. Compare Queries
    • Query 1

Compare Queries

Query 1

SQL
set nocount on
go

set statistics io on
go

declare @dateTS datetime
declare @date datetime

set @dateTS = getdate()
set @date = convert(varchar(10), @dateTS, 23)

select
          @dateTS as [@dateTS]
        , @date as [@date]

select top 10 *

from   [Dimension].[Date] tblD

select top 10 *

from   [Dimension].[Date] tblD

where  tblD.[Date] = @date
Output
Output – Source

query1_Source_20181115_0911AM

Output – Destination

query1_Destination_20181115_0912AM

Explanation
  1. We have data from original source
  2. And, not data from the resultant cloned database

 

Query Plan
Query Plan – Destination

queryPlan_Destination_20181115_0853AM

Query Plan – Source

queryPlan_Source_20181115_0905AM.PNG

Statistics IO
Image – Destination

statisticsIO.Destiation.2018115.0855AM

Image – Source

statisticsIO.Source.2018115.0856AM

Explanation
  1. Statistics IO
    • Scan Counts will match
    • Logical & Physical Reads will not
      • As Source has actual data, but destination does not

 

References

  1. Microsoft
    • Docs / SQL / T-SQL / Database Console Commands
      • DBCC CLONEDATABASE (Transact-SQL)

 

SQL Server – Database Files – Minimum File Size Via “DBCC FILEHEADER”

Background

In our last post, we spoke of using “DBCC PageHeader” to fetch minimum file sizes.

And, as closing, we touched on the fact that the code pasted does not work for log files, just data files.

Code

Syntax


dbcc fileheader
(
      {'dbname' | dbid}
    , filenum

)

Explanation

  1. DBCC FileHeader
    • Database
    • FileID

SQL


set nocount on;
go

set XACT_ABORT on
go

declare @database		    sysname
declare @dbid			    int

declare @fileID			    int
declare @fileIDAsString		varchar(255)

declare @fileSymbolicName   sysname
declare @filePhysicalName	nvarchar(600)

declare @sql				nvarchar(255)

declare @FORMAT_SQL			varchar(255)

declare @tblFileHeader Table
(

      [RecoveryUnitID]			smallint
    , [FileId]					smallint
    , [LogicalName]				sysname
    , [BindingID]				uniqueidentifier
    , [FileGroup]				smallint

    , [Size]					bigint
    , [MaxSize]					bigint
    , [MinSize]					bigint
    , [UserShrinkSize]			varchar(255)

    , [Growth]					varchar(255)

    , [BackupLSN]				decimal(38, 0)
    , [RedoStartLSN]			decimal(38, 0)
    , [FirstLSN]				decimal(38, 0)
    , [MaxLSN]					decimal(38, 0)
    , [FirstUpdateLSN]			decimal(38, 0)
    , [CreateLSN]				decimal(38, 0)

    , [SectorSize]				smallint
    , [ActualSectorSize]		smallint

    , [RecoveryForkGUID]		uniqueIdentifier
    , [RecoveryForkLSN]			decimal(38, 0)

    , [DifferentialBaseLSN]		decimal(38, 0)
    , [DifferentialBaseGuid]	uniqueidentifier

    , [Status]					smallint
    , [RestoreStatus]			smallint

    , [ReadOnlyLsn]				decimal(38, 0)
    , [ReadWriteLsn]			decimal(38, 0)
    , [MaxLsnBranchId]			uniqueIdentifier

    , [RedoTargetPointLsn]		decimal(38, 0)
    , [RedoTargetPointGuid]		uniqueIdentifier

    , [RestoreDiffBaseLsn]		decimal(38, 0)
    , [RestoreDiffBaseGuid]		uniqueIdentifier

    , [RestorePathOriginLsn]	decimal(38, 0)
    , [RestorePathOriginGuid]	uniqueIdentifier

    , [OldestRestoreLsn]		decimal(38, 0)

    , [sizeInKB]
        as (
                [Size] * 8
            )

    , [sizeInMB]
        as
             (
                [Size] * 8
             )
             / ( 1024)

    , [MinSizeInMB]
        as
             (
                [MinSize] * 8
             )
             / ( 1024)

)

set @FORMAT_SQL = 'DBCC FILEHEADER(''%s'',%s) with tableresults, no_infomsgs';

-- exec sp_helpdb 'skySync'
set @database = 'skySync';

set @fileID = 1
set @fileID = 2

set @dbid = db_id(@database);

select
          @fileSymbolicName = tblSMF.[name]
        , @filePhysicalName = tblSMF.physical_name

from   sys.master_files tblSMF 

where  tblSMF.database_id = @dbid

and    tblSMF.[file_id] = @fileID

set @fileIDAsString = cast(@fileID as varchar(10));

exec master..xp_sprintf
            @sql output
        , @FORMAT_SQL
        , @database
        , @fileIDAsString

DBCC TRACEON(3604) with no_infomsgs;

print @sql

insert into @tblFileHeader
exec(@sql)

-- exec sp_helpdb [model]
select
          [database] = @database
        , [fileSymbolicName] = @fileSymbolicName
        , [filePhysicalName] = @filePhysicalName

        , tblFH.[Size]
        , tblFH.[SizeInMB]

        , tblFH.[MinSize]
        , tblFH.[MinSizeInMB]

from   @tblFileHeader tblFH

DBCC TRACEOFF(3604) with no_infomsgs

Output

skySync_20180711_0140PM

Credits

Crediting sqllity

DBCC FILEHEADER: Reading the File Header Page
Link

SQL Server – Database Files – Minimum File Size

Background

There is a setting that is unfortunately difficult to determine within Transact SQL.

That setting is the smallest size a data or log file can be shrunk down to.

GUI

SQL Server Management Studio ( SSMS )

Database Property

Image

bigfix_Enterprise_initialSizes_20180711_1119AM

Code

Syntax


dbcc page
(
      {'dbname' | dbid}
    , filenum
    , pagenum
    [, printopt={0|1|2|3} ]
)

Explanation

  1. DBCC PAGE
    • Database
    • FileID
    • Pagenum
      • 0
    • printopt
      • 3 – page header plus detailed per-row interpretation

SQL

Data files

Code


set nocount on;
go

set XACT_ABORT on
go

declare @database		    sysname
declare @dbid			    int

declare @fileID			    int
declare @fileIDAsString		varchar(255)

declare @fileSymbolicName   sysname
declare @filePhysicalName	nvarchar(600)

declare @sql				nvarchar(255)

declare @FORMAT_SQL			varchar(255)

declare @tblDBCCPage Table
(

	  [parentObject] sysname not null
	, [object]		 sysname not null
	, [field]		 sysname not null
	, [value]		 varchar(255) null	

	, [computedInKB]
		as case
				when ([field] like '%size%') then
						[value] * 8

				else null
		  end

	, [computedInMB]
		as case
				when ([field] like '%size%') then
						(
							[value] * 8
						)
						/ ( 1024)

				else null
		  end
)

set @FORMAT_SQL = 'DBCC PAGE(''%s'',%s,0,3) with tableresults, no_infomsgs';

set @database = 'BFEnterprise';

set @fileID = 1
set @fileID = 1
set @dbid = db_id(@database);

select
		  @fileSymbolicName = tblSMF.[name]
		, @filePhysicalName = tblSMF.physical_name

from   sys.master_files tblSMF 

where  tblSMF.database_id = @dbid

and    tblSMF.[file_id] = @fileID

set @fileIDAsString = cast(@fileID as varchar(10));

if (@fileID is not null)
begin

	DBCC TRACEON(3604) with no_infomsgs;

	exec master..xp_sprintf
			  @sql output
			, @FORMAT_SQL
			, @database
			, @fileIDAsString

	print @sql

	insert into @tblDBCCPage
	(
		  [parentObject]
		, [object]
		, [field]
		, [value]
	)
	exec(@sql)

	-- exec sp_helpdb [model]
	select
		      [database] = @database
			, [fileSymbolicName] = @fileSymbolicName
			, [filePhysicalName] = @filePhysicalName
			, [parentObject]
			, [object]
			, [field]
			, [value]
			, [computedInKB]
			, [computedInMB]

	from   @tblDBCCPage tbl

	where  (
			( field in
				(
					'MinSize'
					, 'Size'
				)
			)

	DBCC TRACEOFF(3604) with no_infomsgs

end -- if (@fileID is not null)

Output

bigfix_Enterprise_Code_20180711_1130AM.png

Log File

Explanation

Unfortunately, same does not work for log files.

You will get the error pasted below.

Output


DBCC PAGE('BFEnterprise',2,0,3) with tableresults, no_infomsgs
Msg 8939, Level 16, State 98, Line 6
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID -1585190943824412672 (type Unknown), page (0:0). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 2057 and -1.
Msg 8909, Level 16, State 1, Line 6
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 13792724832485376 (type Unknown), page ID (2:3) contains an incorrect page ID in its page header. The PageId in the page header = (102:7274528).
Msg 8909, Level 16, State 1, Line 6
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 12947986374328320 (type Unknown), page ID (2:6) contains an incorrect page ID in its page header. The PageId in the page header = (117:7077985).
Msg 8909, Level 16, State 1, Line 6
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 9007714657435648 (type Unknown), page ID (2:7) contains an incorrect page ID in its page header. The PageId in the page header = (32:7536756).
Msg 8939, Level 16, State 98, Line 6
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 7036874954637312 (type Unknown), page (0:0). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 2057 and -14.

<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;"></span>

Crediting

Edward Dortland
File Header Properties
Link

Summary

Unfortunately, the word size is imprecise and thus need to be surrounded to facilitate context.

When Database properties are viewed through SSMS, for each data and log file, there is a number identified as “Initial Size“.

I think one has to be careful and read more into what it means and how it is sourced.

References

  1. Edward Dortland
    • Stack Overflow
      • File Header Properties
        Link
  2. Paul Randal
    • Server & Tools Blogs > Data Platform Blogs > SQL Database Engine Blog
      • How to use DBCC PAGE
        Link

SQL Server – DBCC Info

Background

A few posts back we spoke about “SQL Server – Mapping DBCC Commands to Dynamic Management Views ( DMV )“.

That post is here.

 

DBCC DBINFO

Introduction

DBCC DBINFO offers concise metadata at the database level.

 

SYNTAX

Its syntax is elementary and here it is “DBCC dbinfo with tableresults“.

 

Sample

ROWS

Display result as rows

Query


dbcc dbinfo with tableresults, no_infomsgs

Output

 

COLUMNS

Query



set nocount on
go

set XACT_ABORT on
go


declare @tblDBInfo TABLE
(
      [parentObject] sysname not null

    , [object]		 sysname not null

    , [field]		 sysname not null

    , [sequenceNbr]  smallint 
                     identity(1,1) not null

    , [value]        nvarchar(4000) not null

    , primary key
        (
              [parentObject] 
            , [object]		 
            , [field]		 
            , [sequenceNbr]
        )
)

declare @servername sysname
declare @dbid		int
declare @dbname		sysname

set @servername = cast(
                        serverproperty('servername')
                        as sysname
                      )

set @dbid = db_id()

set @dbname = db_name()

insert into @tblDBInfo
(
      [parentObject]
    , [object]		
    , [field]		
    , [value]       
)
exec ('dbcc dbinfo with tableresults, no_infomsgs')

select *

from   @tblDBInfo

order by 
        [field] asc

; with cteExclusion
(
      [dbid]
    , [Field] 
)
as
(
    select 
           2 as [dbid]
        , 'dbi_dbccLastKnownGood' as [Field]

)
, cteDBInfo
(
      [servername]
    , [dbid]
    , [Field]
    , [VALUE]
)
as
(

    select 
              @servername
            , @dbid
            , tblDBI.Field
            , tblDBI.[VALUE]

    from   @tblDBInfo tblDBI

    where  tblDBI.Field in
            (
                  'dbi_dbccLastKnownGood'
                , 'dbi_version'
                , 'dbi_createVersion'
                , 'dbi_LastLogBackupTime'
                , 'dbi_crdate'
                , 'dbi_dbname'
                , 'dbi_dbid'
                , 'dbi_cmptlevel'
            )


    and not exists
        (
            select 1
            from   cteExclusion cteEX
            where  cteEX.[dbid] = @dbid
            and    cteEX.Field = tblDBI.[Field]   
        )


)

, ctePivot
as
(
    select 
              cteDBI.[serverName]
            , cteDBI.[dbid]
            , cteDBI.[dbi_dbccLastKnownGood]
            , [dbi_version]
            , [dbi_createVersion]
            , [dbi_LastLogBackupTime]
            , [dbi_crdate]
            , [dbi_dbname]
            , [dbi_dbid]
            , [dbi_cmptlevel]	

    from   cteDBInfo cteDBI

    PIVOT
        (
            MAX(VALUE)
    
            FOR [Field] in
            (
                  [dbi_dbccLastKnownGood]
                , [dbi_version]
                , [dbi_createVersion]
                , [dbi_LastLogBackupTime]
                , [dbi_crdate]
                , [dbi_dbname]
                , [dbi_dbid]
                , [dbi_cmptlevel]
            )

        ) cteDBI

)

select 
          cteDBI.[servername]
        , cteDBI.[dbid]
        , cteDBI.[dbi_dbccLastKnownGood]
        , [dbi_version]
        , [dbi_createVersion]
        , [dbi_LastLogBackupTime]
        , [dbi_crdate]
        , [dbi_dbname]
        , [dbi_dbid]
        , [dbi_cmptlevel]

from   ctePivot cteDBI


Output

Summary

DBCC DBINFO has a wealth of information.

Nuggets include:

  1. Database Attributes
    • Compatibility level
    • Collation
    • Containment
    • Create Date
    • Database ID ( dbid )
    • Database name ( dbname )
    • MirrorId ( Mirrored database )
    • familyGUID ( Restored DB & Snapshot )
    • Safety ( Mirrored database )
    • Database Version
    • Database Create Version
  2. Operation Tracking
    • Last time dbcc checkdb was ran
    • Last time Transaction Log backup was taken

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

DBCC MemoryStatus

Background

Wanted to place another stone in our pond of water where we review Memory Usage in MS SQL Server Instance.

DBCC MemoryStatus

Sample Output

Text File

Sample content when we run “dbcc memusage” and capture the output into a text file.

Notepad++

Here is what things look like in Notepad++.

 

Others Work

Wanted to see how others have consumed “dbcc memusage“.

Slava Murygin

Took to the Internet and found a nice post by Slava Murygin.

It is here.

 

Revision

Here is a revised version…



SET NOCOUNT ON
GO


declare @tblMemoryStatusDump TABLE
(
      [id] INT IDENTITY(1,1) PRIMARY KEY
    , [dump] VARCHAR(600)

);


declare @tblMemoryStatus TABLE
(
     id			INT
   , [measure]  VARCHAR(200) 
   , [counter]  VARCHAR(300) 
   , [value]	VARCHAR(200)
   , [rowType]  char(1)
   , [dump]		varchar(600) 

);

declare @serverName  sysname
declare @queryFormat nvarchar(600)
declare @query       nvarchar(600)

declare @tblMemoryStatusHeader TABLE
(
    [seqNumber] smallint not null
        identity(1,1)

    , [id]   int not null
    
    , [dump] nvarchar(600) not null

    , [rangeStart] int null
    , [rangeEnd]   int null

)

set @queryFormat = 'xp_cmdshell ''sqlcmd -E -S %s -Q "DBCC MEMORYSTATUS" ''';

/*

    sys.xp_cmdshell
    Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1 [Batch Start Line 19]
    SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' 
    because this component is turned off as part of the security configuration for this server. 
    A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. 
    For more information about enabling 'xp_cmdshell', search for 'xp_cmdshell' in SQL Server Books Online.

*/

/*

    exec sp_configure 'show advanced options',1
    reconfigure with override;

    exec sp_configure 'xp_cmdshell',1
    go

    reconfigure with override;

*/


set @serverName = cast (serverproperty('servername') as sysname)
exec master..xp_sprintf
          @query output
        , @queryFormat
        , @serverName

print @query

INSERT INTO @tblMemoryStatusDump
(
    [dump]
)
exec(@query)


insert into @tblMemoryStatus
(
       [id]
     , [dump]
     , [counter] 
     , [value] 

)
select 
          tblMSD.[id]

        , tblMSD.[dump]
        
        , [counter]
            = substring
                (
                    tblMSD.[dump]
                    , 1
                    , 42
                )

        , [value]
            = ltrim(rtrim
              (
                substring
                    (
                        tblMSD.[dump]
                        , 43
                        , 20
                    )
              ))

from   @tblMemoryStatusDump tblMSD

update tblMSD

set	
            
       [rowType] 
            = case
                when tblMSDNext.[dump] like '---%' then 't' 
                when tblMSD.[dump] like '---%' then 'd' 
                when tblMSD.[dump] like '(%rows affected)' then 'c' 
                when tblMSD.[dump] like 'DBCC execution completed%' then 'i' 
                when tblMSD.[dump] is null then '0'
                else 'v'
              end
                 
from   @tblMemoryStatus tblMSD

inner join @tblMemoryStatus tblMSDNext

        on tblMSD.[id] + 1  = tblMSDNext.[id]


insert into @tblMemoryStatusHeader
(
      [id] 
    , [dump]
)
select 
      tblMSD.[id]
    , [dump]
        = substring
                (
                    tblMSD.[dump]
                    , 1
                    , 41
                )		

from   @tblMemoryStatus tblMSD

where  tblMSD.[rowType] = 't'

/*
    Get Range
*/
update tblMSH

set	
          [rangeStart] = tblMSH.[id]

        , [rangeEnd] = tblMSHNext.[id]

from   @tblMemoryStatusHeader tblMSH

inner join @tblMemoryStatusHeader tblMSHNext

        on tblMSHNext.[seqNumber] = tblMSH.seqNumber + 1

/*
    Get Range for last record
*/
update tblMSH

set		[rangeEnd] = ( select max([id]) from @tblMemoryStatus tblMSD )

from   @tblMemoryStatusHeader tblMSH

where   rangeEnd is null

/*
    Based on Range, set measure column
*/
update tblMSD

set			
       [measure] = tblMSH.[dump]
                 
from   @tblMemoryStatus tblMSD

inner join @tblMemoryStatusHeader tblMSH

        on tblMSD.[id] between tblMSH.rangeStart and tblMSH.rangeEnd

where tblMSD.[rowType] = 'v'


/*
    remove extra record
*/
delete tblMS
 
from  @tblMemoryStatus tblMS

where tblMS.[rowType] in 
        ( 
              'd'
            , 'c'
            , '0'
            , 'i'
        )

select  
        tblMS.*

from   @tblMemoryStatus tblMS

where  tblMS.rowType in ( 'v')


 

Dedicated

Dedicated to Slava Murygin.

SQL Server – Database Restore Steps thru lenses of errorlog

Introduction

Wanted to document database restore steps as seen from trail left in SQL Server Instance’s errorlog file.

Restore

Restore – Full Database

Step Sub Task Logged Explanation

Start Database (Database started in boot-strap mode )
Starting up database ‘csAnalytics’.

Recovery or NO Recovery
NO Recovery
The database ‘csAnalytics’ is marked RESTORING and is in a state that does not allow recovery to be run. Indicate that we are restoring and will not proceed to actual database recovery; as we would have done during normal SQL Server Instance start.
Recovery
Recovery is writing a checkpoint in database ‘csAnalytics’ (9). This is an informational message only. No user action is required. Database restore wrote a checkpoint ledger to indicate progress.
2018-03-20 05:46:51.110    spid70    Recovery completed for database rbpivr1 (database ID 42) in 12 second(s) (analysis 5444 ms, redo 20 ms, undo 5171 ms.) This is an informational message only. No user action is required. Informational in terms of duration.

Here is what is logged – Database name, Assigned Database ID, and total time.

Time breakdown – Analysis, redo, undo.

2018-03-20 05:46:51.550    spid70    Filegroup fgLOB in database rbpivr1 is unavailable because it is Offline. Restore or alter the filegroup to be available. In the case of partial restores, the file groups implicitly excluded.
2018-03-20 11:10:58.790    Backup    Database was restored: Database: DBMirror, creation date(time): 2018/03/05(14:52:06), first LSN: 35:341:37, last LSN: 35:360:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘C:\Microsoft\SQLServer\Backup\DBMirror.bak’}). Informational message. No user action required. Informational in terms of statistics.

Data logged includes name given to target database, creation time, LSN, number of dump/backup devices, and backup device names.


In-Memory Database ( Hekathon )
[INFO] HkCheckpointCtxtImpl::StartOfflineCkpt(): Database ID: [42]. Starting offline checkpoint worker thread on a hidden SOS scheduler. Hekathon Offline Checkpoint
Check Database
CHECKDB for database ‘csAnalytics’ finished without errors on 2011-08-03 00:06:43.420 (local time). This is an informational message only; no user action is required. Minimal Database Consistency ran.
Resource DB Alignment
2018-03-20 05:46:51.950    spid70    Synchronize Database ‘rbpivr1’ (42) with Resource Database. Database system objects aligned with Instance’s Resource DB.

Especially important when database backup file restored from earlier SQL Server version.


Restore Complete
Restore is complete on database ‘rbpivr1’.  The database is now available. Restore Completed

Database Setting
Setting database option MULTI_USER to ON for database csAnalytics. Database transitioned from Single to Multi User

 

Restore – Transaction Log

Logged Explanation
Log was restored. Database: DBMirror, creation date(time): 2018/03/05(14:52:06), first LSN: 35:341:37, last LSN: 35:422:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘C:\Microsoft\SQLServer\Backup\DBMirror.trn’}). This is an informational message. No user action is required. Conveys that Transaction Log backup file was applied to specified database.  It lets us in on the starting and ending LSN.  And, the name of the transaction backup file.

Summary

Data logged in the SQL Server Instance log is quite useful in gleaning what is occurring behind the scene when a database is restored.

Comparing logs from different SQL Server Instances and Database setup affords familiarity with how various database setup and restore options is considered and acted upon by the DB restore facility.

As summary, the code will consider:

  1. Will the database be recovered or left in no_recovery
    • No recovery basically means subsequent transaction logs will be applied
  2. Fast Database Consistency check occurs
  3. Redo and Undo Log Processing
    • Logs contain both redo and undo portions
      • Redo
        • Once database is fully restored, committed transactions that are still in the transaction log, but have yet to be checkpoint into the database data files are applied to restored database datafiles
      • Undo
        • Undo means that data is not committed and still in flight.
        • The data can later be committed ( commit tran ) or discarded ( rollback )
  4. Resource DB Alignment
    • When a database from a previous version of SQL Server is restored unto a newer version the restored db system dictionary has to be brought up to date
  5. In Memory Database
    • In-Memory tables and programmable objects are read from database files into memory