Is moving large columns to separate table likely to have significant performance improvement? We have a large table that registers Request and Response, and captures other data that is related to that request. We are looking to improving performance. One of the suggestions is to move these larger columns (request and response) to separate tables so they would only be accessed when they actually need to be read and not every time when a record is being looked up.

Is moving large columns to a separate table likely to yield a significant performance improvement?
It is possible that these large string types are not held beside the other columns currently. See here. A forwarding pointer is held instead and dereferenced as required.

It may still be worth splitting out the columns, however, if


the key is smaller than the forwarding pointer; overall space saving, removes the pointer bytes from the row data.
you ever do select * 1; the large fields will not be pulled off disk
the columns are sparsely populated, written to on a different cadence than the other columns, or would benefit from a different indexing strategy.


Conversely  if the values are not too long (say a few hundred bytes) and accessed in most queries you may see a performance improvement by storing them in-row and avoiding the pointer lookup and secondary IO. This can be controlled through sp_tableoption.

1 Not that you ever would, of course.