SQL Server 2005的批处理缓存与重编译策略

需积分: 10 87 浏览量更新于2024-08-02 收藏 216KB PDF 举报

"这篇文档详细阐述了SQL Server 2005中的批量编译、重新编译和计划缓存问题，旨在提供最大化利用缓存计划的最佳实践，以及减少或避免不必要的重新编译的方法。作者是Arun Marathe，由Shu Scott修订。" 在SQL Server 2005中，批量编译是指将一组Transact-SQL语句作为一个单元进行处理和编译的过程。这一过程将这些语句转化为执行计划，并存储在计划缓存中以供后续使用，从而提高性能。计划缓存是数据库引擎存储已编译查询计划的地方，当相同查询再次执行时，可以从缓存中快速检索和重用，而不是每次都重新编译。批量重新编译可能由多种因素触发，包括但不限于以下场景： 1. **对象更改**：当表、视图、存储过程等数据库对象结构发生变化时，与之相关的查询计划需要重新编译。 2. **统计信息更新**：当表的统计信息发生变化时，可能导致计划不再适用，因此需要重新编译。 3. **参数嗅探**：当参数化查询中参数值的变化影响到执行计划的选择时，可能需要重新编译。 4. **并发问题**：并发控制可能导致某些查询在等待其他事务完成时被阻塞，从而触发重新编译。 5. **资源限制**：如内存压力可能导致计划被逐出缓存，需要重新编译。为了减少不必要的重新编译，可以采取以下最佳实践： 1. **优化统计信息管理**：定期更新统计信息以确保查询优化器做出准确的决策。 2. **使用参数化查询**：减少由于参数值变化导致的重新编译。 3. **使用SP_RECOMPILE提示谨慎**：仅在必要时使用，因为它会导致计划立即重新编译。 4. **避免在动态SQL中使用硬编码值**：这可以防止参数嗅探问题。 5. **监控和调整缓存大小**：根据工作负载调整SQL Server的内存配置，确保有足够的空间存储和重用计划。为了识别和诊断重新编译问题，可以使用以下工具和命令： - **SQL Server Profiler**：捕获重新编译事件，以跟踪何时何地发生重新编译。 - **动态管理视图（DMVs）**：如sys.dm_exec_query_stats和sys.dm_exec_plan_cache，提供有关执行计划和编译活动的信息。 - **SQL Server Management Studio（SSMS）**：查询分析器和对象资源管理器可以帮助检查和分析查询计划。总结来说，理解批量编译、重新编译和计划缓存机制对于优化SQL Server 2005的性能至关重要。通过实施最佳实践，可以显著降低不必要的重新编译，提高系统整体效率。附录A中讨论了SQL Server 2005何时不会自动参数化查询，这对于理解参数化查询的工作方式及其对性能的影响也很重要。

context for B is generated. Suppose that during this first execution, the "true" branch of the "if" is

taken. Further, suppose that B was submitted again by another connection during the first execution.

Because the only execution context existing at that moment was in use, a second execution context is

generated and given to the second connection. Suppose that the second execution context takes the

"false" branch of the "if". After both executions complete, B is submitted by a third connection.

Supposing that the third execution of B chooses the "true" branch, the execution will complete slightly

faster if SQL Server chose the first execution context of B for that connection rather than the second

execution context.

Execution contexts of a batch S can be reused even if the calling sequence of S differs. For example,

one calling sequence could be "stored proc 1 --> stored proc 2 --> S", whereas a second calling

sequence could be "stored proc 3 --> S". The execution context for the first execution of S can be

reused for the second execution of S.

If a batch execution generates an error of severity 11 or higher, the execution context is destroyed. If a

batch execution generates a warning (an error with severity 10), the execution context is not

destroyed. Thus, even in the absence of memory pressure — which can cause plan cache to shrink—

the number of execution contexts (for a given query plan) cached in plan cache can go up and down.

Execution contexts for parallel plans are not cached. A necessary condition for SQL Server to compile a

parallel query plan is that the minimum of the number of processors that have survived the processor

affinity mask and the value of the "max degree of parallelism" server-wide option (possibly set using

the "sp_configure" stored procedure) is greater than 1. Even if a parallel query plan is compiled, SQL

Server's "Query Execution" component may generate a serial execution context out of it. Any execution

contexts derived out of a parallel plan — serial or parallel — are not cached. A parallel query plan,

however, is cached.

Query plan caching and various SET options (showplan-related and others)

Various SET options — most of them showplan-related— affect compilation, caching, and reuse of query

plans and execution contexts in complex ways. The following table summarizes the details.

The table should be read as follows. A batch is submitted to SQL Server under a specific mode specified

in the first column. A cached query plan may or may not exist in the plan cache for the submitted

batch. Columns 2 and 3 cover the cases when a cached query plan exists; columns 4 and 5 cover the

cases when a cached query plan does not exist. Within each category, the cases for query plans and

execution contexts are separated. The text explains what happens to a structure (query plan or

execution context): whether it is cached, reused, and used.

Mode name

When a cached

query plan

exists

When a cached

query plan

exists

When a cached

query plan does

not exist

When a cached

query plan does

not exist

Query plan Exec context Query plan Exec context

showplan_text,

showplan_all,

showplan_xml

Reused (no

compilation)

Reused Cached

(compilation)

One exec

context is

generated, not

used, and

cached

statistics profile,

statistics xml,

statistics io,

statistics time

Reused (no

compilation)

Not reused. A

fresh exec

context is

generated,

used, and not

cached

Cached

(compilation)

One exec

context

generated,

used, and not

cached

noexec Reused (no

compilation)

Reused Cached

(compilation)

Execution

context is not

generated

(because of

the "noexec"

mode).

parseonly (e.g.,

pressing "parse"

n/a n/a n/a n/a

e 6 of 30Batch Compilation, Recompilation, and Plan Cachin

Issues in SQL Server 2005

2009-10-15http://technet.microsoft.com/zh-cn/librar

/cc966425(en-us,printer).aspx

剩余29页未读，继续阅读

Willey

粉丝: 0
资源: 2

SQL Server 2005的批处理缓存与重编译策略

批处理实现sqlserver 2005远程异地数据库备份

SQL Server 2005 Express自动备份及删除旧备份文件

SQL_Server2005性能测试实践

SQLServer 循环批处理

sql server 性能优化

Microsoft SQL Server 2008 - T-SQL Querying

SQLServer2005的T-SQL语言详解

Wonderware InSQL Server 9.0 入门指南

Wonderware InSQL Server 9.0入门指南

Wonderware InSQL Server 9.0：入门与下载指南

最新资源