执行大量读取但计划没问题的查询 [英] A query that does a lot of reads, but plan is OK

问题描述

我在 SQL Server 2008 R2 中的特定查询中遇到奇怪的行为.我有一个执行 1900 万次读取且非常耗时的查询，当我尝试检查其缓存计划时，该计划是可以的，没有任何问题.

I'm experiencing a strange behaviour in a specific query in the SQL Server 2008 R2. I've got a query that does 19 million reads and is very time-consuming and when I try to check its cached plan, the plan is OK, without any problem what so ever.

执行DBCC FREEPROCCACHE 后，同一个查询进行了 400 次读取(耗时 16 毫秒).结论是不知何故以错误的计划执行查询，但这不是我从 SQL Server 获得的信息.

After doing the DBCC FREEPROCCACHE, the same query does 400 reads (taking 16 ms). The conclusion is that somehow the query is executed with the wrong plan, but that's not the information I got from the SQL Server.

有人知道这是怎么回事吗?在下面找到我用来提取计划的查询:

Does anyone have any idea what is going on? Find below the queries I used to extract the plan:

SELECT
sqltext.TEXT,
sqlplan.query_plan,
req.session_id [Session ID],
p.kpid [Thread ID],
p.program_name,
req.status,
req.command,
req.cpu_time,
req.logical_reads,
req.blocking_session_id,
req.transaction_id,
req.total_elapsed_time,
req.wait_resource
FROM sys.dm_exec_requests req inner join
     sys.sysprocesses p on req.session_id = p.spid
     CROSS APPLY sys.dm_exec_query_plan(req.plan_handle) AS sqlplan
     CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS sqltext

还有

SELECT db.name,
cp.objtype AS ObjectType,
OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
cp.usecounts AS ExecutionCount,
st.TEXT AS QueryText,
qp.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st 
INNER JOIN sys.sysdatabases db on st.dbid = db.dbid
where st.TEXT like '%part_of_query%'

另一个信息是在free cache命令之前和之后的查询计划是一样的.

Another info is that the query plan is the same before and after the free cache command.

执行计划图片

推荐答案

很多事情都会以这种方式影响查询.最常见的可能是参数嗅探.当第一次执行查询时，计划是使用通过这些精确参数值获得的基数估计来构建的.因此，如果第一次运行中使用的参数值非常有选择性，优化器可能会使用嵌套循环进行连接.所以下一次，如果另一个值影响了一半的表，这个缓存计划将非常无效，因为在这种情况下散列或合并连接更好.

A lot of things can affect the query this way. The most common is, probably, the parameter sniffing. When the query is executed for the first time, the plan is built using cardinality estimates got with these exact parameter values. So, if the parameter value used in the first run is very selective, optimizer will probably utilise nested loops for joins. So the next time, with another value that affects half the table, this cached plan will be extremely ineffective, because hash or merge join is better in this case.

过时的分布统计也可能导致这种行为.以及碎片化的索引.可能还有其他一些可能性——没有看到实际的执行计划，猜测会一直持续下去.

Outdated distribution statistics can also lead to this kind of behaviour. As well as fragmented indices, too. Probably there are some other possibilities - without seeing the actual execution plan, the guessing can go on forever.

但是您可以尝试在查询中添加optimize for unknown 选项，看看它是否有帮助.

But you may try to add optimize for unknown option to the query and see whether it will help.

这篇关于执行大量读取但计划没问题的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！