主页 / dba / 问题 / 38684
Accepted
reticentKoala
Asked: 2013-03-28 08:28:54 +0800 CST

以编程方式查找无法在线重建的索引

  • 772

我正在使用 T-SQL 自动重建和重组索引。我遇到了无法在线重建的索引问题。这主要是因为包含了 ntext/nvarchar 列。

有没有办法以编程方式将它们标识为该语句的一部分？对 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 个回答

  1. 我使用以下内容来检测 LOB：

    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
    )';

    显然，这是我们用来重建和/或重组索引的更大存储过程的代码片段。我建议您在以下网址查看 Ola Hallengren 的脚本： http ://ola.hallengren.com/

    • 3

  2. 具体查看 WHERE 子句中的 NOT EXISTS。

    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

    您可以将其添加到您的WHERE子句中以省略任何包含 LOB 类型的索引（我想我已经全部捕获了它们）：

    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
)

    如果您使用的是 SQL Server 2008 或 2008 R2，则可以将类型搜索更改为以下内容以捕获geography和geometry（归功于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

    当然，重组查询可能更聪明，这样您就可以在线重建索引，并且不要将那些您不能完全排除在维护之外的索引。但我同意 Max 的观点，不要重新发明轮子。去获取 Ola 的脚本，或者，如果你想要更强大的东西，请告诉我。

    • 2

相关问题