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

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


12
0
Sign in
to vote
ID 767250 Comments
Status Closed Workarounds
Type Bug Repros 5
Opened 10/12/2012 1:22:32 PM
Access Restriction Public

Description

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;
Sign in to post a comment.
Posted by Microsoft on 9/3/2014 at 3:15 PM
This is fixed in SQL2012 SP2 and SQL2014. Thanks.
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.