查询计划缓存

最新推荐文章于 2021-03-18 09:52:36 发布

三只蚂蚁2008

最新推荐文章于 2021-03-18 09:52:36 发布

阅读量2.7k

点赞数

文章标签： sqlserver statistics sql database sql server object

当SQL语句执行后，SQLServer会将执行计划缓存，在下次执行的时候使用该查询计划。在一定的条件下，SQLserver会重新编译该查询计划并使用新的查询计划。

查询计划缓存的大小：

SQL Server 7 可以设置查询计划缓存占有内存的大小，而到了SQL2005则缓存计划占用内存的大小是动态的,查询计划的缓存分四种类型：

Object Plans (CACHESTORE_OBJCP)

Object Plans include plans for stored procedures, functions, and triggers
SQL Plans (CACHESTORE_SQLCP)

SQL Plans include the plans for adhoc cached plans, autoparameterized plans, and prepared plans.
Bound Trees (CACHESTORE_PHDR)

Bound Trees are the structures produced by SQL Server's algebrizer for views, constraints, and defaults.
Extended Stored Procedures (CACHESTORE_XPROC)

Extended Procs (Xprocs) are predefined system procedures, like sp_executeSql and sp_tracecreate, that are defined using a DLL, not using Transact-SQL statements. The cached structure contains only the function name and the DLL name in which the procedure is implemented.

你可以通过如下的视图来查询该信息：

select * from sys.dm_os_memory_cache_counters WHERE type IN ('CACHESTORE_OBJCP', 'CACHESTORE_SQLCP',
'CACHESTORE_PHDR', 'CACHESTORE_XPROC');

每种类型的信息都是一个hash表，其查询计划存储在该hash中，查询具体的信息可以通过

SELECT type as 'plan cache store', buckets_count
FROM sys.dm_os_memory_cache_hash_tables
WHERE type IN ('CACHESTORE_OBJCP', 'CACHESTORE_SQLCP',
'CACHESTORE_PHDR', 'CACHESTORE_XPROC'); 来查询

查询计划查询方法：

SQLSever2005中查询计划是可以通过系统视图sys.dm_exec_cached_plans来查找，他的每行都对应着一个查询计划，他重要的信息是以下几列 usecounts,.cacheobjtype, objtype， plan_handle

usecounts代表用户这个查询计划重用的次数

plan_handle 是查询sql的句柄，详细信息可以通过系统函数sys.dm_exec_sql_text获取

cacheobjtype分如下五种：

Compiled Plan
Parse Tree
Extended Proc
CLR Compiled Func
CLR Compiled Proc

objtype 有11中类型
Proc (Stored procedure)
Prepared (Prepared statement)
Adhoc (Adhoc query)
ReplProc (Replication-filter-procedure)
Trigger ()
View
Default (Default constraint or default object)
UsrTab (User table)
SysTab (System table)
Check (CHECK constraint)
Rule (Rule object)
For both permanent and temporary tables, if N is less or equal to 500, the RT value is 500. This means that for a relatively small table, you must make at least 500 changes to trigger recompilation. For larger tables, at least 500 changes must be made, plus 20 percent of the number of rows.
For temporary tables, the algorithm is the same, with one exception. If the table is very small or empty (N is less than 6 prior to any data modification operations), all we need are 6 changes to trigger a recompile. This means that a procedure that creates a temporary table, which is empty when created, and then inserts 6 or more rows into that table, will have to be recompiled as soon as the temp table is accessed.
You can get around this frequent recompilation of batches that create temporary tables by using the KEEP PLAN query hint. Use of this hint changes the recompilation thresholds for temporary tables and makes them identical to those for permanent tables. So if changes to temporary tables are causing many recompilations, and you suspect that the recompilations are affecting overall system performance, you can use this hint and see if there is a performance improvement. The hint can be specified as shown in this query:

RT的更改次数存储在sysindexes的rowmodctr 字段，具体可以如下查找

select top 10 object_name(id), rowmodctr from sysindexes where id=indexid

另外如果查询计划的大小超过8K,那他将不会被缓存，因此，任何情况下该查询都会被重新编译

SQL查询计划不重新编译的强制方法，使用OPTION (KEEP PLAN)
```
SELECT <column list>
FROM dbo.PermTable A INNER JOIN #TempTable B ON A.col1 = B.col2
WHERE <filter conditions>
OPTION (KEEP PLAN)
```

比如：

SELECT usecounts, cacheobjtype, objtype, [text]
FROM sys.dm_exec_cached_plans P
CROSS APPLY sys.dm_exec_sql_text(plan_handle)

清除SQL缓存的方法：

清除查询计划缓存：

1 DBCC FREEPROCCACHE

2 清除某一数据库缓存：DBCC FLUSHPROCINDB (dbid) dbid获取方法：select db_id('northwind')

3 SQL系统自己清除缓存：

在下列情况下清除所有查询计划缓存：

Detaching any database.
Upgrading any database to SQL Server 2005 (on SQL Server 2005 server).
Running the DBCC FREEPROCCACHE command.
Running the ALTER DATABASE . . . MODIFY FILEGROUP command for any database.
Modifying a collation for any database using ALTER DATABASE . . . COLLATE command.

在下列情况下SQLServer清除某一数据库查询计划缓存
Running the DBCC FLUSHPROCINDB command.
Altering a database with any of the following options:
1. ALTER DATABASE . . . MODIFY NAME=command
2. ALTER DATABASE . . . SET ONLINE command
3. ALTER DATABASE . . . SET OFFLINE command
4. ALTER DATABASE . . . SET EMERGENCY command
5. Dropping a DATABASE
6. When a database auto-closes

SQLServer查询计划重新编译：

手动重新编译查询计划：

调用系统存储过程sp_recompile 重新编译一个表或视图

数据库自动重新编译：

1 对象结构更新会引起查询计划重新编译，比如：

Adding or dropping columns to/from a table or view.
Adding or dropping constraints, defaults, or rules to or from a table.
Adding an index to a table or an indexed view.
Dropping an index defined on a table or an indexed view if the index is used by the plan.
Dropping a statistic defined on a table will cause a correctness-related recompilation of any query plans that use that table.
Adding or dropping a trigger from a table.

数据库确定以上行为是通过如下方法来确认的，查询

select modify_date from sys.objects where object_id = object_id('Employees')

确定日期是否更新过

2 调用 SET options引起查询计划重新编译

通过sys.dm_exec_plan_attributes函数可以查询该对象的SET options是否更改过

SELECT usecounts, cacheobjtype, objtype, [text],attr.*
FROM sys.dm_exec_cached_plans P
CROSS APPLY sys.dm_exec_sql_text (plan_handle)
CROSS APPLY sys.dm_exec_plan_attributes(plan_handle) as attr
WHERE cacheobjtype = 'Compiled Plan'

SELECT plan_handle, pvt.set_options, pvt.object_id, pvt.sql_handle
FROM (SELECT plan_handle, epa.attribute, epa.value
       FROM sys.dm_exec_cached_plans
          OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
       WHERE cacheobjtype = 'Compiled Plan'
      ) AS ecpa
PIVOT (MAX(ecpa.value) FOR ecpa.attribute
   IN ("set_options", "object_id", "sql_handle")) AS pvt;

获取对象属性值：

SELECT OBJECTPROPERTY(object_id('<object name>'), 'ExecIsQuotedIdentOn');

3 更新统计信息到一定阙值引起查询计划重新编译

手动更新统计信息：sp_updatestats or   UPDATE STATISTICS command

自动更新统计信息：

当设置了set AUTO_UPDATE_STATISTICS on时系统会自动更新（sqlserver缺省设置）

当tablebe被确认为Stale Statistics状态时，系统会重新编译查询计划

每个table都有一个阙值称为RT,当他达到RT值时便认为table是Stale Statistics状态

RT在如下情况下会有相应的变化：

sql查询计划类型详解：

1 Adhoc Query Caching

当cacheobjtype是Compiled Plan 并且objtype为Adhoc，我们认为该查询计划为Adhoc Query Caching类型。该查询要求精确匹配时才能重用查询计划，也就是查询sql语句完全相等

比如：

USE Northwind;

go;

DBCC FREEPROCCACHE;
GO
SELECT * FROM Orders WHERE CustomerID = 'HANAR';
GO
SELECT * FROM Orders WHERE CustomerID = 'CHOPS';
GO
SELECT * FROM Orders WHERE CustomerID = 'HANAR';
GO
SELECT usecounts, cacheobjtype, objtype, [text]
FROM sys.dm_exec_cached_plans P
   CROSS APPLY sys.dm_exec_sql_text (plan_handle)
WHERE cacheobjtype = 'Compiled Plan'
   AND [text] NOT LIKE '%dm_exec_cached_plans%';

我们可以看到SELECT * FROM Orders WHERE CustomerID = 'HANAR';和SELECT * FROM Orders WHERE CustomerID = 'CHOPS'

是两个查询计划。另外如果sql存在大小写 空格、注释等等，sql也会把他们当做不同的查询计划。

因此，当你在程序开发中如果有大量的SQL查询时候，请不要如下使用

string sql = "SELECT * FROM Orders WHERE CustomerID = '"+customer+"'";

这样会产生大量的查询计划造成数据库内存占用过大，查询性能下降。

Autoparameterization

SQL Server can decide to treat one or more of the constants as parameters，这时候叫Autoparameterization

比如：

USE Northwind
GO
DBCC FREEPROCCACHE;
GO
SELECT FirstName, LastName, Title FROM Employees WHERE EmployeeID = 6;
GO
SELECT FirstName, LastName, Title FROM Employees WHERE EmployeeID = 2;
GO
SELECT usecounts, cacheobjtype, objtype, [text]
FROM sys.dm_exec_cached_plans P
   CROSS APPLY sys.dm_exec_sql_text (plan_handle)
WHERE cacheobjtype = 'Compiled Plan'
   AND [text] NOT LIKE '%dm_exec_cached_plans%';
GO

但是，sqlserver将查询计划作为Autoparameterization是十分谨慎的，上文因为EmployeeID 是聚集索引，所以返回的行数

肯定为1，但是当查询条件的索引为非聚集索引，那么返回的行数是不确定的，此时sqlserver将

不把他们作为Autoparameterization

另外sqlserver可以强制一个数据库查询将常量作为Autoparameterization类型，具体方法为：

ALTER DATABASE <database_name> SET PARAMETERIZATION FORCED;

The sp_executesql Procedure执行的sql也将查询计划设置为Autoparameterization类型

比如：

USE Northwind;
GO
SET STATISTICS IO ON;
GO
DBCC FREEPROCCACHE;
GO
EXEC sp_executesql N'SELECT * FROM Orders
         WHERE CustomerID = @p', N'@p nvarchar(10)', 'CENTC';
GO
EXEC sp_executesql N'SELECT * FROM Orders
         WHERE CustomerID = @p', N'@p nvarchar(10)', 'SAVEA';
GO
SELECT usecounts, cacheobjtype, objtype, [text]
FROM sys.dm_exec_cached_plans P
         CROSS APPLY sys.dm_exec_sql_text (plan_handle)
WHERE cacheobjtype = 'Compiled Plan'
         AND [text] NOT LIKE '%dm_exec_cached_plans%';
GO
SET STATISTICS IO OFF;
GO

Compiled Objects

Cacheobjtype是Compiled Plan Objtype是Proc的时候，认为是Compiled Objects，

存储过程，用户定义函数都属于该类型。

USE Northwind2;
GO
CREATE PROCEDURE P_Customers
   @cust nvarchar(10)
AS
   SELECT RowNum, CustomerID, OrderDate, ShipCountry
   FROM BigOrders
   WHERE CustomerID = @cust;
GO
DBCC FREEPROCCACHE;
GO
SET STATISTICS IO ON;
GO
EXEC P_Customers 'CENTC';
GO
EXEC P_Customers 'SAVEA';
GO

SELECT usecounts, cacheobjtype, objtype, [text]
FROM sys.dm_exec_cached_plans P
         CROSS APPLY sys.dm_exec_sql_text (plan_handle)
WHERE cacheobjtype = 'Compiled Plan';
         GO

EXEC P_Customers 'SAVEA' WITH RECOMPILE; 会使用新的查询计划并且不讲该计划缓存

几个有用的系统视图：

sys.dm_exec_sql_text

sql_handle or a plan_handle 作为他的参数 返回相应的SQL语句

USE Northwind;
DBCC FREEPROCCACHE;
SET QUOTED_IDENTIFIER OFF;
GO
--- this is an example of the relationship between
-- sql_handle and plan_handle
SELECT LastName, FirstName, Country
FROM Employees
WHERE Country <> 'USA';
GO
SET QUOTED_IDENTIFIER ON;
GO
--- this is an example of the relationship between
-- sql_handle and plan_handle
SELECT LastName, FirstName, Country
FROM Employees
WHERE Country <> 'USA';
GO
SELECT st.text, qs. sql_handle, qs.plan_handle
FROM sys.dm_exec_query_stats qs
   CROSS APPLY sys.dm_exec_sql_text(sql_handle) st;
GO

2 sys.dm_exec_cached_plan_dependent_objects

This DMF returns one row for every dependent object of a compiled plan when you pass a valid plan_handle in as a parameter

3 sys.dm_exec_requests

The sys.dm_exec_requests view returns one row for every currently executing request within your SQL Server instance

查询返回耗时前十的正在运行的SQL语句

SELECT TOP 10 SUBSTRING(text, (statement_start_offset/2) + 1,
   ((CASE statement_end_offset
        WHEN -1
           THEN DATALENGTH(text)
        ELSE statement_end_offset
   END - statement_start_offset)/2) + 1) AS query_text, *
FROM sys.dm_exec_requests
   CROSS APPLY sys.dm_exec_sql_text(sql_handle)
ORDER BY total_elapsed_time DESC

4 sys.dm_exec_query_stats

返回平均耗时前十的SQL语句，从实例开始启动计算

SELECT TOP 10 SUBSTRING(text, (statement_start_offset/2) + 1,
   ((CASE statement_end_offset
        WHEN -1
           THEN DATALENGTH(text)
        ELSE statement_end_offset
   END - statement_start_offset)/2) + 1) AS query_text, *
FROM sys.dm_exec_query_stats
   CROSS APPLY sys.dm_exec_sql_text(sql_handle)
   CROSS APPLY sys.dm_exec_query_plan(plan_handle)
ORDER BY total_elapsed_time/execution_count DESC;