sql server性能优化的一些建议(转)-白红宇

sql server性能优化的一些建议(转)

阅读量：5024 次

发布时间：2019-06-12

本文共 28929 字，大约阅读时间需要 96 分钟。

概观

有许多问题影响SQL Server性能和可扩展性。本章讨论这些问题，从数据设计和部署结束。本章强调技巧，你可以使用在性能和可扩展性，以获得最大的收益。你通常能获得最大的收益，通过建立高效的数据访问应用程序的代码，并使用正确的开发技术。你通常不会取得这么大的收益在性能和可扩展性，通过改变SQL Server配置设置。

如图14.1所示，最好是集中你的性能设计和调整力度。

Ff647793.ch14到调（EN-US PandP.10）。gif文件

图14.1：聚焦性能的设计和调整力度

该图是反映了典型的情况，并强调这一点，你获得最佳的性能和可扩展性收益在应用程序开发中。索引被认为是应用程序开发工作的一部分，虽然它也是管理的一部分。

如何使用本章

使用本章应用行之有效的战略和最佳实践，设计和编写高性能的互操作代码。为了得到最出这一章，做到以下几点：

跳转到主题或阅读从开始到结束。在本章的主要标题帮助您找到您感兴趣的主题。另外，你可以读一章，从开始到结束的性能和可扩展性的设计问题，以透彻的升值。

测量你的应用程序的性能。阅读“ADO.NET /数据接入”和“。NET框架技术”部分的第15章，“ “，以了解你可以用它来测量应用程序的性能的关键指标。重要的是为您测量应用程序的性能，让您可以准确地识别并解决性能问题。

测试您的应用程序的性能。阅读第16章，“ “学习如何应用到您的应用程序的性能测试。重要的是，你申请一个连贯的测试过程和结果进行分析。

调整你的应用程序的性能。阅读“ADO.NET调整”和“SQL服务器调节”部分第17章，“ “，以了解如何解决性能问题，您通过使用调谐指标确定。

本指南中的“检查表”部分使用随附的清单。使用“ “清单，以快速查看和评估的指导方针本章中介绍。

SQL：扩展与向外扩展

向上扩展是指应用程序移动到一个更大的使用更强大的处理器，更多的内存，更快的磁盘驱动器类的硬件。向外扩展是指实施联合服务器，消费类电脑的增加和数据的分区或复制它们之间。你可以扩展使用功能分区。例如，你把你的客户关系管理（CRM）功能，在一台服务器和企业资源规划（ERP）在另一台服务器上的功能可能向外扩展。或者，你可以向外扩展，通过使用数据分区。例如，您可能向外扩展，通过创建跨数据库的可更新的分区视图。

不要考虑扩展或向外扩展，直到你确定你得到最佳的性能，你可以通过应用优化。当它涉及到两个常见的可伸缩性的瓶颈考虑以下方案：

处理器和内存相关的瓶颈。向上扩展通常是一个很好的方法，如果你的瓶颈是处理器或内存有关。通过升级到更快的处理器，或通过添加更多的处理器，最大限度地利用现有的硬件资源。您可以通过添加额外的内存或升级现有的内存解决内存瓶颈。/ 3GB开关在Boot.ini文件中和地址窗口扩展（AWE），也有助于最大限度地提高内存的使用。
欲了解更多信息，亚洲国际博览馆，为“亚洲国际博览馆的SQL Server”（不带引号），在Microsoft支持网站搜索。

磁盘I / O 相关的瓶颈。向上扩展也可以帮助解决磁盘I / O？相关的瓶颈。这种形式的瓶颈通常发生在联机事务处理（OLTP）应用程序在应用程序执行随机磁盘读取和写入，循序存取联机分析处理（OLAP）应用相反。对于OLTP应用程序的I / O负载，可以通过添加磁盘驱动器传播。添加内存也有助于减少I / O负载。由于减少I / O负载，在SQL Server缓冲区高速缓存的大小增加。其结果是，页面错误减少。

你决定扩展或向外前考虑以下指导原则：

前优化应用程序扩展或向外扩展。

解决历史和报告数据。

对于大多数应用规模。

规模扩大时是不够的，或成本过高。

向上扩展或向外扩展之前，优化应用程序

在你决定要扩展或向外扩展，你需要可以肯定的，它是需要。缩放效果最好，当您在您的应用开发生命周期的早期阶段的规划和设计。更改您的应用程序后，它是在生产，使您可以扩展或向外是昂贵的。此外，若干初步设计的决定，你可能会阻止您以后扩展。

执行中概述这一章的其余部分的优化步骤，可以解决大多数性能和可伸缩性问题。这些优化有助于减少的影响所造成的具体的设计或执行方面的技术瓶颈。这些优化还有助于确保现有资源得到充分利用。优化，例如，你可以解决瓶颈所造成的低效的锁定，毫无准备的SQL语句，导致CPU使用率增加的贫困指标，内存或磁盘I / O利用率。

在实践中，你需要模拟数据的使用和使用实际工作量的增长，在应用程序生命周期的早期。模拟数据的使用和增长，可以帮助您识别可扩展性的问题宜早不宜迟，这样就可以修改你的设计和方法来减轻这些问题。

解决历史与报告数据

历史数据可能会变得非常大，随着时间的推移，可能会导致长时间运行的查询。考虑一定范围内的历史数据进行分区，和实施的方式来限制较旧的数据。无论是离线移动较旧的数据，或实现一个单独的数据仓库，包含较旧的数据。

报告的需求也可能是非常耗费资源。您可能会考虑升级你的数据库服务器或向外扩展，以满足您的报告需求。通过实施数据仓库或报告服务器，您可能能够提供更快的响应时间和减少资源竞争。此外，数据仓库或报告服务器更易于管理的多台服务器在联合服务器方案。

更多信息

如何分区的历史数据欲了解更多信息，请参阅“垂直和水平分区表”在本章的后面。

对于大多数应用程序扩展

如果您仍然有高水平的系统资源使用你调整你的应用程序后，您解决历史数据和报告问题后，考虑更换新的，更快的组件较慢的硬件组件。或者，考虑到您现有的服务器中添加更多的硬件。

高层次的系统资源使用包括高CPU利用率，内存使用高，过多的磁盘I / O。您可以添加新的组件包括额外的处理器或内存。另外，考虑更换现有的服务器，一个新的，更强大的服务器。

确保任何配置的变化充分利用新的硬件。例如，您可能需要在Boot.ini文件中使用/ 3GB开关。这是一个简单的迁移和维护原因下一步。您应该进行测试，以帮助确定您所需要的新的服务器容量。

更多信息

有关测试的更多信息，请参见第16章，“ “

规模扩大是不够的，或成本过高

如果您的应用程序仍无法执行不够好，你可以考虑向外扩展或实施一个联合服务器选项。这些方法通常需要一定水平分区表，让他们居住在单独的服务器上。方法可能还需要某些域的主表，也可以在一个分区上的服务器之间复制。

也更复杂的联合服务器的灾难恢复和故障转移。你必须确定，如果这种附加的复杂的利益大于消费类计算机能够使用联合服务器的成本优势。

性能和可扩展性问题

有关SQL Server的影响您的应用程序的性能和可扩展性的主要问题归纳在这一节。在本章后面的章节提供战略和技术实施细节，以帮助您防止或解决以下问题：

不知道您的系统性能和可扩展性的特点。如果一个系统的性能和可伸缩性对你很重要，最大的错误，你可以做的是不知道的重要查询的实际性能和可扩展性的特点，和不同的查询在一个多用户系统之间的相互影响。实现性能和可扩展性，当你限制资源使用和处理这些资源的争夺。引起争论的是锁定和物理争夺。资源的使用，包括CPU利用率，网络I / O，磁盘I / O和内存使用。

检索数据太多。一个常见的错误是获取更多的数据比你实际需要。检索数据太多导致网络流量增加，并增加了服务器和客户端资源的使用。这可以包括两列和行。

交易的滥用。长期运行的交易，交易依赖于用户输入，提交，从来没有承诺，因为一个错误的交易，内部交易和非事务性查询导致的可扩展性和性能方面的问题，因为他们锁定资源长于需要。

滥用的索引。如果你不创建索引，支持对你的服务器发出的查询，您的应用程序的性能受到了影响。不过，如果你有太多的索引，插入和更新应用程序的性能会受到影响。您有索引中写入和读取如何使用您的应用程序是基于需求之间找到一个平衡点。

混合OLTP，OLAP和报告工作负载。OLTP工作负载的特点是由许多小的交易，从用户的期望非常快的响应时间。OLAP和报告工作负载的特点是由少数长时间运行的操作，可能会消耗更多的资源，并导致更多的竞争。长时间运行的操作所造成的锁定和底层物理子系统。你必须解决这个矛盾，实现了可扩展的系统。

低效模式。添加索引可以帮助提高性能。然而，他们的影响可能是有限的，如果你的查询都是低效的，因为表的设计不佳太多联接操作或低效的联接操作的结果。架构设计是一个关键的性能因素。它还提供信息的服务器上可能被用来优化查询计划。架构设计主要是良好的读取性能和良好的写入性能之间的权衡。规范化有助于写性能。非规范化有助于读取性能。

使用低效率的磁盘子系统。物理磁盘子系统必须提供足够的I / O处理能力的数据库服务器，允许数据库服务器运行或长期无磁盘排队的I / O等待。

架构

好，高效的架构设计是必不可少的高性能数据访问。当你设计你的数据库架构时，考虑以下指导原则：

适当的资源投入到架构设计。

分离OLAP和OLTP工作负载。

先标准化，非规范化性能。

定义所有的主键和外键关系。

定义所有唯一的约束和检查约束。

选择最合适的数据类型。

使用非规范化的索引视图。

分区表的垂直和水平。

适当的资源投入到架构设计

太多组织设计表都需要在最后一分钟时，他们的查询表。花费时间和资源投入所需要的收集业务需求，设计合适的数据模型，并测试数据模型。确保你的设计是适合您的业务和设计，准确地反映了所有对象之间的关系。更改数据模型后，你的系统已经在生产价格昂贵，费时，并不可避免地影响了很多代码。

独立的OLAP和OLTP工作负载

在一台服务器上的OLAP和OLTP工作负载进行设计，以不妨碍对方。OLAP和报告工作负载的特点往往频繁，长时间运行的查询。用户很少焦急地等待查询完成。OLTP工作负载的特点往往通过大量的小额交易，在不到一秒钟内返回给用户的东西。长时间运行的查询，分析，报告，或即席查询可能会阻止在OLTP工作负载的刀片及其他交易，直至完成OLAP查询。

如果你需要同时支持工作负载，可以考虑创建一个报告服务器支持OLAP和报告工作负载。如果你进行大量的分析，考虑使用SQL Server分析服务来执行这些功能。

标准化首先，非规范化的绩效后

实现好，逻辑数据库设计应用规范化规则设计。规范化提供了几个好处，如减少冗余数据。当你减少冗余数据，您可以创建狭窄，紧凑表。然而，数据库架构overnormalization可能会影响性能和可扩展性。获取正确的规范化程度涉及权衡。一方面，你想有一个规范化的数据库，以限制数据复制，以保持数据的完整性。另一方面，它可以是很难对完全归一化的数据库的程序，和性能会受到影响。

地址是一个数据模型，其中的一部分是典型的非规范化。因为许多系统在很长一段时间的公司或个人存储多个地址，它是关系纠正有一个单独的地址表和连接到该表总是适用的地址。但是，它是常见的做法，以保持在个人表甚至是重复的当前地址，以保持两个地址，因为这种类型的信息是相当静态的，并经常被访问。避免了额外的连接的性能优势，在这种情况下，一般大于一致性问题。

以下非规范化的方法可以帮助：

开始与归一化模型，然后如果需要，非规范化。不要启动与非规范化的模型，然后将其正常化。通常情况下，每一个非规范化要求补偿行动，以确保数据的一致性。补偿行动可能会影响性能。

避免高度抽象的对象模型可能会非常灵活，但复杂，难于理解，导致太多的自我联结。例如，许多事情可以模拟使用对象表，属性表和关系表。此对象模型是非常灵活的，但自联接，别名加入，加入的数量变得如此繁琐，它不仅是难写的查询和了解他们，但遭受的性能和可扩展性。一个抽象的对象模型，试图找到一些常见的对象类型，可以用来作为根据一般的Object类型的亚型，然后尝试的灵活性和性能之间找到最佳平衡点。

定义所有的主键和外键关系

正确定义主键和外键关系有助于确保你能写出最优的查询。一个常见的结果是不正确的关系添加DISTINCT子句，以消除冗余数据，从结果集。

当主键和外键被定义为在数据库架构的限制，服务器可以利用这些信息来创造最佳的执行计划。

声明引用完整性（DRI）的性能优于触发器做，和DRI是更容易维护和故障排除比触发器。DRI是由服务器检查服务器之前执行实际的数据修改请求。当你使用触发器，插入和删除的临时系统表中插入数据修改请求，的触发代码运行。根据触发代码，最后的修改，然后作出或不发，

在图14.2中的示例屏幕截图显示了一个执行计划，访问虽然只有一张桌子，两个表都包含在查询中联接。因为是作者表和titleauthor表中声明的外键关系，并为au_id列在titleauthor上表中是不允许为空，优化器知道它并不需要访问的authors表来解决查询。SET STATISTICS IO命令的结果还表明，永远不会访问的authors表。

Ff647793.ch14采样执行计划（EN-US PandP.10）。

图14.2：样品的执行计划

定义所有的唯一约束和检查约束

唯一约束和检查约束的优化使用，以创造最佳的执行计划提供更多的信息。唯一约束给出了的优化信息预期的结果。可以使用检查约束，以确定是否有一个表或索引进行访问找到结果集。

图14.3显示了一个查询中引用一个表，在执行时不会被扫描检查约束，可以返回任何行，因为优化器知道。要尝试这个例子，创建一个只允许值大于零的数量列的检查约束。SET STATISTICS IO命令输出显示物理或逻辑读取和扫描计数为零。输出显示了这一点，因为约束信息查询回答。

图14.3：一个检查约束，以防止不必要的读取范例

更多信息

欲了解更多信息，请参阅MSDN文章“SET STATISTICS IO” 。

选择最合适的数据类型

选择最合适的数据类型，用适当的规模和可为空。考虑下列各当你选择一个数据类型：

尽量选择最小的数据类型，为每列。此外，选择最合适的类型，因为显式和隐式转换可能是昂贵的时候，它需要做转换。他们也可能是昂贵的表或索引扫描，这可能是因为优化器不能使用索引来评估查询方面。

尽量避免为空的外键列的数额限制外联接，可能需要编写。外连接往往是更昂贵的处理比内部连接。如果有外键值可能不知道的情况下，可以考虑在其他表中添加行，这将是未知的情况下。一些数据库架构师使用未知的情况下，一排的情况下是不适用的，和尚未确定的情况下的一排一排。这种做法不仅可以用于内部连接，而不是外连接，但它提供了更多的信息外键值的实际性质。

使用文本数据类型的列有额外的开销，因为他们是分别存储的文本/图像页面，而不是在数据页上。使用VARCHAR类型，而不是列中包含不到8000个字符的文本卓越的性能。

sql_variant的数据类型允许单个列，参数或变量来存储不同的数据类型，如int和NCHAR数据值。然而，一个sql_variant列的每个实例的数据值和额外的元数据记录。元数据包括基本数据类型，最大大小，规模，精度，和整理。虽然sql_variant的灵活性，sql_variant的使用会影响性能，因为额外的数据类型转换。

NCHAR和nvarchar Unicode数据类型，如采取两倍多的存储空间，相比ASCII数据类型，如CHAR和VARCHAR。下面的“更多信息”一节中引用的文章中讨论到SQL Server的速度的具体因素。但是请注意，在Microsoft NET Framework和在Microsoft Windows 2000内核是Unicode字符串。如果您需要或预期需要Unicode支持，不要犹豫，使用它们。

更多信息

欲了解更多信息，请参阅MSDN上的“性能和存储空间”一节的“国际功能在Microsoft SQL Server 2000” 。

非规范化使用索引视图

当你加入跨多个表不经常改变，如域或查找表，以获得更好的性能，你可以定义一个索引视图。索引视图是一个视图，物理存储的表像。更新索引视图时，SQL Server的表，索引视图是基于更新。这有额外的好处拉我/ O从主表和索引。

水平和垂直分区表

您可以使用垂直分区表不常使用的移动到另一个表中的列。移动不常使用的列窄使得主表，并允许更多的行，以适应一个页面上。

水平的表分区是一个比较复杂一点。但是，当使用水平的表分区表的设计正确，你可能会获得巨大的可扩展性收益。水平的表分区最常见的情况之一是支持的历史或归档数据库分区，可以很容易地划定日期。一个简单的方法，你可以用它来查看数据使用分区视图与检查约束。

依赖于数据的路由选择是非常大的系统更加有效。通过这种方法，您可以使用表来保存分区信息。访问直接路由到相应的分区，从而避免分区视图的开销。

如果您使用的分区视图，确保执行计划显示，只有相关的分区被访问。图14.4显示了一个执行计划超过三个订单水平已订购日期列分区表的一个分区视图。每年有一个表，1997年，1996年和1998年。每个表都有一个，有一个检查约束的partitionid列。还有一个分区表，其中包含一个的partitionid年该分区。然后查询使用分区表，能得到相应的partitionid每年只能访问相应的分区。

虽然图形查询计划包括计划中的两个表，移动鼠标，在过滤器的图标提示，表明这是一个开始看到过滤器，在启动子句中的过滤器的参数。一开始过滤器是一种特殊类型的滤波器，你希望看到计划使用分区视图。

图14.4：过滤器的细节，显示执行计划

注意，SET STATISITCS的IO输出，如图14.5所示，只有Orders98表实际上是访问。

图14.5：SET STATISTICS IO输出

更多信息

对于图形执行计划的更多信息，请参阅“图形显示在MSDN上使用SQL查询分析器”执行计划。

查询

在SQL Server中编写高效的查询，更是一个行使写典雅的关系查询比知道特定的技巧和语法提示。一般来说，一个写得很好，写对一个精心设计的关系数据库模型正确，使用正确的索引关系正确的查询产生的系统表现相当不错，这是可扩展的。下面的指南可以帮助您创建高效的查询：

了解查询性能和可扩展性的特点。

写形成正确的查询。

只返回所需要的行和列。

避免昂贵的运营商，如NOT LIKE。

避免在WHERE子句中显式或隐式的功能。

使用锁定和隔离级别的提示，以尽量减少锁定。

使用存储过程或参数化查询。

尽量减少使用游标。

避免长时间操作触发器。

适当地使用临时表和表变量。

限制查询和索引提示使用。

完全限定的数据库对象。

了解查询性能和可扩展性的特点

实现性能和可扩展性的最好办法是要知道你查询的特点。虽然它不是现实来监控每一个查询，你应该测量并了解你的最常用的查询。不要等到你有问题，执行这项工作。测量您的应用程序的整个生命周期的应用程序的性能。

良好的性能和可扩展性，还需要开发人员和数据库管理员的合作。该过程依赖于查询开发和索引发展。这些领域的发展通常被发现在两个不同的工作角色。每个组织都有一个过程，允许开发人员和数据库管理员合作，并交换彼此的信息。有些组织需要开发人员编写合适的索引，为每个查询执行计划并提交到数据库架构师。架构师负责评估系统作为一个整体，去除冗余，寻找规模效益，并作为开发人员和数据库管理员之间的联络数据库管理员可以获取信息可能需要哪些索引以及如何查询可能会用到。然后数据库管理员可以实现最优的索引。

此外，数据库管理员应该定期监测消耗资源最多的SQL查询和提交信息的建筑师和开发商。这使开发团队保持领先的性能问题。

写正确形成查询

确保你的查询是否正确形成。请确保您的加入是正确的，所有的关键部件都包含在ON子句中，有一个所有查询谓词。需要特别注意，以确保没有交叉产品导致失踪或WHERE子句中加入表。也被称为笛卡儿积的交叉积。

不要自动添加一个DISTINCT子句的SELECT语句。有没有需要包括默认情况下，DISTINCT子句。如果你发现你需要它，因为重复数据返回，可能是由于重复数据的数据模型不正确或不正确的联接。例如，加入一个表，对表外键，主键重复的值，仅引用部分用复合主键。您应该探讨这些问题的查询返回的冗余数据。

只返回所需要的行和列

最常见的性能和可扩展性的问题之一是查询返回的列太多或太多行。尤其是一个查询返回列太多，经常被虐SELECT * FROM结构。优化确定执行计划指标时，也被认为是在SELECT子句中的列。使用SELECT *查询不仅返回不必要的数据，但它也可以强制查询计划的聚簇索引扫描，无论在WHERE子句限制。这是因为回去聚集索引使用非聚集索引，限制结果集返回剩余的数据后，从该行的成本实际上是资源密集型的聚集索引比扫描。

图14.6所示的查询显示在一个SELECT *查询的成本相比，选择列的差异。第一个查询使用聚集索引扫描，因为它具有检索聚集索引中的所有数据，即使是订购日期列上的索引来解决查询。第二个查询使用的订购日期索引来执行索引查找操作。因为查询只返回的OrderID列，因为订单ID列是聚簇键，解析该查询只使用该索引。这是更有效的，相对批量查询的成本是33.61％，而不是66.39％。您的计算机上，这些数字可能会有所不同。

图14.6：一个SELECT *查询查询成本差异相比，选择列

通常情况下，行数太多，因为应用程序的设计允许用户选择从搜索形式的大结果集返回。数百甚至数千结果返回给用户强调的服务器，网络和客户端。大量的数据一般不会是最终用户需要什么。使用一种设计模式，支持分页，并在同一时间只有两页或所请求的数据返回。

调用其他查询的查询到调用的查询返回太多的列和行是另一个经常被忽视的考虑因素。这包括书面的查询，视图或表值函数或意见。虽然意见是有用的原因有很多，他们可以返回更多的比你需要的列，或者他们可能会返回到调用的查询基础表中的所有行。

更多信息

有关数据分页欲了解更多信息，请参阅“ “在”如何“一节本指南。

避免昂贵的运营商，如NOT LIKE

一些运营商在连接或谓词往往会产生资源密集型操作。LIKE操作符用通配符值（“％值％“）几乎总是会导致表扫描。这种类型的表扫描是一个非常昂贵的操作，因为前面的通配符。收通配符LIKE运算符可以使用索引，因为索引是B +树的一部分的，并且索引匹配的字符串值从左至右遍历。

负的操作，如<>或不喜欢，也很难有效解决。尝试重写，如果你能以另一种方式。如果你只检查存在，使用IF EXISTS或IF NOT EXISTS，而不是建造。您可以使用索引。如果您使用的扫描时，可以停止扫描，在第一次出现。

避免在where子句中显式或隐函数

优化不能总是选择一个索引，在WHERE子句中的列，函数内部使用。在WHERE子句中的列被看作是一个表达式，而不是一列。因此，列不用于执行计划中的优化。一个常见的问题是周围datetime列的日期函数。如果你有一个datetime列的WHERE子句中，你需要将它转换或使用数据功能，努力推动字面表达的功能。

的datetime列与函数下面的查询会导致表扫描，即使在Northwind数据库中的订购日期“列上有一个索引：

SELECT OrderID的FROM NorthWind.dbo.Orders WHERE DATEADD（天，15， 订购日期）= '07 / 23/1996'

然而，通过移动到另一侧的WHERE方程的函数，一个索引可用于日期时间的列上。这是显示在下面的示例中：

SELECT OrderID的FROM NorthWind.dbo.Orders WHERE订购日期= DATEADD（天 -15，'07 / 23/1996'）

这两个查询的图形执行计划图14.7所示，它显示了计划的差异。注意扫描的的第一查询和第二个查询的搜索图标图标。图14.7也显示了两个查询的查询成本之间的比较差异，第一个查询中有85.98％的成本相比，14.02％的成本，第二个查询。在您的计算机上的成本可能会有所不同。

图14.7：查询比较

隐式转换也会造成表和索引扫描，这往往是因为数据类型不匹配。要特别警惕的数据类型为nvarchar和varchar数据类型不匹配和NCHAR 字符数据类型不匹配导致的隐式转换。你可以看到这些在下面的执行计划。下面的示例使用对NCHAR列Customers表中的字符类型的局部变量。类型不匹配导致的隐式转换和扫描在这个例子：

DECLARE @ CustID的CHAR（5）SET @客户ID ='FOLKO选择公司名称从NorthWind.dbo.Customers的CustomerID = @ CustID的

图14.8示出的类型不匹配的结果。

图14.8：输出显示的隐式转换

使用锁和隔离级别的提示，以尽量减少锁定

锁定性能和可扩展性上有着巨大的影响。锁定也影响感知的性能，因为等待锁定对象。所有的应用程序遇到一定程度的锁定。关键是要了解不同的锁定发生，被锁定的对象，最重要的是，每个锁定发生的持续时间。

在SQL Server的锁有三种基本类型：

共享

更新

独家

注意： 也有意向，架构和批量更新锁，但这些锁是不太显着，并没有解决本章中。

共享锁与其它共享锁兼容，但它们不兼容使用互斥锁。更新锁与共享锁兼容，但他们不兼容使用互斥锁或其他更新锁。共享锁，更新锁或其他独占锁独占锁不兼容。不同类型的锁持有不同的时间，以获得所要求的隔离级别。

有四种ANSI隔离级别可以指定在SQL Server中的交易：

未提交读

已提交读

重复读

Serializable接口

这些隔离级别中的每一个都允许零个或多个隔离级别现象发生：

脏读。脏读取看到其他交易的影响，从来没有承诺的交易。

不可重复读。不可重复读是只能看到已提交的数据从其他交易的交易。在非重复读取，数据发生变化时，它的交易中多次引用。

“鬼怪”。“鬼怪”的交易，看到或看不到从另一个事务尚未提交的行插入或删除。

在SQL Server 2000的默认隔离级别是读承诺。图14.9所示的现象，允许在每个隔离级别。

Ff647793.ch14 ANSI隔离级别（EN-US PandP.10）。

图14.9：ANSI隔离级别

而不是接受默认的SQL Server已提交读隔离级别，你可以明确地选择适当的隔离级别的代码。为此，您可以通过使用隔离级别或锁定提示。

以NOLOCK和WITH READUNCOMMITTED的的

如果你设计你的应用程序使用锁定提示，请使用WITH（NOLOCK）或SELECT语句中的表提示（READUNCOMMITTED）的，以避免产生读锁，可能不需要。这可以提供一个显著增加的可扩展性，尤其是在SELECT语句都运行在一个序列化的隔离级别，因为SELECT语句被称为一个明确的交易开始在一个中间层对象使用微软事务服务器（MTS），COM +，或企业内服务。另一种方法是，作为一个整体的事务，以确定是否可以运行在较低的隔离级别。您可以使用SET TRANSACTION ISOLATION LEVEL命令来更改一个SQL Server会话中的所有事务的隔离级别。

UPDLOCK

处理死锁的一种常用方法是使用UPDLOCK表提示的SELECT语句通常参与交易僵局。UPDLOCK问题更新锁，并持有锁，直到事务结束。典型的共享锁发出SELECT语句只持有，直到该行已被读取。更新锁，直到事务结束，其他用户仍然可以读取数据，但无法获取锁，您可能需要。这是一种常见的死锁情况。

TABLOCK

您可以使用TABLOCK表提示，以提高性能，当您使用BULK INSERT命令。当有大量的插入，在整个表上请求一个锁，有助于解除锁管理器管理动态锁定的开销。然而，在整个表上请求一个锁，阻止所有其他用户在桌子上。因此，它是不是当其他用户需要使用该系统时，你应该做的事情。

要有效使用锁定和隔离级别的锁提示，你要明白锁定行为在SQL Server中，您的应用程序的特定需求。然后，您可以选择键查询的最佳机制。在一般情况下，SQL Server的默认的隔离级别和锁定是最好的，但你可以增加可扩展性，当你需要使用其他锁定提示。

使用存储过程或参数化查询

SQL Server 2000中的动态优化的代码，尤其是与sp_executesql的系统存储过程，并增加了能够重复使用参数化查询的执行计划，重大工作已经完成。然而，存储过程仍然普遍提供更高的性能和可扩展性。

当你决定是否存储在服务器上的SQL命令，使用存储过程或嵌入在你的应用程序，通过使用嵌入式SQL语句的命令，请考虑以下问题：

逻辑分离。当你设计你的数据访问策略的性能，可维护性和灵活性的好处，独立的业务逻辑从数据操作逻辑。验证业务规则，然后再发送数据到数据库中，以帮助减少网络游。从业务逻辑中分离数据操作逻辑隔离数据库变更或业务规则变动的影响。使用存储过程来澄清分离移动数据操作逻辑从业务逻辑，所以这两个不相互交织。制定标准，确定正确的编码标准，避免交织的逻辑。

调整和部署。存储过程的代码存储在数据库中，并允许数据库管理员审查数据访问代码和调整存储过程和数据库独立部署的应用程序。你并不总是需要重新部署你的应用程序时，存储过程的变化。部署嵌入式SQL应用程序代码的一部分，需要数据库管理员可以分析应用程序来识别，实际上是使用的SQL。这种复杂的调整，必须重新部署应用程序，如果在作出任何改变。

网络带宽。存储过程是存储在服务器上的源代码，你只在网络中的名称和参数发送到服务器。但是，当您使用嵌入式SQL，完整的源代码的命令必须在每次运行命令发送。使用存储过程，你可以减少发送到服务器的数据量，尤其是在经常运行大型的SQL操作。

简化命令配料。预存程序提供简化，更易于维护的批处理工作。

提高了数据的安全性和完整性。存储过程是强烈建议，以确保数据的安全性，以促进数据的完整性，并支持性能和可扩展性。管理员可以确保表对直接访问或操纵。授予用户和应用程序访问的存储过程执行数据完整性规则。使用嵌入式SQL通常需要高级权限表上的数据可能会允许未经授权的修改。

SQL注入。避免使用动态生成SQL的用户输入。恶意用户输入用来执行未授权操作如检索数据太多，或破坏性修改数据时可能会出现SQL注入。参数化存储过程和参数化SQL语句既可以帮助减少SQL注入的可能性。使用的参数集合，你强迫参数被视为文字值而不是可执行代码。你也应该限制所有的用户输入的SQL注入攻击的可能性降低。

更多信息

有关如何防止SQL注入欲了解更多信息，请参见第14章，“构建安全的数据访问，”在提高Web应用程序安全性：威胁和对策。

减少游标的使用

游标强制数据库引擎重复读取行，洽谈阻塞，管理锁，并发送结果。使用只进，只读游标，除非你需要更新表。可能会使用比实际需要更多的锁，tempdb数据库有冲击。影响各不相同，根据所使用的游标类型。

只进，只读游标是最快和最密集资源的方式从服务器获取数据。这种类型的光标也被称为流水游标或本地的快进光标。如果你觉得你真的需要使用游标，了解更多有关不同类型的游标，他们的锁定，和他们的影响在tempdb数据库。

通常情况下，使用游标一行一行地执行一个功能。如果有一个表的主键，通常你可以编写一个WHILE循环做同样的工作，而不会产生一个游标的开销。下面的例子是很简单，但说明了这种方法：

“DECLARE @ currid诠释选择@ currid =最小值（订单编号）从订单，订购日期<'7 / 10/1996'而@ currid是不是null开始  打印@ currid  选择@ currid =最小值（订单编号）  从订单   订购日期<'7 / 10/1996'  和OrderID> @ currid结束

更多信息

有关游标欲了解更多信息，请参见“Transact-SQL游标” 。

避免长时间操作触发器

触发代码时，往往忽视开发评估系统的性能和可伸缩性问题。由于触发器的INSERT，UPDATE或DELETE调用交易，触发一个长期运行的行动可能会导致锁的时间比预期，导致阻塞其它查询。保持你的触发小和尽可能高效的代码。如果您需要执行一个长时间运行或资源密集型任务，可以考虑使用异步消息队列来完成任务。

适当地使用临时表和表变量

如果应用程序频繁创建临时表，可以考虑使用表变量或永久表。您可以使用表数据类型存储在内存中的行集，表变量自动清理的功能结束时，存储过程或批处理。许多要求创建临时表定义它们可能会导致两者争tempdb数据库中的系统表。非常大的临时表也存在问题。如果您发现您所创建的许多大的临时表时，你可能要考虑用途之间的永久表可以截断。

tempdb数据库中使用表变量表变量是如何使用临时表类似的方式，所以要避免大的表变量。此外，表变量不考虑优化时，优化器生成的执行计划和并行查询。因此，表变量可能会导致性能下降。最后，表变量不能灵活临时表索引。

你必须测试临时表和表变量的使用性能。很多用户对于可扩展性测试，以确定最好的方法，是对每一种情况。此外，要知道，有可能是并发的问题，当有许多请求资源在tempdb数据库中的临时表和变量。

限制查询和索引提示使用

虽然上一节讨论了如何使用表提示锁定限制，你应该只在必要时才使用查询和索引提示。查询提示包括合并，哈希，LOOP和FORCE ORDER提示，直接选择一个特定的连接算法优化。索引提示表提示指定某个索引的优化使用。一般情况下，优化选择最高效的执行计划。强制执行计划，通过指定索引或连接算法，应该是最后的手段。另外，请记住，SQL Server使用基于成本的优化;成本随时间变化的数据的变化。提示可能不再查询工作，并提示可能永远不会被重新评估。

如果您发现优化器没有选择的最佳计划，尝试破成小块查询。或者，尝试另一种方法来查询，以获得一个更好的计划，然后再决定使用硬编码的查询提示。

完全限定数据库对象

完全符合所有数据库对象所有者，名称解析的开销降至最低，并避免潜在的的架构锁和执行计划重新编译。例如，dbo.Authors语句或执行dbo.CustOrdersHist的语句SELECT * FROM性能优于著者或在EXEC CustOrderHist语句SELECT * FROM。在系统有很多存储过程中，大量的时间都花在解决非限定存储过程名称加起来。

指标

索引是高效的数据访问至关重要。然而，创建和维护一个索引结构相关联的成本。有大量的表上的索引，可能会导致更快的select语句，但速度较慢的插入，更新和删除语句。不同的应用程序和数据库的性能开销。如果你有大量的表上的索引，你提高的机会，优化器将选择一个次优的索引查询计划。

从一个特定的索引列，包括数据存储在一个索引页。索引是建立在B-树结构形成的8 KB索引页。有聚集索引和非聚集索引在SQL Server中。非聚集索引的叶级节点只包含索引数据指针相关的数据页，其余的数据驻留。因此，使用一个非聚集索引的数据访问可能会导致额外的读取数据页的数据。有聚簇索引的叶级的B-树的节点包含实际的数据行的表。只能有一个聚集索引的每个表。记住聚类的关键是用在所有非聚集索引的行标识符，所以明智地选择他们。

索引是一种艺术，是依赖于数据分发，成本，使用，了解SQL Server如何使用索引。它需要时间来得到它的权利。使用下面的指导方针，以帮助创建高效的索引：

创建索引的基础上使用。

保持尽可能小的聚集索引键。

考虑聚集索引的数据范围。

所有外键上创建索引。

创建高选择性指标。

考虑为经常使用的，高冲击查询，覆盖索引。

使用多个窄索引，而不是一个数宽指标。

首先最严格的列创建复合索引。

考虑WHERE中使用的列上的索引，ORDER BY，GROUP BY，DISTINCT子句。

删除未使用的指标。

使用索引优化向导。

创建基于使用的索引

索引来写和读操作之间必须平衡成本。写操作可能是负面和正面影响指标。读操作主要受益指标。你必须了解您的系统使用的方式，找到最优的索引。深奥的讨论在何种程度上插入操作的性能受指标的价值是有限的，如果插入操作的数量小，而且你的系统进行密集的读操作。花时间的评价指标的最常用的查询表中，最常用的查询，和最有问题的查询。一个系统性的方式来支持这些表和查询的设计指标。如前所述，索引设计是一门艺术，不是一门科学。它需要你的系统的知识创造效益的指标。

不要创建索引如果表很少被质疑，或者如果一个表不似乎永远要使用的优化。避免位，文本，ntext的，或image数据类型的索引，因为他们很少使用。避免不具有选择性非常广泛的索引和索引。

聚集索引键保持尽可能小

因为非聚集索引存储聚集索引键作为他们的行定位器。该行定位器引用的实际数据行。因此，聚集索引键保持尽可能小。

聚簇索引的考虑范围数据

如果你经常在数据库中查询范围行使用条款等之间，或者运营商，如>和<，考虑一个聚集索引，WHERE子句中指定的列。一般来说，不能作为有效的交易表中的主键聚集索引，但它们可能永远不会被使用在连接以外的域或查找表中的主键是非常有效的。在一般情况下，每个表都应该有一个聚集索引，除非有不有一个明显的性能的原因。

所有外键上创建索引

确保任何外键上创建索引。因为连接中使用的外键，外键几乎总是从索引中受益。

创建高选择性指标

创建索引，具有较高的选择性。换句话说，创建索引有许多不同的价值观。例如，可以有一个小的区域“列上的索引的数量的不同值。因此，可能不存在足够的优化器使用不同的值。一个项目，可能没有足够的不同的值的另一个例子是一个有点列。由于只有两个值，一个索引不能是非常有选择性的，其结果是，索引可能无法使用。

使用DBCC SHOW_STATISTICS命令在一个表或索引中的列，以更好地理解统计指标。在此命令的输出中，密度是用来表示选择性。密度的计算方法的不同值的数目除以。因此，有一个唯一的索引行的密度为1╱。例如，一个具有1000行的表将有一个密度为0.001。比特列上的索引密度为0.5，因为你把一个只有两种可能有点列中唯一值。密度较小的数，的选择性就越大。

最好使用密度是所有密度数字DBCC SHOW_STATISTICS命令输出，而不是密度在生产的第一个结果。

图14.10显示订单表PK_Orders指数DBCC SHOW_STATISTICS命令输出。输出显示了一个非常有选择性的密度，因为它使用的主键。

图14.10：DBCC SHOW_STATISTICS输出

更多信息

有关统计欲了解更多信息，请参阅“查询优化器所使用的统计信息在SQL Server 2000”在。

考虑一个覆盖经常使用的，高影响力的查询索引

被频繁调用的查询，有问题的查询，或使用大量资源的查询是一个覆盖索引的很好的候选人。覆盖索引的索引，其中包含WHERE和SELECT子句中引用的所有列。指数“涵盖了”查询，并可以完全不用到的基础数据查询服务。这实际上是一个物化视图的查询。覆盖指数表现良好，因为数据是在一个地方，并按照要求的顺序。覆盖索引可以提高可扩展性，通过删除主表的争用和访问。

使用多个窄指标，而不是一个数宽指标

SQL Server可以使用多个每个表的索引，并可以相交索引。因此，你应该使用多个窄包括只有一列的索引，因为窄索引往往比宽的复合索引提供了更多选择。

此外，统计数据只保存在一个复合索引的第一列。多个单列索引，确保这些列被统计。复合索引覆盖索引的最大价值。因为第一列的列统计，通常使用复合索引，如果该列的WHERE子句中也是一个参考。

建立较小的索引，阿方考虑使用校验功能是一个很宽的列上创建一个哈希索引。这使您可以创建较小的指标。这是一个很好的方法，当你需要长字符列上的索引，你还需要您的使用空间限制。

首先最严格的列创建综合指数

当您创建一个综合指数，记住，只有第一列存储统计。尽量让该列最严格的列。如果复合索引选择性不足，优化程序可能无法使用它。此外，一个WHERE子句，不使用复合索引中包含的所有列可能导致不使用索引。例如，综合指数在中间跳过一列的WHERE子句可能导致不使用索引。

考虑在WHERE中使用的列上的索引，ORDER BY，GROUP BY，DISTINCT第

考虑创建一个索引列在WHERE子句中使用聚合操作，比如GROUP BY，DISTINCT，MAX，MIN或ORDER BY。这些一般从索引中受益，虽然你需要测量和测试验证，在您的方案还有一个好处。

删除未使用的索引

一定要删除所有未使用或过期指标。未使用或过期股指继续影响写操作，因为他们需要保持，即使他们不使用。他们仍然使用由优化器在执行计划的考虑。您可以使用SQL Profiler来确定所使用的指标。

使用索引优化向导

索引优化向导（ITW）使用相同的信息和统计数据，优化程序用来创建一个执行计划。你应该使用这个工具来获得指导和技巧，否则可能会被忽略的指数期权。然而，它不是唯一的工具，和系统知识仍是最好的方式来创建有效的索引。全系统的指数建议使用SQL事件探查器输入ITW捕捉有代表性的跟踪。

交易

高效的事务处理，大大提高了可扩展性。你必须要小心，正确代码交易。交易持有锁的资源，可以阻止其他交易。下面的建议是一些更有效的事情可以做，以创建高效的交易：

避免长时间运行的事务。

避免交易需要用户输入的承诺。

访问频繁使用的数据，在事务结束。

尝试在同一顺序访问资源。

使用隔离级别提示，以尽量减少锁定。

确保显式事务提交或回滚。

避免长时间运行的事务

持有锁，所以在交易过程中是至关重要的，以保持尽可能短的交易。不要忘了，你就可以开始交易，从应用层。一种常见的技术是做所有需要的验证检查，然后再开始交易。你仍然需要再次检查，但你在交易过程中避免很多情况下，你开始一个事务，然后回滚事务。

避免交易需要提交用户输入

要小心，不要启动一个事务，需要用户输入才能提交。在这种情况下。事务锁持有，直到输入收到一些在未来不确定的时间。

访问频繁使用的数据在事务结束

尝试把所有的读操作在一个事务的开始，结束时把写操作，并把最有争议的资源，在最末尾。这确保了在最短的锁持对别人的资源，是最常用的。以这种方式创建您的交易有助于阻止其他交易的限制。

在相同的顺序尝试访问资源

以相同的顺序使用资源，降低死锁。例如，如果存储过程SP1和SP2使用表T1和T2，确保SP1和SP2的过程中T1和T2相同的顺序。否则，如果SP1使用T1和T2和SP2使用T2和T1，每个存储过程可以在等待使用其他存储过程已经在使用的资源。其结果是当这种情况发生的死锁。

避免锁定冲突在多用户情况下是不容易的。你的目标应该是减少死锁的机会，并减少持有锁的时间长度。

使用隔离级别提示，以尽量减少锁定

如果允许的话，你的业务逻辑的隔离级别降低限制较少。一种常见的方式，以较低的隔离级别使用NOLOCK提示在交易中的SELECT语句的。

确保显式事务提交或回滚

所有事务的代码，应该有明确的错误处理，要么提交或回滚一个错误。这种类型的错误处理允许公开交易，持有锁释放锁时，交易无法完成。否则，本次交易不会释放锁。您可以使用DBCC OPENTRAN命令找到交易很长时间，可能是开放的。

存储过程

存储过程提供了更高的性能和可扩展性的系统。当你开发存储过程，保持记住以下建议：

使用存储过程中的SET NOCOUNT ON。

自定义存储过程，请不要使用sp_前缀。

使用SET NOCOUNT ON存储过程中

使用SET NOCOUNT ON语句DONE_IN_PROC消息发送中的每个语句的存储过程，防止SQL Server。例如，如果你有八个业务在存储过程中，你还没有使用这个选项八个消息返回给调用者。每条消息都包含各自的语句影响的行数。

不要使用sp_前缀自定义存储过程

SQL Server始终看起来在master数据库中的存储过程以sp_前缀开头。然后，SQL Server使用任何提供的限定词，如数据库名或所有者。因此，，以sp_前缀，如果你使用一个用户创建的存储过程，你把它在当前数据库中，主数据库仍然首先检查。发生这种情况，即使你的数据库名称限定存储过程。要避免此问题，请使用一个自定义的命名惯例，并且不使用以sp_前缀。

执行计划

为了提高性能，关键是你的T-SQL代码来了解和衡量目前的表现。一个常见的错误是投入太多精力在写一个优雅的一段代码，担心具体的编码技巧和提示。相反，你应该记得来看看您的查询的执行计划，并了解如何运行查询。以下指南列出了一些可以提高性能和可扩展T-SQL代码的主要途径：

评估查询执行计划。

避免表扫描和索引扫描。

评估哈希联接。

评估书签。

评估排序和筛选。

比较实际与估计的行和处决。

评估查询执行计划

在SQL查询分析器，使显示执行计划选项，对有代表性的数据负载看到计划所建立的查询优化和运行查询。评估这一计划，然后确定什么好的索引，优化器可以使用。此外，识别查询的一部分，它的运行时间最长，可能是更好的优化。了解运行的实际计划，是优化查询的第一步。与索引一样，它需要时间和知识，您的系统能够识别的最佳方案。

避免表和索引扫描

表和索引扫描是昂贵的操作，他们变得更加昂贵，随着数据的增长。调查在执行计划中，你看到的每一个表或索引扫描。创建索引，将使搜索操作，而不是表扫描？消除不必要的扫描引起的I / O是一个最快捷的方法获得了实质性的性能提升。

并非所有的表或索引扫描是坏的。优化器选择的扫描少于几百行的表有一个聚集索引扫描可能是最有效的选择对某些查询。但是，在一般应该避免扫描。

评估哈希联接

确保您调查中的散列连接查询执行计划。哈希联接可能是最好的选择，但经常被选中，哈希联接，因为没有索引，优化器可以用来执行一个有效的嵌套循环或合并联接。哈希联接索引的情况下，是最好的选择。然而，更好的索引，可能会发生一个嵌套循环或合并联接。请注意，哈希联接也相当密集的CPU。如果你有很高的CPU使用率，你不觉得对正在执行的服务器有足够的工作来证明这一点，评估执行计划，通过使用SQL事件探查发现，如果你有很多的哈希联接。

查询使用并行执行计划往往有进行的哈希联接重组成品并行流。哈希联接在这种情况下通常是优化的，不应该是一个关注。

评估书签

在执行计划中的书签表明使用了索引来限制表，书签，然后用来探测获取更多的数据，是不是可以在索引聚簇索引或堆表。书签中经常使用这种方式来检索，是一个SELECT子句中的列。这意味着，至少两倍的I / O是必要的检索结果。

书签是不是永远是个问题，但你应该发现，如果添加一个覆盖索引可能会更有效。甲书签可能不是一个问题，如果原来的索引是非常有选择性的，在这种情况下，需要几个书签查找。然而，一个索引，这是非常有选择性的将数据从一个书签是有问题的，尤其是遍布的页表中的一个显着的百分比，则在结果表中的行。

评估排序和过滤器

排序和过滤器都是CPU密集型和内存密集型的，因为服务器在内存中执行这些操作。当有排序和筛选的实例，如果你能创建一个索引，将支持排序或过滤。筛选的结果往往是隐式转换，因此调查的过滤器，以了解如果发生转换。排序和过滤器并不总是坏的，但他们是潜在的问题的关键指标，你应该做进一步的调查。

比较实际与估计行和处决

当你读SHOWPLAN语句的输出，开始从最缩进的行，具有最高的增量变化在TotalSubtreeCost列。仔细评估指标的选择和优化的估计，通过使用SET STATISTICS PROFILE ON命令。运行此命令的说法，所以只使用SELECT语句或T-SQL代码，不修改数据，或者你可以命令前一个BEGIN TRAN / ROLLBACK语句。

作为替代方案，使用新的分析器性能：在SQL 2000中显示计划统计事件。此事件属于事件类98。此事件报告四列显示的估计和实际的行和处决。Profiler事件添加数据的T-SQL或SP：stmtcompleted事件之前，您必须选择二进制数据列。

预计行计数的巨大差异可能表明优化的最新统计或倾斜统计。例如，如果估计行数为2行，实际行数为50,000，优化可能有日期的统计或倾斜的统计。尝试使用更新统计FULLSCAN命令。

重新编译执行计划

性能的影响在创建一个新的执行计划或重新编译一个计划时，每一次查询结果。RECOMPILES并不总是一件坏事。创建最初的计划，可能不是最优的，其他电话或数据可能已经改变。可能需要重新编译，创造一个更好的计划。优化程序通常会导致重新编译时，它是必要的，但是，有步骤，你可以采取重新编译，以确保不会发生当它是不需要的。以下准则可以帮助你避免频繁地重新编译：

使用存储过程或参数化查询。

使用sp_executesql的动态代码。

避免交织在存储过程中，包括在tempdb数据库的DDL DDL和DML。

避免使用游标在临时表。

使用存储过程或参数化查询

服务器会保存在大多数情况下，存储过程和参数化查询的执行计划。这使他们能够被重用以后调用。

使用sp_executesql动态代码

如果您必须使用动态代码在你的应用程序，尝试把它包在sp_executesql的系统存储过程。该系统存储过程允许你写在T-SQL参数化查询和保存代码的执行计划。如果再次被称为动态代码的机会不大，有没有价值，节省了执行计划，执行计划到期时，因为执行计划最终将被从缓存中删除。评估是否应保存或不执行计划。需要注意的是包装代码sp_executesql的系统存储过程无需使用参数不提供节省编译时间性能。

动态代码往往是用于查询生成器应用程序，它通常是资源密集的，在这种情况下，它经常被重复使用。使用sp_executsql的系统存储过程来包装这个代码可以帮助提高性能。

避免交错存储过程中，包括在tempdb数据库的DDL DDL和DML

交错在存储过程中的数据定义语言（DDL）和数据操作语言（DML）是最常见的原因，重新编译存储过程。一个常见的场景是创建一个临时表，将数据插入到表，创建一个索引，然后选择从表中的数据。这一连串事件通常会导致重新编译。为了避免重新编译，在存储过程的开始，把所有的DDL和DML DDL后。

下面的代码显示了一个存储过程，创建了一个表（DDL），将数据插入到表中（DML语句），创建索引（DDL语句），然后选择从表中的数据（另一个DML语句）：

CREATE PROCEDURE RecompileExample @ EMPLOYEEID诠释ASSET NOCOUNT ONCREATE TABLE＃EmployeeOrders（订单ID int非空）INSERT＃EmployeeOrders的选择WHERE EMPLOYEEID = @雇员Northwind.dbo.Orders OrderID的创建聚集索引乳油对＃EmployeeOrders（订单编号）按SELECT * FROM＃EmployeeOrders订单的订单编号GO

运行SQL事件探查器和捕捉SP：重新编译事件，你可以看到一个重新编译每次交错DDL和DML运行的程序。这显示在图14.11。重新编译发生这个简单的示例代码，不可能花费太多时间。然而，更复杂的查询可能会导致显着的成本重新编译。

图14.11：SQL事件探查器显示重新编译

将下面的代码开始时将所有的DDL，以便有没有交错的DDL和DML。这意味着，不需要重新编译。

CREATE PROCEDURE NoRecompileExample @ EMPLOYEEID诠释ASSET NOCOUNT ONCREATE TABLE＃EmployeeOrders（订单ID int非空）创建聚集索引乳油对＃EmployeeOrders（订单编号）INSERT＃EmployeeOrders的选择WHERE EMPLOYEEID = @雇员Northwind.dbo.Orders OrderID的按SELECT * FROM＃EmployeeOrders订单的订单编号GO

SQL事件探查器跟踪修改后的代码如图14.12所示不再显示重新编译。

图14.12：没有重新编译的探查输出

避免临时表上的游标

一个游标的DECLARE语句中选择一个临时表中的数据几乎总是会导致重新编译。因此，应避免使用游标在临时表。

SQL XML

SQL Server 2000中增加了一系列新的XML功能。虽然这些都是流行的和灵活的，你应该知道使用这些新功能中所涉及的一些以下的可扩展性问题：

避免OPENXML大型XML文档。

避免大量并发OPENXML语句XML文档。

避免OPENXML大型XML文档

要知道有OPENXML构建对XML文档进行操作的内存量的限制。此操作构建文档对象模型（DOM），可以在SQL缓冲区空间远远大于原始文档尺寸。此外，这个操作是有限的缓冲空间的八分之一，大型XML文档可能会消耗内存相当迅速，导致在服务器上的内存错误。不要建立基于此功能的大型系统没有进行显着的负载测试。您可能还需要使用XML批量加载选项如果可能的话。

避免大量并发OPENXML语句在XML文档

你还必须要考虑的问题，当你使用OPENXML使用OPENXML批量插入。这是一个相当普通的操作，因为它是一个有效的方式来发出一组插入一个语句。发出一组插入，减少多重插入语句和多次往返的开销。但是，要知道，这种做法可能不是很可扩展性，因为上述的内存限制。

调音

为了提高性能，关键是理解和衡量当前的代码性能。需要注意的是调整是一个持续的，反复的过程。由于SQL Server使用基于成本的优化器，因为成本可能会改变，特定的查询的效率会随时间而改变。

以下指南列出了一些你的T-SQL代码以提高性能和可扩展性的主要途径：

使用SQL Profiler来辨别长时间运行的查询。

请注意经常调用的小查询。

使用sp_lock和sp_who2，评估阻塞和锁定。

评估的waittype和waittime在master .. sysprocesses中。

使用DBCC OPENTRAN找到长期运行的事务。

使用SQL Profiler来确定长时间运行的查询

使用SQL事件探查SQLProfiler TSQL_Duration的的模板来识别持续时间最长的查询。长时间运行的查询资源锁定时间越长，阻塞其他用户，并限制可扩展性的最大潜力。他们还优化的最佳人选。查看长时间运行的查询，是一个持续的过程，需要代表性的负载，以确保有效的调整。

在某些情况下，使用SQL事件探查器模板有所限制，当您使用它们来衡量业绩的变化，同时测试新的索引或应用程序的设计变更。SQL事件探查器的基线可以成为一个非常强大的工具，当你保存你的性能测试结果，作为一个跟踪文件。跟踪文件使用。TRC扩展的。与SQL Server 2000开始，你可以使用这些跟踪文件，写自动化报告，定量测量中的应用程序的性能收益，否则将不会进行分组正确使用模板时对于某些类型的查询。fn_trace_gettable将是聪明的跟踪报告功能。此跟踪功能，在下面的示例代码所示：

SELECT COUNT（*）作为CountOfEvents，AS AvgDuration，AVG（持续时间）SUM（持续时间）AS [SumDuration]的SUBSTRING（TextData资料，1，30）AS [文字资料]从:: fn_trace_gettable将（“F：\ MyTrace.trc'，默认）WHERE EventClass（10,12） -  BatchCompleted，RPC完成GROUP BY SUBSTRING（TextData资料，1，30）ORDER BY DESC SUM（持续时间）的

请注意通常被称为小查询

通常情况下，运行相当迅速，但被称为小查询往往被忽视。使用SQL Profiler来辨别，经常被称为查询，然后尝试优化他们。优化一个查询运行数百次，比优化一个长时间运行的查询，只运行一次，可能会更有效。

您可以修改fn_trace_gettable将示例代码，以便通过SUM（CPU），使之成为一个有效的工具，用于识别小查询或存储过程可在一小时内调用数千次。当你符合他们的CPU成本，这些查询可以代表一个巨大的开支，您的SQL Server的整体性能。通过正确设计指标和避免书签查找，可以缩短每个呼叫毫秒。随着时间的推移，这样可以达到一个很大的节省。

这种报告也可安排小查询读取和写入。在您升级的子系统，可以考虑使用这些报告的方法来识别小查询。然后，您可以计划的应用程序设计的变化，可能会有助于巩固这些小查询到更大的批次。

使用评估sp_lock和Sp_who2的锁定和阻塞

使用以sp_锁和sp_who2系统存储过程，以找出哪些锁被收购您的查询。您应该使用尽可能限制最少的锁。调查查询结果表锁。这些表锁阻止其他用户访问整个表和可扩展性可能会严重限制。

在master .. sysprocesses中评估的waittype Waittime的

OLTP服务器可能会报告退化简单的插入操作，更新操作，随着时间的推移和删除操作。平均持续时间的增加，可能会出现由于到sysprocesses.waittype的值的0x0081。这是日志作家，此waittype的值是指在执行中有一个延迟，而你的系统进程ID（SPID）上的两阶段提交过程等待到事务日志。您可以测量这个延迟通过捕捉sysprocesses.waittime值。此值可能表明，你的事务日志是在同一主轴作为你的数据集（卷）。它也可能表明你没有足够的I / O子系统中存在的日志文件，或者你有一个配置不当的IO子系统存在的日志文件。

共同锁定的资源，你的数据库管理员也应该密切关注。这些可以指示一个特定的表中的特定问题。特定的waittype值可以是一个早期的迹象表明，你的服务器是动力不足或过度使用磁盘，CPU，或事务日志。你可以找到，如果你的服务器是通过系统进程和syslockinfo表大约每五秒钟拍摄快照，通过测量一个SPID等待多久动力不足或泛滥之资源。

更多信息

对于更多信息SP_LOCK，请参阅 “SP_LOCK”中的“Transact-SQL参考MSDN上。

有关如何分析阻断欲了解更多信息，请参阅以下知识库中相应的文章：

2244453，“INF：了解和解决SQL Server 7.0或2000阻塞问题，” 。

324885，“支持网络发布：Microsoft SQL Server的快速拦截器脚本分析，”在。

使用DBCC OPENTRAN找到长期运行的事务

运行DBCC OPENTRAN命令，发现是开放的很长一段时间的交易。重复运行此命令发现期间，因为来来去去交易。然而，交易连续报道此命令可运行时间太长或不承诺。您应该调查这些交易。

测试

一个查询的性能是不调谐可以相差很大，这取决于数据的大小。需要不到一秒钟的一个小的数据库查询可能需要几分钟，在一个数据库中，有数百万行。如果你的生产数据库很大，填充测试数据库开发和测试过程中具有同等的数据量。这给你一个机会来测试您的应用程序的性能，用真实数据，并找到需要优化的查询。确保你检查你的查询执行计划使用表包含实际数据的数量和分布。

当你用大量的测试数据填充表时，遵循以下原则：

确保事务日志不填。使用一个简单的循环机制，可以填写你的每一个插入的事务日志。

预算你的数据库的增长。

使用工具来填充数据。
SQL Server资源工具包提供了有价值的工具在你的数据库，如数据库锤和数据库发生器生成测试数据。欲了解更多信息，请参见第39章，“工具，样品，电子图书，以及更多，”SQL Server上的资源光盘。此内容也是可在网上。

使用现有的生产数据。
如果您的应用程序将被用于对现有的数据库，可以考虑在您的开发，测试生产数据的副本，和临时环境。如果你的生产数据库包含敏感的数据，如工资信息，学生成绩，或其他敏感数据，确保你剥离出来，或随机。

使用常见的用户场景之间的平衡读写操作。

使用测试工具，系统上进行压力测试和负载测试。

监控

你能想到的一个数据库系统作为一个生活和成长的事情，你必须不断监控和调整。微调不是一个事件，它是一个持续的过程。度量，计数器和性能应主动定期检讨。以下指引，帮助您保持您的应用程序的性能和可扩展性，为您的数据库中年龄：

保持统计日期。

使用SQL事件探查调整长时间运行的查询。

使用SQL Profiler监视表和索引扫描。

使用性能监视器来监视高资源使用率。

设置的经营和发展的反馈回路。

保持统计日期

SQL Server使用基于成本的优化器提供了对表和索引的统计信息，如在一个表中的行数和平均密钥长度是敏感。没有正确和最新统计信息，SQL Server可能落得一个最优的执行计划，针对特定查询。

SQL Server中的每个表上的维持成本为基础的决策，以帮助优化器的统计信息包括的行数，表中所使用的页的数目，以及自上次修改表中的键的数量统计信息的更新。除了维护索引列上的统计，它有可能保持没有被索引的列上的统计。

的日期或缺少的统计显示图形方式显示在SQL查询分析器是查询的执行计划时警告。以红色文字显示的表名。监控缺少的列统计事件类使用SQL事件探查器，让你知道当缺少统计信息。要开启更新统计数据库选项，右键单击SQL Server企业管理器中的数据库，然后单击“ 属性“。单击“ 选项 “选项卡，然后选中” 自动更新统计信息 “复选框。此外，你可以从SQL查询分析器中运行sp_updatestats将系统存储过程，在数据库中更新该数据库的统计数据。

使用UPDATE STATISTICS命令或sp_updatestats将系统存储过程，大的变化，数据统计后，或在每天手动更新，如果每天有窗口可用。

更多信息

欲了解更多信息，请参阅知识库文章195565，“INF：如何在SQL Server 7.0和SQL服务器工作2000 Autostats的，”在。

使用SQL事件探查调整长时间运行的查询

定期使用SQL事件探查器如前所述，不断调整长时间运行的查询。据统计和使用情况变化出现的最长的查询，查询将发生变化。

使用SQL Profiler监视表和索引扫描

定期使用SQL事件探查器不断寻找表和索引扫描。由于统计和使用情况变化，表和索引扫描出现将会改变。

使用性能监视器来监测高资源使用

定期使用性能监视器来确定高资源使用率的领域，然后进行调查。

成立作战和发展反馈回路

实现生产和操作人员之间的定期沟通和发展组。确保所有各方交换性能和可伸缩性或发展变化，可能会影响性能和可扩展性的相关信息。

部署注意事项

物理部署的性能和可扩展性的一个重要因素。然而，在性能和可扩展性调整是一个常见的错误，首先集中于扩展或向外扩展的硬件。虽然资源有限的计算机不影响性能，可以使最大的收益，最大限度地减少资源的使用，如本章前面所述。您应该只考虑添加硬件后，CPU使用率限制，网络I / O，磁盘I / O和内存使用。

物理配置的要求非常具体的不同的场景，所以他们不包括在本节中的深度。然而，以下是你应该牢记一些重要的准则：

对于大多数应用程序，使用默认的服务器配置设置。

找到日志和tempdb数据库的数据在单独的设备。

提供大量访问的表和索引单独的设备。

使用适当的RAID配置。

使用多个磁盘控制器。

前养成的数据库和日志，以避免自动增长和分散性能的影响。

最大化可用内存。

管理索引碎片。

记住保持数据库管理员任务。

使用默认的服务器配置对于大多数应用程序的设置

当它是新安装的SQL Server使用的最佳配置设置。更改配置设置，实际上可能会降低性能，在某些高负载的情况下除外。任何配置更改之前彻底测试它，以确保真正的变化提高了性能和可扩展性。唯一的例外是内存设置，这是在本节后面讨论。

要找出如果你的服务器设置符合常见的最佳实践，你可以下载微软SQL Server 2000的最佳实践分析工具[内容链接不再可用，原

更多信息

对于SQL Server配置设置的更多信息，请参阅知识库文章319942“HOW TO：确定正确的SQL Server配置设置”，。

找到日志和tempdb数据库在单独的设备上，从数据

你可以提高性能，通过定位你的数据库日志和tempdb数据库的物理磁盘阵列或设备从主数据设备是分开的。由于数据修改写入日志和数据库和tempdb数据库，如果使用临时表，有三个不同的地点，不同的磁盘控制器提供了显着的效益。

提供单独的设备的大量访问的表和索引

如果你有一个特定的表或索引的I / O瓶颈，尝试把自己的文件组的表或索引单独的物理磁盘阵列或设备，以缓解性能瓶颈。

使用适当的RAID配置

对于数据库服务器，你应该选择，而不是硬件级RAID软件RAID。软件RAID通常是便宜，但使用的CPU周期。如果CPU的利用率是你的瓶颈，SQL Server可能无法达到最佳性能。

双核RAID级别是一个数据库服务器的值：

带奇偶校验的条带化（RAID 5）

条带化镜像（RAID 0 +1）

当你选择一个RAID级别，你必须要考虑你的成本，性能和可用性要求。RAID 5是不太昂贵的比RAID 0 +1和RAID 5的性能更好的读操作比写操作。RAID 0 +1是更加昂贵，并且写密集型操作和tempdb数据库访问性能更好。

使用多个磁盘控制器

磁盘控制器有限制吞吐量。关联太多的磁盘在一个磁盘控制器，可导致I / O瓶颈。

更多信息

对于如何确定每个磁盘控制器的磁盘的更多信息，请参阅“微软SQL Server 7.0的性能优化指南”在MSDN上

预增长的数据库和日志，以避免自动增长和碎片对性能的影响

如果您已启用自动增长，确保您使用了正确的自动增长选项。您可以种植％或固定大小的数据库大小。避免频繁更改的数据库大小。如果要导入大量的数据往往是一个固定大小的每周一次的基础上，增长由一个固定大小的数据库，以适应新的数据。

最大化可用内存

增加内存是提高SQL Server的性能，因为更多的数据可以缓存在内存中的最佳途径之一。启用地址窗口扩展（AWE）由SQL Server内存利用率更高。在Boot.ini文件中启用了/ 3GB开关，允许一个进程使用3 GB的虚拟内存。默认情况下，系统使用2 GB。操作系统限制由一个进程使用的内存为2 GB。

使用性能计数器来决定，你需要的内存量。某些性能计数器，你可以用它来衡量你的内存需要载列如下：

SQLSERVER：缓冲区管理器：缓冲区高速缓存命中率计数器表示数据从内存缓存中检索。这个数字应该在90左右。较低的值表示SQL Server需要更多的内存。

内存：可用字节计数器显示可用的RAM量。低内存的可用性是一个问题，如果计数器显示，10兆字节（MB）或更少的内存。

SQLSERVER：缓冲区管理器：免费的网页计数器，应该不会有持续的价值超过两秒钟，4个或更少。在缓冲池中有没有免费的网页时，您的SQL Server的内存需求可能已经变得如此激烈，懒惰的作家或检查指着过程是无法跟上。缓冲池压力的典型迹象是高于正常懒每秒写入或较多的检查点页每秒SQL Server尝试空的程序和数据缓存，以获得足够的可用内存，来电查询计划执行服务。这是一种有效的检测机制，表明您的程序或数据高速缓存内存饿死。要么增加分配给SQL Server的内存，或者找到散列或排序可能会发生大量的。

内存配置选项是一个服务器配置设置，你应该评估，并可能改变，如果有SQL Server以外的服务器上运行的进程。如果是这样的话，改变内存选项设置为固定，并留出足够的内存用于操作系统和其他进程可能运行。

更多信息

有关SQL Server内存要求的更多信息，请参阅“SQL Server 2000中的内存管理设施” 里面。

此外，请参阅知识库文章274750“HOW TO：配置内存超过2 GB，在SQL Server中，”在。