STATISTICS IO under-reports logical reads for parallel plans

STATISTICS IO under-reports logical reads for parallel plans by aaronbertrand

Resolved

as Fixed

Type:	Bug
ID:	767250
Opened:	10/12/2012 1:22:32 PM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

We recently stumbled on a scenario where if a plan uses TOP and parallelism, with a low enough DOP, SET STATISTICS IO ON produces incorrect and unreliable information. Thanks to Steve Wright and Thomas Stringer for helping me narrow in and focus on the true issue. Note that DBCC SETCPUWEIGHT is present only to ensure a parallel plan is used for this quite simplistic repro.

USE AdventureWorks2012;
GO

SET NOCOUNT ON;
SET STATISTICS IO ON;
DBCC SETCPUWEIGHT(1000) WITH NO_INFOMSGS;
GO

-- STATISTICS IO shows 4 logical reads, trace shows 420:
SELECT TOP 15000 * FROM Sales.SalesOrderHeader WHERE OrderDate < '20080101';

-- STATISTICS IO and trace both show 333 logical reads:
SELECT TOP 15000 * FROM Sales.SalesOrderHeader WHERE OrderDate < '20080101' OPTION (MAXDOP 1);

DBCC SETCPUWEIGHT(1) WITH NO_INFOMSGS;

Details (expand)

English

SQL Server 2012 - Enterprise Edition

SQL Engine

Not Applicable

US English

USE AdventureWorks2012;
GO

SET NOCOUNT ON;
SET STATISTICS IO ON;
DBCC SETCPUWEIGHT(1000) WITH NO_INFOMSGS;
GO

-- STATISTICS IO shows 4 logical reads, trace shows 420:
SELECT TOP 15000 * FROM Sales.SalesOrderHeader WHERE OrderDate < '20080101';

-- STATISTICS IO and trace both show 333 logical reads:
SELECT TOP 15000 * FROM Sales.SalesOrderHeader WHERE OrderDate < '20080101' OPTION (MAXDOP 1);

DBCC SETCPUWEIGHT(1) WITH NO_INFOMSGS;

Table 'SalesOrderHeader'. Scan count 1, logical reads 4, physical reads 0, ...
Table 'SalesOrderHeader'. Scan count 1, logical reads 333, physical reads 0, ...

Table 'SalesOrderHeader'. Scan count 1, logical reads 420, physical reads 0, ...
Table 'SalesOrderHeader'. Scan count 1, logical reads 333, physical reads 0, ...

X64

0 attachments

Posted by Microsoft on 5/3/2013 at 1:10 PM

Hello.
We are sorry for the confusion. We recognize this as a product bug, and fixed it for the future release of SQL server. Unfortunately, it is not yet available for SQL2012. We are evaluating whether this fix can be made in the next SQL2012 service pack release. Thank you very much for your contribution to making SQL server better.

Posted by aaronbertrand on 4/25/2013 at 9:17 AM

Resolved as fixed? When? Where? Any further comments?

Posted by Microsoft on 10/26/2012 at 11:02 AM

Thanks for bringing this issue to Microsoft. We are looking into this request

Posted by Paul White NZ on 10/14/2012 at 6:34 PM

Another AdventureWorks repro, but which does not require messing about with weights:

SELECT TOP (15000)
th.*
FROM Production.TransactionHistory AS th
WHERE
th.TransactionDate < (SELECT '20080101')
OPTION (QUERYTRACEON 8649, MAXDOP 4);

I found the issue occurs much less frequently at higher DOP, hence the MAXDOP hint.