基于sql server中的碎片重建索引。

Question

所以，我在网上寻找我的问题的解决方案，我对DBA的事情相当陌生，所以请不要对我苛刻:)

我有一段代码，它可以帮助我在索引碎片达到一定百分比时重建索引。它有一个重组的IF条件，但我不需要它，我需要修改代码，以便它查询某个数据库和模式的索引，并仅在碎片大于5%时进行重建。

USE [master]
GO

/****** Object:  StoredProcedure [dbo].[spKodyaz_Index_Maintenance]    Script Date: 19.11.2012 19:10:11 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[al]
(
    @DatabaseId smallint
)
AS

DECLARE
    @dbname sysname,
    @schema_name sysname,
    @object_id int,
    @object_name sysname,
    @index_name sysname,
    @avg_fragmentation float,
    @sql nvarchar(max)

CREATE TABLE ##tmpIndexMaintenance (
    object_schema_name sysname NULL,
    object_name sysname NULL,
    object_id int NULL,
    index_name sysname NULL,
    avg_fragmentation_in_percent float NULL
)

SET @dbname = DB_NAME(@DatabaseId);

SET @sql = N'USE [' + @dbname + '];
INSERT INTO ##tmpIndexMaintenance
SELECT
    object_schema_name(ps.object_id) as ObjectSchema,
    object_name(ps.object_id) as ObjectName,
    ps.object_id ObjectId,
    i.name as IndexName,
    ps.avg_fragmentation_in_percent
FROM [' + @dbname + '].sys.dm_db_index_physical_stats(' + CONVERT(nvarchar(5), @DatabaseId) + ', null, null, null, null) ps
inner join [' + @dbname + '].sys.indexes i
    on i.object_id = ps.object_id and i.index_id = ps.index_id
WHERE
    avg_fragmentation_in_percent > 5 -- reorganize and rebuild
    and ps.index_id > 0
ORDER BY avg_fragmentation_in_percent DESC'

--print @sql
EXEC (@sql);

DECLARE indexFragmentation CURSOR FAST_FORWARD FOR SELECT * FROM ##tmpIndexMaintenance

OPEN indexFragmentation
FETCH NEXT FROM indexFragmentation INTO @schema_name, @object_name, @object_id, @index_name, @avg_fragmentation

WHILE @@FETCH_STATUS = 0
BEGIN

    IF @avg_fragmentation > 30 -- ReBuild index
        SELECT @sql = 'USE [' + @dbname + ']; ALTER INDEX [' + @index_name +'] ON [' + @schema_name + '].[' + @object_name + '] REBUILD WITH (ONLINE = ON)';

    ELSE -- ReOrganize index
        SELECT @sql = 'USE [' + @dbname + ']; ALTER INDEX [' + @index_name +'] ON [' + @schema_name + '].[' + @object_name + '] REORGANIZE';

    PRINT @sql;
    EXEC (@sql);

    FETCH NEXT FROM indexFragmentation INTO @schema_name, @object_name, @object_id, @index_name, @avg_fragmentation

END

CLOSE indexFragmentation
DEALLOCATE indexFragmentation

DROP TABLE ##tmpIndexMaintenance


GO 

Answer 1

游标已检索碎片超过5%的数据库。只需删除if条件并在缺省情况下重新构建它们

​

WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @sql = 'USE [' + @dbname + ']; ALTER INDEX [' + @index_name +'] ON [' + @schema_name + '].[' + @object_name + '] REBUILD WITH (ONLINE = ON)';

    PRINT @sql;
    EXEC (@sql);

    FETCH NEXT FROM indexFragmentation INTO @schema_name, @object_name, @object_id, @index_name, @avg_fragmentation

END

​