SQL Server :- DBCC Buffer

Background

Wanted to cover a DBCC Command that is not well documented.

It is “DBCC Buffer“.

I am not covering it cause it stands out so much, but to show a couple of troubleshooting tips that I was not previously aware of till someone else covered it.

DBCC

DBCC means Database Consistency Checker and it has been around for a while now.

Microsoft continues to add more functionality to it.

DBCC HELP

To get a list of available DBCC Commands, please issue “DBCC Help”.

Default

Outline

To have dbcc help show hidden options, please issue dbcc traceon(2588) beforehand.

And, be sure to issue dbcc traceoff(2588) afterwards.

Syntax

dbcc traceon(2588)
with no_infomsgs
go

DBCC HELP ('?')
with no_infomsgs
GO

dbcc traceoff(2588)
with no_infomsgs
go

Output

v2017

Full Set

Syntax


DBCC HELP ('?')
    with no_infomsgs

Output

v2017

Explanation

When we precede dbcc help with dbcc traceon(2588), we get a more complete list.

DBCC BUFFER

To review actually loaded in memory for a particular table and index, we can issue “DBCC Buffer“.

Outline

Here are the steps that we will follow :-

  1. dbcc help(‘buffer’)
  2. dbcc traceon(3604)
    • Allows result of certain dbcc commands to be showed on screen
  3. Prime Memory
    • Read data from targeted table
  4. dbcc buffer
  5. dbcc traceoff(3604)

dbcc help(‘buffer’)

Syntax

dbcc traceon(2588)
with no_infomsgs
go

DBCC HELP ('buffer')
with no_infomsgs
GO

dbcc traceoff(2588)
with no_infomsgs
go

Output

Output – v2017

dbcc traceon(3604)

Syntax


dbcc traceon(3604)
with no_infomsgs

Prime Memory

Sample


select top 1 * from [bible].[dbo].[kjv]

dbcc buffer

Syntax


dbcc buffer
(
      database
    , table
    , numberofbuffers
    , printoption
)
with
      all_errormsgs
    , no_infomsgs
    , maxdop=1

Sample


dbcc buffer
(
      'bible' -- database
    , '[dbo].[kjv]'   -- table
    , 1 -- number of buffers
    , 1 -- printopt
)
with
      all_errormsgs
    , no_infomsgs
    , maxdop=1

Output

Output – v2017

Explanation – v2017

  1. Allocation Status
    • GAM ( Global Allocation Map )
    • SGAM ( Shared Global Allocation Map )
    • PFS ( Page Free Space )
  2. Record Type
    • Primary Record
  3. Record Size
    • We see the size of each record
      • 81 in this case
  4. Memory Dump
    • Actual Memory Dump of database record’s content

dbcc traceoff(3604)

Syntax


dbcc traceoff(3604)
with no_infomsgs

Summary

As always, nada.

Hopefully, you will need to issue “dbcc buffer“.

I wanted to familiarize myself with dbcc help as a gateway to reviewing other dbcc commands.

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s