I have a very strange problem. When I run a query with SET STATISTICS IO ON, it shows that I had a total of 183 reads. But when I look at the query plan dmv it shows I did 555,132 reads. I cleared the query cache before I ran my query, so I know that run was mine. Any ideas?

The reason I am checking this is I'm seeing some sort of bad parameter sniffing going on and when I look at the trace file and the query plan cache I see a query doing around 550k reads, but when I try it manually I only get 183 reads. So I'm guessing there is some setting in my SSMS that is different than the calling server where mine does not use the bad plan, but compiles a new one.

Stats I/O output:

Table 'Artifact'.     Scan count 1, logical reads 3,   ...
Table 'ArtifactType'. Scan count 0, logical reads 180, ...
Table 'ObjectType'.   Scan count 1, logical reads 3,   ...
  • Yes, if you are getting different plans, that would explain the difference. sqlperformance.com/2014/11/t-sql-queries/… – Aaron Bertrand Dec 18 '14 at 21:36
  • Yes, I knew that part (frustrating as it is) :), but what about the other part? Where SET STATISTICS IO shows 183 reads, but the DMV shows that same exact run did 555,132 reads? – Chris Woods Dec 18 '14 at 21:38
  • I don't believe that it is conclusive that those were the same runs. You are alleging that because you cleared the plan cache first, that run must be yours, but how are you sure? – Aaron Bertrand Dec 18 '14 at 21:39
  • I can recreate it. I can clear it, run it once and see. Then clear it and run it twice and see. And even cleared it and ran it 13 times and it shows 13 executions. – Chris Woods Dec 18 '14 at 21:44
  • Can you show a sample of the STATISTICS IO output, the relevant columns from the DMV, and the actual execution plan (not the estimated garbage that's in the plan cache)? – Aaron Bertrand Dec 18 '14 at 21:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.