September 12, 2012 at 12:52 pm
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,
September 13, 2012 at 2:52 am
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?
-------------------------------------------------------------------
September 13, 2012 at 3:21 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 23, 2013 at 7:41 am
I recognize this as a CampusVue table.
Did you figure out a workaround for this issue?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply