If you must load LOBs, make sure that you’ve used set the table option to store the LOBs out of row. I’ve recently done some testing that shows (at least for the test data I built) that INSERTs of LOBs with that option turned on make the INSERTs about 19.6% faster and it doesn’t hurt reads either. It fact, it makes the reads of the LOB data (I used a LIKE search) an average of 3.9% faster for the test data I used.
Of course, forcing LOBs to be out of row so that the stuff that could fit won’t end up in-row will also help your non-LOB selects sing because they won’t have to read larger unused LOB data. As a bit of a sidebar and major benefit, the option also took the Clustered Index rebuilds for the test data I used down from 7500 milliseconds down to 185 milliseconds not to mention the huge savings in Logical Reads for queries that don’t address the LOB data.
Here’s the code to set the option for a given table.
EXEC sp_tableoption ‘dbo.SomeTable’,’large value types out of row’,1;
Please DO read up on the use of sp_tableoption for tables that already have data in them. There are extra steps required to force existing in-row lobs out-of-row. (An “in-place” update must be done).
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
"If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
"Change is inevitable... change for the better is not."
When you put the right degree of spin on it, the number 3|8
is also a glyph that describes the nature of a DBAs job. 😉
How to post code problems