Post reply

t-sql for which columns spilled to row_overflow_data

  • September 1, 2015 at 11:04 am

    #304028

    In addition to wishing to know how to query for a list of columns which got stored in row_overflow_data from a particular table,

    my ultimate question is how SQL Server determines which columns to send to row_overflow_data...

    Does it try to fit as many columns as possible onto the main row page, regardless of column order? OR, does it push all subsequent columns after the initial overflow column into overflow?

    e.g., for a table with 3 varchar(max) columns, an INSERT into myTable(col1, col2, col3), according to column order, has say, 4000 bytes of data in col1, 5000 bytes in col2, and 20 bytes in col3. Where does col3 get stored?

  • September 2, 2015 at 7:40 am

    #1823985

    Here are some links that may help.

    Interestingly, when I ran sys.dm_db_index_physical_stats on one table that had a ton of VARCHAR(1000) columns, the report indicated nothing in the overflow.

    So this does seem to answer the question regarding whether or not it will make much difference performance-wise if you trim you VARCHAR columns to the optimal width.

    If your table is not showing overflow, then it appears it is not worth the effort to trim the columns.

    At least, that's how I read this.

  • September 2, 2015 at 9:46 am

    #1824052

    Thanks - that's some great info to chew on! (I'm only seeing 1 link..?)

    However, it doesn't quite seem to answer my question on the mechanics of how it maximizes storage on the main data page - i.e., at what point does SQL Server decide to overflow, does it do any column pre-sorting or post-sorting once an overflow column is identified (to maximize columns on the main data page)... and the link doesn't seem to offer a means of capturing the overflow column names beyond potentially parsing the memory dump from dbcc page...

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply