Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Understandig row length calculation for sparse columns Expand / Collapse
Author
Message
Posted Wednesday, September 12, 2012 12:52 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:23 AM
Points: 43, Visits: 227
Hello,

I hope someone can explain the internals for row length.

We have a 3rd party vendor table with following definition: 7 non-sparse columns and 340 varchar(max) sparse columns:

CREATE TABLE [dbo].[ReportSparseData](
[ReportSparseDataId] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[ReportMainId] [int] NOT NULL,
[ReportYearId] [char](2) NOT NULL,
[UserId] [int] NOT NULL,
[DateAdded] [datetime] NOT NULL,
[DateLstMod] [datetime] NOT NULL,
[ts] [timestamp] NOT NULL,
[C1] [varchar](max) SPARSE NULL,
[C2] [varchar](max) SPARSE NULL,
[C3] [varchar](max) SPARSE NULL,
[C4] [varchar](max) SPARSE NULL,
[C5] [varchar](max) SPARSE NULL,
[C6] [varchar](max) SPARSE NULL,
--

--

--
[C339] [varchar](max) SPARSE NULL,
[C340] [varchar](max) SPARSE NULL,
CONSTRAINT [Pk_ReportSparseData_[ReportSparseDataId] PRIMARY KEY CLUSTERED
(
[ReportSparseDataId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

-------------------------------------------------------------------
Question 1: What would be the row size for this table definition?
-------------------------------------------------------------------

We need to start replicating this table to another environement for reporting. Our publisher, subscriber and distributor are sql server 2008 r2.

When I try to create snapshot for this table, it fails with the following error:
Message: Cannot create a row of size 8875 which is greater than the allowable maximum row size of 8060.
Stack: (Source: MSSQLServer, Error number: 511)

------------------------------------------------------------------------------------------------
Question 2: How do I get transactional replication to work for this table? Is it possible with all columns? --------------------------------------------------------------------------------------------------

Say If every page has row overflow allocation pointer for all columns, then also page size will be (340*24) + 38 bytes = 8198 for the first 7 columns. That still does not add up to 8875 bytes.

I checked the data in the table. All rows have in-line data and no row-overflow allocation despite varchar(max) data type. We added total bytes for all columns for each row in the table. The max row size was 3071 bytes.

------------------------------------------------------------------
Question 3: where does this total 8875 bytes coming from?
------------------------------------------------------------------

I just would like to clear my concepts here on what would be the row size for the table definition I provided? What overheads am I missing in calculation?


We have case opened with Microsoft for over a week now since this table needs to be replicated in prod environemnt in a week from now but they are unable to provide any help on this matter so far. I hope to get some answer here. Please let me know if I am not clear in my question.

Thanks,
Post #1358165
Posted Thursday, September 13, 2012 2:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:23 AM
Points: 43, Visits: 227

Simplifying the post hoping to get answer:

We have a 3rd party vendor table with following definition: 7 non-sparse columns and 340 varchar(max) sparse columns:

CREATE TABLE [dbo].[ReportSparseData](
[ReportSparseDataId] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[ReportMainId] [int] NOT NULL,
[ReportYearId] [char](2) NOT NULL,
[UserId] [int] NOT NULL,
[DateAdded] [datetime] NOT NULL,
[DateLstMod] [datetime] NOT NULL,
[ts] [timestamp] NOT NULL,
[C1] [varchar](max) SPARSE NULL,
[C2] [varchar](max) SPARSE NULL,
[C3] [varchar](max) SPARSE NULL,
[C4] [varchar](max) SPARSE NULL,
[C5] [varchar](max) SPARSE NULL,
[C6] [varchar](max) SPARSE NULL,
--

--

--
[C339] [varchar](max) SPARSE NULL,
[C340] [varchar](max) SPARSE NULL,
CONSTRAINT [Pk_ReportSparseData_[ReportSparseDataId] PRIMARY KEY CLUSTERED
(
[ReportSparseDataId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

-------------------------------------------------------------------
Question 1: What would be the row size for this table definition?
-------------------------------------------------------------------


Post #1358407
Posted Thursday, September 13, 2012 3:21 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:00 AM
Points: 42,774, Visits: 35,871
Varchar(max) Sparse???
That's an incredibly weird definition.

LOB columns take a 16 byte overhead unless null (that's normal, don't need sparse for that). If they're defined sparse but are populated, they take more than that.

Personally I'd suggest removing the SPARSE attribute, that should drop the max row size to somewhere roughly around 5500 bytes (excluding header) assuming all the LOB is out of row.

You won't have row overflow with that. Overflow is for non-MAX variable length data types.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1358421
Posted Monday, September 23, 2013 7:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 11:26 AM
Points: 20, Visits: 54
I recognize this as a CampusVue table.

Did you figure out a workaround for this issue?

Post #1497384
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse