Query Performance Degrades with High Number of Logical Reads
- by electricsk8
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?
