[转]SQL SERVER整理索引碎片测试

SQL SERVER整理索引碎片测试

SQL SERVER整理索引的方法也就这么几种,而且老是自作聪明的加入智能判断很不爽,还是比DBMS_ADVISOR差远了:

1[转]SQL SERVER整理索引碎片测试SQL SERVER 2000/2005

查询

2000
use DB_Name
declare @table_id int
set @table_id=object_id('Table_Name')
dbcc showcontig(@table_id)

2005
SELECT index_id,index_type_desc,avg_fragmentation_in_percent,page_count 
FROM sys.dm_db_index_physical_stats(db_id(), OBJECT_ID('Table_Name'), NULL, NULL , 'LIMITED');

整理

2000
DBCC INDEXDEFRAG (DB_Name, 'dbo.Table_Name', Index_Name)
DBCC DBREINDEX ('DB_Name.dbo.Table_Name','',100)

2005
ALTER INDEX Index_Name ON dbo.Table_Name REBUILD

以下是一个网上找的不错的例子,链接是:
http://www.cnblogs.com/perfectdesign/archive/2008/02/20/sqlserverreindexrebuild.html
新建一个表:

[转]SQL SERVER整理索引碎片测试create table t3
[转]SQL SERVER整理索引碎片测试(
[转]SQL SERVER整理索引碎片测试i int primary key,
[转]SQL SERVER整理索引碎片测试xx varchar(200) not null
[转]SQL SERVER整理索引碎片测试)

加入数据:

[转]SQL SERVER整理索引碎片测试declare @x int
[转]SQL SERVER整理索引碎片测试set @x = 0
[转]SQL SERVER整理索引碎片测试while @x <1000
[转]SQL SERVER整理索引碎片测试begin                       
[转]SQL SERVER整理索引碎片测试insert into t3 values (@x,'qweasdqweasdqweasdqweqweasdqwe')
[转]SQL SERVER整理索引碎片测试set @x = @x+1
[转]SQL SERVER整理索引碎片测试end

执行动态管理视图:

[转]SQL SERVER整理索引碎片测试SELECT index_id,index_type_desc,avg_fragmentation_in_percent,page_count 
FROM sys.dm_db_index_physical_stats
(db_id(), OBJECT_ID('t3'), NULL, NULL , 'LIMITED');
[转]SQL SERVER整理索引碎片测试

可以看到:
[转]SQL SERVER整理索引碎片测试

index_id为0表示这个是堆,平均的碎片有33%

现在执行几个可以减少碎片的方法都不管用,不能减少碎片。
包括:

[转]SQL SERVER整理索引碎片测试DBCC INDEXDEFRAG (test, 'dbo.t3', PK__t3__0EA330E9)
[转]SQL SERVER整理索引碎片测试
[转]SQL SERVER整理索引碎片测试alter index PK__t3__0EA330E9 on t3
[转]SQL SERVER整理索引碎片测试rebuild
[转]SQL SERVER整理索引碎片测试
[转]SQL SERVER整理索引碎片测试dbcc dbreindex ('t3')
[转]SQL SERVER整理索引碎片测试

这几个方法还有删除重建索引,都不能减少碎片数量。

后来我觉得是因为数据太少了,导致页也很少,数据库可能存在某种智能,判断是否值得去做重建索引的工作,所以加大的数据量:

[转]SQL SERVER整理索引碎片测试declare @x int
[转]SQL SERVER整理索引碎片测试set @x = 1000
[转]SQL SERVER整理索引碎片测试while @x <10000
[转]SQL SERVER整理索引碎片测试begin                       
[转]SQL SERVER整理索引碎片测试insert into t3 values (@x,'qweasdqweasdqweasdqweqweasdqwe')
[转]SQL SERVER整理索引碎片测试set @x = @x+1
[转]SQL SERVER整理索引碎片测试end

再执行语句:

[转]SQL SERVER整理索引碎片测试SELECT index_id,index_type_desc,avg_fragmentation_in_percent,page_count 
FROM sys.dm_db_index_physical_stats
(db_id(), OBJECT_ID('t3'), NULL, NULL , 'LIMITED');
[转]SQL SERVER整理索引碎片测试
alter index t3index on t3 rebuild

显示出来了!
[转]SQL SERVER整理索引碎片测试

结论:
SQL Server在执行相关的操作的时候都会智能去判断是否值得去做,比如在页面数太小的情况下可以不去重建索引,rebuild reindex 。类似的,在SQL Server 2005 里面也多了许多智能的判断来保证一个完整庞大而又不失智能的设计,
比如:
生成查询计划的阀值
缓存机制,缓存的筛选,LRU算法
预读机制
checkpoint减少回滚距离
智能join判断
重编译

了解SQL Server这种类似的软件产品能够为我们在设计产品的时候提供更多的思路想法,即使你了解上面的东西对你的SQL开发也不会有太多帮助。

另外附上几种方式的区别:
reindex是比较好的选择,速度快,但是他不能在线操作
INDEXDEFRAG 比较慢,但是可以在线操作
rebuild建议在碎片较少时采用。

附上微软的重建索引脚本,从里面也可以看出微软根据碎片大小推荐的方式,不过这个要随每个不同的数据库而定。

[转]SQL SERVER整理索引碎片测试-- ensure a USE <databasename> statement has been executed first.
[转]SQL SERVER整理索引碎片测试SET NOCOUNT ON;
[转]SQL SERVER整理索引碎片测试DECLARE @objectid int;
[转]SQL SERVER整理索引碎片测试DECLARE @indexid int;
[转]SQL SERVER整理索引碎片测试DECLARE @partitioncount bigint;
[转]SQL SERVER整理索引碎片测试DECLARE @schemaname sysname;
[转]SQL SERVER整理索引碎片测试DECLARE @objectname sysname;
[转]SQL SERVER整理索引碎片测试DECLARE @indexname sysname;
[转]SQL SERVER整理索引碎片测试DECLARE @partitionnum bigint;
[转]SQL SERVER整理索引碎片测试DECLARE @partitions bigint;
[转]SQL SERVER整理索引碎片测试DECLARE @frag float;
[转]SQL SERVER整理索引碎片测试DECLARE @command varchar(8000);
[转]SQL SERVER整理索引碎片测试-- ensure the temporary table does not exist
[转]SQL SERVER整理索引碎片测试IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
[转]SQL SERVER整理索引碎片测试    DROP TABLE work_to_do;
[转]SQL SERVER整理索引碎片测试-- conditionally select from the function, converting object and index IDs to names.
[转]SQL SERVER整理索引碎片测试SELECT
[转]SQL SERVER整理索引碎片测试    object_id AS objectid,
[转]SQL SERVER整理索引碎片测试    index_id AS indexid,
[转]SQL SERVER整理索引碎片测试    partition_number AS partitionnum,
[转]SQL SERVER整理索引碎片测试    avg_fragmentation_in_percent AS frag
[转]SQL SERVER整理索引碎片测试INTO work_to_do
[转]SQL SERVER整理索引碎片测试FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
[转]SQL SERVER整理索引碎片测试WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
[转]SQL SERVER整理索引碎片测试-- Declare the cursor for the list of partitions to be processed.
[转]SQL SERVER整理索引碎片测试DECLARE partitions CURSOR FOR SELECT * FROM work_to_do;
[转]SQL SERVER整理索引碎片测试
[转]SQL SERVER整理索引碎片测试-- Open the cursor.
[转]SQL SERVER整理索引碎片测试OPEN partitions;
[转]SQL SERVER整理索引碎片测试
[转]SQL SERVER整理索引碎片测试-- Loop through the partitions.
[转]SQL SERVER整理索引碎片测试FETCH NEXT
[转]SQL SERVER整理索引碎片测试   FROM partitions
[转]SQL SERVER整理索引碎片测试   INTO @objectid, @indexid, @partitionnum, @frag;
[转]SQL SERVER整理索引碎片测试
[转]SQL SERVER整理索引碎片测试WHILE @@FETCH_STATUS = 0
[转]SQL SERVER整理索引碎片测试    BEGIN;
[转]SQL SERVER整理索引碎片测试        SELECT @objectname = o.name, @schemaname = s.name
[转]SQL SERVER整理索引碎片测试        FROM sys.objects AS o
[转]SQL SERVER整理索引碎片测试        JOIN sys.schemas as s ON s.schema_id = o.schema_id
[转]SQL SERVER整理索引碎片测试        WHERE o.object_id = @objectid;
[转]SQL SERVER整理索引碎片测试
[转]SQL SERVER整理索引碎片测试        SELECT @indexname = name 
[转]SQL SERVER整理索引碎片测试        FROM sys.indexes
[转]SQL SERVER整理索引碎片测试        WHERE  object_id = @objectid AND index_id = @indexid;
[转]SQL SERVER整理索引碎片测试
[转]SQL SERVER整理索引碎片测试        SELECT @partitioncount = count (*) 
[转]SQL SERVER整理索引碎片测试        FROM sys.partitions
[转]SQL SERVER整理索引碎片测试        WHERE object_id = @objectid AND index_id = @indexid;
[转]SQL SERVER整理索引碎片测试
[转]SQL SERVER整理索引碎片测试-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding
[转]SQL SERVER整理索引碎片测试IF @frag < 30.0
[转]SQL SERVER整理索引碎片测试    BEGIN;
[转]SQL SERVER整理索引碎片测试    SELECT @command = 'ALTER INDEX ' + @indexname + ' ON ' + @schemaname + '.' + @objectname + ' REORGANIZE';
[转]SQL SERVER整理索引碎片测试    IF @partitioncount > 1
[转]SQL SERVER整理索引碎片测试        SELECT @command = @command + ' PARTITION=' + CONVERT (CHAR, @partitionnum);
[转]SQL SERVER整理索引碎片测试    EXEC (@command);
[转]SQL SERVER整理索引碎片测试    END;
[转]SQL SERVER整理索引碎片测试
[转]SQL SERVER整理索引碎片测试IF @frag >= 30.0
[转]SQL SERVER整理索引碎片测试    BEGIN;
[转]SQL SERVER整理索引碎片测试    SELECT @command = 'ALTER INDEX ' + @indexname +' ON ' + @schemaname + '.' + @objectname + ' REBUILD';
[转]SQL SERVER整理索引碎片测试    IF @partitioncount > 1
[转]SQL SERVER整理索引碎片测试        SELECT @command = @command + ' PARTITION=' + CONVERT (CHAR, @partitionnum);
[转]SQL SERVER整理索引碎片测试    EXEC (@command);
[转]SQL SERVER整理索引碎片测试    END;
[转]SQL SERVER整理索引碎片测试PRINT 'Executed ' + @command;
[转]SQL SERVER整理索引碎片测试
[转]SQL SERVER整理索引碎片测试FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag;
[转]SQL SERVER整理索引碎片测试END;
[转]SQL SERVER整理索引碎片测试-- Close and deallocate the cursor.
[转]SQL SERVER整理索引碎片测试CLOSE partitions;
[转]SQL SERVER整理索引碎片测试DEALLOCATE partitions;
[转]SQL SERVER整理索引碎片测试
[转]SQL SERVER整理索引碎片测试-- drop the temporary table
[转]SQL SERVER整理索引碎片测试IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
[转]SQL SERVER整理索引碎片测试    DROP TABLE work_to_do;
[转]SQL SERVER整理索引碎片测试GO

BOL的推荐:

avg_fragmentation_in_percent 值 修复语句

> 5% 且 < = 30%

ALTER INDEX REORGANIZE

> 30%

ALTER INDEX REBUILD WITH (ONLINE = ON)*

小于5没必要重建,所以上面的SQL语句还是有得商量的地方。

http://blog.csdn.net/greenery/archive/2008/08/06/2778486.aspx

/**************************************
作用:在线整理索引碎片
时间:2008-5-15
说明:
          根据微软的示例改编
          适用于无法停机重建索引的情况,
          在线整理索引碎片,并更新表的统计信息以增强效果。
          另外可以自定义需要整理的索引的逻辑碎片程度,扫描密度,页数
          必须要在数据库访问较少的时候执行 
***************************************/
CREATE PROC [dbo].[SYS_IndexDrag]
AS
BEGIN

SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE @maxfrag DECIMAL--逻辑碎片

DECLARE @MaxScanDensity DECIMAL--扫描密度

DECLARE @Page INT --8k页数,设定一个限度对一定大的索引进行整理

DECLARE @TmpName VARCHAR(150)

-- Decide on the maximum fragmentation to allow

SELECT @maxfrag = 30.0
SELECT @MaxScanDensity=70.0
SELECT @Page=400

-- Declare cursor

DECLARE tables CURSOR FOR
   SELECT TABLE_NAME
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_TYPE = 'BASE TABLE'

-- Create the table

CREATE TABLE #fraglist (
   ObjectName CHAR (255),
   ObjectId INT,
   IndexName CHAR (255),
   IndexId INT,
   Lvl INT,
   CountPages INT,
   CountRows INT,
   MinRecSize INT,
   MaxRecSize INT,
   AvgRecSize INT,
   ForRecCount INT,
   Extents INT,
   ExtentSwitches INT,
   AvgFreeBytes INT,
   AvgPageDensity INT,
   ScanDensity DECIMAL,
   BestCount INT,
   ActualCount INT,
   LogicalFrag DECIMAL,
   ExtentFrag DECIMAL)

-- Open the cursor

OPEN tables

-- Loop through all the tables in the database

FETCH NEXT
   FROM tables
   INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table

INSERT INTO #fraglist
   EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
      WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
    
   FETCH NEXT
      FROM tables
      INTO @tablename
END

-- Close and deallocate the cursor

CLOSE tables
DEALLOCATE tables

-- Declare cursor for list of indexes to be defragged

DECLARE indexes CURSOR FOR
   SELECT ObjectName, ObjectId, IndexId, LogicalFrag
   FROM #fraglist
   WHERE (LogicalFrag >= @maxfrag OR [ScanDensity]<=@MaxScanDensity) AND [CountPages]>=@page
      AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

-- Open the cursor

OPEN indexes

-- loop through the indexes

FETCH NEXT
   FROM indexes
   INTO @tablename, @objectid, @indexid, @frag

WHILE @@FETCH_STATUS = 0
BEGIN
  
   --在线整理碎片

PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
      ' + RTRIM(@indexid) + ') - fragmentation currently '
       + RTRIM(CONVERT(varchar(15),@frag)) + '%'
   SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
       ' + RTRIM(@indexid) + ')'
   EXEC (@execstr)
  
   --更新统计信息

IF @TmpName<>@tablename
   BEGIN
        SET @tmpName=@tableName
        EXEC('UPDATE STATISTICS '+@TableName) 
   END
    
   FETCH NEXT
      FROM indexes
      INTO @tablename, @objectid, @indexid, @frag
END

-- Close and deallocate the cursor

CLOSE indexes
DEALLOCATE indexes

-- Delete the temporary table

DROP TABLE #fraglist

END

GO

上一篇:安装 Google BBR 加速VPS网络


下一篇:Python发送QQ邮件