优化数据库性能

查询速度慢如何解决------主要针对SQL2005为例引起查询或更新的执行时间超过预期时间的原因有多种。查询运行慢，可能是由与运行SQLServer的网络或计算机相关的性能问题引起的，也可能是由物理数据库设计问题引起的。查询和更新运行慢的最常见原因有：网络通讯速度慢。服务器的内存不足，或者没有足够的内存供SQLServer使用。索引列上缺少有用的统计信息。索引列上的统计信息过期。缺少有用的索引。缺少有用的索引视图。缺少有用的数据条带化。缺少有用的分区。1、用于对运行慢的查询进行故障排除的清单当查询或更新花费的时间比预期时间长时，请考虑以下问题，找到可解答前一节中列出的查询运行慢的原因：①.是与组件而不是与查询相关的性能问题吗？例如，是网络性能低的问题吗？有其他可能引起或造成性能降低的组件吗？Windows系统监视器可用于监视与SQLServer和非SQLServer相关的组件的性能。有关详细信息，请参阅监视资源使用情况（系统监视器）。②.如果性能问题与查询相关，那么涉及到的是哪个或哪组查询？首先使用SQLServerProfiler来帮助找出运行慢的查询。有关详细信息，请参阅使用SQLServerProfiler。在找出运行慢的查询后，可以使用SET语句启用SHOWPLAN、STATISTICSIO、STATISTICSTIME和STATISTICSPROFILE选项，进一步分析查询的性能，相关描述如下：SETSHOWPLAN_XMLON描述SQLServer查询优化器选择用来检索完善的XML文档数据的方法。有关详细信息，请参阅SETSHOWPLAN_XML(Transact-SQL)。在MicrosoftSQLServer2005中，建议使用这种方法。此SET选项生成的信息比SHOWPLAN_ALL和SHOWPLAN_TEXTSET选项生成的信息详细。SETSHOWPLAN_ALLON描述SQLServer查询优化器选择的数据检索方法。有关详细信息，请参阅SETSHOWPLAN_ALL(Transact-SQL)。此SET选项生成的信息比SHOWPLAN_TEXTSET选项生成的信息详细。SETSHOWPLAN_TEXTON返回每条Transact-SQL语句的执行信息，但不执行它们。有关详细信息，请参阅SETSHOWPLAN_TEXT(Transact-SQL)。SETSTATISTICSXMLON显示每个查询执行后的结果集，并以完善的XML文档的形式显示查询执行的概要信息。有关详细信息，请参阅SETSTATISTICSXML(Transact-SQL)。在SQLServer2005中，建议使用这种方法。此SET选项生成的信息比STATISTICSPROFILERSET选项生成的信息详细。SETSTATISTICSPROFILEON显示每个查询执行后的结果集，并显示查询执行的概要信息。有关详细信息，请参阅SETSTATISTICSPROFILE(Transact-SQL)。SETSTATISTICSIOON报告与语句中引用的每个表的扫描数、逻辑读取数（在高速缓存中访问的页数）和物理读取数（访问磁盘的次数）的相关信息。有关详细信息，请参阅SETSTATISTICSIO(Transact-SQL)。SETSTATISTICSTIMEON显示分析、编译和执行查询所需的时间（毫秒）。有关详细信息，请参阅SETSTATISTICSTIME(Transact-SQL)。在SQLServerManagementStudio中，还可以打开估计的或实际的图形执行计划选项，以查看SQLServer如何检索数据的图示。估计的图形执行计划选项是基于SHOWPLAN_XMLSET选项的，实际的图形执行计划选项则是基于STATISTICSXMLSET选项的。有关详细信息，请参阅显示图形执行计划(SQLServerManagementStudio)。由这些工具收集的信息使您能够确定SQLServer查询优化器如何执行查询以及使用的是哪些索引。利用这些信息，可以确定通过重写查询、更改表上的索引或修改数据库设计等方法能否提高性能。有关详细信息，请参阅分析查询。③.是否已经用有用的统计信息优化查询？SQLServer自动在索引列上创建对列内值的分布情况的统计信息。也可以手动（使用SQLServerManagementStudio或CREATESTATISTICS语句）或自动（如果将AUTO_CREATE_STATISTICS数据库选项设置为TRUE）在非索引列上创建这些统计信息。查询处理器可以利用这些统计信息来确定最佳的查询评估策略。在联接操作所涉及的非索引列上维护附加的统计信息可以提高查询性能。有关详细信息，请参阅索引统计信息。使用SQLServerProfiler或SQLServerManagementStudio内的图形执行计划来监视查询，以确定查询是否有足够的统计信息。有关详细信息，请参阅ErrorsandWarnings事件类别（数据库引擎）。④.查询统计是最新的吗？统计信息是自动更新的吗？SQLServer自动在索引列上创建并更新查询统计信息（只要没有禁用对查询统计信息的自动更新）。另外，也可以手动（使用SQLServerManagementStudio或UPDATESTATISTICS语句）或自动（如果将AUTO_UPDATE_STATISTICS数据库选项设置为TRUE）在非索引列上更新统计信息。最新的统计信息不取决于日期或时间数据。如果尚未执行UPDATE操作，则查询统计信息仍是最新的。如果没有将统计信息设置为自动更新，请进行设置。有关详细信息，请参阅索引统计信息。⑤.有合适的索引吗？添加一个或多个索引会不会提高查询性能？有关详细信息，请参阅常规索引设计指南和数据库引擎优化顾问参考。数据库引擎优化顾问也可以建议创建必要的统计信息。⑥.有数据热点或索引热点吗？请考虑使用磁盘条带化。使用0级RAID（独立磁盘冗余阵列）可实现磁盘条带化，在这种RAID上，数据分布在多个磁盘驱动器上。有关详细信息，请参阅使用文件和文件组和RAID。⑦.是否为查询优化器提供了优化复杂查询的最有利条件？有关详细信息，请参阅查询优化建议。⑧.如果数据量很大，需要将其分区吗？便于数据管理是分区的主要优点，而如果将数据的表和索引进行相似的分区，则分区还可以提高查询性能。有关详细信息，请参阅了解分区和优化物理数据库设计。2、执行计划优化的举例说明2.1执行计划的说明setstatisticsprofileon(显示语句的配置文件信息。）setstatisticsioon(显示关于Transact-SQL语句生成的磁盘活动量的信息)setstatisticstimeon(显示分析、编译和执行各语句所需的毫秒数)select*fromcva_benstatus_oprsetstatisticstimeoffsetstatisticsiooffsetstatisticsprofileoffprofile的说明：列名描述Rows各运算符生成的实际行数Executes运算符执行的次数StmtText对于不是PLAN_ROW类型的行，该列包含Transact-SQL语句的文本。对于PLAN_ROW类型的行，该列包含对操作的描述。该列包含物理运算符，也可以选择包含逻辑运算符。该列还可以跟有由物理运算符决定的描述。有关更多信息，请参见逻辑运算符和物理运算符。StmtId当前批处理中的语句数。NodeId当前查询内的节点ID。Parent上一级步骤的节点ID。PhysicalOp节点的物理实现算法。仅限于PLAN_ROWS类型的行。LogicalOp该节点表示的关系代数运算符。仅限于PLAN_ROWS类型的行。Argument提供有关所执行操作的辅助信息。该列的内容取决于物理运算符。DefinedValues包含该运算符所引入值的用逗号分隔的列表。这些值可以是出现在当前查询（例如，在SELECT列表或WHERE子句中）内的计算表达式，或者是由查询处理器为处理该查询而引入的内部值。以后在该查询内的任何其它地方都可以引用这些定义的值。仅限于PLAN_ROWS类型的行。EstimateRows由该运算符输出的预计行数。仅限于PLAN_ROWS类型的行。EstimateIO该运算符的预计I/O成本。仅限于PLAN_ROWS类型的行。EstimateCPU该运算符的预计CPU成本。仅限于PLAN_ROWS类型的行。AvgRowSize正通过该运算符传递的行的预计平均行大小（以字节为单位）。TotalSubtreeCost该操作和所有下一级操作的预计（累积）成本。OutputList包含当前操作所计划的列的用逗号分隔的列表。Warnings包含与当前操作相关的警告信息的用逗号分隔的列表。警告信息可以在列的列表中包含字符串NOSTATS:()。该警告信息表示查询优化器曾尝试根据该列的统计做决策，但没有可用的统计。因此，查询优化器不得不进行推测，这可能已导致选择低效的查询计划。有关创建或更新列统计（这些统计有助于查询优化器选择更高效的查询计划）的更多信息，请参见UPDATESTATISTICS。该列可以选择包含字符串MISSINGJOINPREDICATE，这表示正在进行的联接（与表有关）未使用联接谓词。意外地除去联接谓词可能导致查询的时间比预期长得多，并返回巨大的结果集。如果出现该警告，请验证是否有意除去了联接谓词。Type节点类型。对于每个查询的父节点，这是Transact-SQL语句类型（如SELECT、INSERT、EXECUTE等）。对于表示执行计划的子节点，这是PLAN_ROW类型。Parallel0=运算符没有以并行方式运行。1=运算符正在以并行方式运行。EstimateExecutions该运算符预计在当前查询运行期间将执行的次数。2.2执行计划分析：像Concatenation，TableSpool、IndexSpool和Parallelism都是使查询速度加快的SQL优化措施。TableSpool和IndexSpool操作，这个操作的是将输入的表或者索引直接放到缓存（通常是tempdb）中以减小对输入的重新扫描。Parallelism是当执行并行执行计划时，系统所有空闲的CPU一起参与执行这个语句，从而获得更好的性能。SP_Configure中使用costthresholdforparallelism选项指定MicrosoftSQLServer创建和运行并行查询计划的阈值。仅当运行同一查询的串行计划的估计开销高于在costthresholdforparallelism中设置的值时，SQLServer才创建和运行该查询的并行计划。开销指的是在特定硬件配置中运行串行计划估计需要花费的时间（秒）。只能在对称多处理器系统上设置costthresholdforparallelism。这个阙值一般是5秒，并行执行计划对一个具体的查询而言，性能肯定是提高的，但对于一个系统而言，过多的使用并行执行计划，会引起整体性能的下降，所以要掌握一个度。也就是说阕值调为1秒了，但整体的查询性能可能小降了，所以这个值要根据硬件和实际使用情况进行相应的调整。执行计划是系统自动优化的结果，为了提高某个查询的性能，也可以使用计划强制来指定查询计划。（具体怎么做，需要查询SQL在线帮助）2.3查询语法方面的优化措施：UnionAll（SQLServer将OR条件看成UnionAll）会使SQLServer生成的查询计划中偏好于Concatenation，TableSpool和IndexSpool操作。在查询中增加一个条件永远为假的OR子句，促使SQL选择TableSpool和IndexSpool操作提高查询性能.3、统计信息和索引优化的举例说明3.1相关基础知识统计信息相关知识：SQLServer2005允许创建有关列中值的分布情况的统计信息。为了评估查询的开销来确定最