Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 
share|improve this question
add comment

3 Answers 3

up vote 1 down vote accepted

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.

share|improve this answer
    
I like your answer since it actually answers the question. Good advice overall too! –  Max Vernon Mar 27 '13 at 16:48
    
Thanks all. I've used this method for now. I've looked at Ola's scripts. The problem I was originally grappling with was massive log bloat during index rebuild, so I'm trying an approach of rebuilding the 20 worst indexes at a time - I couldn't figure out how to incorporate this into Ola's script just yet. –  reticentKoala Mar 28 '13 at 15:48
add comment

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/

share|improve this answer
add comment

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 
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.