Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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
add comment

1 Answer

up vote 3 down vote accepted

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/

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
add comment

Your Answer

 
discard

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.