CODEWITHSUNDEEP
sql-serversql-execution-plan
bigster
bigster

Reputation: 63

A query that does a lot of reads, but plan is OK

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.

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

And 

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%'

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

Image of the Execution Plan

Upvotes: 1

Views: 230

Answers (1)

Roger Wolf
Roger Wolf

Reputation: 7692

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.

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

Upvotes: 1

Related Questions