2

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?

1 Answer 1

4

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/

2
  • Thanks Brent. Is this also applicable to dynamic sql? Feb 23, 2011 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, 2011 at 22:17

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .