我正在尝试使用 SentryOne Plan Explorer 工具(版本 3.0,内部版本 11.0.84.0)和 SQL Server Microsoft SQL Server 2014 (SP2-CU3) 调整查询。Plan Explorer 报告的每个表的读取次数与查询报告的总数之间的读取次数不一致。报告的总数比每个表读取的总和大一个数量级,并且比原始查询大得多。
很抱歉我无法发布屏幕截图。我在高度安全的行业工作,我无法访问图片托管网站。
这是我正在运行的查询:
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
我将从 SSMS 复制每个表的数字,因为它已经是文本并且包含与 Plan Explorer 报告的信息相同的信息:
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.
这是来自 Plan Explorer,在“结果”选项卡中,在“声明”下:
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...
您可以看到总读取数为 3590,比 292 大得多。我也很想知道为什么它会报告写入。
这是执行计划的文本版本:
|--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)
您可以看到它没有使用任何可能溢出到磁盘的运算符。
我已经对查询进行了试验,似乎两个连接都是看到这种行为所必需的。我还发现ss.*
从列子句中删除也会使差异消失。
免责声明:我在为社区提供 Plan Explorer 的公司 SentryOne 工作。
语句树中的“Reads”数本质上是批处理完成的跟踪报告,而不是表 I/O 选项卡上单个 I/O 的总和。运行您的批次并跟踪捕获它,我敢打赌您会看到那里报告的批次完成的类似数字。确定这些阅读的来源并不总是那么容易,尤其是当您对我们隐瞒了这么多内容时,但它们肯定来自您的会话。
也可能来自函数调用(这些对象视图中的任何一个?),它们只会在调用堆栈中显示为它们的内部查询,并且不会显示在计划中,如本答案中所述: