March 15, 2012 at 11:50 am
I typically use profiler to determine which queries have high reads and then take steps to evaluate what may be causing poor performance by looking into the execution plan. We have a query (below) that profiler reported as having 2000 reads. During the times it ran (with apparently not much else running on the system) it took between 3-8 seconds to run. I do know that this query
When I execute the same TSQL myself and turn on IO, the execution is practically instantaneous - and I see a completely different set of reads than what profiler reported:
Table 'Table_Secondary'. Scan count 0, logical reads 7208, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table_Primary'. Scan count 3, logical reads 99, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SET STATISTICS IO ON
Select MyDB.dbo.Table_Secondary.MBCMatch, MyDB.dbo.Table_Primary.TranId,
MyDB.dbo.Table_Primary.TranTime, MyDB.dbo.Table_Primary.PrimaryAccountNumber,
MyDB.dbo.Table_Secondary.OriginalAmount_, MyDB.dbo.Table_Primary.FleetNumber,
MyDB.dbo.Table_Secondary.ApprovalCode, MyDB.dbo.Table_Primary.CMTTRANTYPE,
MyDB.dbo.Table_Secondary.SettlementDate, MyDB.dbo.Table_Primary.CardAcceptorBusinessCode,
MyDB.dbo.Table_Primary.CardAcceptorTerminalID,
MyDB.dbo.Table_Primary.AuthTranId, MyDB.dbo.Table_Primary.TranRef
from MyDB.dbo.Table_Primary with (noLock) LEFT OUTER JOIN MyDB.dbo.Table_Secondary
with (noLock) ON ( MyDB.dbo.Table_Primary.TranId = MyDB.dbo.Table_Secondary.TranId )
where
(((MyDB.dbo.Table_Primary.CMTTRANTYPE = '40')
OR (MyDB.dbo.Table_Primary.CMTTRANTYPE = '41')
OR (MyDB.dbo.Table_Primary.CMTTRANTYPE = '43'))
AND ((MyDB.dbo.Table_Secondary.MBCMatch = '0')
OR (MyDB.dbo.Table_Secondary.MBCMatch = '1'))
AND (MyDB.dbo.Table_Primary.FleetNumber = '429252')
AND (MyDB.dbo.Table_Primary.CardAcceptorTerminalID = '423437')
AND (MyDB.dbo.Table_Secondary.OriginalAmount_ = 73))
What I want to understand is which one is more accurate and how can I determine this myself for any query that I want to tune?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 15, 2012 at 1:34 pm
I am certainly not an expert in this, but the profiler specifically looks at "logical Disk Reads" whereas the statistics IO show"logical reads." I am guessing it has something to do with the differences between these 2 things, not a matter of one being right or wrong.
Jared
CE - Microsoft
March 15, 2012 at 1:38 pm
From MS http://support.microsoft.com/kb/314648
In comparing the numbers reported by SQL Profiler with those from STATISTICS IO, it is important to note that logical and physical IOs may be incurred doing work that is related to your query or batch but not specific to the query itself.
Jared
CE - Microsoft
March 15, 2012 at 11:13 pm
Hope the wonderful blog by Gail shaw, is what you are looking for.
it is in 2 parts.
March 16, 2012 at 12:13 pm
Unfortunately no, I've read this great article in the past and while it's great for tuning, it doesn't discuss the difference between reads found in profiler with the reads returned from turning on IO STATS .
Thanks though!
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply