Programmatically find indexes that cannot be rebuilt online

Question

I am automating rebuild and reorganise indexes using T-SQL. I run into problems with indexes that cannot be rebuilt online. Primarily this happens because ntext/nvarchar columns are included.

Is there a way to programmatically identify them as part of this statement? So kind of addition to the WHERE predicate

SELECT 
--TOP 20
OBJECT_NAME(IPS.OBJECT_ID) AS [TableName], avg_fragmentation_in_percent, SI.name         [IndexName],  
schema_name(ST.schema_id) AS [SchemaName], 0 AS IsProcessed 
INTO #FramentedTableList 
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL , NULL) IPS 
JOIN sys.tables ST WITH (nolock) ON IPS.OBJECT_ID = ST.OBJECT_ID 
JOIN sys.indexes SI WITH (nolock) ON IPS.OBJECT_ID = SI.OBJECT_ID AND IPS.index_id =   SI.index_id 
WHERE ST.is_ms_shipped = 0 AND SI.name IS NOT NULL 
AND avg_fragmentation_in_percent >= CONVERT(DECIMAL,   @FragmentationThresholdForReorganizeTableLowerLimit) 
ORDER BY avg_fragmentation_in_percent DESC 

Answer 1

You can add this to your WHERE clause to leave out any indexes that include a LOB type (I think I captured them all):

AND NOT EXISTS (
  SELECT 1 FROM sys.index_columns AS ic
  INNER JOIN sys.columns AS c
  ON ic.object_id = c.object_id
  AND ic.column_id = c.column_id
  AND ((c.system_type_id IN (34,35,99,241)) -- image, text, ntext, xml
   OR (c.system_type_id IN (167,231,165) -- varchar, nvarchar, varbinary
       AND max_length = -1))
  WHERE ic.object_id = SI.object_id
  AND ic.index_id = SI.index_id
)

If you're using SQL Server 2008 or 2008 R2, you can change the type search to the following in order to capture geography and geometry (credit Kenneth Fisher):

  AND ((c.system_type_id IN (34,35,99,241)) -- image, text, ntext, xml
   OR (c.system_type_id IN (167,231,165)    -- varchar, nvarchar, varbinary
       AND max_length = -1)
   OR (c.user_type_id IN (129,130)))        -- geometry, geography

Of course, it may be smarter to restructure your query so that you rebuild the indexes you can online, and don't leave those that you can't out of your maintenance altogether. But I agree with Max, don't reinvent the wheel. Go get Ola's scripts or, if you want something much more robust, please let me know.

Answer 2

I use the following to detect LOBs:

SET @LOBCount = (
    SELECT COUNT(*) FROM (
        select t.name as TableName, i.name as IndexName, c.name as ColumnName, ty.name, ic.is_included_column
            from [' + @DatabaseName + '].sys.indexes i 
            INNER JOIN [' + @DatabaseName + '].sys.tables t ON t.object_id = i.object_id 
            inner join [' + @DatabaseName + '].sys.index_columns ic on i.object_id = ic.object_id and i.index_id = ic.index_id
            inner join [' + @DatabaseName + '].sys.columns c on ic.object_id = c.object_id and ic.column_id = c.column_id
            INNER JOIN [' + @DatabaseName + '].sys.types ty ON c.system_type_id = ty.system_type_id
            where 
                t.name = ''' + @TableName + '''  
                AND i.name = ''' + @IndexName + '''
                AND (
                    ty.name IN (''text'', ''ntext'', ''image'')
                    or 
                    (
                        ty.name IN (''varchar'', ''nvarchar'', ''varbinary'', ''xml'') 
                        AND c.max_length = -1
                    )
                )
        ) as x
    )';

Obviously, this is a code fragment from a much larger stored proc that we use to rebuild and/or reorganize indexes. I would recommend you look at Ola Hallengren's scripts at: http://ola.hallengren.com/

Answer 3

Specifically look at the NOT EXISTS in the WHERE clause.

SELECT 
--TOP 20
OBJECT_NAME(IPS.OBJECT_ID) AS [TableName], avg_fragmentation_in_percent, 
    SI.name [IndexName],  schema_name(ST.schema_id) AS [SchemaName], 
    0 AS IsProcessed 
INTO #FramentedTableList 
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL , NULL) IPS 
JOIN sys.tables ST WITH (nolock) 
    ON IPS.OBJECT_ID = ST.OBJECT_ID 
JOIN sys.indexes SI WITH (nolock) 
    ON IPS.OBJECT_ID = SI.OBJECT_ID AND IPS.index_id =   SI.index_id 
WHERE ST.is_ms_shipped = 0 AND SI.name IS NOT NULL 
AND avg_fragmentation_in_percent >= 
        CONVERT(DECIMAL, @FragmentationThresholdForReorganizeTableLowerLimit) 
AND NOT EXISTS (
        SELECT 1 
        FROM sys.index_columns
        JOIN sys.columns
            ON sys.index_columns.column_id = sys.columns.column_id
            AND sys.columns.object_id = sys.index_columns.object_id
        JOIN sys.types
            ON sys.types.system_type_id = sys.columns.system_type_id
        WHERE sys.index_columns.index_id = SI.index_id -- Join to get the correct index
            AND sys.index_columns.object_id = ips.object_id -- Join to get the correct table
            AND (sys.types.name IN ('text','ntext','xml','image','geometry','geography')
                OR  (sys.types.name IN ('varchar','nvarchar','varbinary')
                    AND sys.columns.max_length = -1) -- means MAX
                )
            )
ORDER BY avg_fragmentation_in_percent DESC