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:

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.

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:

Source code

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.