毕业设计（论文）-SQL Server性能优化与研究.doc

SQL Server性能优化与研究目 录一、引言1二、硬件优化3(一)调整处理器3(二)调整内存3(三)调整磁盘子系统4三、软件优化5(一)数据库设计5(二)SQL Server数据库的优化71.索引优化7索引基本原理7索引的优化设计10使用索引的注意事项122.查询优化12查询优化原理13关于在SQL Server中查询优化的建议5，614四、总结16致谢：17参考文献：1724SQL Server 性能优化与研究摘 要 在当今社会，网络发展飞速，更多的应用程序都是基于网络设计的。在分布式数据库系统中，随着数据量的飞速增长，怎样加快数据库的检索速度，是整个系统性能提高的关键。SQL Server是一个数据库管理系统，它功能强大，操作简便，而且越来越多的开发工具提供了与SQL Server的接口。所以，对于使用SQL Server为数据库的系统，怎样提高SQL Server的性能，是整个系统性能提高的关键。本文主要从硬件（CPU、内存和磁盘）和软件（索引和查询优化）两个方面给出一些SQL Server性能优化的建议。全文共分四个部分，第一部分是全文引言，第二部分是介绍怎么从硬件上提高性能，第三部分主要从索引和查询优化上面给出一些优化的建议，第四部分是全文的总结。关键词 性能调整，磁盘整列，索引，查询优化一、 引言本文对SQL Server性能的优化与研究是基于SQL Server 2000这一版本，之所以这一版本是因为今天的商业环境要求不同类型的数据库解决方案。性能、可伸缩性及可靠性是基本要求，而进入市场时间也非常关键。除这些核心企业品质外，SQL Server 2000 还为您的数据管理与分析带来了灵活性，允许单位在快速变化的环境中从容响应，从而获得竞争优势。从数据管理和分析角度看，将原始数据转化为商业智能和充分利用 Web 带来的机会非常重要。作为一个完备的数据库和数据分析包，SQL Server 2000 为快速开发新一代企业级商业应用程序、为企业赢得核心竞争优势打开了胜利之门。作为重要的基准测试可伸缩性和速度奖的记录保持者，SQL Server 2000 是一个具备完全 Web 支持的数据库产品，提供了对可扩展标记语言 (XML) 的核心支持以及在 Internet 上和防火墙外进行查询的能力。1）完全的 Web 支持 SQL Server 2000 提供了以 Web 标准为基础的扩展数据库编程功能。丰富的 XML 和 Internet 标准支持允许您使用内置的存储过程以 XML 格式轻松存储和检索数据。您还可以使用 XML 更新程序容易地插入、更新和删除数据。A. 通过 Web 轻松访问数据。有了 SQL Server 2000，您可以使用 HTTP 来向数据库发送查询、对数据库中存储的文档执行全文搜索、以及通过 Web 进行自然语言查询。 B. 强大而灵活的基于 Web 的分析。SQL Server 2000 分析服务功能被扩展到了 Internet。您可以通过 Web 浏览器来访问和控制多维数据。 2）高度的可伸缩性和可靠性 A. 使用 SQL Server 2000 可以获得非凡的可伸缩性和可靠性。通过向上伸缩和向外扩展的能力，SQL Server 满足了苛刻的电子商务和企业应用程序要求。B. 向上伸缩。SQL Server 2000 利用了对称多处理器 (SMP) 系统。SQL Server Enterprise Edition 最多可以使用 32 个处理器和 64 GB RAM。 C. 向外扩展。向外扩展可以将数据库和数据负载分配给多台服务器。 D. 可用性。通过增强的故障转移群集、日志传送和新增的备份策略，SQL Server 2000 达到了最大的可用性。 3）最快的进入市场时间 SQL Server 2000 是 Microsoft .NET Enterprise Server 的数据管理与分析中枢。SQL Server 2000 包括加速从概念到最后交付开发过程的工具。A. 集成和可扩展的分析服务。有了 SQL Server 2000，您可以建立带有集成工具的端到端分析解决方案，从数据创造价值。此外，还可以根据分析结果自动驱动商业过程以及从最复杂的计算灵活地检索自定义结果集。 B. 快速开发、调试和数据转换。SQL Server 2000 带有交互式调节和调试查询、从任何数据源快速移动和转化数据、以及按 Transact-SQL 方式定义和使用函数等功能。您可以从任意 Visual Studio 工具以可视化方式设计和编写数据库应用程序。 C. 简化的管理和调节。使用 SQL Server 2000，您可以很容易地在企业资源旁边集中管理数据库。可以在保持联机的同时轻松地在计算机间或实例间移动和复制数据库。 1速度一直是在数据库的访问中一个非常重要的问题。优化SQL Server性能的目的是通过网络流量，磁盘I/O阵列和CPU时间减到最小，使每个查询的相应时间最短，并最大限度地提高整个数据库服务器的吞吐量。为达到此目的，需要了解应用程序的需求和数据的逻辑和物理结构，并在相互冲突的数据库是用之间，如联机事务处理（OLTP）与决策支持系统，进行权衡。一般而言，对性能问题的的考虑必须贯穿于整个开发阶段的全过程，而不应只在最后实现系统时才考虑这个问题。性能调整（Performance Tuning）是通过修改系统参数（软件调试）或修改系统配置（硬件调试）来改变系统性能的操作。性能调整包括对硬件配置、操作系统和关系数据库管理系统（RDBMS）以及访问这些组件的应用程序的详细分析。应用程序的调整通常是调整一个SQL Server系统的第一步，这是因为应用程序是最有可能导致系统性能的组件。但是在本文中，这不是要论述的主要问题，本文主要通过SQL Server调整和硬件调整以达到SQL Server整体性能优化的目的。在下面的内容中，主要从硬件和软件两个方面对SQL Server进行优化，其中，第二部分从硬件上进行优化；第三部分从软件上优化，这一部分的优化主要从索引优化和查询优化来进行论述。二、 硬件优化硬件优化，主要就是通过硬件调整服务器，调整系统中硬件的数量、大小和速度。而在硬件调整中最为主要的硬件处理器，磁盘和内存。 (一) 调整处理器如果处理器使用率保持在80%或更高，或者经常出现峰值使用率，系统可能就会出现CPU瓶颈，这是一个普遍的规律。如果发现了CPU瓶颈，可以在系统中添加更多或更快的处理器，这样可以提高系统的性能。例如，系统有一个1GHz的CPU，其平均使用率保持在75%到80%之间，这种情况，如果在系统中增加第二个1GHz的CPU，是系统具有并行能力。如果系统主板只支持单CPU，那么就不能通过增加CPU来解决系统性能问题，我们只能更换速度更快的CPU。这两种方法都可以提高系统性能，但是如果可能的话，建议使用前者。关于操作系统不同版本和SQL Server 2000不同版本支持的CPU请查阅相关资料，这里给出Windows2000和SQL Server 2000各版本支持的CPU个数。 (二) 调整内存内存调整包括物理内存调整和SQL Server内存调整。为SQL Server方案确定合适的内存设置对于实现良好的性能是至关重要的。SQL Server用内存做过程缓存、数据和索引项缓存、静态服务器开支和设置开支。SQL Server最多能利用2GB虚拟内存，这也是最大的设置值。还有一点必须考虑的是Windows NT和它的所有相关的服务也要占用内存。表2-1 Windows2000和SQL Server 2000各版本支持的CPU个数SQL Server企业版SQL Server标准版SQL Server个人版SQL Server开发人员版Windows 2000 Datacenter Server324232Windows 2000 Advanced Server8428Windows 2000 Server4424Windows 2000 Professional2222Windows NT为每个WIN32应用程序提供了4GB的虚拟地址空间。这个虚拟地址空间由Windows NT虚拟内存管理器(VMM)映射到物理内存上，在某些硬件平台上可以达到4GB。SQL Server应用程序只知道虚拟地址，所以不能直接访问物理内存，这个访问是由VMM控制的。Windows NT允许产生超出可用的物理内存的虚拟地址空间，这样当给SQL Server分配的虚拟内存多于可用的物理内存时，会降低SQL Server的性能。这些地址空间是专门为SQL Server系统设置的，所以如果在同一硬件平台上还有其它软件(如文件和打印共享，应用程序服务等)在运行，那么应该考虑到它们也占用一部分内存。一般来说硬件平台至少要配置32MB的内存，其中，Windows NT至少要占用16MB。1个简单的法则是，给每一个并发的用户增加100KB的内存。例如，如果有100个并发的用户，则至少需要32MB+100用户*100KB42MB内存，实际的使用数量还需要根据运行的实际情况调整。可以说，提高内存是提高系统性能的最经济的途径。 (三) 调整磁盘子系统设计一个良好的磁盘I/O系统是实现良好的SQL Server实例很重要的一方面。对磁盘子系统进行调整，这样系统的I/O处理就不用经历过长的I/O等待时间，并且读写操作可以以最佳的速度执行。磁盘的I/O能力是指一个磁盘在系统I/O请求的等待队列达到一定数目从而使等待时间呈指数规律增长降低系统性能之前所能完成的最大I/O操作数目。在磁盘子系统中，还有一个困扰我们的的问题就是磁盘的出错问题，一旦磁盘出错了，我们怎么办？为了解决这些问题，我们可以将两个或者更多的磁盘组合成一个RAID阵列。其主要特点是，多个物理磁盘驱动器可以组合成一个逻辑磁盘驱动器。首先，我们介绍一下常见的RAID级别。1)RAID 0RAID 0是Data Striping(数据分割)技术的实现，它将所有硬盘构成一个磁盘阵列，可以同时对多个硬盘做读写动作，但是不具备备份及容错能力，它价格便宜，硬盘使用效率最佳，但是可靠度是最差的。以一个由两个硬盘组成的RAID Level 0磁盘阵列为例，它把数据的第1和2位写入第一个硬盘，第三和第四位写入第二个硬盘以此类推，所以叫“数据分割”，因为各盘数据的写入动作是同时做的，所以它的存储速度可以比单个硬盘快几倍。但是，这样一来，万一磁盘阵列上有一个硬盘坏了，由于它把数据拆开分别存到了不同的硬盘上，坏了一颗等于中断了数据的完整性，如果没有整个磁盘阵列的备份磁带的话，所有的数据是无法挽回的。2)RAID 1RAID 1使用的是Disk Mirror(磁盘映射)技术，就是把一个硬盘的内容同步备份复制到另一个硬盘里，所以具备了备份和容错能力，这样做的使用效率不高，但是可靠性高。3)RAID3RAID 3采用Byteinterleaving(数据交错存储)技术，硬盘在SCSI控制卡下同时动作，并将用于奇偶校验的数据储存到特定硬盘机中，它具备了容错能力，硬盘的使用效率是安装几个就减掉一个，它的可靠度较佳。4)RAID5RAID 5使用的是Disk Striping(硬盘分割)技术，与Level 3的不同之处在于它把奇偶校验数据存放到各个硬盘里，各个硬盘在SCSI控制卡的控制下平行动作，有容错能力，跟Level 3一样，它的使用效率也是安装几个再减掉一个。5)RAID10RAID10是RAID 0和RAID 1的组合。RAID10包括镜像一个磁盘条，每个磁盘将具有一个相同的副本，但是每个磁盘将只包含有一部分数据。RAID级别的优点和缺点：RAID 0存取速度最快，但没有容错；RAID 1完全容错，但成本高；RAID 3写入性能最好，但没有多任务功能；RAID 5具备多任务及容错功能，写入时有overhead；RAID 10 速度快、完全容错；可是成本比较高。表2-2给出了不同级别的RAID的特性。表2-2 RAID级别RAID级别性能容错性成本RAID 0最佳没有最经济RAID1良好良好最昂贵RAID5读操作良好，写操作速度较慢一般最经济的容错性RAID10良好完全最昂贵在采用了磁盘阵列以后，如果我们还是碰到了磁盘性能瓶颈，我们可以增加阵列中的磁盘，增加的时候，要注意所采取的RAID级别，按规则增加。三、 软件优化 (一) 数据库设计对于数据库的优化，我们不能只停留在后期优化上，应该在数据库设计之初就拿出一个很好的数据库设计方案。在实际工作中，许多SQL Server方案往往是由于数据库设计得不好导致性能很差。所以，要实现良好的数据库设计就必须考虑这些问题。一般来说，逻辑数据库设计会满足规范化的前3级标准：1) 第1范式：没有重复的组或多值的列。2）第2范式：每个非关键字段必须依赖于主关键字，不能依赖于1个组合式主关键字的某些组成部分。3）第3范式：1个非关键字段不能依赖于另1个非关键字段。规范化程度越高，消除操作异常和减少数据冗余的机会就越多。但是，检索数据的速度就会相应地减慢。这是因为，减少数据冗余势必要以多重的关联作为代价，在数据库中使用复合关键字以及数据库之间的关联，都可能造成在检索数据时要访问更多的表，进行更多的关联操作，肯定会影响检索的速度，导致效率下降。由此可见，并不是规范程度越高，就一定会收到好的效果。对数据库的规范化设计，要具体问题具体分析。一般可遵循下列原则：1）每个表都有一个主键。2）所有字段中包含的数据都应当是不可再分的。3）不能有重复字段。4）每个数据表中应当包含单个实体的信息。5）表中的每个字段都应依赖于主键中的所有字段。6）所有非主属性字段应该相互独立，没有传递依赖关系。但是，规范化的数据库并不是完美的，而它的缺点，大多数是与性能相关的，其缺点如下：1）在规范化数据库中，需要更多的连接把信息从多个表中组合到一起；2）联接需要更多的I/O操作，因此从性能角度来看比单个表开销要大；3）规范化数据通常会引起额外的CPU操作，需要用CPU资源来执行联接逻辑和维护数据及引用完整性。规范化数据库没有摘要数据，因为其在两方面违反规范化：他是多余的信息，并且他没有独立的事务意义；为了计算摘要数据，要聚合多行数据，这会引起大量的CPU处理和I/O访问。因此，为了提高性能，在必要的时候，可以适当的建立非规范化的数据库。非规范化是为了提高性能有意违反规范化逻辑设计的规则的过程。规范化的数据表通常只包含必须的内容，当需要检索信息时，可能要从多个表中根据外部键，主键信息进行组合查询多次联接会造成数据传输速度变慢。如果规范化数据库模型生成具有多个主键的表，特别是当键的字段在4个以上，并且用于和其他表的联接时，就应当考虑数据非规范化模式，引入代理键。通常，可使用如下方法提高系统的性能：1）使用标识字段和唯一性约束组合来建立代理键，然后在数据表中加入任意外部键，联接主数据表，并执行代理键的联接。2）如果在查询时要生成的计算值涉及到多个联接，则应当考虑数据非规范化，以便在数据表中增加存储这些计算字段，而不是检索时再去生成这些字段。例如，某一个项目的计划管理系统中有计划表，其字段为：项目编号、年初计划、二次计划、调整计划、补列计划，而计划总数(年初计划+二次计划+调整计划+补列计划)是用户经常需要在查询和报表中用到的，在表的记录量很大时，有必要把计划总数作为1个独立的字段加入到表中。同时，可使用触发器确保相关字段更新时自动更新这些计算列。或者，如果数据库包含超大表格，则应当把数据非规范化，以便建立多个冗余表，可按行或列进行操作。3）重新定义实体以减少外部属性数据或行数据的开支。相应的非规范化类型是：A把1个实体(表)分割成2个表(把所有的属性分成2组)。这样就把频繁被访问的数据同较少被访问的数据分开了。这种方法要求在每个表中复制首要关键字。这样产生的设计有利于并行处理，并将产生列数较少的表。B把1个实体(表)分割成2个表(把所有的行分成2组)。这种方法适用于那些将包含大量数据的实体(表)。在应用中常要保留历史记录，但是历史记录很少用到。因此可以把频繁被访问的数据同较少被访问的历史数据分开。而且如果数据行是作为子集被逻辑工作组(部门、销售分区、地理区域等)访问的，那么这种方法也是很有好处的。应该明白，非规范化设计是为特殊的应用程序而调整数据库的一种技巧。规范化过程是以有组织的方式布置数据，而非规范化设计则是在数据中有意引入冗余。冗余数据通过减少联接或计算来帮助提高性能，反过来也减少了I/O和CPU操作。因此，非规范化数据必须要有明确的目的，否则，随意进行数据非规范化设计可能会破坏数据的完整性，同时也会影响系统性能。 (二) SQL Server数据库的优化1索引优化 索引基本原理索引是一个单独的，物理的数据库结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。一个索引是一个辅助的数据结构，可以用来帮助SQL Server 2000访问数据。通常，索引的结构是一个B树，B树结构以一个根节点（Root Node）作为开始，这个根节点就是索引的起始点。根节点包含索引行（即索引数据行）；索引行含有索引键值的范围和指向下一个索引节点（称为分支节点，Branch Node）的指针；分支节点依次含有索引行，它们具有指向其他分支节点的更细化的值。每级分支节点称为一个索引级别（Index Level）。在索引树最底层的节点称为叶子节点（Leaf Node）。叶子节点含有索引键数据及被引用数据位置或数据自身的信息，这主要要根据索引是非聚集还是聚集的而定。到达一个叶子节点必须经过的索引级数决定了找到需要的数据行所必须的I/O数量。图3-1是前面介绍的一些概念，每个节点不一定如图3-1只包含两个指针，事实上，节点中的每条索引行含有一个指向其他节点的指针。图3-2所示的是常规索引结构。图3-1 一个索引示例图3-2 一个索引结构SQL Server支持两种主要的索引类型：聚集索引和非聚集索引。对于这两种索引类型中的任何一种索引，都可以指定该索引是否是唯一索引。另一种索引称为全文索引，它更像一个目录。全文索引具有与聚集和非聚集索引不同的特性，是代表它自己的一类索引。下面是关于这些索引的特性及使用场合。1）聚集索引聚集索引确定表中数据的物理顺序。聚集索引类似于电话簿，后者按姓氏排列数据。由于聚集索引规定数据在表中的物理存储顺序，因此一个表只能包含一个聚集索引。但该索引可以包含多个列（组合索引），就像电话簿按姓氏和名字进行组织一样。聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻。例如，如果应用程序执行的一个查询经常检索某一日期范围内的记录，则使用聚集索引可以迅速找到包含开始日期的行，然后检索表中所有相邻的行，直到到达结束日期。这样有助于提高此类查询的性能。同样，如果对从表中检索的数据进行排序时经常要用到某一列，则可以将该表在该列上聚集（物理排序），避免每次查询该列时都进行排序，从而节省成本。42）非聚集索引非聚集索引与课本中的索引类似。数据存储在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置。索引中的项目按索引键值的顺序存储，而表中的信息按另一种顺序存储（这可以由聚集索引规定）。如果在表中未创建聚集索引，则无法保证这些行具有任何特定的顺序。使用书中索引的方式相似，SQL Server 2000 在搜索数据值时，先对非聚集索引进行搜索，找到数据值在表中的位置，然后从该位置直接检索数据。这使非聚集索引成为精确匹配查询的最佳方法，因为索引包含描述查询所搜索的数据值在表中的精确位置的条目。如果基础表使用聚集索引排序，则该位置为聚集键值；否则，该位置为包含行的文件号、页号和槽号的行 ID (RID)。43）唯一索引唯一索引是一个被特别创建成具有唯一性的聚集索引或非聚集索引。插入到键值列或多个列中的值必须是唯一的，不允许有重复的键值被插入。一旦你在一个表上拥有唯一索引，SQL Server会阻止向表中输入相同的数据，并且给出一条错误信息，指出试图插入相同的行。4）全文索引 使用全文索引，必须安装SQL Server标准版或企业版提供的Full-Text Search Service（全文索引服务）全文索引要比前面讨论的索引类型复杂。全文索引实际上就像一个目录。全文索引使用的并不是简单地在有序B树中进行查找的方式，而是允许使用关键字组进行查找。与B树的索引不同，全文索引存储在数据库的外部，由数据库来维护。通过这种方式，索引可以保持自己的结构。全文索引具有普通B树索引所没有的强大功能，因为这种索引被设计为一个文本搜索引擎，所以他还支持除了普通文本搜索外的其它功能。使用全文索引，可以执行如下所示类型的操作：搜索词或短语；搜索单个词或成组词；搜索彼此相似的词。2 索引的优化设计数据表中的数据既有关系数据，也有物理顺序。数值型的关系数据对于数字是常用的运算顺序，对于字符型则是物理（按字典）顺序，表中记录的物理顺序是磁盘驱动器上的连续页或每页中的连续记录的顺序。因此，要更好地处理插入和更新数据库的操作，有效地索引设计对提高SQL Server的性能非常重要。因为索引不看作是逻辑数据库设计的一部分，因而对索引的操作不会影响到数据库结构，只会影响到数据库或者应用程序的性能。通常情况，一个好的索引往往会提高select语句的性能。但是，由于改变一个表的内容将会影响索引，因此也会影响到每个insert，update和delete语句的性能，所以，不要在一个单表上用大量的索引，不要在共享列上使用重叠的索引。如果表中有主关键字约束，则需要唯一的索引项来保证主关键字的完整性，避免出现重复。因此，如果只是为了访问数据，则可以不需要索引，SQL Server 可以通过对数据的扫描进行数据行访问。但是，对于包含上百万行的表，利用表扫描去获取某一行，则是非常困难的。如果表每页存储50行，那么结果可能是读20000页来访问某一行，在这种情况下，应该用更直接的路径来访问数据，因此，只有利用索引才能解决这个问题。为了有效的决定是否要创建索引，应该了解SQL Server是否会使用该索引。如果SQL Server不使用这个索引，这个索引只会浪费空间，并且在数据更新时产生不必要的开销。设计一个索引时要注意以下几点：1）编写更新记录的查询时，用一条语句而不是多条语句来更新通一记录；2）编写好查询后，使用索引查询向导进行分析；3）对生成聚集索引时，使用整数字段并把它设置为唯一的非空或者标识字段；4）为用于查询的所有字段生成索引；5）不能对只有很少唯一值的字段生成索引。现在，我们来看看怎样使用Index Tuning Wizard（索引调整向导，下简称ITW）来优化索引。可以让ITW根据指定的Workload（工作负荷）推荐最佳的数据库索引配置。Workload是保存到外部文件的SQL脚本或跟踪结果。ITW的建议是根据给定Workload而提出的最优化建议，因此事先准备合适的基础数据非常重要。为ITW创建工作负荷文件最简单、最全面的方法或许应该是保存由SQL Profiler创建的跟踪。使用SQL Profiler为ITW创建跟踪时，应当确保跟踪已经记录了典型的数据库活动。换句话说，应当选择一个数据库负载不是特别繁重、也不是特别轻松的时段进行跟踪。至于跟踪要运行多少长时间，这由系统本身的特点决定。有些时候，我们可能只需跟踪数小时就可以得到系统典型活动情况的记录；有些时候，我们可能要让跟踪持续几天，才能记录下数据库中所有典型的活动情况。准备好工作负荷文件以后，我们可以从企业管理器中启动ITW。展开服务器组，选中服务器，从“工具”菜单选择“向导”，在树形视图中找到“管理”节点，选择索引调整向导，此时我们就可以看到ITW的欢迎屏幕。ITW的第二个屏幕让我们指定要分析的是哪一个服务器和数据库。在这个屏幕上，我们还有另外两个选项：保留现有索引，优化模式。清除“保留现有索引”选项使得ITW能够提出最优索引建议，但此时ITW可能建议删除或者修改现有的某些索引。如果你不想修改现有的索引，请保留这个选项的选中状态。选中“优化模式”选项使得ITW进行最广泛、深入的分析。虽然进行完全分析可能提高分析结果的质量，但它一般需要较长的时间才能完成；而且完全分析运行时，它很可能导致服务器负载过重。由于这些原因，如果你需要执行完全分析，那么最好在测试服务器上进行，或者在正式提供服务的机器上，选择一个比较空闲的时段进行。ITW的第三个屏幕让我们指定对哪一个工作负荷进行分析。如果你使用的是SQL Profiler创建的文件，请选择“我的工作负荷”选项按钮，然后在文件对话框中找到以前保存的跟踪文件。在这个屏幕中，点击“高级选项”命令按钮可以设置一些高级选项。这些选项包括：被推荐的索引可以使用的最大磁盘空间总量，工作负荷文件中查询取样的最大数量。 在第四个屏幕中，我们可以指定ITW应该对哪个或者哪些表的索引提出建议。只选择那些相关的表有利于节省时间，而且它有助于我们把注意力集中到特定的问题之上。不过，如果要让ITW对整个数据库的优化提出建议，我们应该选中数据库里面所有的表。ITW的下一个屏幕根据我们设定的条件，显示出它对索引配置的建议（见图3-3）。我们可以选择立即执行它提出的建议，或者计划在以后执行，或者把执行脚本保存到外部文件。ITW不会对主键或者其他唯一性索引提出建议，也不会对系统表的索引提出建议。ITW的其他局限还包括：在给定的工作负荷中，它分析的索引不能超过32767个；不能对SQL Server 6.5或者更早版本创建的数据库提出索引建议。注意，ITW是以用户所提供数据的样本为基础提出索引配置建议。由于这个原因，你可能会发现：如果让ITW对同一个工作负荷分析多次，它可能会提出多种不同的索引配置建议。如果ITW不能提出任何建议，它可能是由于下面两种原因之一造成：第一，与数据库中现有的索引配置方案相比，ITW无法提出任何能够进一步提高性能的索引建议；第二；取样的表里面没有足够的数据，无法确定一个合适的索引配置方案。9图3-3 索引配置向导第三屏 使用索引的注意事项虽然使用索引的好处很多，可以减少I/O操作的次数，减少查询所需记录的时间，提高应用程序的性能等，但是，有一个问题却是不能忽视的，那就是索引要付出的代价。使用索引会增加不必要的时间和空间的开销，特别是对于更新操作的性能会是个障碍，这是由于在数据修改，插入和删除时需要同时更新索引，这会增加额外的开销。因此，当我们优化索引时，一定要谨慎，要选择恰当的索引，尽量减少磁盘访问的次数，是否使用索引取决于查询的复杂程度。决定定义哪些索引，需要对一个查询进行详细的分析：首先检查查询语句，看看它指向哪些字段，了解数据的关联程度，以便决定使用索引要起到哪些作用。其次，按照查询的重要程度划分查询等级。最后还要考虑在数据表中运行的其他查询，不要只针对单个查询创建索引。7，82查询优化查询优化在关系型数据库中有着非常重要的地位。查询语句的好坏，将直接影响查询的速度问题。查询速度慢的原因很多，常见如下几种：1）没有索引或者没有用到索引(这是查询慢最常见的问题，是程序设计的缺陷)；2）没有创建计算列导致查询不优化；3）查询出的数据量过大（可以采用多次查询，其他的方法降低数据量）；4）锁或者死锁(这也是查询慢最常见的问题，是程序设计的缺陷)；5）sp_lock,sp_who,活动的用户查看,原因是读写竞争资源；6）返回了不必要的行和列；7）查询语句不好，没有优化。 查询优化原理首先，我们来看一个实例，数据库中有如下三个数据表：student，course，seletable。其中student是学生信息表，其结构如下：表3-1 student表字段数据类型可否空学号Char（10）否姓名Char（10）年龄Int性别Char（2）系别Char（10）年级Char（10）course是课程表,其结构如下：表3-2 course表字段数据类型可否空课程号Char（10）否课程名Char（10）学分Intseletable是选修表，其结构如下：表3-3 seletable表字段数据类型可否空学号Char（10）否课程号Char（10）成绩Int让我们来看一个查询语句，求选修课程号为8的学生的姓名：select student.姓名 from student,seletable where student.学号=seletable.学号 and seletable.课程号=8。系统可以用多种关系代数表达式完成，如：1）Q1=姓名(student.学号=seletable.学号seletable.课程号=8(studentseletable)2）Q2=姓名(seletable.课程号=8(studentseletable)3）Q3=姓名(studentseletable.课程号=8(seletable)假设内存中一块能装100个student或1000个 seletable元组，在内存中可以放5块student元组和 一块seletable元组。表中有10000个学生记录和100000个选课记录，则第一种情况总读取块数为：10000/100+10000/100*5*100000/1000=2100。设每秒读取20块，总计要花205s，连接后的元组为109 个，每块能装100个元组，则写要5*104s,则总的查询时间为105+2*5*104=105s,而第二种情况的查询时间为205s，第三种情况的查询时间为10s。3 这个例子就充分说明了查询优化的重要性。我们在做查询优化的时候一般遵循如下几个准则：1）选择运算应尽可能先做；2）在执行连接前对关系适当地预处理；3）把投影运算和选择运算同时进行；4）把投影同期前后的双目运算结合起来，没有必要为了去掉某些字段而扫描一遍关系；5）把某些选择同在他前面要执行的笛卡尔积结合起来成为一个连接运算，连接特别是等值连接运算要比同样关系上的笛卡尔积省很多的时间；6）找出公共子表达式。5 关于在SQL Server中查询优化的建议5，6SQL Server优化器通过分析查询语句，自动对查询进行优化并决定最有效的执行方案。优化器分析查询语句来决定哪个子句可以被优化，并针对可以被优化查询的子句来选择有用的索引。最后优化器比较所有可能的执行方案并选择出最有效的一个方案。但是，在写查询语句的时候，还应该注意以下的一些问题：1）根据查询条件,建立索引,优化索引、优化访问方式，限制结果集的数据量。注意填充因子要适当（最好是使用默认值0）。索引应该尽量小，使用字节数小的列建索引好（参照索引的创建）,不要对有限的几个值的字段建单一索引，如性别字段。2）如果是使用like进行查询的话，简单的使用index是不行的，但是全文索引，比较耗空间。 like a% 使用索引； like %a 不使用索引；用 like %a% 查询时，查询耗时和字段值总长度成正比,所以不能用CHAR类型，而是VARCHAR。对于字段的值很长的建全文索引。3）重建索引 DBCC REINDEX ,DBCC INDEXDEFRAG,收缩数据和日志 DBCC SHRINKDB,DBCC SHRINKFILE. 设置自动收缩日志.对于大的数据库不要设置数据库自动增长，它会降低服务器的性能。 在T-sql的写法上有很大的讲究，下面列出常见的要点：首先，DBMS处理查询计划的过程是这样的： A查询语句的词法、语法检查 B将语句提交给DBMS的查询优化器 C优化器做代数优化和存取路径的优化 D由预编译模块生成查询规划 E然后在合适的时间提交给系统处理执行 F最后将执行结果返回给用户其次，看一下SQL SERVER的数据存放的结构：一个页面的大小为8K(8060)字节，8个页面为一个盘区，按照B树存放。4）在查询Select语句中用Where字句限制返回的行数,避免表扫描,如果返回不必要的数据，浪费了服务器的I/O资源，加重了网络的负担降低性能。如果表很大，在表扫描的期间将表锁住，禁止其他的联接访问表,后果严重。例如：执行select * from 入库单表 where 入库单编号 like %3比执行select * from 入库单所消耗的时间少，前者在99998条记录的查找时间为1s，而后者查找消耗时间为3s。5）union 剔除整个大集合中的相同记录只保留一条（有个排序过程）；union all 把两个集合叠在一起，不用排序。union all 比union 速度快很多，建议在相同的情况下用 union all。6）注意使用DISTINCT，在没有必要时不要用，它同UNION一样会使查询变慢，重复的记录在查询里是可以的。7）查询时不要返回不需要的行、列。8）使用Query Analyzer，查看SQL语句的查询计划和评估分析是否是优化的SQ，一般的20%的代码占据了80%的资源，优化的重点是这些慢的地方。9）将需要查询的结果预先计算好放在表中，需要查询时，再用select语句进行搜索，这样也可以加快查询速度。10）没有必要时不要用DISTINCT和ORDER BY，这些动作可以改在客户端执行。它们增加了额外的开销。这同UNION 和UNION ALL一样的道理。11）Between在某些时候比IN速度更快,Between能够更快地根据索引找到范围。用查询优化器可见到差别。 select * from chineseresume where title in (男,女) Select * from chineseresume where between 男 and 女 是一样的。由于in会在比较多次，所以有时会慢些。12）一般在GROUP BY HAVING字句之前就去除多余的行，所以尽量不要用它们来做除行的工作。他们的执行顺序应该如下最优：select 的Where字句选择所有合适的行，Group By用来分组个统计行，Having字句用来剔除多余的分组。这样Group By Having的开销小，查询快.对于大的数据行进行分组和Having十分消耗资源。如果Group BY的目的不包括计算，只是分组，那么用Distinct更快。13）SELECT COUNT(*)的效率教低，尽量变通他的写法，而EXISTS快.同时请注意区别： select count(Field of null) from Table 和 select count(Field of NOT null) from Table 的返回值是不同的。14）将一个大的查询拆分成多步执行查询，也可以优化执行的性能。因为一个大的多表查询是一个包含很多表的查询，要查询多个表的行必须对它们建立相应的联接，但是建立联接的开销也可能会很大，所以有可能会影响执行的性能。下面简要给出怎样拆分一个大的查询表：A使用多表联接时，应了解外部/内部的处理过程；B寻找一些条件，使得优化器不使用所有可用的索引资源；C寻找嵌套扫描大表所出现的条件；D通过建立临时的结果集合来拆分查询；E使用SQL语句来减少被处理的查询。四、 总结在上面的文章中，我们分别从硬件和软件的两个方面介绍了怎样优化SQL Server数据库。最后，我们来做一些总结。首先，在调整数据库之前，要知道如何评估性能。优化的数据库性能来自最小的响应时间，最大的网络通信量，最快的磁盘I/O通信量和最快的CPU时间。知道了这些，就确定了优化要进行的工作。接着，就按着如下步骤着手优化数据库：1）确定问题；2）设计一个可能的问题解决方案；3）实现解决方案；4）分析解决方案的结果。系统性是有效调整系统的关键，下面是一些技巧和建议：1）将所有事情记入文档。文档是数据库性能调整和功能计划非常重要的部分。文档应该清晰，简明，最后由以下模块组成：硬件配置，软件组件，配置的修改和性能说明。2）一次修改一项。这样，、，可以确定到底哪一次修改提高了系统性能以及哪一次修改降低了系统性能。3）不要恐慌。也许性能的问题是紧急的，但是请时刻保持冷静，以避免大的错误的产生，有时候，最好将问题搁置在一边，休息几个小时，以免产生增加问题难度的错误。注意，处理问题时一定要做到核实问题，谨慎处理，将所有事情记入文档。4）不要害怕请求帮助。请求专家帮助并不是代表你能力问题，而是你聪明智慧的体现。了解了上面这些，之后就是SQL Server性能优化工作的开始：1）进行应用程序的调整，这里的调整包括分析结构化查询语言语句以及确定这些查询语句是否是高效率的。通过调整和修改这些SQL语句以及应用程序访问数据库德方法可以有效地改进系统的性能。2）SQL Server调整，涉及修改SQL Server分配资源的方式以及修改其配置参数后SQL Server如何让运行。某些配置参数与资源的使用有关，而有些则无关。3）硬件调整，指为预期的工作量提供足够的硬件资源的操作，它是处于大小调整和容量规划之间的中间步骤。要调整系统硬件，必须确定为SQL Server分配何种资源才能实现更好的性能。这些资源包括，内存，CPU，I/O等。2参考文献：1SQL Server产品概述 /china/sql/evaluation/overview/default.asp2 （美）Edward Whalen SQL Server 2000性能调整技术指南武欣，何畅，罗云峰 等译机械工业出版社 20023 范立南，刘天惠SQL Server 2000实用教程M北京：清华大学出版社，20044 萨师煊，王珊数据库系统概论M北京：高等教育出版社 20005 飞思科技产品研发中心SQL Server 2000 高级管理与开发M北京：电子工业出版社 20026 新概念电脑教程编委会数据库应用与开发SQL Server 2000西安：电子工业出版社20027 佚名影响SQLserver性能的关键三个方面/Article_Show.asp?ArticleID=15378 佚名SQL Server数据库性能优化技术 /www/news/article_show.asp?id=8455 9 佚名SQL Server查询优化方法 /article_2408.16997.htmlOptimize AND Research SQL Server PerformanceAbstract Now，with the development of internet technique, many application is designed base on internet. In DDBMS, the data increase very fast. So, how to search the data which you are interested in as soon as fast is the key to improve the whole systems performance. SQL Server is a kind of DBMS. Its easy to use and it include many useful tools. Many developer tools have interfa