Início / dba / Perguntas / 38684
Accepted
reticentKoala
Asked: 2013-03-28 08:28:54 +0800 CST

Localizar programaticamente índices que não podem ser reconstruídos online

  • 772

Estou automatizando a reconstrução e reorganização de índices usando T-SQL. Tenho problemas com índices que não podem ser reconstruídos online. Principalmente isso acontece porque as colunas ntext/nvarchar estão incluídas.

Existe uma maneira de identificá-los programaticamente como parte desta declaração? Então, tipo de adição ao predicado WHERE

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
sql-server index

3 respostas

  1. Eu uso o seguinte para detectar 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
    )';

    Obviamente, este é um fragmento de código de um procedimento armazenado muito maior que usamos para reconstruir e/ou reorganizar índices. Eu recomendaria que você olhasse os scripts de Ola Hallengren em: http://ola.hallengren.com/

    • 3

  2. Observe especificamente o NOT EXISTS na cláusula WHERE.

    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
    • 3
  3. Best Answer

    Você pode adicionar isso à sua WHEREcláusula para deixar de fora quaisquer índices que incluam um tipo LOB (acho que capturei todos):

    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
)

    Se você estiver usando o SQL Server 2008 ou 2008 R2, poderá alterar a pesquisa de tipo para o seguinte para capturar geographye geometry(crédito 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

    Claro, pode ser mais inteligente reestruturar sua consulta para que você reconstrua os índices que você pode online e não deixe aqueles que você não pode fora de sua manutenção. Mas concordo com Max, não reinvente a roda. Vá buscar os scripts de Ola ou, se você quiser algo muito mais robusto, por favor me avise.

    • 2

relate perguntas