Sql-server – Understanding `dbcc showcontig` Record Size

sql serversql-server-2012

I am comparing two tables. They are:

CREATE TABLE Table1(
    Column1 [bigint] NOT NULL,
    Column2 [VARBINARY](max) NULL,
    Column3 [int] NULL,
    Column4 [DATETIME] NULL,
    Column5 [INT] NULL,
    Column6 [DATETIME] NULL,
    [RowVersionId] [timestamp] NOT NULL,
 CONSTRAINT [PK_BagImage] PRIMARY KEY CLUSTERED (Column1 ASC)
)

This is the second table:

CREATE TABLE Table2(
    Column1 [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    Column2 [BIGINT] NULL,
    Column3 [VARCHAR](1) NOT NULL,
    Column4 [int] NOT NULL,
    Column5 [varchar](11) NOT NULL,
    Column6 [varchar](13) NOT NULL,
    Column7 [datetime] NOT NULL,
    Column8 [bigint] NULL,
    Column9 [bit] NOT NULL,
    Column10 [uniqueidentifier] NOT NULL,
    [RowVersionId] [timestamp] NOT NULL,
 CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED ( Column1 ASC)
)

Neither of them have any non-clustered indexes. They do each have a foreign key, but the query plans for looking them up are lightening fast! (Clustered Index Seek that takes 00:00:00).

When I copy 55K of rows into the first one it takes 45 minutes. When I copy 7.7 million rows into the second it takes 7 minutes.

That all seems to make sense to me. (copying images should take longer, right?)

But I wanted to calculate a rough IO for this operation. So I ran dbcc showcontig on these tables.

Table   MinimumRecordSize  MaximumRecordSize   AverageRecordSize  
Table1        75                  123                81.733
Table2        101                 101                101

So here is where I am getting confused. If the average record size is 20 less for the table with the images in it, then why does it take so very much longer to insert the data? Especially when I am inserting 7 Million rows more into the second table.

Best Answer

When you're storing data that is bigger than what fits into the SQL Server's 8k page, like varbinary(max) or varchar(max), it's stored in an row overflow data structure. It seems showcontig doesn't show this data at all according to books online:

DBCC SHOWCONTIG does not display row-overflow storage information and other new off-row data types, such as nvarchar(max), varchar(max), varbinary(max), and xml.

It seems showcontig is deprecated too. Its replacement sys.dm_db_index_physical_stats can be setup to show storage of "off row" data. SAMPLE or DETAIL seems to show this data best.

Related Question