Automating DBCC DBTABLE – Obtaining the Disk Sector Size

It’s a best practice to have the disk sector size (also known as the allocation unit size or block size) set to 64kb (see this white paper), so how can you get that info programmatically?

I was recently reading this msdn article on Ghost Records, and it mentioned that you could get the number of ghost records on a page with DBCC DBTABLE… and it also mentioned that you need to be sure that you enable Trace Flag 3604 in order to see the results. So, two things immediately jumped out at me. First, I wanted to look at this to see where the ghost records were located. Secondly, I’ve just written a few articles (here, here, here and here) where I’ve been able to use the “WITH TABLERESULTS” option on the DBCC command to avoid using this trace flag and to provide automation for the process, and I wanted to see if that would work here also.

The good news is that “WITH TABLERESULTS” does indeed work with DBCC DBTABLE. The bad news is that I could not find the ghost record count in the results.

When I was looking for this information, I noted that the results meta-data are identical to the way DBCC PAGE has its output, so this means that the automation processes already developed will work for them. And as I was looking through the results, looking for a ghost record counter, I noticed two interesting fields:

m_FormattedSectorSize 4096
m_ActualSectorSize 512

Hmm, this is showing me the disk Sector Size of each database file. After checking things on a few different systems, it looks like the m_ActualSectorSize is what the sector size is for the disk that the database file is currently on, and the m_FormattedSectorSize appears to be the sector size for when the database was created – and it is copied from the model database, so it appears to be what the disk was like when Microsoft created the model database.

disc sector size

 

Here’s where I answer my question from the first paragraph. After digging through the Object and ParentObject columns, I developed this script to get the current allocation using size (Sector Size) for each drive:

USE master;
GO
IF OBJECT_ID('tempdb.dbo.#DBTABLE') IS NOT NULL DROP TABLE #DBTABLE
CREATE TABLE #DBTABLE (
    ParentObject VARCHAR(255),
    OBJECT       VARCHAR(255),
    Field        VARCHAR(255),
    VALUE        VARCHAR(255));
INSERT INTO #DBTABLE
EXECUTE ('DBCC DBTABLE WITH TABLERESULTS');
 
WITH cte1 AS
(
-- get the objects for the dbt_dbid. Distinct to return only one per database
SELECT  DISTINCT OBJECT
FROM    #DBTABLE
WHERE   Field = 'dbt_dbid'
), cte2 AS
(
-- get the objects related to the dbt_dbid for the m_Startup% field
-- SQL 2005/2008/2008R2 - looking for m_StartupState
-- SQL 2012+ - Looking for m_StartupPhase
-- So use m_Startup%
SELECT  DISTINCT t1.OBJECT
FROM    #DBTABLE t1
JOIN    cte1 ON cte1.OBJECT = t1.ParentObject
WHERE   t1.Field LIKE 'm_Startup%'
), cte3 AS
(
-- get the filepath and sector size for each file
SELECT  fcb_filepath = MAX(CASE WHEN Field = 'fcb_filepath' THEN VALUE ELSE NULL END),
        m_ActualSectorSize = MAX(CASE WHEN Field = 'm_ActualSectorSize' THEN VALUE ELSE NULL END)
FROM    #DBTABLE t1
JOIN    cte2 ON cte2.OBJECT = t1.ParentObject
WHERE   t1.Field IN ('fcb_filepath', 'm_ActualSectorSize')
GROUP BY cte2.OBJECT, t1.OBJECT
)
-- and now get the distinct list of drives and their sector sizes
SELECT  DISTINCT Drive,
        m_ActualSectorSize,
        is_64kb = CASE WHEN m_ActualSectorSize % 65535 = 0 THEN 1 ELSE 0 END
FROM    cte3
CROSS APPLY (SELECT Drive = UPPER(LEFT(fcb_filepath, 2))) ca
ORDER BY Drive;

And here we have yet another way for how a process can be automated by using “WITH TABLERESULTS” on a DBCC command. I think that this one is a particularly good one to show the possibilities – to get this information you have to hit multiple parts of the DBCC results, and repeat it for each file in each database. Doing this by using the 3604 trace flag, finding the appropriate piece and then proceeding on to the piece would be very time consuming to do manually.

Finally, a quick note here: there are better ways of getting the disk sector size – since you can get it with WMI calls, you can get it with PowerShell (or even dos), and there are also command line utilities that will also get you this information. This is just a way to do it from within SQL Server. Note also that this only gets the drives that contain database files on this SQL Server instance – if you are looking for other drives, then this won’t work for you.

Take a look at the other fields that are available in DBTABLE – you just might find another item that you’d like to be able to retrieve.

Please share this

This Post Has 2 Comments

  1. @sqL_handLe

    Hello!
    Good stuff!
    One correction: sector size is not the same as cluster/allocation unit size. Sector size is for communication with the device, most commonly 512bytes or 4k. 4k sector size devices are more recent. Allocation units or cluster size is a filesystem organization unit. Typically 4k or 64k.

    You referenced the following white paper above.
    https://msdn.microsoft.com/en-us/library/dd758814%28v=sql.100%29.aspx
    In the whitepaper ‘fsutil fsinfo ntfsinfo’ example device sector size and filesystem cluster size are both indicated.

    Bytes Per Sector : 512
    Bytes Per Cluster : 65536

    1. Jason Brimhall

      Thanks for correction. It is an important distinction and is clearly noted in the white paper.

      Tracks are divided into sectors. A sector is the minimum chunk of data that can be read or written to a hard drive. Historically, sector size has been fixed at 512 bytes. Newer drives may offer 1 KB, 2 KB, or 4 KB sectors.
      Many engineers will recognize file allocation unit by another name: cluster. Cluster size is determined when the partition is formatted by the operating system (or the user). For example, if the sectors of a hard drive are 512 bytes, a 4 KB cluster has 8 sectors, and a 64 KB cluster has 128 sectors.

Leave a Reply

Related Articles

A toy machine and a microphone on a table Description automatically generated

The Right Tool for the Right Job

I’m looking forward to 2024. From many perspectives, I think we are going to see great advancements, including in technology, that on one hand will make our lives easier, but also make our decisions a bit harder.

Read More »