我有一个sql查询,其确切代码在C#中生成,并作为基于文本的SqlCommand通过ADO.Net传递。
查询看起来像这样:
SELECT TOP (@n)
a.ID,
a.Event_Type_ID as EventType,
a.Date_Created,
a.Meta_Data
FROM net.Activity a
LEFT JOIN net.vu_Network_Activity na WITH (NOEXPAND)
ON na.Member_ID = @memberId AND na.Activity_ID = a.ID
LEFT JOIN net.Member_Activity_Xref ma
ON ma.Member_ID = @memberId AND ma.Activity_ID = a.ID
WHERE
a.ID < @LatestId
AND (
Event_Type_ID IN(1,2,3))
OR
(
(na.Activity_ID IS NOT NULL OR ma.Activity_ID IS NOT NULL)
AND
Event_Type_ID IN(4,5,6)
)
)
ORDER BY a.ID DESC
该查询已经运行了相当长的一段时间。它利用了我们在这些表上拥有的一些索引。
无论如何,此查询突然开始运行非常缓慢,但几乎在SSMS中立即运行。
最终,在阅读了一些资源之后,我能够验证我们得到的速度下降是由于参数嗅探不佳所致。
通过将所有参数复制到局部变量,我能够成功地减少问题。事实是,这对我来说简直是种种错误。
我假设发生了什么事,其中一个表的统计信息已更新,然后由于运气不好,第一次重新编译此查询时,调用了导致执行计划不同的参数值?
我能够在活动监视器中跟踪查询,导致查询在约13秒内运行的执行计划是:
在SSMS中运行会产生以下执行计划(仅花费约100毫秒):
我想我的问题是:如何在不将参数复制到局部变量的情况下解决此问题,这可能导致大量的缓存执行计划?
您可以在存储过程中使用局部变量来“避免”参数嗅探。但是,请理解,这可能会导致许多计划存储在缓存中。这可能会对性能产生影响。没有一个万能的解决方案!
我的想法是,如果我有某种方法可以从temp db中手动删除当前执行计划,那可能就足够了……但是我在网上找到的所有内容仅向我展示了如何针对实际的命名存储执行此操作程序。
这是来自C#的基于文本的SqlCommand,所以我不知道如何查找带有嗅探参数值的缓存执行计划,并将其删除?
注意:“仅仅创建一个适当的存储过程”这种明显的解决方案很难做到,因为可以通过多种不同的方式来生成此查询……并且需要进行一些令人不愉快的重构。
如果要从缓存中删除特定计划,则实际上是一个两步过程:首先获取该特定计划的计划句柄;然后使用DBCC FREEPROCCACHE从缓存中删除该计划。
要获取计划句柄,您需要查看执行计划缓存。下面的T-SQL是一个示例,说明如何搜索计划并获取该句柄(您可能需要对filter子句进行一些操作以磨练您的特定计划):
SELECT top (10)
qs.last_execution_time,
qs.creation_time,
cp.objtype,
SUBSTRING(qt.[text], qs.statement_start_offset/2, (
CASE
WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.[text])) * 2
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2 + 1
) AS query_text,
qt.text as full_query_text,
tp.query_plan,
qs.sql_handle,
qs.plan_handle
FROM
sys.dm_exec_query_stats qs
LEFT JOIN sys.dm_exec_cached_plans cp ON cp.plan_handle=qs.plan_handle
CROSS APPLY sys.dm_exec_sql_text (qs.[sql_handle]) AS qt
OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) tp
WHERE qt.text like '%vu_Network_Activity%'
有了计划处理程序后,请按以下方式致电DBCC FREEPROCCACHE:
DBCC FREEPROCCACHE(<plan_handle>)
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句