none
使用这个脚本来对索引进行重建或者重组,怎么运行之后,还是有些索引的碎片为80%多。 RRS feed

  • 问题


  • alter  PROCEDURE [dbo].[RebuildIndex_sp]
     
    AS
    BEGIN
     SET NOCOUNT ON;
    DECLARE @objectid int;
    DECLARE @indexid int;
    DECLARE @partitioncount bigint;
    DECLARE @schemaname sysname;
    DECLARE @objectname sysname;
    DECLARE @indexname sysname;
    DECLARE @partitionnum bigint;
    DECLARE @partitions bigint;
    DECLARE @frag float;
    DECLARE @command varchar(8000);
    -- ensure the temporary table does not exist
    IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
        DROP TABLE work_to_do;
    -- conditionally select from the function, converting object and index IDs to names.
    SELECT
        object_id AS objectid,
        index_id AS indexid,
        partition_number AS partitionnum,
        avg_fragmentation_in_percent AS frag
    INTO work_to_do
    FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
    WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
    -- Declare the cursor for the list of partitions to be processed.
    DECLARE partitions CURSOR FOR SELECT * FROM work_to_do;

    -- Open the cursor.
    OPEN partitions;

    -- Loop through the partitions.
    FETCH NEXT
       FROM partitions
       INTO @objectid, @indexid, @partitionnum, @frag;

    WHILE @@FETCH_STATUS = 0
        BEGIN;
            SELECT @objectname = o.name, @schemaname = s.name
            FROM sys.objects AS o
            JOIN sys.schemas as s ON s.schema_id = o.schema_id
            WHERE o.object_id = @objectid;

            SELECT @indexname = name
            FROM sys.indexes
            WHERE  object_id = @objectid AND index_id = @indexid;

            SELECT @partitioncount = count (*)
            FROM sys.partitions
            WHERE object_id = @objectid AND index_id = @indexid;
      print(@objectname+':'+@indexname)
    -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding
    IF @frag < 30.0
        BEGIN;
        SELECT @command = 'ALTER INDEX [' + @indexname + '] ON ' + @schemaname + '.' + @objectname + ' REORGANIZE';
        IF @partitioncount > 1
            SELECT @command = @command + ' PARTITION=' + CONVERT (CHAR, @partitionnum);
        EXEC (@command);
        END;

    IF @frag >= 30.0
        BEGIN;
        SELECT @command = 'ALTER INDEX [' + @indexname +'] ON ' + @schemaname + '.' + @objectname + ' REBUILD ';
      
        EXEC (@command);
        END;
     
    PRINT 'Executed ' + @command;

    FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag;
    END;
    -- Close and deallocate the cursor.
    CLOSE partitions;
    DEALLOCATE partitions;

    -- drop the temporary table
    IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
        DROP TABLE work_to_do;

    END

     

     

    用下面的脚本查询出索引的碎片。

    SELECT
        object_id AS objectid,
        index_id AS indexid,
        partition_number AS partitionnum,
        avg_fragmentation_in_percent AS frag_in_percent
    INTO temp_work_to_do_newest_one

    FROM sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL, 'LIMITED')
    WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0
    ORDER BY avg_fragmentation_in_percent DESC;


    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.
    2011年5月23日 3:06

答案

全部回复

  • SELECT
        object_id AS objectid,
        index_id AS indexid,
        partition_number AS partitionnum,
        avg_fragmentation_in_percent AS frag
    FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
    WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

    执行这个sql可以查询到一个表在这个list中。但是执行这个存储过程之后,发现这个表的碎片还是80%多。

    另外发现使用alter index S_overwrite2 on S_OVERWRITE rebuild 手动执行之后,这个索引的碎片还是80%多。

    是不是这个索引的fillfactor参数没设置好?还是什么其他原因?


    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.
    2011年5月23日 3:16
  • What's fill factor? Does the table have clustered index? How big the table is?
    2011年5月23日 3:50
  • Fillfactor都是没设置的。都是默认值,0.

    表都是有聚簇索引的。有大表(数据量多余50W的),也有小表(数据量为几百)


    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.
    2011年5月24日 1:16
  • 而且我重建之后立即查询索引的碎片就是这么高、
    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.
    2011年5月24日 1:17
  • 而且我新建一个表,定义完全拷贝之前的表,插入数据之后,发现碎片还是那么高。

    我有测试设置fillfactor为80%,新建之后,发现碎片相对低点,但是一重建 又达到之前的程度了。


    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.

    2011年5月24日 1:20
  • 在同一台机器上,另外新建一个数据库,并且使用相同的表定义,插入数据之后碎片一样。在我本机建立这个表,并且插入数据之后,索引碎片由80%下降到30%。很奇怪。我看了下实例中的默认fillfactor的设置都是0.
    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.
    2011年5月24日 1:33
  • Try run 'dbcc showcontig (tab_name) with all_indexes', it shows more clear results.

    2011年5月24日 2:42
  • 是的,我用到你的语句也是这种情况


    星光总能为我指引方向
    2011年5月24日 2:46
  • 是的,我用到你的语句也是这种情况


    星光总能为我指引方向


    但是就算我手动执行 alter index indexname on tablename rebuild

    之后索引的碎片还是没变化。


    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.
    2011年5月24日 2:49
  • DBCC SHOWCONTIG scanning 'T_D_PIV_OSA' table...
    Table: 'T_D_PIV_OSA' (1348915877); index ID: 1, database ID: 5
    TABLE level scan performed.
    - Pages Scanned................................: 55571
    - Extents Scanned..............................: 6981
    - Extent Switches..............................: 53005
    - Avg. Pages per Extent........................: 8.0
    - Scan Density [Best Count:Actual Count].......: 13.11% [6947:53006]
    - Logical Scan Fragmentation ..................: 97.43%
    - Extent Scan Fragmentation ...................: 99.68%
    - Avg. Bytes Free per Page.....................: 2409.5
    - Avg. Page Density (full).....................: 70.23%
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    DBCC SHOWCONTIG scanning 'S_EMPLOYEE' table...
    Table: 'S_EMPLOYEE' (91863394); index ID: 1, database ID: 5
    TABLE level scan performed.
    - Pages Scanned................................: 273
    - Extents Scanned..............................: 42
    - Extent Switches..............................: 270
    - Avg. Pages per Extent........................: 6.5
    - Scan Density [Best Count:Actual Count].......: 12.92% [35:271]
    - Logical Scan Fragmentation ..................: 97.80%
    - Extent Scan Fragmentation ...................: 88.10%
    - Avg. Bytes Free per Page.....................: 2831.6
    - Avg. Page Density (full).....................: 65.02%
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    DBCC SHOWCONTIG scanning 'RC_UTIL_WEEK' table...
    Table: 'RC_UTIL_WEEK' (1010102639); index ID: 0, database ID: 5
    TABLE level scan performed.
    - Pages Scanned................................: 110
    - Extents Scanned..............................: 26
    - Extent Switches..............................: 25
    - Avg. Pages per Extent........................: 4.2
    - Scan Density [Best Count:Actual Count].......: 53.85% [14:26]
    - Extent Scan Fragmentation ...................: 96.15%
    - Avg. Bytes Free per Page.....................: 1585.1
    - Avg. Page Density (full).....................: 80.42%
    DBCC SHOWCONTIG scanning 'RC_UTIL_WEEK' table...
    Table: 'RC_UTIL_WEEK' (1010102639); index ID: 2, database ID: 5
    LEAF level scan performed.
    - Pages Scanned................................: 120
    - Extents Scanned..............................: 26
    - Extent Switches..............................: 112
    - Avg. Pages per Extent........................: 4.6
    - Scan Density [Best Count:Actual Count].......: 13.27% [15:113]
    - Logical Scan Fragmentation ..................: 97.50%
    - Extent Scan Fragmentation ...................: 80.77%
    - Avg. Bytes Free per Page.....................: 3392.1
    - Avg. Page Density (full).....................: 58.09%
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

     

    看起来- Avg. Pages per Extent距离8还是有点距离。

    scan density都太小了。Logical和Extent scan fragmentation都太高了。

    平均页空闲字节都太高平均也密度也偏低


    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.

    • 已编辑 Wison-Ho 2011年5月24日 3:19
    2011年5月24日 2:58
  • RC_UTIL_WEEK is heap, can't defrag it without clustered index. For other 2 tables, just run 'alter index all on tab_name rebuild' then run dbcc again.
    2011年5月24日 3:16
  • 那对堆表来说,对索引碎片要怎么处理呢?


    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.
    2011年5月24日 3:17
  • Create clustered index then drop it in the table, but better to find proper column for clustered index and keep it.

    2011年5月24日 3:22
  • 现在暂时只能对表S_EMPLOYEE进行reindex的操作。另外一个表太大了。不能在生产时间作业。

    DBCC SHOWCONTIG scanning 'S_EMPLOYEE' table...
    Table: 'S_EMPLOYEE' (91863394); index ID: 1, database ID: 5
    TABLE level scan performed.
    - Pages Scanned................................: 177
    - Extents Scanned..............................: 23
    - Extent Switches..............................: 22
    - Avg. Pages per Extent........................: 7.7
    - Scan Density [Best Count:Actual Count].......: 100.00% [23:23]
    - Logical Scan Fragmentation ..................: 0.00%
    - Extent Scan Fragmentation ...................: 43.48%
    - Avg. Bytes Free per Page.....................: 165.4
    - Avg. Page Density (full).....................: 97.96%
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

     

    对比了下,确实好很多了。


    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.
    2011年5月24日 3:42
  • You should double check your RebuildIndex_sp.
    2011年5月24日 3:51
  • 存在一个1000多笔数据的表,存在一个聚簇和另外2个非聚簇索引。查询碎片,发现其中一个非聚簇索引碎片率较高,执行alter index all on tablename rebiuld之后,发现碎片还是没变化呢。
    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.
    2011年5月24日 4:02
  • Result of dbcc?
    2011年5月24日 4:07
  • DBCC SHOWCONTIG scanning 'S_UDA_MASTER' table...
    Table: 'S_UDA_MASTER' (693577509); index ID: 1, database ID: 7
    TABLE level scan performed.
    - Pages Scanned................................: 14
    - Extents Scanned..............................: 8
    - Extent Switches..............................: 7
    - Avg. Pages per Extent........................: 1.8
    - Scan Density [Best Count:Actual Count].......: 25.00% [2:8]
    - Logical Scan Fragmentation ..................: 35.71%
    - Extent Scan Fragmentation ...................: 62.50%
    - Avg. Bytes Free per Page.....................: 579.1
    - Avg. Page Density (full).....................: 92.84%
    DBCC SHOWCONTIG scanning 'S_UDA_MASTER' table...
    Table: 'S_UDA_MASTER' (693577509); index ID: 4, database ID: 7
    LEAF level scan performed.
    - Pages Scanned................................: 7
    - Extents Scanned..............................: 7
    - Extent Switches..............................: 6
    - Avg. Pages per Extent........................: 1.0
    - Scan Density [Best Count:Actual Count].......: 14.29% [1:7]
    - Logical Scan Fragmentation ..................: 85.71%
    - Extent Scan Fragmentation ...................: 57.14%
    - Avg. Bytes Free per Page.....................: 879.9
    - Avg. Page Density (full).....................: 89.13%
    DBCC SHOWCONTIG scanning 'S_UDA_MASTER' table...
    Table: 'S_UDA_MASTER' (693577509); index ID: 5, database ID: 7
    LEAF level scan performed.
    - Pages Scanned................................: 8
    - Extents Scanned..............................: 8
    - Extent Switches..............................: 7
    - Avg. Pages per Extent........................: 1.0
    - Scan Density [Best Count:Actual Count].......: 12.50% [1:8]
    - Logical Scan Fragmentation ..................: 87.50%
    - Extent Scan Fragmentation ...................: 75.00%
    - Avg. Bytes Free per Page.....................: 471.3
    - Avg. Page Density (full).....................: 94.18%
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

     

     

     

    DBCC SHOWCONTIG scanning 'S_UDA_MASTER' table...
    Table: 'S_UDA_MASTER' (693577509); index ID: 1, database ID: 7
    TABLE level scan performed.
    - Pages Scanned................................: 14
    - Extents Scanned..............................: 8
    - Extent Switches..............................: 7
    - Avg. Pages per Extent........................: 1.8
    - Scan Density [Best Count:Actual Count].......: 25.00% [2:8]
    - Logical Scan Fragmentation ..................: 50.00%
    - Extent Scan Fragmentation ...................: 87.50%
    - Avg. Bytes Free per Page.....................: 579.1
    - Avg. Page Density (full).....................: 92.84%
    DBCC SHOWCONTIG scanning 'S_UDA_MASTER' table...
    Table: 'S_UDA_MASTER' (693577509); index ID: 4, database ID: 7
    LEAF level scan performed.
    - Pages Scanned................................: 7
    - Extents Scanned..............................: 7
    - Extent Switches..............................: 6
    - Avg. Pages per Extent........................: 1.0
    - Scan Density [Best Count:Actual Count].......: 14.29% [1:7]
    - Logical Scan Fragmentation ..................: 85.71%
    - Extent Scan Fragmentation ...................: 85.71%
    - Avg. Bytes Free per Page.....................: 879.9
    - Avg. Page Density (full).....................: 89.13%
    DBCC SHOWCONTIG scanning 'S_UDA_MASTER' table...
    Table: 'S_UDA_MASTER' (693577509); index ID: 5, database ID: 7
    LEAF level scan performed.
    - Pages Scanned................................: 8
    - Extents Scanned..............................: 8
    - Extent Switches..............................: 7
    - Avg. Pages per Extent........................: 1.0
    - Scan Density [Best Count:Actual Count].......: 12.50% [1:8]
    - Logical Scan Fragmentation ..................: 87.50%
    - Extent Scan Fragmentation ...................: 87.50%
    - Avg. Bytes Free per Page.....................: 471.3
    - Avg. Page Density (full).....................: 94.18%
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.


    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.
    2011年5月24日 4:30
  • The table is too small so sql puts it on several shared extents, leave it for now.
    2011年5月24日 13:38