Heim > Datenbank > MySQL-Tutorial > SQL Server 重建索引 Rebuild Index

SQL Server 重建索引 Rebuild Index

WBOY
Freigeben: 2016-06-07 14:56:55
Original
1953 Leute haben es durchsucht

重建索引是为了减少数据碎片。数据碎片会导致 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
Nach dem Login kopieren
 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  
Nach dem Login kopieren
 --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)  
Nach dem Login kopieren
--方法一:

 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  
Nach dem Login kopieren
Verwandte Etiketten:
Quelle:php.cn
Erklärung dieser Website
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Beliebte Tutorials
Mehr>
Neueste Downloads
Mehr>
Web-Effekte
Quellcode der Website
Website-Materialien
Frontend-Vorlage