首頁 >資料庫 >mysql教程 >SQL Server 重建索引 Rebuild Index

SQL Server 重建索引 Rebuild Index

WBOY
WBOY原創
2016-06-07 14:56:551963瀏覽

重建索引是为了减少数据碎片。数据碎片会导致 SQL Server 进行不必要的数据读,降低 SQL Server 的性能。重建索引也会同时更新列统计,而如果查询所使用的列缺少或遗漏统计信息,这可能导致 SQL Server 内部的优化器选择比预期效率低的查询计划。 如果您重建

重建索引是为了减少数据碎片。数据碎片会导致SQL Server进行不必要的数据读,降低SQL Server的性能。重建索引也会同时更新列统计,而如果查询所使用的列缺少或遗漏统计信息,这可能导致SQL Server内部的优化器选择比预期效率低的查询计划。

 

如果您重建了某张表上的聚集索引,该表上的非聚集索引也同时会被更新。

 

 

要更新索引,您可以使用Maintenance Wizard(相关内容您可以参考http://msdn.microsoft.com/en-us/library/ms180074.aspx),或在SQL Server代理(Agent)中运行如下的自定义代码来更新某个数据库中所有表上的索引:

您可以根据您的需求修改DBREINDEX的参数。

需要注意的是,重建非聚集索引时该表会暂时加上共享锁,对用户不可进行SELECT以外的操作;重建聚集索引时该表会暂时加上排外锁,不允许任何用户访问。因此需要制定好计划来预防可能的访问问题。

 

REBUILD有一个fill factor参数,如果fill factor设置为100%,这意味着每一个索引页都是完全满的,如果fill factor设置为50%意味着每个索引页都是半满的。对于fill factor 100%,每次新插入或更新一个记录,由于当前页没有空间可用,可能有分页情况产生。过多的分页会降低SQL Server的性能。下面具体举个例子:

假设您在一张表上建立了一个使用默认fill factor的新索引。当SQL Server创建索引时,它会把索引放置在连续的物理页上,以使数据顺序地被读,I/O访问最优化。但当表因INSERTUPDATEDELETE等操作增长改变时,分页发生,SQL Server在磁盘的其他地方分配新的页,导致新的页与原物理页不连续,增加了随机I/O,访问索引页变慢。

 

那么fill factor的合适值应该为多少?这取决于表的读/写比:

低更新表(读/写比:1001):100% fill factor

高更新表(写超过读):50%-70% fill factor

居中:80%-90% fill factor

 

过低的fill factor会增加页的数量,也会导致更多的页需要被移至缓存,缓存中有用的数据减少。默认的fill factor0(即100% fill factor),通常这不是个好的选择,特别是对于聚集索引。

如果您无法判断设置什么fill factor,您首先需要确定磁盘的读/写比.方法就是使用如下两个计数器:

Physical Disk Object: % Disk Read Time Physical Disk Object: % Write Time。另外一个可能有用的计数器就是:SQL Server Access Methods: Pages Splits/Sec。这个计数器测量SQL Server内每秒分页的次数。如果该数值过高,您需要降低fill factor防止新的分页。

 

如果您想确认您的索引因分页产生的碎片程度,您可以运行DBCC SHOWCONTIG命令。如果看特定表和特定索引,您可以运行如下代码:

结果集中最重要的参数是Scan Density,越接近100%越好。如果Scan Density小于75%,那么您可能需要重建表中的索引。

对于小于100数据页,重建索引并不会有明显的性能改善。这是因为物理硬件缓存,SQL Server缓存和SQL Server预读机制隐藏了碎片的负面作用。但对于非常大的表,重建索引会使它受益匪浅,因为涉及大量磁盘I/O操作。

ALTER INDEX Idx_name ON tblCompany REBUILD
 USE databasename --Enter the name of the database you want to reindex
DECLARE @TableName VARCHAR(255)
DECLARE tablecursor CURSOR FOR
  SELECT table_name
  FROM   information_schema.tables
  WHERE  table_type = 'base table'

OPEN tablecursor

FETCH next FROM tablecursor INTO @TableName

WHILE @@FETCH_STATUS = 0
  BEGIN
      DBCC dbreindex(@TableName, '', 90)

      FETCH next FROM tablecursor INTO @TableName
  END

CLOSE tablecursor

DEALLOCATE tablecursor  
 --Script to identify table fragmentation
--Declare variables
DECLARE @ID        INT,
        @IndexID   INT,
        @IndexName VARCHAR(128)

--Set the table and index to be examined
SELECT @IndexName = 'index_name' --enter name of index
SET @ID = Object_id('table_name') --enter name of table
--Get the Index Values
SELECT @IndexID = indid
FROM   sysindexes
WHERE  id = @ID
       AND name = @IndexName

--Display the fragmentation
DBCC showcontig (@id, @IndexID)  
--方法一:

 USE adventureworks

go

EXEC Sp_msforeachtable
  @command1="print '?' DBCC DBREINDEX ('?', '', 90)"

go

EXEC Sp_updatestats

go  

--方法二:

USE adventureworks

go

CREATE PROCEDURE Usp_reindex_updatestats
AS
    DECLARE @MyTable VARCHAR(255)
    DECLARE mycursor CURSOR FOR
      SELECT table_name
      FROM   information_schema.tables
      WHERE  table_type = 'base table'
             AND table_name NOT LIKE 'sys%'

    OPEN mycursor

    FETCH next FROM mycursor INTO @MyTable

    WHILE @@FETCH_STATUS = 0
      BEGIN
          PRINT 'Reindexing Table:  ' + @MyTable

          DBCC dbreindex(@MyTable, '', 90)

          FETCH next FROM mycursor INTO @MyTable
      END

    CLOSE mycursor

    DEALLOCATE mycursor

    EXEC Sp_updatestats

go  
陳述:
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn