0

I have a table with 100 columns and I know there is an 8k limit before a table overflows in SQL Server

I'm using the following query's to have a look at the table stats

dbcc showcontig ('Products') with tableresults

and

SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(N'tablename'), OBJECT_ID(N'Products'), NULL, NULL , 'DETAILED'

I notice, with the second query as follows:

index_type_desc,    alloc_unit_type_desc, page_count
CLUSTERED INDEX,    LOB_DATA            , 2

If I am reading it correctly there is only 2 pages of overflow?

MinimumRecordSize,  MaximumRecordSize,  AverageRecordSize
628,                7928,               950.048

Is there further metrics to say (in a more concise way)? If I really need to go to the extra effort of vertically partitioning this table?

At the moment, it looks like i am no where near the 8k column limit for native column size, and the majority of my data is all in Row Data

Is it a fair assumption to say query performance should not be impacted all that much?

P.S.: I know this is a long question, however some good concise metrics would be a good start for me

1
  • LOB_DATA <> row overflow data. This means your table definition has one or more of the following types: deprecated text,ntext or image or varchar(max), nvarchar(max), varbinary(max) or xml. You see only 2 pages because it's probably varchar(max) and the values were fitted into a row, so there is only IAM page and 1 allocated page of LOB data
    – sepupic
    Feb 5, 2018 at 9:31

1 Answer 1

1

You should consider vertical partitioning of your table if your columns have different access pattern: some columns are often read and (almost) never updated, others are often updated.

Unless you have columnstore indexes or covering indexes for your queries, every time you need only some columns a whole row is retrived, so if you want namy rows but for limited columns vertical partitioning may be your case.

Please also take a look on this: Vertical Partitioning as the way to reduce IO

Note that MinimumRecordSize 628 + MaximumRecordSize 7928 means that your rows have fields of variable length data types such as varchar(max), and this means that you did not reach max row size. If MinimumRecordSize was equal to MaximumRecordSize this could mean that you may will not be able to add othe columns to your table, but since MinimumRecordSize is only 628, many other variable length columns can still be added.

When row-overfloing data does not fit in a row, a newe page is allocated to accomodate it and only a small pointer is left on original row that points to row-overflow page containing the value.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.