Kanasz罗伯特
第2部分（索引维护）:数据库性能优化
{A}{A2的}{A3的} {A4纸} {A5的} {A6的} {A7的}{A8的} {A9的} {A10的} 
&
#160; {A13号}{A14高速公路} {A15} {A16}
0;{A17中}
在我的{A18} 有关数据库性能调整的文章中，我所描述的索引策略和我刚才提到的某些方面，你应该牢记，在设计索引。现在，我将继续讨论有关数据库调优。在第二篇文章中，我会告诉你有关索引的维护。有没有什么新的指标，在SQL Server中存在的权利。落实指标不是这么简单的任务，因为设计良好的索引策略可以迅速提高数据库的性能，但尴尬的指标，另一方面会导致性能下降。由于几乎所有数据库中需要维护，索引太需要它。索引维护的是一种罕见的，非常艰巨的任务，因为某些操作可能需要很长一段时间，你必须记住这一点，当你计划维护。
作为DBA，您必须确保数据库的最佳性能。正如我在上面提到的重点任务之一是维护索引。使一个很好的指标是指以减少I / O操作。有一些活动作为DBA，你应该执行:改组和重建索引。这个过程涉及的索引和磁盘空间的优化重组。使用fill factor选项进行微调索引数据存储和性能。执行在线索引操作，通过使用行索引选项提供用户访问数据在索引操作期间。配置并行索引操作使用的最大并行度选项。使用查询优化器来开发基于统计最优的查询计划。提高大容量复制操作的群集和非聚集索引的表上的表现。选择合适的恢复模式的数据库索引操作，并列出可用的日志记录的类型。关于本活动中，我们将在后面讨论int的这篇文章。一开始，我认为是适当的，以确定一些基本条件，稍后我们将使用。
{A19中}
了解聚集索引是很容易在指数选择方面。聚集索引基本上都是引用每一行唯一的钥匙。即使你定义一个聚集索引和独特，SQL Server的不申报，仍然使聚集索引的独特幕后加入一个4字节的quot; uniqueifierquot;它。额外quot; uniqueifierquot;增加，从而导致增加了维护时间和较慢的搜索聚集索引的宽度。由于聚集索引标识每一行的关键，他们在每个查询使用。
当我们开始谈论非聚集索引，事情变得扑朔迷离。查询可以忽略非聚集索引，原因如下:高不成？如果指数超过40％的碎片，优化可能会忽略的指标，因为它是更昂贵的搜索一个支离破碎的指数，而不是执行表扫描。独特性？？如果优化器确定一个非聚集索引是不是很独特，它可以决定一个表扫描是比试图使用非聚集索引快。例如:如果一个查询引用位列（其中位= 1）和列的统计数据说，75％的行1，那么优化可能会决定表扫描会得到更快与尝试扫描的结果在一个非聚集索引。过时的统计？？如果列上的统计数据已经过时，那么SQL Server可以误导一个非聚集索引的好处。自动更新的统计数据不只是减慢数据修改脚本，但随着时间的推移，它也成为同步与行的真正的统计数字。偶尔，它是一个好主意，运行sp_updatestats或更新统计。函数的用法？？SQL Server中是无法使用索引，如果一个函数是在目前的标准。如果你引用一个非聚集索引列，但你正在使用的功能，如CONVERT（VARCHAR，Col1_Year）= 2004，则SQL Server不能上Col1_Year使用索引。？错列，如果一个非聚集索引的定义（COL1，COL2，COL3）和您的查询中有一个条款，如"COL2 ='somevalue'quot，该指数将不会被使用。只能用一个非聚集索引，如果索引中的第一列在where子句中引用。一个条款，如quot; COL3 ='someval'quot，不会使用索引，但一个条款，如quot; COL1 ='someval'quot;或"COL1 ='someval和col3 = "someval2'quot;便拿起指数。该指数不会使用COL3其寻求的，因为该列后COL1在索引定义。如果你想COL3有一个寻求在像这样的情况下发生的，那么它就是最好的，如果你定义两个独立的非聚集索引，一个在col1和COL3的。
{A20的}
来存储数据，SQL Server使用8 KB数据块的网页。填补了网页的数据量被称为填充因子，填充因子越高，更加饱满的8 KB页。意味着较少的页面将导致更少的IO / CPU / RAM的使用需要较高的填充因子。在这一点上，你可能要设置您的所有索引，以100％的填充因子，然而，这里是"疑难杂症:一旦网页填写和值适合填充索引范围内，则SQL Server将使房指数由做quot;页split.quot;
在本质上，SQL Server将完整的网页，并分成两个单独的页面，其中有大幅度增加，在这一点上。你可以考虑这个问题，设置了70％左右的填充因子。这允许传入值的30％的自由空间。这种方法的问题是，你不断有quot;重新indexquot;指数，以便它保持了30％的可用空间百分比。{A21号}
虽然索引可以加快查询执行的若干倍，因为他们可以更快地使查询过程中，还有与之相关的开销。他们占用额外的磁盘空间，需要更多的时间来更新自己每当数据更新，删除或追加表。此外，当您执行索引碎片可能发生的任何数据修改操作（INSERT，UPDATE或DELETE语句）和索引中的信息可以在数据库中分散。支离破碎指数数据可以导致SQL Server进行不必要的数据读取和切换不同页面，可以对大量零散的表的查询性能很差。在这篇文章中，我会写碎片和不同的查询，以确定碎片的级别。有两大类型的破碎:逻辑（外部碎片）和SQL Server的碎片。{A22号}
逻辑碎片发生时，索引的叶子页面是没有逻辑顺序，换句话说，它发生时，该指数的逻辑顺序不匹配索引的物理顺序。这将导致SQL Server执行额外的工作，返回排序的结果。在大多数情况下，外部碎片是为处理特定搜索返回很少记录或查询返回的结果集，不需要责令不太大。页拆分可能发生的情况下:插入或更新沉重删除，可能会导致网页被删除从页链，显示连续的页链
{A23}
这种类型的碎片发生时，有太多自由空间索引页。通常情况下，一些自由的空间是可取的，尤其是当创建索引或重建。你可以指定填充因子设置建立或重建索引时表示索引页创建时的百分比。如果过于分散的索引页，它会导致查询需要更长的时间（因为额外的读取需要找到数据集），并导致索引增长超过必要的。如果没有空间可用的索引中的数据页，数据的变化（主要是插入）会导致页拆分正如上面所讨论的，这也需要额外的系统资源来执行。内部碎片通常发生在:在空的空间数据页上随机删除页拆分由于插入或更新更新一个较大的值，以一个较小的值时，萎缩，如行使用小于100的填充因子使用一个大的行大小
{A24的}
碎片程度时，会出现一个表或索引的范围与数据库离开混杂在文件中的一个或多个索引的程度是不连续的。这可能是由于:随机删除，可以离开一些，而在一定程度上未使用的页面的程度本身仍保留部分作为表的空间分配。认为它像内部的分裂，但在程度，而不是页面删除表内的连续行的范围，导致整个程度的一个或多个成为分配，从而留下一个表或索引的周围程度之间的差距交叉表的数据扩展与其他对象的范围
{A25}
严重碎片化的指标可以降低查询性能显着，并导致应用程序访问它的响应速度很慢。为了帮助您确定碎片的SQL Server 2005和2008nbsp金额; 来;与动态管理function.nbsp {A26}
下面的查询将返回有关碎片水平AdvendureWorks2008R2数据库的详细信息。select
OBJECT_NAME(stats.object_id) as [Object_Name],
idx.name as [Index_Name] ,
stats.avg_fragmentation_in_percent,
stats.avg_page_space_used_in_percent
from
(select OBJECT_ID, index_id, avg_fragmentation_in_percent, avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats (DB_ID('AdventureWorks2008R2'),null,null,null,'DETAILED')
where index_id<>0) as stats
join sys.indexes idx
on idx.object_id=stats.object_id
and idx.index_id = stats.index_id
{A27高速公路}
当你想解决一些碎片问题，SQL Server提供了如何解决问题的ALTER INDEX ...重组或ALTER INDEX ...重建。{A28}
在SQL Server 2005的ALTER INDEX重组语句已取代DBCC INDEXDEFRAG语句。可以重组一个分区索引的单个分区，使用此语句。
此语句整理的叶级聚集和非聚集索引表和视图的重组和重新安排，以符合逻辑顺序，即左到右叶节点。该指数是在分配页和组织，如果他们跨越多个文件，它们是重组一次。没有页面文件之间的迁移。此外，网页压缩，因此创建的空页面被删除，并释放磁盘空间。压实sys.indexes目录视图中的填充因子值确定。聚集索引或基础表中的大对象数据类型也将被压缩，默认情况下，如果LOB条款存在。
好消息是，重组过程中对系统资源的经济和自动执行线上。有没有长期阻塞锁了工程果酱！DBA的建议重组索引，当它被微创支离破碎。大量零散的索引需要重建。 Microsoft建议重组索引当60
内部碎片被删除由填充factor.nbsp;您可以使用ONLINE选项，这将导致该表和它的索引是用于选择和数据modification.nbsp;{A30的}
当你重建索引，默认情况下SQL Server的锁，以防止任何数据修改的表。重写此行为，您可以使用ONLINE选项。在这种情况下，SQL Server的索引重建IN3阶段。准备phaseBuild Finalnbsp;
在筹备阶段所有系统元数据的收集，创建一个新的空索引结构。快照表定义和行versioningnbsp;用于提供事务级的读一致性。很短的时间，任何concurrentnbsp; 写在桌子上的操作被封锁
在构建阶段中的数据进行扫描，排序，合并，并插入到target.nbsp的;并发用户选择，插入，更新，并删除操作应用于原有指标和任何新的指标是built.nbsp
前的最后阶段，所有未提交的更新事务必须完成。 所有读取和写入操作是一个很短的时间内阻塞，直到此阶段完成。系统元数据被更新到源对象取代的target.nbsp的;
{A31号}
SQL Server 2005企业版支持使用以下索引创建多个处理器和重建报表:
CREATE INDEX语句的DROP INDEX（只适用于聚集索引）改变表添加约束（只适用于索引约束）的ALTER TABLE DROP约束（只适用于聚集索引）使用的处理器的数量取决于配置选项和正在创建索引时要重修时的免费资源量"最大并行度"。建设大型的索引使用所有可用的处理器将提供最佳的性能，然而，这也可能导致用户查询资源的严重短缺。因此，如果SQL Server决定，该系统是忙碌的，它只会索引操作中使用的处理器的子集。使用MAXDOP查询提示可以并处索引操作的处理器使用率的进一步限制，例如，下面的语句限制在2个处理器的ALTER INDEX语句:
{C}
如果的MAXDOP提示指定所有索引操作将是连续的，而不是平行; 0是默认值，不限制数量的处理器。任何大于1的值将被解释为要考虑的处理器数量，但是，用于建立索引的处理器的实际数量可能取决于系统负载。
并行索引操作可能是不结盟的分区索引，特别是资源密集型。这是因为SQL Server必须为每个分区（无论是在各自的文件组，分区存储在tempdb中，取决于SORT_IN_TEMPDB选项是否用于）建立一个排序表。如果索引与表分区方案或聚集索引对齐，然后建立排序表顺序为每个分区。但对于所有非对齐索引排序表是建立在一个操作中，除非你设置最大并行度为1。并行度较高的排序表，必须建立不结盟分区索引在同一时间，因此，将需要更多的内存。如果系统没有足够的内存来创建一个表上的索引足够的排序表，与众多的分区操作将失败。如果是这样，你通常可以解决该问题通过指定并行度较低。第A32}
正如我前面提到的DBA的主要任务之一是维护索引。有时索引需要重建。这项任务可能是一个时间消耗在许多指标的情况下。 ALTER INDEX语句允许您重建索引，但你必须指定对象的名称，您要重建indexes.Using ALTER INDEX ALL选项允许你指定表或视图的所有索引重建，但如果你有很多的表？这可能是一个问题，因为你必须创建所有你要重建索引的表或视图的ALTER语句。在这里，我为您提供一个重建所有索引的所有表和数据库的脚本。DECLARE @Database VARCHAR(255)
DECLARE @Table VARCHAR(255)
DECLARE @cmd NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 90
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','msdb','tempdb','model','distribution')
ORDER BY 1
OPEN DatabaseCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' +
table_name + '']'' as tableName FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES
WHERE table_type = ''BASE TABLE'''
-- create table cursor
EXEC (@cmd)
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@@MICROSOFTVERSION / POWER(2, 24) >= 9)
BEGIN
-- SQL 2005 or higher command
SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' +CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@cmd)
END
ELSE
BEGIN
-- SQL 2000 command
DBCC DBREINDEX(@Table,' ',@fillfactor)
END
FETCH NEXT FROM TableCursor INTO @Table
END
CLOSE TableCursor
DEALLOCATE TableCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
这个脚本创建的Greg Robidoux，你可以找到更多有关此{A34高速公路}
索引可以迅速影响数据库的性能。索引太少，可能会导致扫描和，从而大大提高您的应用程序的响应时间。过多的索引会导致索引的维护开销，在数据更新和臃肿数据库。这是非常困难的，知道哪些索引正在使用以及它们是如何被使用。有时是非常好的得到消除未使用的索引。对于这种类型的任务的SQL Server带有许多新的动态管理视图。两个新的看法，提供有关索引使用的数据是sys.dm_db_index_operational_stats和sys.dm_db_index_usage_stats。{A35号}
这种动态管理视图为您提供了关于插入的信息，更新和删除特定的索引操作。此DMV还提供了锁定，锁定和访问方法的信息。有几列返回类型从这个角度:叶级插入leaf_insert_count - 总数叶级删除leaf_delete_count - 总数leaf_update_count - 叶级更新总数row_lock_count - 行锁的总数 - 次row_lock_wait_count总数的数据库引擎上的行锁等待page_latch_wait_count - 数据库引擎等待的时间总数，由于闩锁争
{C5的}
下面是从上述查询的输出。从这一观点，我们可以得到一个想法多少插入，更新和删除操作，对每个表和索引执行。{S0的}
这种观点给你有关索引的整体接入方式的信息。有几列，从这个DMV返回，但这里有一些有用的关于使用索引列:user_seeks - 指数旨在user_scans - 索引扫描user_lookups - 索引查找user_updates - 数字插入，更新或删除操作
{5233}
下面是从上述查询的输出。从这一观点，我们可以得到多少的目的，扫描，查找和整体更新（插入，更新和删除）发生的想法。{S}
以下示例返回所有尚未使用的索引。{C7-}{S2的}
在此查询中，我们列出每个用户表，所有指标和指数列。与此查询的问题是，你有一排可以得到混乱，如果你有很多指标的指数中的每一列。{C8的}{中三}
在此查询中，我们使用的查询，但我们正在做的一个支点，所以我们可以看到一排索引和索引列。这只占7索引列，但它可以很容易地增加更多的处理PIVOT操作。这里是另一个支点，交叉使用的PIVOT查询在SQL Server 2005使用尖。如果你想更好地了解如何PIVOT作用。SELECT TABLENAME, INDEXNAME, INDEXID, [1] AS COL1, [2] AS COL2, [3] AS COL3,
[4] AS COL4, [5] AS COL5, [6] AS COL6, [7] AS COL7
FROM (SELECT A.NAME AS TABLENAME,
B.NAME AS INDEXNAME,
B.INDEX_ID AS INDEXID,
D.NAME AS COLUMNNAME,
C.KEY_ORDINAL
FROM SYS.OBJECTS A
INNER JOIN SYS.INDEXES B
ON A.OBJECT_ID = B.OBJECT_ID
INNER JOIN SYS.INDEX_COLUMNS C
ON B.OBJECT_ID = C.OBJECT_ID
AND B.INDEX_ID = C.INDEX_ID
INNER JOIN SYS.COLUMNS D
ON C.OBJECT_ID = D.OBJECT_ID
AND C.COLUMN_ID = D.COLUMN_ID
WHERE A.TYPE <> 'S') P
PIVOT
(MIN(COLUMNNAME)
FOR KEY_ORDINAL IN ( [1],[2],[3],[4],[5],[6],[7] ) ) AS PVT
ORDER BY TABLENAME, INDEXNAME;
的
在此查询，我们在我们上面的PIVOT查询与sys.dm_db_index_usage_stats配合，所以我们可以看已自最后一次重置的统计指标。{C10的}{五}
此查询还使用与sys.dm_db_index_usage_stats PIVOT查询，所以我们还可以看到上已使用的索引的统计信息。{C11的}{六}
这方面的例子都取自{A37}历史
8月22日 - 原始版本发表于