Home Dashboard Directory Help
Search

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


Status: 

Closed
 as Fixed Help for as Fixed


12
0
Sign in
to vote
Type: Bug
ID: 767250
Opened: 10/12/2012 1:22:32 PM
Access Restriction: Public
0
Workaround(s)
view
5
User(s) can reproduce this bug

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;
Details
Sign in to post a comment.
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.
Sign in to post a workaround.