SQLSERVER性能调优.docx

发布时间：2022-06-24 发布人：admin 分类：说明书资料大小：0.03M 资料格式：docx 举报版权申诉

sunhaook666-10931242-SQL-Server程序性能调优.docx.pdf-第1页.png

第1页 / 共8页

sunhaook666-10931242-SQL-Server程序性能调优.docx.pdf-第2页.png

第2页 / 共8页

sunhaook666-10931242-SQL-Server程序性能调优.docx.pdf-第3页.png

第3页 / 共8页

sunhaook666-10931242-SQL-Server程序性能调优.docx.pdf-第4页.png

第4页 / 共8页

sunhaook666-10931242-SQL-Server程序性能调优.docx.pdf-第5页.png

第5页 / 共8页

sunhaook666-10931242-SQL-Server程序性能调优.docx.pdf-第6页.png

第6页 / 共8页

sunhaook666-10931242-SQL-Server程序性能调优.docx.pdf-第7页.png

第7页 / 共8页

sunhaook666-10931242-SQL-Server程序性能调优.docx.pdf-第8页.png

第8页 / 共8页

SQL Server硬件配置

SQL Server设计优化

SQL Server调优SQL编程

如何优化你的T-SQL代码

谨慎使用触发器

只访问所需要的数据

避免使用游标（Cursors）

合理使用联合（Joins）

将代码封装在

如何选择索引来最优化数据库性能

选择聚集索引的技巧

选择非聚集索引的技巧

SQL Server 硬件配置当应用程序性能出现问题时，服务器硬件通常会背上黑锅，人们想到的往往是如何优化服务器的硬件。实际上恰恰相反，多数情况下，硬件并非导致性能问题的罪魁祸首。对于基于 SQL Server 的应用程序的性能和升级，服务器硬件所起的影响要远比人们想象的小。多数应用程序运行缓慢的原因是因为其糟糕的前期设计，而并非硬件性能不够。硬件通常被冠以恶名的原因是，在应用程序运行缓慢之前，性能问题通常不是那么显而易见。而且应用程序的设计不是说改就改的，人们解决性能问题最简单直接的办法就是提高其硬件性能。虽然这种办法也有一定效果，不过它不能真正完全解决问题，这也是为什么人们常常将性能低下归结为硬件问题的原因。虽然硬件有时候确实会导致性能问题，但多数情况下它却不是主要原因。为了防止你的服务器硬件给 SQL Server 应用软件拖后腿，首先让我们简单看一下一些常见的硬件选择和调优问题。选择硬件为你的 SQL Server 应用选取最佳硬件要参照很多因素，诸如数据库的规模、用户的数量，数据库被使用的方式（OLTP 或 OLAP）等等。虽然没有成功的公式来估算服务器硬件需求，最好的办法就是在开发阶段提前开始测试你的应用。尽管许多有经验的 DBA 可以对你所需要的最佳硬件给出合理的估测，只有通过实际的测试才可确信满足你的应用需要的硬件是什么。在考察服务器硬件时，需要牢记以下硬件选择方面的事项： CPU：要购买可以扩展 CPU 数量的服务器。例如，通过测试结果你认为单 CPU 服务器就够用，那么你应该购买具备至少两个 CPU 安装空间的服务器，哪怕现在空着另一个 CPU 插槽的位置。预留下将来升级扩展的空间。内存：它可能是对 SQL Server 的性能影响最大的硬件部分。理想情况下，你的整个数据库应该可以 fit into 内存。不幸的是，这一般是不可能的。最低要求是，内存的大小应该能够容纳你的数据库中最大表，如果经济上可以接受，为服务器配备其能够支持大小的内存，换句话说，内存多了没坏处。 I/O 子系统：它对 SQL Server 性能的影响仅次于内存，也非常重要。最低要求是，使用硬件 RAID 系统来运行你的数据库。大概来说，你应该购买多个小硬盘，而不是一个大硬盘。在阵列中的硬盘数量越多，就可以获得更快的 I/O。网络连接：在你的数据库服务器上，至少应该有一个百兆网卡，而且它应该连接到一个交换机上。理想情况下，服务器应该有两块网卡，通过全双工方式连接到交换机。调优服务器如果没有正确的配置和优化，最贵的服务器硬件未必具有最好的性能。我曾经遇到过很多硬件相关的性能问题，其多数原因是驱动未正确安装。这些硬件性能相关的问题中很多往往难于跟踪和解决。一般来说，应该让一个有经验的技术高手来确保硬件被正确安装和配置。然后，在该服务器被用于生产环境之前，在一定条件下测试你的应用程序，以发现潜在的性能问题。另外，你的操作系统也必须被正确的配置，这涉及到很多方面，在这儿无法具体介绍。为了在一个服务器获得最好性能，SQL Server 应该独享一台服务器，而不应该同时还安装其它管理工具。不要为了省一点钱而将你的 IIS 或 MTS 服务器与 SQL Server 安装在同一

台服务器上。这不仅仅会影响 SQL Server 的性能，而且使得性能调优和故障排查工作非常难于进行。优化 SQL Server 配置调优 SQL Server 的另一个常见误解是，为了获得最佳性能你必须定制优化它的多处配置。对于一些早期版本的 SQL Server 来说，这种做法或许有一定道理，但是对于最近版本的 SQL Server，配置通常已经不再是一个问题，当然对于那些超大、超忙碌的服务器来说或许是另外一种情况。多数情况下，SQL Server 可以自我调优。也就是说，SQL Server 可以检查自己运行的任务，然后自动进行内部调整，以使指定任务获得尽可能高的性能。当你对 SQL Server 进行性能测试时，需要牢记 SQL Server 需要花一点时间来将自己调整到最优化。换言之，启动 SQL Server 服务后你立即获得的性能，与在有负载情况下运行几个小时后的 SQL Server 的性能是不相同的。因此在进行测试之前，要让 SQL Server 有一定时间来适应你的负载。通过企业管理器，或者 sp_configure 存储过程，你可以修改 36 个 SQL Server 配置选项。如果你没有调优 SQL Server 的丰富经验，我不建议你修改任何 SQL Server 的设置。如果你是一个新手，你所做的修改往往会适得其反，会降低 SQL Server 的性能。因为一旦修改了 SQL Server 的设置后，会使其丧失其自我调优的能力。如果经过深思熟虑后，你仍然认为修改一个或多个 SQL Server 配置可以提高其在特定环境下的性能，那么你应该稳妥谨慎的来对其进行修改。在你修改设置前，首先应通过诸如性能监视器之类的工具来了解当前 SQL Server 的性能，以其作为基准。每次只进行一处修改。不要一次进行多个修改，因为这样你无法明确每一个设置带来了性能上的什么变化。在进行了一处修改后，再次在相同负载下测量 SQL Server 的性能是否真正有所提高。如果没有，那么恢复到默认设置。如果的确有提高，再继续检查性能在其它负载下是否也会提高。通过后期测试，你或许会发现你的修改在某些负载下可以提高性能，但在其它负载下却会降低性能。这也是为什么我不推荐你修改多数设置的原因之一。一般来说，如果你的 SQL Server 应用程序遭遇到了性能相关问题，通过修改 SQL Server 设置方法解决这些问题的可能性非常小。 SQL Server 设计优化如果你为应用程序使用了多层设计，SQL Server 只是一个大型应用程序的一部分。多层设计的实现方式对应用程序性能影响之大，或许会远远超乎你的想象，它比 SQL Server 所带来的影响大的多。不幸的是，在应用程序性能低下时，人们往往将其原因归咎于 SQL Server，而没有反思应用程序的设计，实际上很多情况下设计缺陷才是导致应用程序性能问题的主要原因。下面我提供一些可以帮助你进行应用设计的建议，以防止 SQL Server 继续独担性能低下的罪名。在设计多层应用时你首先需要决定的是，选择逻辑和物理设计。在这两种设计中，物理设计中最易发生导致性能问题的错误，原因是在这个设计中要完成理论在真实世界中的实现。和任何其它事情一样，你面临着多种选择，其中很多选择会带来升级或性能问题。为了确定哪一种选择才是正确的，需要你再次借助于测试手段，在设计阶段就开始早期潜在测试，你可以使用快速原型测试法，来判断哪一种实现可以最好的满足用户的需要。

另外，当你在设计物理实现时，尽量遵循以下建议，来确保应用程序的可升级性和最优化性能：尽可能将以数据为中心的任务以存储过程的形式在 SQL Server 上完成。避免在展现层和业务层处理数据。不要在业务层保存修改状态数据，尽可能的在数据库中实现。不要创建复杂或难懂的对象分级。复杂类的创建和使用通常会比较耗资源，会降低应用程序的性能和扩展性。原因是当创建和释放这些对象时，内存分配操作的开销通常比较大。在进行应用程序设计时，可以考虑使用微软事务处理服务器（MTS）来充分利用数据库连接池和对象池的优势。MTS 可以运行将数据库连接和对象都放到 pool 中，可以大大提高应用程序的整体性能和可扩展性。如果你的应用程序针对 SQL Server 的查询耗时较长，在设计应用程序时可以考虑异步进行查询。这样一个查询不用必须等待前面一条执行完后才能进行。将这个功能加入到你的多层应用软件的一个办法是使用微软消息队列服务器（MSMQ）。虽然按照以上建议并不能确保你获得一个可升级、快速执行的应用程序，却可以说是一个好的开始。优化数据库的设计与应用程序设计类似，数据库设计对 SQL Server 应用程序的可升级性和性能也非常关键。同样与应用程序设计类似，如果你在开始的时候没有合理的进行数据库设计，当应用程序被投入到生产环境中后，再对其进行修改往往非常困难，且代价较高。在设计 SQL Server 数据库时，以下几件事情对其升级和性能非常关键，需要牢记。同样的道理，你需要尽可能早的使用真实数据来测试你的设计。这意味着你需要开发具有示例数据的原型数据库，然后使用预计会在真实应用中发生的行为类型来对该设计进行测试。一开始就需要你确定的设计原则之一是，数据库将被使用来进行联机事务处理（OLTP），还是在线分析处理（OLAP）。在设计数据库时人们常犯的一个最大错误是，试图设计数据库同时满足 OLTP 和 OLAP 需要。如果你希望获得高性能和可扩展性，这两种应用程序类型是互相排斥的。 OLTP 数据库通常是高度规格化的，有助于降低必须存储的数据量。存储的数据越少， SQL Server 执行的 I/O 操作就越少，数据库访问就会越快。事务处理也尽可能在短时间内完成，以减少锁定冲突现象。最后一点，为降低大量插入、更新和删除操作的开销，要尽可能少的使用索引。另一方面，OLAP 数据库则是高度反规格化的。另外，它不使用事务处理，因为数据库是只读的记录锁定不是什么问题。当然，为了满足广泛的报表需求，需要大量使用索引。由此可见，OLTP 和 OLAP 数据库实现的目的完全不同，你不可能设计一个数据库同时满足这两种需求。在数据库设计早期阶段发现问题后，修改起来相对比较容易，因此不要等到应用程序开发完成后，再去修改数据库设计，这几乎是不可能的。 SQL Server 调优 SQL 编程现在，应用程序和数据库设计应该已经完成，而且都使用快速原型技术进行了性能和可扩展

性的测试。现在我们需要为应用程序编写与 SQL Server 协同的代码。如何进行应用程序编程，对性能和可扩展性也有很大影响，就如同数据库设计和整体应用程序设计对性能的影响一样。有的时候，选择一个更适合的简单编程技巧就可以带来较大的性能提高。实现一个任务的代码可能有很多种，不过获得最优性能的往往只有一个。如何优化你的 T-SQL 代码和任何编程语言一样，T-SQL 提供了多种方式来实现同一个任务。其中有的方法所实现的性能要高于其它方法。在这一部分中，我将向大家介绍一些编写高性能 T-SQL 代码的诀窍。选择合适的数据类型：数据类型选择好，可以大大提高 SQL Server 执行 SELECT、INSERT、 UPDATE 和 DELETE 操作的速度。不过，选择最优的数据类型并不总是一件很简单的事情。在创建 SQL Server 物理表的时候，以下建议可以有助于获得最优性能。选择能满足你需要的最小数据类型。例如，如果某一列需要存储的是数字 1 到 10，那么该列的数据类型选择 TINYINT 会比 INT 更好。CHAR 和 VARCHAR 的选择也是遵循同样的原则。另外，对于字符列的字符数不要设定太大，满足自己需要就可以，这样 SQL Server 能够在其数据和索引页面中存储更多行记录，降低读取它们时所需的 I/O 次数。另外，它将减少从服务器移动到客户端的数据量，降低网络流量和延时。如果某一列的文本数据在长度上差别很大，使用 VARCHAR 数据类型来取代 CHAR 数据类型。尽管 VARCHAR 数据类型比 CHAR 数据类型的开销略微有些大，但是使用 VARCHAR 数据类型可以大大节省空间，可以降低 I/O，提高整体 SQL Server 性能。除非你需要存储 Unicode 数据，不要使用 NVARCHAR 或 NCHAR 数据类型。它们所占用的空间是 VARCHAR 或 CHAR 的两倍，可以增加服务器 I/O 开销。如果你需要存储较大的字符串数据，而且它们不超过 8000 字符，那么最好使用 VARCHAR 数据类型，而不要使用 TEXT 数据类型。TEXT 数据类型开销较大，会降低性能。如果有一列只用来存储数字，使用数值型数据类型，诸如 INTERGER，而不要使用 VARCHAR 或 CHAR 数据类型。Numeric 数据类型一般会需要较小的空间来存储数值。这样有助于降低数据列的大小，而且当列内容被搜索或与其它列联合时，可以提高性能。谨慎使用触发器在 T-SQL 中，触发器是一个强大的工具，但是由于它们每次被执行的时候，需要对表进行 INSERT、UPDATE 或 DELETE 操作，这可能带来大量开销。以下是如何优化触发器性能的一些技巧。保持触发器中的代码最精简以降低开销。触发器中运行的代码越多，它所进行的每一个 INSERT、UPDATE 和 DELETE 就会越慢。不过某个任务可以使用更高效的技术实现，就不要使用触发器。尽量不要使用回滚触发器，因为其相关开销太大。与其让触发器发现问题后对事务处理进行回滚操作，不如在它进入触发器之前就捕获该错误。与让触发器回滚相比，在触发器启动之前提前发现错误会消耗更少的服务器资源。

只访问所需要的数据尽管这个建议听起来很简单，实现起来却很难，在很多 SQL Server 应用程序中，这是一个非常常见的性能相关的问题。通过以下思路，可以减少返回客户端的数据量。不要返回非必须的更多行或列数据给客户端。这会加大磁盘 I/O 开销和网络流量，都会影响性能。在 SELECT 语句中，不要使用 SELECT *来返回行数据，只根据你的需要来返回需要的列。在多数情况下，务必要包含 WHERE 条件子句来降低返回数值或行的数量。如果你的应用允许用户运行查询，但是你又不能防止用户返回数千行、数万行非必须的数据，可以考虑在 SELECT 语句中使用 TOP 运算符。这样，你可以限制返回行的数量。避免使用游标（Cursors） T-SQL 擅长处理结果集，而并非单条记录。这时候游标有了用武之地。它们让你可以处理单条记录。单条记录处理的唯一问题是非常慢。理想情况下，对于高性能 SQL Server 应用程序来说，应尽量避免使用游标。如果你需要实现逐行操作，尽量使用另一种方法来完成这个任务。在客户端某些选项可以完成逐行操作的任务，例如使用服务器端的 tempdb 表，或者使用一个关联子查询。不幸的是，这些方法并不通用，有时候你可能必须使用一个游标。如果你发现必须在你的应用程序中使用游标功能，那么以下这些建议或许对你有所帮助。 SQL Server 为你提供了几个不同类型的游标，每一个都有不同的性能特点。你应该选择可以实现你的目的同时开销又最小的游标类型。你可以选择的最高效游标是快速只进游标。在使用服务器端游标时，尽可能取最小的数据集。这包括只获取客户端立即需要的行和列。无论哪一种类型的服务器端游标，它越小，所使用的资源就越少，性能自然就越高。当你使用完一个游标后，不要简单的 CLOSE 它，你还必须对其进行 DEALLOCATE 操作，这样才会释放该游标所使用的 SQL Server 资源。如果你只是关闭它，锁虽然被释放，但是 SQL Server 资源却并未被释放，因此会降低服务器的性能。合理使用联合（Joins）对表进行联合（join）是造成性能问题的另一个主要做法，尤其是在联合的表超过了 3 个或者表非常大的情况下，更是如此。不幸的是，联合在关系数据库中是必不可少的。既然我们做不到不使用它，那么就看一下如何尽可能最优的使用它吧，请看以下技巧。如果你有两个或更多表需要频繁的进行联合，那么用于联合的列应该具有一个合适的索引。如果用于联合的列不简洁，那么可以考虑想数据表中增加代理键，以降低键值的大小，这样可以降低联合过程中的读 I/O，从而提高整体性能。另外，你还需要了解更多的索引相关的信息。为使性能最大化，用于联合的列应该数据类型相同。而且尽可能为数值型数据类型而非字符类型。避免根据值不唯一的列进行表联合。如果用于联合的列在数值上不唯一，即使这些列上有索引，SQL Server 优化器也将会执行表扫描然后进行联合。因此，为了获得更好的性能，要使用具有唯一索引的列来进行联合。

如果你必须对四个以上的表定期进行联合，以获得所需的数据集，那么你可以考虑对这些表进行反规范化（denormalizing），这样联合表的数量会减少。一般情况下，通过从一个表向其它表中增加 1-2 列，就可以大大降低联合的需要。将代码封装在存储过程中实际上，在你的 SQL Server 应用程序中使用的所有 T-SQL 代码都应该被封装在存储过程中，而不应作为动态 SQL 或脚本来运行。这不仅会降低网络流量，而且会加速 T-SQL 执行，因为服务器上存储过程中的代码已经被预编译。在编写存储过程时要想获得最优性能，需牢记以下几点。当一个存储过程第一次被执行时，它会被优化，一个查询计划被编译并缓存在 SQL Server 内存中。如果相同的存储过程被再次调用，它将不会在创建一个新查询计划，而是使用缓存的计划，这会节省时间、提高性能。这可能并非一直都是你所希望的。如果存储过程中的该查询每次都完全相同，那么这是一件好事。但是如果查询是动态的（例如 WHERE 子句可能会有所不同），那么这将非常糟糕，因为查询运行时不会再给优化，该查询的性能可能会受影响。如果你知道存储过程中的查询每次运行时会有所不同，那么在你创建存储过程时，你可以考虑增加 WITH RECOMPILE 选项。这将会强制存储过程每次运行时需要重新编译。记住在你的存储过程中增加“SET NOCOUNT ON”这个声明。如果你没有打开这个功能，那么每次一个 SQL 语句被执行是，SQL Server 将会发送一个响应给客户端，显示该语句所影响的行数。实际上客户端一般不需要这个信息。使用这个声明可以有助于减少服务器和客户端之间的通信。为避免在你的 SQL Server 应用程序中发生死锁现象，按照以下建议来设计你的应用程序： 1、应用程序每次按照相同的顺序来访问服务器对象；2、在事务处理过程中，不允许任何用户输入。在事务处理开始前对其进行收集；3、保持事务处理简短且在一个单一批处理中。如何选择索引来最优化数据库性能对于许多 SQL Server 数据库管理员和开发者来说，索引选择是一件神秘的事情。当然，我们知道它们可以做什么以及它们如何提高性能。问题在于如何选择理想类型的索引（聚集索引还是非聚集索引），以及需要索引的列的数量（是否需要对多个列进行索引），和哪一个列应该被索引。在这一部分中，我们将就如上问题给出简短的答案。不过，这些答案并不一定适合每一种情景。与大多数 SQL Server 性能调优和优化一样，你必须进行一些试验来发现最理想的索引。下面让我们先开始看看一些通用的索引创建规则，然后详细的看一下如何选择聚集和非聚集索引。索引有可能是坏事吗？是的。有的人认为只要把所有一切都索引，然后所有性能问题就会被解决。事实并非如此。索引可以加速数据访问，但是如果它被不合适的选择，同样也会降低数据访问。索引过多所带来的问题是，每次一条记录被插入、删除或更新操作时，SQL Server 必须对索引进行维护。维护 1、2 个索引或许不会给 SQL Server 带来太大开销，但是如果你的索

引在 5 个以上，这可能会给数据表带来较大的性能负担。理想情况下，索引应该越少越好。为了获得最优化性能，选择合适的索引数需要你掌握好一个平衡的度。大概来说，只有你确信真正需要添加索引的时候，才对表添加索引。你必须非常了解对数据所进行的查询类型，然后根据这些信息选择合适的索引，索引创建后，必须通过测试来查看它是否真正可以提高性能。对于 OLTP 应用来说，选择最佳索引通常比较困难，因为这类应用通常要进行大量的插入、更新和删除行为。尽管你需要好的索引来迅速定为那些需要进行插入、更新或删除操作的记录，但是如果索引太多，会使得每一次操作带来太大的开销。另一方面，如果你有一个仅对数据库进行只读操作的 OLAP 应用，那么你可以根据自己的需要来尽可能多的添加索引。由此可见，你的应用程序使用方式不同，会给你的索引建立策略带来很大差别。还有两种情况，索引不会给你的 SQL Server 带来性能上的提高，一是数据表非常小、记录非常少的情况下；二是被索引的列有超过 95%以上的数值不是唯一的。这两种情况下 SQL Server 查询优化器不会使用你选择的索引，因此它们对 SQL Server 是一个负担，需要删除之。选择聚集索引的技巧由于对每一个表只能创建一个聚集索引，因此有必要多花些时间来仔细的考虑如何创建它。考虑下需要对表进行查询的类型，判断一下哪一个索引是最关键的，以及具有一个聚集索引是否对该查询的性能提高有帮助。一般来说，当选择一个列进行聚集索引时，需要规则来进行衡量。表的主键并不一定总是一个聚集索引。如果你创建了主键，且没有指定其它聚集索引，那么 SQL Server 会自动将主键作为聚集索引。将主键作为聚集索引的推荐情况如下。如果你需要一个分类结果，那么聚集索引对该查询比较适合。因为数据已经在索引中预先进行了分类。例如，当你的查询中使用了 BETWEEN、<、>、GROUP BY、ORDER BY 以及诸如 MAX、MIN 和 COUNT 之类的聚合操作时，适合使用聚集索引。当你需要查询一个具有唯一值的记录，以及当你需要一条记录中的多数或全部数据时，选择聚集索引比较合适。因为这类查询会被该索引所包含。聚集索引适合那些使用 JOIN 或 GROUP BY 子句的查询。聚集索引适合那些需要返回大量行数据的查询，因为这些数据在索引中，就不再用到处查阅。如果在你的表中有多列需要进行聚集，那么以上建议可能适用性不大。但是我们知道，每一个表只能有一个聚集索引。这种情况下，我们需要对所有可能进行评估，然后选择可以带来最大整体性能提高的那一个。选择非聚集索引的技巧选择非聚集索引相对来说简单一些，因为你可以根据需要对你的表建立多个索引。以下技巧可以帮助你选择对哪一列添加非聚集索引。对于需要返回较少行数据的查询，最适合使用非聚集索引。不过，如同上面所提到的，如果数据表中某一列的数值不满足 95%上是唯一的，那么 SQL Server 查询优化器不会使用

该列的非聚集索引，因此不要对该列添加非聚集索引。例如，如果某一列的数据就是“yes” 和“no”，它就不适合使用非聚集索引。保持索引的“width”尽可能的窄，尤其是当你创建组合（多列）索引时更是如此。这可以降低索引的大小，降低对该索引进行读操作的数量，从而提升性能。如果可能的话，尽量针对包含整数值的列创建索引，尽量不要对字符列创建索引。整数值所需要的开销比字符值要少。如果你确信你的应用程序会反复对同一个表进行相同的查询，那么可以考虑对该表创建一个 covering 索引。一个 covering 索引包含在查询中引用的全部列。这样，索引包含你要查找的数据，SQL Server 就不用必须去表中查看实际的数据，可以减少逻辑或物理上的 I/O。另一方面，如果索引太大，也会增加 I/O，降低性能。总体来说，如果表只需要一个索引，可以对其添加聚集索引。如果表需要多个索引，那么你只能使用非聚集索引。通过遵循以上建议，你应该更容易为你的数据表选择最佳索引。

分享到：

赞收藏

资料库

SQLSERVER性能调优.docx

相关推荐

数据库

热门标签

最新资料