sql server 缓存_了解SQL Server查询计划缓存

sql server 缓存

Whenever a query is run for the first time in SQL Server, it is compiled and a query plan is generated for the query. Every query requires a query plan before it is actually executed. This query plan is stored in SQL Server query plan cache. This way when that query is run again, SQL Server doesn’t need to create another query plan; rather it uses the cached query plan which improved database performance.

每当在SQL Server中首次运行查询时，都会对其进行编译并为该查询生成查询计划。 每个查询在实际执行之前都需要一个查询计划。 此查询计划存储在SQL Server查询计划缓存中。 这样，当再次运行该查询时，SQL Server无需创建另一个查询计划； 而是使用缓存的查询计划来提高数据库性能。

The duration that a query plan stays in the plan cache depends upon how often a query is executed. Query plans that are used more often, stay in the query plan cache for longer durations, and vice-versa.

查询计划在计划缓存中保留的持续时间取决于执行查询的频率。 使用频率更高的查询计划会在查询计划缓存中保留更长的时间，反之亦然。

In this article we will look at:

在本文中，我们将研究：

• How to view the query plan cache
  如何查看查询计划缓存
• How to clear the plan cache
  如何清除计划缓存
• How to use parameterized queries to reuse query plans.
  如何使用参数化查询重用查询计划。

如何查看SQL Server查询计划缓存 (How to view the SQL Server query plan cache)

SQL Server provides the following dynamic management views and functions that can be used to find out what is in the plan cache at any given time.

SQL Server提供以下动态管理视图和功能，这些视图和功能可用于在任何给定时间查找计划缓存中的内容。

• sys.dm_exec_cached_plans
  sys.dm_exec_cached_plans
• sys.dm_exec_sql_text
  sys.dm_exec_sql_text
• sys.dm_exec_query_plan
  sys.dm_exec_query_plan

The first dm_exec_cached_plans is a dynamic management view while the remaining two are dynamic management functions.

第一个dm_exec_cached_plans是动态管理视图，其余两个是动态管理功能。

Let us use these functions and views to see what is in the SQL Server cached query plan. Execute the following query on your SSMS (SQL Server Management Studio):

让我们使用这些功能和视图来查看SQL Server缓存查询计划中的内容。 在SSMS（SQL Server Management Studio）上执行以下查询：

SELECT cplan.usecounts, cplan.objtype, qtext.text, qplan.query_plan
FROM sys.dm_exec_cached_plans AS cplan
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS qtext
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qplan
ORDER BY cplan.usecounts DESC

A SELECT statement is used to select usecounts, object type, query text and an XML representation of the query plan of all the queries that currently reside in the query plan cache. Notice that the CROSS APPLY operator has been used to join the output from dynamic management views and functions. Finally, the result set is sorted in the descending order of the use counts. The output on my machine looks like this (It may differ on your machine depending upon the queries that reside in your query plan):

SELECT语句用于选择使用计数，对象类型，查询文本以及当前驻留在查询计划缓存中的所有查询的查询计划的XML表示形式。 请注意，已使用CROSS APPLY运算符将动态管理视图和功能的输出联接在一起 。 最后，结果集按使用次数的降序排序。 我的计算机上的输出如下所示（根据您的查询计划中的查询，您的计算机上的输出可能会有所不同）：