【MSSQL】查询缓存和执行计划

查询缓存

我们在测试运行效率时，会发现第二次及以后所得到结果的时间比第一次远远短很多，这是由于SQLSERVER的缓存机制引起的。执行以下语句以清除缓存数据：

# https://docs.microsoft.com/zh-cn/sql/t-sql/database-console-commands/dbcc-dropcleanbuffers-transact-sql?view=sql-server-ver15
# 使用 DBCC DROPCLEANBUFFERS 测试包含冷缓存的查询，而不用关闭和重新启动服务
DBCC DROPCLEANBUFFERS
# https://docs.microsoft.com/zh-cn/sql/t-sql/database-console-commands/dbcc-freeproccache-transact-sql?view=sql-server-ver15
# 删除计划高速缓存中的所有元素，通过指定计划句柄或 SQL 句柄从计划高速缓存中删除特定计划，或者删除与指定资源池相关联的所有高速缓存条目。
DBCC FREEPROCCACHE
# https://docs.microsoft.com/zh-cn/sql/t-sql/database-console-commands/dbcc-freesystemcache-transact-sql?view=sql-server-ver15
# 从所有缓存中释放所有未使用的缓存条目
DBCC FREESYSTEMCACHE

查询相关系统视图

视图	说明
sys.dm_exec_cached_plans
sys.dm_exec_cursors
sys.dm_exec_requests
sys.dm_exec_sessions
sys.dm_exec_query_memory_grants
sys.dm_exec_query_stats
sys.dm_exec_xml_handles
sys.dm_resource_governor_workload_groups
sys.dm_resource_governor_resource_pools

执行计划

https://docs.microsoft.com/zh-cn/sql/relational-databases/performance/compare-execution-plans

SELECT TOP 1000
QS.creation_time,
QS.plan_handle,
SUBSTRING(ST.text,(QS.statement_start_offset/2)+1,((CASE QS.statement_end_offset WHEN -1 THEN DATALENGTH(ST.text)ELSE QS.statement_end_offset END - QS.statement_start_offset)/2) + 1) AS statement_text,
ST.text,
QS.execution_count,max_elapsed_time,max_rows,
QS.total_worker_time,QS.last_worker_time,QS.max_worker_time,QS.min_worker_time
FROM
	sys.dm_exec_query_stats QS CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) ST
where ST.text like '%%'
ORDER BY
QS.creation_time DESC;

select * from sys.dm_exec_query_plan(0x060005009408C43970F04D98EC01000001000000000000000000000000000000000000000000000000000000);

物理和逻辑 IO 活动量的相关信息

输出项	含义
表	表的名称。
Scan count（扫描计数）	在任意方向到达叶级别之后开始的搜索或扫描次数，搜索/扫描目的是检索所有用于构造输出的最终数据集的值。如果使用的索引是主键上的唯一索引或聚集索引，且只搜索一个值，则扫描计数为 0。 例如，WHERE Primary_Key_Column = 。当使用对非主键列定义的非唯一的聚集索引搜索一个值时，扫描计数为 1。 此过程的目的是针对你正在搜索的键值检查重复值。 例如，WHERE Clustered_Index_Key_Column = 。当 N 为通过使用索引键定位键值后，在叶级别的左侧或右侧启动的不同查找或扫描数时，则扫描计数为 N。
logical reads（逻辑读取次数）	从数据缓存读取的页数。
physical reads（物理读取次数）	从磁盘读取的页数。
read-ahead reads（预读次数）	为进行查询而放入缓存的页数。
lob logical reads（lob 逻辑读取次数）	从数据缓存读取的页数。 包括 text、ntext、image、varchar(max)、nvarchar(max)、varbinary(max) 或列存储索引页。
lob physical reads（lob 物理读取次数）	从磁盘读取的页数。 包括 text、ntext、image、varchar(max)、nvarchar(max)、varbinary(max) 或列存储索引页。
lob read-ahead reads（lob 预读次数）	为进行查询而放入缓存的页数。 包括 text、ntext、image、varchar(max)、nvarchar(max)、varbinary(max) 或列存储索引页。

DBCC DROPCLEANBUFFERS;
SET STATISTICS IO ON;
-- SQL
SET STATISTICS IO OFF;

执行的毫秒级信息

SET STATISTICS TIME ON;
SET STATISTICS TIME OFF;