登录  
 加关注
查看详情
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

梦想之鹰的天空

天高任鸟飞......放飞....心情..........放飞.....梦想

 
 
 

日志

 
 

SQL Server索引维护指导  

2008-04-18 17:34:40|  分类: 网络 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

索引在数据库相关工作者的日常工作中占据了很重要的位置,索引需要牵涉到索引创建、优化和维护多方面的工作,本文以实例结合相关原理来介绍索引维护相关的知识。文中的相关代码,也可以满足多数情况下索引的维护需求。

  实现步骤

  1. 以什么标准判断索引是否需要维护?

  2. 索引维护的方法有哪些?

  3. 能否方便地整理出比较通用的维护过程,实现自动化维护?

  一、 以什么标准判断索引是否需要维护?

  由于本文集中讨论索引维护相关,所以我们暂且抛开创建的不合理的那些索引,仅从维护的角度来讨论。从索引维护的角度来讲,最主要的参考标准就是索引碎片的大小。通常情况下,索引碎片在10%以内,是可以接受的。下面介绍获取索引碎片的方法:

  SQL Server 2000: DBCC SHOWCONTIG

  SQL Server 2005: sys.dm_db_index_physical_stats

  实例(取db_test数据库所有索引碎片相关信息):

  SQL Server 2000:

USE [db_test];

GO

DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES

GO

SQL Server 2005:

DECLARE @db_name VARCHAR(256)

SET @db_name='db_test' 

SELECT

   db_name(a.database_id) [db_name],

   c.name [table_name],

   b.name [index_name],

   a.avg_fragmentation_in_percent

FROM

   sys.dm_db_index_physical_stats (DB_ID(@db_name), NULL,NULL, NULL, 'Limited') AS a

 JOIN

   sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id

 JOIN

   sys.tables AS c ON a.object_id = c.object_id

WHERE

  a.index_id>0

  AND a.avg_fragmentation_in_percent>5 -–碎片程度大于5

  二、 索引维护的方法有哪些?

  注:维护方式的选择,一方面要考虑是否是联机维护,另一方面就是速度上的考虑。一般碎片<=30%时,使用重新组织的方法速度比索引重建快;碎片>30%时,索引重建的速度比重新组织要快。

  1. 联机维护

  SQL Server2000:

  DBCC INDEXDEFRAG 重新组织索引,占用资源少,锁定资源周期短,可联机进行。

  SQL Server 2005:

  1. 联机重新组织:

  ALTER INDEX [index_name] ON [table_name]

  REORGANIZE;

  2. 联机重建:

  ALTER INDEX [index_name] ON [table_name]

  REBUILD WITH (FILLFACTOR = 85, SORT_IN_TEMPDB = OFF,

  STATISTICS_NORECOMPUTE = ON,ONLINE = ON);

  2. 脱机维护

  SQL Server2000:DBCC DBREINDEX

  SQL Server 2005:ALTER INDEX [indexname] ON [table_name] REBUILD;

  CREATE INDEX WITH DROP_EXISTING

 3. 能否方便地整理出比较通用的维护过程,实现自动化维护?

  a) 获取及查看所有索引的碎片情况

  SQL Server2000:

/*

描述:获取服务器上所有数据库的逻辑碎片率>5的索引信息

适用:SqlServer2000以后版本

*/

SET NOCOUNT ON

DECLARE @db_name varchar(128)

DECLARE @tablename varchar(128)

DECLARE @table_schema varchar(128)

DECLARE @execstr   varchar(255)

DECLARE @objectid  int

DECLARE @indexid   int

DECLARE @frag      decimal

DECLARE @maxfrag   decimal

DECLARE @sql    varchar(8000)

-- Decide on the maximum fragmentation to allow for.

SELECT @maxfrag = 5

-- Create the table.

if not exists(select 1 from sys.tables where name = 'dba_manage_index_defrag')

create table dba_manage_index_defrag

([db_name] varchar(255)

,[table_name] varchar(255)

,[index_name] varchar(255)

,avg_fragmentation_in_percent real

,write_time datetime default getdate()

)

if not exists(select 1 from dbo.sysobjects where name = 'dba_manage_index_defrag_temp')

CREATE TABLE dba_manage_index_defrag_temp (

   [db_name] char(255) default '',

   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)

-- Declare a cursor.

DECLARE databases CURSOR FOR

   select

  name

 from

  master.dbo.sysdatabases

 where

  dbid>4

-- Open the cursor.

open databases

fetch databases into @db_name

while (@@fetch_status=0)

begin

 insert into dba_manage_index_defrag_temp

 (ObjectName ,

   ObjectId ,

   IndexName,

   IndexId ,

   Lvl ,

   CountPages ,

   CountRows ,

   MinRecSize ,

   MaxRecSize ,

   AvgRecSize ,

   ForRecCount ,

   Extents ,

   ExtentSwitches ,

   AvgFreeBytes ,

   AvgPageDensity ,

   ScanDensity ,

   BestCount ,

   ActualCount ,

   LogicalFrag ,

   ExtentFrag )

 exec('use ['+@db_name+'];

    dbcc showcontig

   with

   FAST,

   TABLERESULTS,

   ALL_INDEXES,

   NO_INFOMSGS')

 

 update

   dba_manage_index_defrag_temp

 set

   [db_name] = @db_name

 where  

   [db_name] = ''

 fetch next from databases into @db_name

end

close databases

deallocate databases

insert into dba_manage_index_defrag

 ([db_name]

 ,[table_name]

 ,[index_name]

 ,avg_fragmentation_in_percent

 )

select

 [db_name],

 ObjectName [table_name],

 indexname [index_name],

 LogicalFrag [avg_fragmentation_in_percent]

from

 dba_manage_index_defrag_temp

where

 logicalfrag>5

-- Delete the temporary table.

DROP TABLE dba_manage_index_defrag_temp

GO

SELECT * FROM dba_manage_index_defrag  --查看结果

  SQL Server 2005:

/*

描述:只显示逻辑碎片率大于5%的索引信息

限制:针对SqlServer2005以后版本。

功能:对数据库服务器所有非系统数据库进行索引碎片检查

  返回碎片率>5%的索引信息

*/

create proc p_dba_manage_get_index_defrage

as

set nocount on

if not exists(select 1 from sys.tables where name = 'dba_manage_index_defrag')

create table dba_manage_index_defrag

([db_name] varchar(255)

,[table_name] varchar(255)

,[index_name] varchar(255)

,avg_fragmentation_in_percent real

,write_time datetime default getdate()

)

declare @db_name nvarchar(40)

set @db_name = ''

 

declare cur_db_name cursor for

 select

  name

 from

  sys.databases

 where

  database_id > 4 and state = 0

open cur_db_name

fetch cur_db_name into @db_name

while (@@fetch_status=0)

begin

 

 insert into dba_manage_index_defrag

   ([db_name]

   ,table_name

   ,index_name

   ,avg_fragmentation_in_percent)

 SELECT

   db_name(a.database_id) [db_name],

   c.name [table_name],

   b.name [index_name],

   a.avg_fragmentation_in_percent

 FROM

   sys.dm_db_index_physical_stats (DB_ID(@db_name), null,NULL, NULL, 'Limited') AS a

 JOIN

   sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id

 join

   sys.tables as c on a.object_id = c.object_id

 where

  a.index_id>0

  and a.avg_fragmentation_in_percent>5

fetch next from cur_db_name into @db_name

end

CLOSE cur_db_name

DEALLOCATE cur_db_name

GO

select * from dba_manage_index_defrag –查看结果

  b) 根据索引碎片的情况自动选择合适的处理方法

  针对Sql Server2000的联机维护:

/*Perform a 'USE <database name>' to select the database in which to run the script.*/

-- Declare variables

SET NOCOUNT ON;

DECLARE @tablename varchar(128);

DECLARE @execstr   varchar(255);

DECLARE @objectid  int;

DECLARE @indexid   int;

DECLARE @frag      decimal;

DECLARE @maxfrag   decimal;

-- Decide on the maximum fragmentation to allow for.

SELECT @maxfrag = 30.0;

-- Declare a cursor.

DECLARE tables CURSOR FOR

   SELECT TABLE_SCHEMA+'.'+TABLE_NAME --MSDN上面直接使用TABLE_NAME,如果SCHEMA不是DBO就会出错

   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 the cursor for the list of indexes to be defragged.

DECLARE indexes CURSOR FOR

   SELECT ObjectName, ObjectId, IndexId, LogicalFrag

   FROM #fraglist

   WHERE LogicalFrag >= @maxfrag

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

   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;

GO

  针对SQL Server 2000的脱机维护:

  sp_msforeachtable @command1="dbcc dbreindex('?','',85)"

  针对SQL Server 2005的通用维护过程

  (碎片小于30%的联机组织,碎片>=30%的脱机重建):

-- ensure a USE <databasename> statement has been executed first.

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;

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding

IF @frag < 30.0 and @frag>5

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

    IF @partitioncount > 1

        SELECT @command = @command + ' PARTITION=' + CONVERT (CHAR, @partitionnum);

    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;

GO

  总结

  索引的维护是有参考依据的,应该根据具体的碎片情况以及是否需要联机操作等需求,采用合理的维护方法。自动化的索引维护策略是可行的。

  评论这张
 
阅读(402)| 评论(0)

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018