For DB professionals that look for having optimal setting of the databases they care, this script provide a way to quick check for tables that have big columns (XML, VARCHAR(MAX), NVARCHAR(MAX),...) and that are not using the feature Large Value Types Out Of Row, available in MSSQL since 2008R2.

It is recommended setting this option to ON for tables in which most statements do not reference the large value types columns. Storing these columns out-of-row implies that more rows can be fit per page, therefore reducing the number of I/O operations and CPU required to scan the table.

Enabling this feature should be tested in lower envinronments before using in a Production Database.

SQL
Edit|Remove
/* 
  File:     LOB_cols_out_of_the_row.sql 
 
  SQL Server Versions: 2008R2 onwards 
 
  Written by Pedro Bonilla 
  For more scripts and sample code, check out https://bonisql.wordpress.com/    
   
  This code and information are provided "as is" without warranty of  
  any kind, either expressed or implied, including but not limited  
  to the implied warranties of merchantability and/or fitness for a 
  particular purpose. 
*/ 
 
USE AdventureWorks2014-- Use your DB name here :) 
GO 
 
-- This query return tables with columns that are not using the LARGE_VALUE_TYPES_OUT_OF_ROW feature 
SELECT s.[nameAS schemaName 
        , t.[nameAS tableName 
        , bigCols.colName 
        , bigCols.typeName 
        , bigCols.max_length  
        , t.large_value_types_out_of_row 
    FROM sys.tables AS t 
        INNER JOIN sys.schemas AS s ON t.[schema_id] = s.[schema_id] 
        INNER JOIN (SELECT [object_id], col.[nameAS colNamety.[nameAS typeNamecol.max_length 
                            FROM sys.columns AS col 
                                INNER JOIN sys.types AS ty ON col.system_type_id = ty.system_type_id  
                                    AND col.user_type_id = ty.user_type_id 
                            WHERE col.max_length >= 8000  
                                OR col.max_length < 1 
                        ) AS bigCols ON t.[object_id] = bigCols.[object_id] 
    ORDER BY schemaName 
        , tableName 
        , colName;
 

Hope that helps,

Pedro Bonilla.