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.
Best Answer
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.
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: