2

I am attempting to tune a query using the SentryOne Plan Explorer tool (version 3.0, build 11.0.84.0) and SQL Server Microsoft SQL Server 2014 (SP2-CU3). Plan Explorer is reporting an inconsistent number of reads between the per-table numbers and the total reported for the query. The reported total is an order-of-magnitude larger than the sum of the per-table reads, and much larger than it was with the original query.

I apologize that I am unable to post a screenshot. I work in a high-security industry, and I am unable to access image-hosting sites.

This is the query I'm running:

SELECT sd.*, ss.*
    FROM dbo.SecurityActivityRange AS ar
    JOIN dbo.SecurityDaily AS sd ON sd.accountId = ar.accountId
        AND sd.securityId = ar.securityId
        AND sd.reportDate = 5000
        AND sd.isHeld = 1
    JOIN dbo.SecuritySemiStatic AS ss ON ss.accountId = sd.accountId
        AND ss.securityId = sd.securityId
        AND ss.semiStaticDate = sd.semiStaticDate
    WHERE ar.accountId = 1
            AND ar.startDate <= 5000
            AND ar.endDate >= 5000

I'm going to copy the per-table numbers from SSMS, because it's already text and contains the same information as what Plan Explorer is reporting:

Table 'SecurityActivityRange'. Scan count 0, logical reads 92, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SecuritySemiStatic'. Scan count 0, logical reads 194, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SecurityDaily'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

This is from Plan Explorer, in the "Results" tab, under "Statement":

Statement     Est Cost   Duration   CPU   Est CPU Cost   Reads   Writes   Est IO Cost   Est Rows   End Time   Actual Rows   Start Time
SELECT ...    100.0%     33         47    100.0%         3,590   78       100.0%        1          2017-...   46            2017-02...

You can see that the total reads is 3590, which is much larger than 292. I'm also curious to know why it's reporting writes.

This is the textual version of the execution plan:

  |--Nested Loops(Inner Join, OUTER REFERENCES:([ss].[securityId]))
       |--Nested Loops(Inner Join, OUTER REFERENCES:([sd].[securityId], [sd].[semiStaticDate], [Expr1005]) WITH UNORDERED PREFETCH)
       |    |--Clustered Index Seek(OBJECT:([sws_db].[dbo].[SecurityDaily].[PK_SecurityDaily] AS [sd]), SEEK:([PtnId1001]=(100) AND [sd].[accountId]=(1) AND [sd].[reportDate]=(5000)),  WHERE:([sws_db].[dbo].[SecurityDaily].[isHeld] as [sd].[isHeld]=(1)) ORDERED FORWARD)
       |    |--Clustered Index Seek(OBJECT:([sws_db].[dbo].[SecuritySemiStatic].[PK_SecuritySemiStatic] AS [ss]), SEEK:([PtnId1003]=RangePartitionNew([sws_db].[dbo].[SecurityDaily].[semiStaticDate] as [sd].[semiStaticDate],(1),(1097),(1462),(1828),(1918),(2009),(2101),(2193),(2224),(2252),(2283),(2313),(2344),(2374),(2405),(2436),(2466),(2497),(2527),(2558),(2589),(2617),(2648),(2678),(2709),(2739),(2770),(2801),(2831),(2862),(2892),(2923),(2954),(2983),(3014),(3044),(3075),(3105),(3136),(3167),(3197),(3228),(3258),(3289),(3320),(3348),(3379),(3409),(3440),(3470),(3501),(3532),(3562),(3593),(3623),(3654),(3685),(3713),(3744),(3774),(3805),(3835),(3866),(3897),(3927),(3958),(3988),(4019),(4050),(4078),(4109),(4139),(4170),(4200),(4231),(4262),(4292),(4323),(4353),(4384),(4415),(4444),(4475),(4505),(4536),(4566),(4597),(4628),(4658),(4689),(4719),(4750),(4781),(4809),(4840),(4870),(4901),(4931),(4962),(4993),(5023),(5054),(5084),(5115),(5146),(5174),(5205),(5235),(5266),(5296),(5388),(5480),(5570),(5661),(5753),(5845),(5936),(6027),(6119),(6211),(6301),(6392),(6484),(6576),(6666),(6757),(6849),(6941),(7031),(7122),(7214),(7306),(7397),(7488),(7580),(7672),(7762),(7853),(7945),(8037),(8127),(8218),(8310),(8402),(8492),(8583),(8675),(8767),(8858),(8949),(9041),(9133),(9223),(9314),(9406),(9498),(9588),(9679),(9771),(9863),(9953),(10044),(10136),(10228),(10319),(10410),(10502),(10594),(10684),(10775),(10867),(10959),(11049),(11140),(11232),(11324),(11414),(11505),(11597),(11689),(11780),(11871),(11963),(12055),(12145),(12236),(12328),(12420),(12510),(12601),(12693),(12785),(12875),(12966),(13058),(13150),(13241),(13332),(13424),(13516),(13606),(13697),(13789),(13881),(13971),(14062),(14154),(14246),(14336),(14427),(14519),(14611),(14702),(14793),(14885)) AND [ss].[accountId]=(1) AND [ss].[semiStaticDate]=[sws_db].[dbo].[SecurityDaily].[semiStaticDate] as [sd].[semiStaticDate] AND [ss].[securityId]=[sws_db].[dbo].[SecurityDaily].[securityId] as [sd].[securityId]) ORDERED FORWARD)
       |--Clustered Index Seek(OBJECT:([sws_db].[dbo].[SecurityActivityRange].[PK_SecurityActivityRange] AS [ar]), SEEK:([ar].[accountId]=(1) AND [ar].[securityId]=[sws_db].[dbo].[SecuritySemiStatic].[securityId] as [ss].[securityId]),  WHERE:([sws_db].[dbo].[SecurityActivityRange].[startDate] as [ar].[startDate]<=(5000) AND [sws_db].[dbo].[SecurityActivityRange].[endDate] as [ar].[endDate]>=(5000)) ORDERED FORWARD)

You can see that it isn't using any operators that might spill to disk.

I've experimented with the query, and it seems that both joins are necessary to see this behavior. I also found that dropping ss.* from the column clause makes the difference go away too.

Improve this question
0

1 Answer 1

Reset to default
1

Disclaimer: I work for SentryOne, the company that offers Plan Explorer to the community.

The "Reads" number in the statement tree is essentially what trace reports for batch completed, as opposed to a sum of individual I/Os on the Table I/O tab. Run your batch and capture it with a trace, and I bet you see similar numbers reported there for batch completed. It's not always easy to determine where these reads come from, especially when you are keeping so much from us, but they definitely came from your session.

  • Does the table I/O tab have additional line items, like workfile / worktable?
  • Are any of the expressions in the plan computed columns?
  • Do any of the plan operators have warnings about residual I/O? (Apologies, I forget what builds of SQL Server surface that information in plan XML.)

Could also be from function calls (are any of these objects views?), which will only show up as their inner queries in the call stack, and won't show in the plan, as described in this answer:

Improve this answer
1
  • I can not reproduce the problem now, probably due to the overnight maintenance done on the server. None of the issues you've listed apply to my situation, but they certainly could to someone else, so I am going to mark this as my answer. Thank you!
    – sam.bishop
    Feb 23, 2017 at 16:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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