您现在的位置： IT专家网 > 数据库子站 > 数据库技巧

SQL Server索引维护指导

作者: 王红波, 　出处:IT专家网,　责任编辑: 李书琴,　

2008-04-15 09:48

　　本文以笔者在实际工作中对SQL Server数据库中索引维护的思路和方法为导向，为大家介绍SQL Server索引维护相关的知识和方法。

　　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