优化数据库性能

1、查询速度慢如何解决 -主要针对SQL 2005 为例引起查询或更新的执行时间超过预期时间的原因有多种。查询运行慢，可能是由与运行 SQL Server 的网络或计算机相关的性能问题引起的，也可能是由物理数据库设计问题引起的。查询和更新运行慢的最常见原因有： 网络通讯速度慢。 服务器的内存不足，或者没有足够的内存供 SQL Server 使用。 索引列上缺少有用的统计信息。 索引列上的统计信息过期。 缺少有用的索引。 缺少有用的索引视图。 缺少有用的数据条带化。 缺少有用的分区。1、 用于对运行慢的查询进行故障排除的清单当查询或更新花费的时间比预期时间长时，请考虑以下问题，找到可解答前一节中列出

2、的查询运行慢的原因： . 是与组件而不是与查询相关的性能问题吗？例如，是网络性能低的问题吗？有其他可能引起或造成性能降低的组件吗？Windows 系统监视器可用于监视与 SQL Server 和非 SQL Server 相关的组件的性能。有关详细信息，请参阅监视资源使用情况（系统监视器）。. 如果性能问题与查询相关，那么涉及到的是哪个或哪组查询？首先使用 SQL Server Profiler来帮助找出运行慢的查询。有关详细信息，请参阅使用 SQL Server Profiler。在找出运行慢的查询后，可以使用 SET 语句启用 SHOWPLAN、STATISTICS IO、STATISTIC

3、S TIME 和 STATISTICS PROFILE 选项，进一步分析查询的性能，相关描述如下： SET SHOWPLAN_XML ON 描述 SQL Server 查询优化器选择用来检索完善的 XML 文档数据的方法。有关详细信息，请参阅 SET SHOWPLAN_XML (Transact-SQL)。在 Microsoft SQL Server 2005 中，建议使用这种方法。此 SET 选项生成的信息比 SHOWPLAN_ALL 和 SHOWPLAN_TEXT SET 选项生成的信息详细。 SET SHOWPLAN_ALL ON 描述 SQL Server 查询优化器选择的数据检索方法

4、。有关详细信息，请参阅 SET SHOWPLAN_ALL (Transact-SQL)。此 SET 选项生成的信息比 SHOWPLAN_TEXT SET 选项生成的信息详细。 SET SHOWPLAN_TEXT ON 返回每条 Transact-SQL 语句的执行信息，但不执行它们。有关详细信息，请参阅SET SHOWPLAN_TEXT (Transact-SQL)。 SET STATISTICS XML ON 显示每个查询执行后的结果集，并以完善的 XML 文档的形式显示查询执行的概要信息。有关详细信息，请参阅 SET STATISTICS XML (Transact-SQL)。在 SQL

5、Server 2005 中，建议使用这种方法。此 SET 选项生成的信息比 STATISTICS PROFILER SET 选项生成的信息详细。 SET STATISTICS PROFILE ON 显示每个查询执行后的结果集，并显示查询执行的概要信息。有关详细信息，请参阅 SET STATISTICS PROFILE (Transact-SQL)。 SET STATISTICS IO ON 报告与语句中引用的每个表的扫描数、逻辑读取数（在高速缓存中访问的页数）和物理读取数（访问磁盘的次数）的相关信息。有关详细信息，请参阅 SET STATISTICS IO (Transact-SQL)。 SE

6、T STATISTICS TIME ON 显示分析、编译和执行查询所需的时间（毫秒）。有关详细信息，请参阅 SET STATISTICS TIME (Transact-SQL)。在 SQL Server Management Studio 中，还可以打开估计的或实际的图形执行计划选项，以查看 SQL Server 如何检索数据的图示。估计的图形执行计划选项是基于 SHOWPLAN_XML SET 选项的，实际的图形执行计划选项则是基于 STATISTICS XML SET 选项的。有关详细信息，请参阅显示图形执行计划 (SQL Server Management Studio)。由这些工具收集

7、的信息使您能够确定 SQL Server 查询优化器如何执行查询以及使用的是哪些索引。利用这些信息，可以确定通过重写查询、更改表上的索引或修改数据库设计等方法能否提高性能。有关详细信息，请参阅分析查询。 . 是否已经用有用的统计信息优化查询？ SQL Server 自动在索引列上创建对列内值的分布情况的统计信息。也可以手动（使用 SQL Server Management Studio 或 CREATE STATISTICS 语句）或自动（如果将 AUTO_CREATE_STATISTICS 数据库选项设置为 TRUE）在非索引列上创建这些统计信息。查询处理器可以利用这些统计信息来确定最佳的查

8、询评估策略。在联接操作所涉及的非索引列上维护附加的统计信息可以提高查询性能。有关详细信息，请参阅索引统计信息。使用 SQL Server Profiler或 SQL Server Management Studio 内的图形执行计划来监视查询，以确定查询是否有足够的统计信息。有关详细信息，请参阅Errors and Warnings 事件类别（数据库引擎）。. 查询统计是最新的吗？统计信息是自动更新的吗？ SQL Server 自动在索引列上创建并更新查询统计信息（只要没有禁用对查询统计信息的自动更新）。另外，也可以手动（使用 SQL Server Management Studio 或 UP

9、DATE STATISTICS 语句）或自动（如果将 AUTO_UPDATE_STATISTICS 数据库选项设置为 TRUE）在非索引列上更新统计信息。最新的统计信息不取决于日期或时间数据。如果尚未执行 UPDATE 操作，则查询统计信息仍是最新的。如果没有将统计信息设置为自动更新，请进行设置。有关详细信息，请参阅索引统计信息。. 有合适的索引吗？添加一个或多个索引会不会提高查询性能？有关详细信息，请参阅常规索引设计指南和数据库引擎优化顾问参考。数据库引擎优化顾问也可以建议创建必要的统计信息。. 有数据热点或索引热点吗？请考虑使用磁盘条带化。使用 0 级 RAID（独立磁盘冗余阵列）可实现磁

10、盘条带化，在这种 RAID 上，数据分布在多个磁盘驱动器上。有关详细信息，请参阅使用文件和文件组和 RAID。. 是否为查询优化器提供了优化复杂查询的最有利条件？有关详细信息，请参阅查询优化建议。. 如果数据量很大，需要将其分区吗？便于数据管理是分区的主要优点，而如果将数据的表和索引进行相似的分区，则分区还可以提高查询性能。有关详细信息，请参阅了解分区和优化物理数据库设计。2、 执行计划优化的举例说明2.1 执行计划的说明set statistics profile on (显示语句的配置文件信息。）set statistics io on (显示关于Transact-SQL 语句生成的磁盘活

11、动量的信息)set statistics time on (显示分析、编译和执行各语句所需的毫秒数)select * from cva_benstatus_oprset statistics time off set statistics io offset statistics profile offprofile的说明：列名描述Rows各运算符生成的实际行数Executes运算符执行的次数StmtText对于不是 PLAN_ROW 类型的行，该列包含 Transact-SQL 语句的文本。对于 PLAN_ROW 类型的行，该列包含对操作的描述。该列包含物理运算符，也可以选择包含逻辑运算符。

12、该列还可以跟有由物理运算符决定的描述。有关更多信息，请参见逻辑运算符和物理运算符。StmtId当前批处理中的语句数。NodeId当前查询内的节点 ID。Parent上一级步骤的节点 ID。 PhysicalOp节点的物理实现算法。仅限于 PLAN_ROWS 类型的行。LogicalOp该节点表示的关系代数运算符。仅限于 PLAN_ROWS 类型的行。Argument提供有关所执行操作的辅助信息。该列的内容取决于物理运算符。DefinedValues包含该运算符所引入值的用逗号分隔的列表。这些值可以是出现在当前查询（例如，在 SELECT 列表或 WHERE 子句中）内的计算表达式，或者是由查询

13、处理器为处理该查询而引入的内部值。以后在该查询内的任何其它地方都可以引用这些定义的值。仅限于 PLAN_ROWS 类型的行。EstimateRows由该运算符输出的预计行数。仅限于 PLAN_ROWS 类型的行。EstimateIO该运算符的预计 I/O 成本。仅限于 PLAN_ROWS 类型的行。EstimateCPU该运算符的预计 CPU 成本。仅限于 PLAN_ROWS 类型的行。AvgRowSize正通过该运算符传递的行的预计平均行大小（以字节为单位）。TotalSubtreeCost该操作和所有下一级操作的预计（累积）成本。OutputList包含当前操作所计划的列的用逗号分隔的列表

14、。Warnings包含与当前操作相关的警告信息的用逗号分隔的列表。警告信息可以在列的列表中包含字符串NO STATS:()。该警告信息表示查询优化器曾尝试根据该列的统计做决策，但没有可用的统计。因此，查询优化器不得不进行推测，这可能已导致选择低效的查询计划。有关创建或更新列统计（这些统计有助于查询优化器选择更高效的查询计划）的更多信息，请参见 UPDATE STATISTICS。该列可以选择包含字符串MISSING JOIN PREDICATE，这表示正在进行的联接（与表有关）未使用联接谓词。意外地除去联接谓词可能导致查询的时间比预期长得多，并返回巨大的结果集。如果出现该警告，请验证是否有意除

15、去了联接谓词。Type节点类型。对于每个查询的父节点，这是 Transact-SQL 语句类型（如 SELECT、INSERT、EXECUTE 等）。对于表示执行计划的子节点，这是 PLAN_ROW 类型。Parallel0 = 运算符没有以并行方式运行。1 = 运算符正在以并行方式运行。EstimateExecutions该运算符预计在当前查询运行期间将执行的次数。2.2执行计划分析：像Concatenation，Table Spool、Index Spool和Parallelism都是使查询速度加快的SQL 优化措施。 Table Spool和Index Spool操作，这个操作的是将输入

16、的表或者索引直接放到缓存（通常是tempdb）中 以减小对输入的 重新 扫描。 Parallelism是当执行并行执行计划时，系统所有空闲的CPU一起参与执行这个语句，从而获得更好的性能。SP_Configure 中使用 cost threshold for parallelism 选项指定 Microsoft SQL Server 创建和运行并行查询计划的阈值。仅当运行同一查询的串行计划的估计开销高于在 cost threshold for parallelism 中设置的值时，SQL Server 才创建和运行该查询的并行计划。开销指的是在特定硬件配置中运行串行计划估计需要花费的时间（秒）

17、。只能在对称多处理器系统上设置 cost threshold for parallelism。这个阙值一般是5秒，并行执行计划对一个具体的查询而言，性能肯定是提高的，但对于一个系统而言，过多的使用并行执行计划，会引起整体性能的下降，所以要掌握一个度。也就是说阕值调为1秒了，但整体的查询性能可能小降了，所以这个值要根据硬件和实际使用情况进行相应的调整。 执行计划是系统自动优化的结果，为了提高某个查询的性能，也可以使用计划强制来指定查询计划。（具体怎么做，需要查询SQL在线帮助）2.3查询语法方面的优化措施： Union All（SQL Server 将OR条件看成Union All）会使SQL

18、Server生成的查询计划中偏好于Concatenation，Table Spool和Index Spool操作。在查询中增加一个条件永远为假的OR子句，促使SQL选择Table Spool和Index Spoo l操作提高查询性能.3、 统计信息和索引优化的举例说明3.1相关基础知识统计信息相关知识：SQL Server 2005 允许创建有关列中值的分布情况的统计信息。为了评估查询的开销来确定最佳的执行计划，查询优化器需要使用这些统计信息评估可用的索引。您可以使用DBCC SHOW_STATISTICS 查看具体对象的 统计信息。 随着时间的推移， 统计信息 可能会过时（Out of Da

19、te），也就是说它已经不能比较真实地反映数据的分布情况，这时当SQL Server 优化一个查询的时 候，查询优化器就会首先更新统计信息，带来额外的时间开销。所以在不影响服务器负载的情况下，建议您开启数据库自动更新统计信息（Auto Update Statistics）的选项。具体有关统计信息（statistics）的相关内容请参考如下的文档：/en-us/library/ms.aspx Statistics Used by the Query Optimizer in Microsoft SQL Server 2005http:/www.mic

20、/technet/prodtechnol/sql/2005/qrystats.mspx 索引碎片相关知识：索引建立时，索引页（Index Page）的存储在逻辑上都是连续的。 例如在进行Insert操作时，可能需要在两个索引之间插 入这个新的索引。如果在索引插入位置的索引页还有足够的空间，则会直接在这个索引页中插入新的索引值；如果在这个索引页上空间已满或者不够新的索引值所需空间，则SQL Server会进行页分割（Page Split），将插入位置的索引页一部分数 据移走，以释放出空间来插入新的索引，被移走的数据在其它位置重新分配新的页存放。这样，随着Insert操作的增加

21、，索引页在逻辑上的连续程度就越来越低。因为在这种情况下读取一段连续数据时磁盘头（Disk Head）必须向前 后连个方向移 动以读取索引页而不是朝着一个方向扫描，增加了磁盘IO。所以定时整理索引碎片有助于提高某些Select查询的性能。关于索引碎片和整理请参考如下文档：/zh-cn/library/ms.aspx3.2具体优化措施： 更新统计信息, 运行sp_updatestats语句 开启数据库的Auto Create Statistics和Auto Update Statistics选项，以确保SQLServer生成查询计划时使用最新 的统计

22、信息。 定期执行Rebuild Index（或者可以建立一个相应的维护计划），定期重建索引，以减小索引碎片对查询性能的影响。在SQL 2005中的管理的维护计划中，对于工具箱中的更新统计信息、重新生成索引和重新组织索引这三个任务放在维护计划中。4、 使用内存配置选项优化服务器性能4.1配置虚拟内存Microsoft Windows NT 或 Windows 2000 虚拟内存大小应基于计算机上并发运行的服务进行配置。运行 Microsoft SQL Server 2000 时，可考虑将虚拟内存大小设置为计算机中安装的物理内存的 1.5 倍。如果另外安装了全文检索功能，并打算运行 Microso

23、ft 搜索服务以便执行全文索引和查询，可考虑： 将虚拟内存大小配置为至少是计算机中安装的物理内存的 3 倍。如果将虚拟内存设置配置得太低，则会出现下面的 Windows NT 错误：Your system is running low on virtual memory. Please close some applications. You can then start the System option in the Control Panel and choose the Virtual Memory button to create an additional paging file

24、or increase the size of your current paging file.4.2 SQL Server 内存的配置Microsoft SQL Server 2000 的内存管理组件消除了对 SQL Server 可用的内存进行手工管理的需要。SQL Server 在启动时根据操作系统和其它应用程序当前正在使用的内存量，动态确定应分配的内存量。当计算机和SQL Server 上的负荷更改时，分配的内存也随之更改。有关更多信息，请参见内存构架。下列服务器配置选项可用于配置内存使用并影响服务器性能： min server memory max server memory ma

25、x worker threads index create memory min memory per query min server memory 服务器配置选项可用于确保 SQL Server 至少以最小的分配内存量启动，并且内存低于该值时不会释放内存。可以基于 SQL Server 的大小及活动将该配置选项设置为特定的值。始终将 min server memory 服务器配置选项设置为某个合理的值，以确保操作系统不向 SQL Server 请求太多内存而影响 SQL Server 性能。max server memory 服务器配置选项可用于：在 SQL Server 启动及运行时，指

26、定 SQL Server 可以分配的最大内存量。如果知道有多个应用程序与 SQL Server 同时运行，而且想保障这些应用程序有足够的内存运行，可以将该配置选项设置为特定的值。如果这些其它应用程序（如 Web 服务器或电子邮件服务器）只根据需要请求内存，则 SQL Server 将根据需要给它们释放内存，因此不要设置 max server memory 服务器配置选项。然而，应用程序通常在启动时不假选择地使用可用内存，而如果需要更多内存也不请求。如果有这种行为方式的应用程序与 SQL Server 同时运行在相同的计算机上，则将 max server memory 服务器配置选项设置为特定的值，以保障应用程序所需的内存不由 SQL Server 分配出。将 SQL Server max server memory 服务器配置选项配置为物理内存的 1.5 倍。不要将 min server memory 和 max server memory 服务器配置选项设置为相同的值，这样做会使分配给 SQL Server 的内存量固定。动态内存分配可以随时间提供最佳的总体性能。