最近遇到一个奇怪的情况，同样的库及表结构，同样的执行sql语句，在库1查询执行很快，在库2查询执行就会超时，问题是库2的表相对于库1的表中数量要小很多。

然后再次对比了表结构、索引等情况，发现还是一致。

但是在针对这个sql语句做查询计划分析的时候，居然不一样，想到会不会是因为索引导致的。因为之前有耳闻索引失效的问题。

于是针对此查询条件中主要的几个表做索引碎片查询，看看是不是不一样：

-- 查看表中具体的碎片情况
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,
    ind.name AS IndexName,
    indexstats.avg_fragmentation_in_percent,
    indexstats.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind 
    ON ind.object_id = indexstats.object_id
    AND ind.index_id = indexstats.index_id
WHERE OBJECT_NAME(ind.OBJECT_ID) = 'YourTableName'  -- 替换为你的表名
ORDER BY avg_fragmentation_in_percent DESC;

-- 检查所有索引碎片情况
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,
    ind.name AS IndexName,
    indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id
    AND ind.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 30;--碎片率

然后针对查询出来的结果做判别：

-- 重组索引（碎片率 5-30%）
ALTER INDEX IndexName ON TableName REORGANIZE;
-- 重建索引（碎片率 >30%）
ALTER INDEX IndexName ON TableName REBUILD;
-- 重建表的所有索引
ALTER INDEX ALL ON TableName REBUILD;

因为我的表数据量很小，所以直接选择重建表的所有索引，执行完后，在查询对应的查询语句后，是正常了，然后看查询计划也是一样的了。

以前一直以为建立了索引就行了，然后就不会去关注。 今天的这个情况在网上搜索下，频繁的数据插入、更新、删除操作、 大批量数据导入、 数据按非聚集索引顺序插入、 页面分裂(Page Split)操作频繁 都会导致索引碎片产生。针对此情况，也可以做以下的措施：

-- 创建定期维护作业
CREATE PROCEDURE sp_MaintainIndexes
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @TableName NVARCHAR(255)
    DECLARE @IndexName NVARCHAR(255)
    DECLARE @Fragmentation FLOAT
    
    -- 创建临时表存储需要维护的索引
    CREATE TABLE #FragmentedIndexes
    (
        TableName NVARCHAR(255),
        IndexName NVARCHAR(255),
        Fragmentation FLOAT
    )
    
    -- 获取碎片率超过5%的索引
    INSERT INTO #FragmentedIndexes
    SELECT 
        OBJECT_NAME(s.[object_id]),
        i.name,
        s.avg_fragmentation_in_percent
    FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') s
    INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] 
        AND s.index_id = i.index_id
    WHERE s.avg_fragmentation_in_percent > 5
    
    -- 处理每个碎片索引
    DECLARE index_cursor CURSOR FOR
    SELECT TableName, IndexName, Fragmentation
    FROM #FragmentedIndexes
    
    OPEN index_cursor
    FETCH NEXT FROM index_cursor INTO @TableName, @IndexName, @Fragmentation
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        BEGIN TRY
            IF @Fragmentation >= 30
                EXEC('ALTER INDEX [' + @IndexName + '] ON [' + @TableName + '] REBUILD;')
            ELSE
                EXEC('ALTER INDEX [' + @IndexName + '] ON [' + @TableName + '] REORGANIZE;')
                
            -- 更新统计信息
            EXEC('UPDATE STATISTICS [' + @TableName + '] ([' + @IndexName + ']) WITH FULLSCAN;')
        END TRY
        BEGIN CATCH
            -- 记录错误信息
            PRINT 'Error maintaining index: ' + @IndexName + ' on table: ' + @TableName
        END CATCH
        
        FETCH NEXT FROM index_cursor INTO @TableName, @IndexName, @Fragmentation
    END
    
    CLOSE index_cursor
    DEALLOCATE index_cursor
    
    DROP TABLE #FragmentedIndexes
END

-- 创建维护作业
USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name=N'IndexMaintenance', 
    @enabled=1, 
    @notify_level_eventlog=0, 
    @notify_level_email=2, 
    @notify_level_page=2, 
    @delete_level=0, 
    @job_id = @jobId OUTPUT

-- 添加作业步骤
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Maintain Indexes',
    @command=N'EXEC sp_MaintainIndexes'

-- 设置作业计划（例如每周日凌晨2点执行）
EXEC msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'WeeklyMaintenance',
    @freq_type=8, 
    @freq_interval=1,  -- Sunday
    @freq_subday_type=1,
    @freq_subday_interval=0,
    @freq_relative_interval=0,
    @freq_recurrence_factor=1,
    @active_start_time=20000

-- 定期检查未使用的索引
SELECT 
    OBJECT_NAME(i.[object_id]) AS TableName,
    i.name AS IndexName,
    i.type_desc AS IndexType,
    ius.user_seeks,
    ius.user_scans,
    ius.user_lookups,
    ius.user_updates,
    ius.last_user_seek,
    ius.last_user_scan,
    ius.last_user_lookup,
    ius.last_user_update
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats ius ON i.[object_id] = ius.[object_id] 
    AND i.index_id = ius.index_id
WHERE ius.database_id = DB_ID()
    AND i.type_desc <> 'HEAP'
    AND (ius.user_seeks = 0 AND ius.user_scans = 0 AND ius.user_lookups = 0)
ORDER BY ius.user_updates DESC;

 
-- 示例维护脚本，手动去执行
DECLARE @TableName NVARCHAR(255)
DECLARE @IndexName NVARCHAR(255)
DECLARE @Fragmentation FLOAT

DECLARE IndexCursor CURSOR FOR
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,
    ind.name AS IndexName,
    indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind 
    ON ind.object_id = indexstats.object_id
    AND ind.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 5

OPEN IndexCursor
FETCH NEXT FROM IndexCursor INTO @TableName, @IndexName, @Fragmentation

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @Fragmentation >= 30
        EXEC('ALTER INDEX ' + @IndexName + ' ON ' + @TableName + ' REBUILD;')
    ELSE
        EXEC('ALTER INDEX ' + @IndexName + ' ON ' + @TableName + ' REORGANIZE;')
        
    FETCH NEXT FROM IndexCursor INTO @TableName, @IndexName, @Fragmentation
END

CLOSE IndexCursor
DEALLOCATE IndexCursor