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?
Best Answer
It's typically called parameter sniffing, and here's an excellent post on how to deal with it:
http://www.simple-talk.com/sql/t-sql-programming/parameter-sniffing/