sqlserver缓存_了解SQLServer查询计划缓存

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

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.

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

In this article we will look at:

在本文中&＃xff0c;我们将研究&＃xff1a;

• 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提供以下动态管理视图和功能&＃xff0c;这些视图和功能可用于在任何给定时间查找计划缓存中的内容。

• 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是动态管理视图&＃xff0c;其余两个是动态管理功能。

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&＃xff08;SQL Server Management Studio&＃xff09;上执行以下查询&＃xff1a;

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

Here the usecount column contains a count for the number of times a query has been executed. The objtype column contains information about the object through which a query is executed. It is important to mention that up till SQL Server 6.5 only stored procedure queries were stored in the cached plan. From SQL Server 7.0 onwards, dynamic and ad-hoc queries are also stored in the cached plan. The text column contains the text of the query and finally, the query_plan column contains the XML representation of the query. Click any row in the query_plan column to see the detailed XML representation of the plan.

此处usecount列包含查询执行次数的计数。 objtype列包含有关通过其执行查询的对象的信息。 重要的是要提到&＃xff0c;直到SQL Server 6.5为止&＃xff0c;只有存储过程查询存储在缓存的计划中。 从SQL Server 7.0起&＃xff0c;动态查询和即席查询也存储在缓存的计划中。 text列包含查询的文本&＃xff0c;最后&＃xff0c;query_plan列包含查询的XML表示形式。 单击query_plan列中的任何行以查看该计划的详细XML表示形式。

清除计划缓存 (Clearing the plan cache)

To clear the plan cache, execute the following:

要清除计划缓存&＃xff0c;请执行以下操作&＃xff1a;

DBCC FREEPROCCACHE


存储过程查询计划 (Stored procedure query plan)

Now let’s execute a simple stored procedure and see what we get in our SQL Server query plan cache.

现在&＃xff0c;让我们执行一个简单的存储过程&＃xff0c;看看我们在SQL Server查询计划缓存中得到了什么。

First let’s create a dummy database and a table inside that database:

首先&＃xff0c;我们创建一个虚拟数据库和该数据库内的表&＃xff1a;

CREATE Database company;USE company;CREATE TABLE department
(id INT PRIMARY KEY,dep_name VARCHAR(50) NOT NULL,)


Next insert some records into the table:

接下来将一些记录插入表中&＃xff1a;

USE company;INSERT INTO department VALUES
(1, &＃39;Sales&＃39;),
(2, &＃39;HR&＃39;),
(3, &＃39;IT&＃39;),
(4, &＃39;Marketing&＃39;),
(5, &＃39;Finance&＃39;)


Finally, let’s create a simple stored procedure that retrieves all the records from the department table of the company database:

最后&＃xff0c;让我们创建一个简单的存储过程&＃xff0c;该存储过程从公司数据库的部门表中检索所有记录&＃xff1a;

USE company
GOCREATE PROCEDURE getdepartment
AS
SELECT * FROM department
GO


Before we execute the getdepartment stored procedure, clear the query plan cache using following query:

在执行getdepartment存储过程之前&＃xff0c;请使用以下查询清除查询计划缓存&＃xff1a;

CREATE Database company;


Now, execute the newly created getdepartment stored procedure:

现在&＃xff0c;执行新创建的getdepartment存储过程&＃xff1a;

exec getdepartment


Once the stored procedure is executed, try to retrieve the information about all the query plans in the plan cache, again execute the following query:

一旦执行了存储过程&＃xff0c;请尝试在计划缓存中检索有关所有查询计划的信息&＃xff0c;然后再次执行以下查询&＃xff1a;

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


When the above query is executed, you will see that two query plans will be retrieved: One query plan for the stored procedure and the other for the query that retrieves the query plan. The output looks like this:

执行上述查询后&＃xff0c;您将看到将检索两个查询计划&＃xff1a;一个用于存储过程的查询计划&＃xff0c;另一个用于检索该查询计划的查询。 输出看起来像这样&＃xff1a;

In the first row, you can see the object type Proc. This refers to the stored procedure query plan that we just executed. Inside the text column, you can also see the information about the stored procedure and the actual query that we executed inside the stored procedure.

在第一行中&＃xff0c;您可以看到对象类型Proc。 这是指我们刚刚执行的存储过程查询计划。 在文本列中&＃xff0c;您还可以查看有关存储过程的信息以及我们在存储过程中执行的实际查询。

Here the usecount column displays the number of times query is executed. Execute the stored procedure once more and then retrieve the query plan cache, and you will see following results:

这里的usecount列显示查询执行的次数。 再次执行存储过程&＃xff0c;然后检索查询计划缓存&＃xff0c;您将看到以下结果&＃xff1a;

You can see that count for both the stored procedure and the query plan access queries have jumped to 2.

您可以看到存储过程和查询计划访问查询的计数已跃升至2。

查询计划取决于查询文本 (The query plan depends upon the query text)

SQL Server generates a query plan using a hash value that is calculated from the query text. When a query is run, SQL Server calculates its hash value and checks if a plan with the same hash value exists in the plan cache. If a plan with same hash value exists, that plan is executed. However, if a plan with the newly calculated hash value doesn’t exist, a new query plan is generated and stored in the cache plan.

SQL Server使用从查询文本计算得出的哈希值来生成查询计划。 运行查询时&＃xff0c;SQL Server将计算其哈希值&＃xff0c;并检查计划缓存中是否存在具有相同哈希值的计划。 如果存在具有相同哈希值的计划&＃xff0c;则将执行该计划。 但是&＃xff0c;如果不存在具有新计算的哈希值的计划&＃xff0c;则会生成一个新的查询计划并将其存储在缓存计划中。

The hash value for the query plan is generated from the text. Therefore if there is even a slight change in the query text (e.g. a change of case, comma or space) a new hash value will be generated and thus a new query plan. Let’s see an example of this.

查询计划的哈希值是从文本生成的。 因此&＃xff0c;即使查询文本有微小变化&＃xff08;例如大小写&＃xff0c;逗号或空格的变化&＃xff09;&＃xff0c;也会生成新的哈希值&＃xff0c;从而生成新的查询计划。 让我们来看一个例子。

Clear the query plan cache and execute the following query twice:

清除查询计划缓存&＃xff0c;并两次执行以下查询&＃xff1a;

SELECT * FROM department where dep_name &＃61; &＃39;Sales&＃39;


When you retrieve the query plan cache, you can see that usecount for the Adhoc query that we just executed twice is 2:

检索查询计划缓存时&＃xff0c;您可以看到我们刚刚执行了两次的Adhoc查询的usecount为2&＃xff1a;

Now let’s make a slight change to our query:

现在&＃xff0c;让我们对查询进行一些更改&＃xff1a;

SELECT * FROM department Where dep_name &＃61; &＃39;Sales&＃39;


Here we have replaced small case w of the where clause from the previous query to capital case W. This is a minor change, but when the above query is executed a new hash value is calculated and hence a new query plan is generated for the query. If you look at the query cash plan now, you will see following output:

在这里&＃xff0c;我们将前一个查询的where子句的小写字母w替换为大写字母W。 这是一个较小的更改&＃xff0c;但是当执行以上查询时&＃xff0c;将计算新的哈希值&＃xff0c;因此将为该查询生成新的查询计划。 如果现在查看查询现金计划&＃xff0c;您将看到以下输出&＃xff1a;

From the output, you can see that there are two query plans with same functionality but different text.

从输出中&＃xff0c;您可以看到有两个具有相同功能但文本不同的查询计划。

Similarly, if you change the value from the dep_name from Sales to HR, a new query plan will be generated.

同样&＃xff0c;如果将值从dep_name从Sales更改为HR&＃xff0c;将生成一个新的查询计划。

使用参数化查询以实现可重用性 (Use parameterized queries for reusability)

We know that when a query’s text changes, a new query plan is generated instead of reusing the existing one. Parameterized queries resolve this issue. In a parameterized query we pass dynamic values in the form of parameters to the query. The query text itself doesn’t change; therefore existing query plans can be reused instead of creating a new plan. Let’s take a look at a simple example.

我们知道&＃xff0c;当查询的文本更改时&＃xff0c;将生成一个新的查询计划&＃xff0c;而不是重用现有的查询计划。 参数化查询可解决此问题。 在参数化查询中&＃xff0c;我们将参数形式的动态值传递给查询。 查询文本本身不变。 因此&＃xff0c;可以重复使用现有的查询计划&＃xff0c;而不用创建新的计划。 让我们看一个简单的例子。

Let’s create a parameterized stored procedure that retrieves the department record by name. Execute the following query:

让我们创建一个参数化的存储过程&＃xff0c;该存储过程按名称检索部门记录。 执行以下查询&＃xff1a;

USE company
GOCREATE PROCEDURE getdepartmentbyname &＃64;name nvarchar(30)
AS
SELECT * FROM department WHERE dep_name &＃61; &＃64;name
GO


The above stored procedure filters department records using department name passed as parameters. Now clear the query plan cache and execute the following stored procedure:

上面的存储过程使用作为参数传递的部门名称来过滤部门记录。 现在&＃xff0c;清除查询计划缓存并执行以下存储过程&＃xff1a;

exec getdepartmentbyname &＃39;Sales&＃39;


Execute the getdepartmentbyname stored procedure again but with different parameters this time:

再次执行getdepartmentbyname存储过程&＃xff0c;但是这次使用不同的参数&＃xff1a;

exec getdepartmentbyname &＃39;HR&＃39;


Now if you retrieve the values from the query plan cache you will see that although you executed the stored procedure twice with different text, there is only one plan for the stored procedure getdepartmentbyname and its usecount is 2 which means that the plan has been executed twice:

现在&＃xff0c;如果您从查询计划缓存中检索值&＃xff0c;您将看到尽管您使用不同的文本执行了两次存储过程&＃xff0c;但是存储过程getdepartmentbyname只有一个计划&＃xff0c;其usecount为2&＃xff0c;这意味着该计划已执行了两次&＃xff1a;

This shows that using parameterized queries, you can reuse the existing query plans despite changing query text.

这表明使用参数化查询&＃xff0c;尽管更改了查询文本&＃xff0c;仍可以重用现有查询计划。

本的其他精彩文章 (Other great articles from Ben)

翻译自: https://www.sqlshack.com/understanding-sql-server-query-plan-cache/

sql server 缓存