max data types performance
On some occasions, migrating from text, ntext or image to varchar(max), nvarchar(max) or varbinary(max) can hurt performance.
How is that possible?
Well, when using text, ntext and image, SQL Server’s default behavior is to keep the LOB data outside of the row, and it just adds each row a 16 byte pointer that references to the place where the relevant LOB information is stored.
When using max fields, the default is the opposite. Up to a size of 8000 bytes, SQL Server keeps LOB data inside the row, and not outside of it, which makes the row much wider. As a result, when performing clustered index seekscan, there is a need to read much more pages at the leaf level of the index, and it makes the query run much longer.
To make SQL Server save LOB data outside of the row when working with max fields, execute the following command:
[code lang=”sql”]
exec sp_tableoption N’MyTable’,’large value types out of row’,’ON’
[/code]
For further reference, see: In-Row Data