June 8, 2007 at 8:57 am
I'm doing a lot of performance tuning on the stored procedures in our system, and have been noticing a big discrepancy between the no of Reads that SQL Profiler tells you about, and the stats you get in Query Analyzer if you turn "set statistics io" on.
The most significant example I've just found is if I select 30,000 rows from a table that has 88 columns and has a row width of about 7K. Query Analyzer tells me:
Table 'Event'. Scan count 1, logical reads 5965, physical reads 0, read-ahead reads 0.
but if I run a trace using SQL Profiler, it gives me a Reads figure of 284,420 which seems a lot more realistic. Has anyone come across this kind of thing before, and can you point me in the right direction to explain the difference? My strategy up until now has been to rely on Profiler to tell me what's performing badly, then use Query Analyzer to do the detailed work of analysing the problems, but it now appears like Analyzer might not be telling me everything!
Thanks in anticipation,
Dave
Dave J
June 11, 2007 at 5:23 am
Exactly the same issue (and bizarrely almost identical counts from Profiler and QA) cropped up here recently. I've been scratching my head and getting no where so far.
June 11, 2007 at 1:11 pm
June 12, 2007 at 1:55 am
Thanks Lucy, that article makes sense - it doesn't really explain the huge differences I'm seeing though. Discrepancies in the hundreds, or even a few thousand, I can understand and live with, but what I'm seeing making reliable performance analysis a bit tricky! Nice to know I'm not alone Mark - I'll update this thread if I come across any more info.
Dave J
June 15, 2007 at 6:48 am
oh and when using profiler you might want to turn off io stats and show plan in QA
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
June 15, 2007 at 6:48 am
You should make sure you consistantly use one method or the other. Profiler will report the use of worktables which QA will not. In certain circumstances profiler shows the true cost of parallel io which QA does not.
Add a maxdop to your query to see if this is the case, you'll see a change in profiler io. If you have distincts, group by and order by statements ( for example ) then these may well create work tables in tempdb, most cases QA doesn't show this io, profiler does.
Got to be honest I thought everyone was well aware of this one now.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
June 15, 2007 at 7:00 am
Thanks Colin. In my experience QA usually does report worktable stats, but anyway in the case of the query I was using ("select * from table"), there are no disincts, group bys, order bys, or anything else that should be using tempdb. Also I did what you suggested with max degree of parallelism, and still have the massive discrepancies in my results.
Dave J
June 15, 2007 at 7:29 am
a single table select or a join ? A join may still create worktables.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
June 15, 2007 at 7:41 am
No literally, it's just "select * from event" ! It's an extreme example both due to it's simplicity and the size of the discrepancy, but very useful to illustrate my problem... of course usually I'm dealing with large multi-table joins, and the thing that concerns me is when fiddling with a complex query to try different ways of making it more efficient, I'd like to be able to rely on QA telling me everything that's going on.
Dave J
June 15, 2007 at 8:36 am
I've seen discrepancies as well, though I dont think that extreme. I use QA/SSMS to view the plans, Profiler to measure performance. With two monitors it works well and I just dont see the difference that way. Whether you use QA or Profiler, as long as you use the same tool during the session you get apples to apples. Doesnt explan the problem of course, I just elected to go around and not worry with it!
June 15, 2007 at 9:18 am
I agree Andy, I use both methods, there's something I can't just remember about why some of this is so, I've just scanned a 200k row table and get figures which don't match, however if I scan down an index the pages are near enough the same - It may relate to order of the data and fragmentation, QA reports the same number of io as there are pages in a dbcc showcontig, but profiler doesn't agree - however on more selective queries I get the same figures, near enough, it just seems on scans I get this problesm. If you have text data then you'll get more io reported .. if I get to figure it out/remember than I'll post back about it
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply