Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm using Confio Ignite8 to derive this information, and monitor waits.

I have one query that runs frequently, and I notice that on some days there is an extremely high number of logical reads incurred, +300,000,000 for 91,000 executions. On a good day, the logical reads are much lower, 18,000,000 for 94,000 executions.

The execution plan for the query utilizes clustered index seeks, and is below.

StmtText |--Nested Loops(Inner Join, OUTER REFERENCES:([f].[ParentId])) |--Clustered Index Seek(OBJECT:([StructuredFN].[dbo].[Folder].[PK_Folders] AS [f]), SEEK:([f].[FolderId]=(8125)), WHERE:([StructuredFN].[dbo].[Folder].[DealId] as [f].[DealId]=(300)) ORDERED FORWARD) |--Clustered Index Seek(OBJECT:([StructuredFN].[dbo].[Folder].[PK_Folders] AS [p]), SEEK:([p].[FolderId]=[StructuredFN].[dbo].[Folder].[ParentId] as [f].[ParentId]), WHERE:([StructuredFN].[dbo].[Folder].[DealId] as [p].[DealId]=(300)) ORDERED FORWARD)

Output from showstatistics io ... Table 'Folder'. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Any ideas on how to troubleshoot where these high logical reads come from on certain days, and others nothing?

share|improve this question
up vote 3 down vote accepted

It's typically called parameter sniffing, and here's an excellent post on how to deal with it:

share|improve this answer
Thanks Brent. Is this also applicable to dynamic sql? – electricsk8 Feb 23 '11 at 20:25
Yep, depending on how SQL Server and the T-SQL statement works, even dynamic SQL can get a cached plan. – Brent Ozar Feb 23 '11 at 22:17

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.