Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

SQL Server graphical execution plans read right to left and top to bottom. Is there a meaningful order to the output generated by SET STATISTICS IO ON?

The following query:

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;

Generates this plan:

Graphical execution plan

And this STATISTICS IO output:

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 logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderHeader'. Scan count 1, logical reads 689, physical reads 1, read-ahead reads 685, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Product'. Scan count 1, logical reads 15, physical reads 1, read-ahead reads 14, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

So, I reiterate: what gives? Is there a meaningful ordering to STATISTICS IO output or is some arbitrary order used?

share|improve this question
3  
Not that I've been able to tell. In fact, it will even combine multiple uses of the same table into a single output for the table. For example, if your plan showed multiple queries on it, the IO for all queries would be combined together rather than showing a separate set of output for each query. –  Robert L Davis Aug 7 '13 at 14:16
    
@RobertLDavis That is not my experience. I see multiple instances of statistics IO for a table that match the ordering of the queries in the code. –  RLF Aug 7 '13 at 15:34
3  
This is a total guess, but does degree-of-parallelism change the STATISTICS IO output order at all? Perhaps the listing is in order of data returned by the database engine? –  Max Vernon Aug 7 '13 at 18:31
    
@MaxVernon Any change to the plan changes the STATISTICS IO output. It may very well be order returned to the engine. –  Jeremiah Peschka Aug 7 '13 at 19:06
add comment

4 Answers

My initial playing around with various queries suggested no pattern at all but on paying closer attention it appears to be predictable for serial plans. I ended up at KB314648 which @AustinZellner mentions:

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.

And the section relevant to your question:

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.

The observed behaviour suggests that entries are made to the array in the order that IO is generated, essentially the result of GetNext() on a physical operator. The last entry in the statistics output is the first table that resulted in an IO being recorded, the first entry is the last table. I’d speculate that the order for parallel plans is not predictable (or less so) as there is no guarantee as to which parallel task will be scheduled first.

share|improve this answer
add comment

Looks to me like it is the opposite order of data read access in the plan. Your plan will first read from the Product table to build the hash table (worktable). Than it reads from SalesOrderHeader and form SalesOrderDetail combining them with the merge join operator. The worktable then is read from last to hash-match the original Product rows with those from the merge join. That is the exact opposite order in which they are listed in your statistics output.

However, I am not aware of any documentation that would specify this. If you want to be sure in what order table access happened, read the execution plan.

share|improve this answer
    
In this case it comes through in an opposite order, in others it's different. I suspect that there is no order that's discoverable without intimate knowledge of the engine that isn't generally available to the public. –  Jeremiah Peschka Aug 7 '13 at 19:03
    
Do you have an example of where it is in a different order? –  Sebastian Meine Aug 7 '13 at 19:33
    
SELECT * FROM Sales.SalesOrderHeader AS soh JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID LEFT JOIN Sales.SalesPerson AS sp ON soh.SalesPersonID = sp.BusinessEntityID LEFT JOIN Person.Person AS p2 ON sp.BusinessEntityID = p2.BusinessEntityID JOIN Production.Product AS p ON sod.ProductID = p.ProductID; –  Jeremiah Peschka Aug 7 '13 at 20:19
    
As long as no parallelism is involved my observation holds true. You can run your query with a TOP(100), TOP(1000) and TOP(10000) to see serial plans. However, with TOP(100000) or without TOP you get two different parallel plans and there all bets seem to be off. –  Sebastian Meine Aug 7 '13 at 21:03
add comment

I always thought it had an order, from back when I did more programming than administration. I ran through a few execution plans and double checked my beliefs.

Here is what I see:

In a multi-step query (such as many of our stored procedures) the order reflects the physical order in which the queries are run.

For a particular query, it looks like the statistics IO reflect the execution plan by reporting statistics starting at from the right and working to the left

Perhaps this is more of an observation than anything else.

share|improve this answer
2  
Might be something in this. Reversing the order of the tables in SELECT COUNT(*) FROM HumanResources.EmployeeDepartmentHistory UNION ALL SELECT COUNT(*) FROM HumanResources.Employee UNION ALL SELECT COUNT(*) FROM HumanResources.Department also reverses the IO output but it doesn't explain why the work table is reported first in the example in the question. –  Martin Smith Aug 7 '13 at 14:40
    
@MartinSmith Yes, the worktable is a wild card from my limited perspective. –  RLF Aug 7 '13 at 15:30
add comment

So I think that the results of statistics io give much more insight into what is actually happening at runtime, as it will take into account and be affected by the need to read from disk instead of cache, and also be influenced by permissions of the account that the query is being run under. The position of the table in the statistics return then is influenced by other factors than those considered by the profiler.

Here is a kb article that gives the insight and some examples: http://support.microsoft.com/kb/314648

share|improve this answer
1  
The question is not about the output of STATISTICS IO in general. It is purely about the order that the reads of the various tables are reported. I don't see anything about this in your link. –  Martin Smith Aug 8 '13 at 15:30
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.