如何查看执行计划使用次数
SELECT
a.usecounts,
a.objtype,
c.text,
b.query_plan,
a.plan_handle
FROM sys.dm_exec_cached_plans a
CROSS APPLY sys.dm_exec_query_plan(a.plan_handle) b
CROSS APPLY sys.dm_exec_sql_text(a.plan_handle) AS c
WHERE a.cacheobjtype = 'Compiled Plan'
AND c.text LIKE '%xxx查询语句中的关键字xxx%'
AND c.text NOT LIKE '%sys.dm_exec_query_plan%'
AND b.dbid = DB_ID()
来自:https://www.sqlindia.com/difference-between-sp_execute-sp_executesql-exec/
关于执行计划
缓存执行计划 SQL Server 2008提供了一些服务器对象来分析执行计划 Sys.dm_exec_cached_plans: 包含缓存的执行计划,每个执行计划对应一行。 Sys.dm_exec_plan_attributes: 这是一个系统函数,每一个执行计划都对应着一些属性,在这个系统函数中包含着这些属性。 Sys.dm_exec_sql_text: 这是一个系统函数,返回文字格式的执行计划。 Sys.dm_exec_query_plan: 这是一个系统函数,返回xml格式的执行计划。 SQL Server 2008还提供了一个兼容性的视图sys.syscacheobject,这个视图中保存了所有的执行计划的信息。
清除缓存 在进行性能分析的时候有时候需要清除缓存以便进行下一次分析。SQL Server提供了一些工具来清除缓存的性能数据。使用下面的语句来完成这些任务。 清除全局缓存使用下面的语句: DBCC DROPCLEANBUFFERS; 从全局缓存中清除执行计划,使用下面的语句:
DBCC FREEPROCCACHE;
清除某一个数据库中的执行计划,使用下面的语句:
DBCC FLUSHPROCINDB(
); 清除一个特定的执行计划使用下面的语句:
DBCC FREESYSTEMCACHE(
); 可以使用’ALL’,pool_name,’Object Plan’,’SQL Plans’,’Bound Trees’作为输入参数。’ALL’参数标明要清除所有的缓存,pool_name的值表明要清除的一个缓存池的名字。’Object Plans’清除对象计划(例如存储过程,触发器,用户定义函数等等)。’SQL Plans’用来清除要立即执行的语句。’Bound Trees’定义清除视图,约束等的缓存。
注意:在使用这些语句清除缓存之前要想清楚,特别是在生产环境。这些对性能有很大的影响。清除这些缓存之后SQL Server需要从数据页中重新读取数据。并且SQL Server需要重新生成新的执行计划。因此在清除之前要想清楚这些对生产或者测试环境的影响。
哪些操作会缓存
- sp_prepexec:https://docs.microsoft.com/zh-cn/sql/relational-databases/system-stored-procedures/sp-prepexec-transact-sql?view=sql-server-ver15
- sp_prepare + sp_execute:https://docs.microsoft.com/zh-cn/sql/relational-databases/system-stored-procedures/sp-prepare-transact-sql?view=sql-server-ver15、https://docs.microsoft.com/zh-cn/sql/relational-databases/system-stored-procedures/sp-execute-transact-sql?view=sql-server-ver15
sp_executesql:https://docs.microsoft.com/zh-cn/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-ver15
优化VARCHAR类型的性能问题:sendStringParametersAsUnicode
JDBC默认会传输Unicode类型的字符串
- SQL Server在执行SQL的时候会进行类型转换,确保表达式两边为同一类型(A=B也是一种表达式)
- NVARCHAR(Unicode)的类型优先级高于VARCAHR(ASCII/MBCS)
- SQL Server在比较时会先进行类型转换处理,把VARCHAR转为NVARCHAR
- 经实测这时会有一个要命的问题:在VARCHAR列上的索引会失效!
解决方法:
- 在连接信息上加上sendStringParametersAsUnicode=false,迫使JDBC处理VARCHAR时使用ASCII/MBCS字符串(’’)而非Unicode字符串(N’’)
- 修改字段类型,改为NVARCHAR