sql-server – 执行计划与STATISTICS IO订单

SQL Server图形执行计划从右到左,从上到下阅读. SET STATISTICS IO ON生成的输出是否有意义的顺序？

以下查询：

SET STATISTICS IO ON;

SELECT  *
FROM    Sales.SalesOrderHeader AS soh
        JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID
        JOIN Production.Product AS p ON sod.ProductID = p.ProductID;

生成此计划：

而这个STATISTICS IO输出：

Table 'Worktable'. Scan count 0,logical reads 0,physical reads 0,read-ahead reads 0,lob logical reads 0,lob physical reads 0,lob read-ahead reads 0.
Table 'SalesOrderDetail'. Scan count 1,logical reads 1246,physical reads 3,read-ahead reads 1277,lob read-ahead reads 0.
Table 'SalesOrderHeader'. Scan count 1,logical reads 689,physical reads 1,read-ahead reads 685,lob read-ahead reads 0.
Table 'Product'. Scan count 1,logical reads 15,read-ahead reads 14,lob read-ahead reads 0.

所以,我重申一下：是什么给出的？对STATISTICS IO输出有一个有意义的排序还是使用了一些任意顺序？

解决方法

Each SQL Server connection has an associated process status structure
(PSS) that maintains connection-specific state information. Each
unique server process ID (SPID) in the sysprocesses system table
represents a different PSS,and the information in the sysprocesses
virtual table is a “view” into this status information.

以及与您的问题相关的部分：

If STATISTICS IO is enabled for a connection,SQL Server allocates an
array during query execution to track IO information on a per-table
basis. As SQL Server processes the query,it records each logical
request for a page in the appropriate table’s entry in this array,
along with whether that logical IO request resulted in a physical IO.
SQL Server returns the information,at the end of the query,in error
message 3615.

观察到的行为表明,按照生成IO的顺序对数组进行了条目,这实际上是物理运算符上GetNext()的结果.统计信息输出中的最后一个条目是导致记录IO的第一个表,第一个条目是最后一个表.我推测并行计划的顺序是不可预测的(或者更少),因为不能保证首先安排哪个并行任务.

（编辑：92站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!