Is the SQL Server Profiler Reads Column Incorrect For Parallel Plans?

Question

Is the SQL Server Profiler Reads Column Incorrect For Parallel Plans?

Tim Walker.

SSCertifiable

Points: 5173
More actions
August 19, 2015 at 6:04 am

#299786

Is the SQL Server Profiler Reads Column Incorrect For Parallel Plans?
I often use profiler as one tool to help identify bad plans. The reads column gives me a good indication of excessive IO to dig into and correct if necessary. I often use it with Showplan so I can see what a query does, replicate it and fix it.
However I have just lost some faith in it. I am looking at a poorly performing query joining five tables. A parallel plan has been generated and one table is being scanned (in parallel) due to a missing index. This table had in excess of 4 million rows in it. The rest hitd indexes well. However the entire query generates ONLY 12 READS.
Once corrected, a single processor plan is used. This looks really efficient and uses 120 reads. That looks the right figure to me.
Clearly 12 reads is wrong. Can anyone shed any light on this? Does the profiler only display one thread of a parallel plan perhaps? Or something else?
I'm a bit worried now that I can't trust some of he statistics I base my decisions on.
(I do use a number of other techniques, such as DMVs, too)
Thanks
Tim
.

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

Tim Walker. (8/19/2015)
Is the SQL Server Profiler Reads Column Incorrect For Parallel Plans?
I often use profiler as one tool to help identify bad plans. The reads column gives me a good indication of excessive IO to dig into and correct if necessary. I often use it with Showplan so I can see what a query does, replicate it and fix it.
However I have just lost some faith in it. I am looking at a poorly performing query joining five tables. A parallel plan has been generated and one table is being scanned (in parallel) due to a missing index. This table had in excess of 4 million rows in it. The rest hitd indexes well. However the entire query generates ONLY 12 READS.
Once corrected, a single processor plan is used. This looks really efficient and uses 120 reads. That looks the right figure to me.
Clearly 12 reads is wrong. Can anyone shed any light on this? Does the profiler only display one thread of a parallel plan perhaps? Or something else?
I'm a bit worried now that I can't trust some of he statistics I base my decisions on.
(I do use a number of other techniques, such as DMVs, too)
Thanks
Tim

No, profiler will report parallel seek/scan reads correctly. I note that single and parallel plans can be VERY different. I would like to see both actual execution plans please.

A problem here is that you describe a scan of a 4M row table as taking either 12 or 120 reads. NEITHER of those can POSSIBLY be correct! You can't fit 4M rows of any table into 120 pages. Thus I question your understanding of what is actually happening with your plans.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Tim Walker. SSCertifiable Points: 5173 More actions · Answer 2

Hi Kevin

thanks for your reply.

I didn't say that though. I said I had corrected the plan so it now does an efficient B-Tree seek on the offending table so it only needs a couple of rows to hit the right details. These are then joined efficiently to 4 other tables. This gives 120 reads.

I'm saying that 120 reads seems about right to me to read a few rows from 5 tables efficiently.

The bad query should be much worse. I agree with you that 13 reads (correction - last time I said 12) cannot possibly be right to read 4M rows - hence my question!

I've attached a screen print of the profile in question.

.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

Can't do anything with a picture. But a scan doesn't necessarily mean what you think sometimes. What is the actual row count entering that table on the loop? What is the object being used for the actual access? What does SET STATISTICS IO ON show as accesses for every table in the query?

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Please post the execution plan. A picture doesn't have all the properties needed.

It's worth noting that a scan doesn't have to be the entire table, and that's probably what's happening in both plans. Operators abort as soon as they return all the rows that the operator above asks for. If there's a max, min, top, rowcount, offset/fetch or a few other things involved, the scan can easily abort after only reading a page or two.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Couple other things.

Don't use the Profiler GUI against a production server. It can and will cause problems, up to and including crashing the server (I've done it). Statistics profile is a very heavy event, be careful running traces with it unless filtered right down, again you can cause problems for the server you're tracing.

Since you're on SQL 2012, rather use Extended Events. They're lighter than a profiler trace, have less effect on the server.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Tim Walker. SSCertifiable Points: 5173 More actions · Answer 6

GilaMonster (8/19/2015)
Don't use the Profiler GUI against a production server. It can and will cause problems, up to and including crashing the server (I've done it). Statistics profile is a very heavy event, be careful running traces with it unless filtered right down, again you can cause problems for the server you're tracing.

As a sidebar I agree it can be a heavy event but occasionally I do it strictly filtered by either Object ID / DB_ID (for an SP) or SPID (for a session re-creating a fault). Whilst not ideal this seems tolerable to me for short durations.

.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

Tim Walker. (8/19/2015)
GilaMonster (8/19/2015)
Don't use the Profiler GUI against a production server. It can and will cause problems, up to and including crashing the server (I've done it). Statistics profile is a very heavy event, be careful running traces with it unless filtered right down, again you can cause problems for the server you're tracing.
As a sidebar I agree it can be a heavy event but occasionally I do it strictly filtered by either Object ID / DB_ID (for an SP) or SPID (for a session re-creating a fault). Whilst not ideal this seems tolerable to me for short durations.

I concur with this, and do it all the time - although not with the heavy columns you are grabbing. My performance tuning profiler settings are exceptionally light-weight.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Tim Walker. SSCertifiable Points: 5173 More actions · Answer 8

GilaMonster (8/19/2015)
Please post the execution plan. A picture doesn't have all the properties needed.
It's worth noting that a scan doesn't have to be the entire table, and that's probably what's happening in both plans. Operators abort as soon as they return all the rows that the operator above asks for. If there's a max, min, top, rowcount, offset/fetch or a few other things involved, the scan can easily abort after only reading a page or two.

Thanks for that feedback, I do get that a scan can bail out early, or very early, but the figures don't smell right to me.

I'll have to think about the best way to obtain the stats (since it is now fixed) to give you some more information.

Thanks

Tim

.

Tim Walker. SSCertifiable Points: 5173 More actions · Answer 9

Sorry guys I've just checked the plan and there are client specific details in it so I can't post it.

Thanks for your input anyway, I appreciate it.

Thanks

Tim

.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Open the XML in a text editor, do a find/replace for table and column names. Save, make sure it still loads in SSMS, if so post it.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Tim Walker. SSCertifiable Points: 5173 More actions · Answer 11

Tim Walker.

SSCertifiable

Points: 5173

August 19, 2015 at 7:44 am

#1821048

Good Tip!

Attached...

.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

Actual rows 0, so not 100% sure, but I suspect what's happening is that the TOP only ends up requesting one row from the stream aggregate. Stream aggregate isn't a blocking operator, so it only requests rows from the next loop down until the Shipping Number changes, each loop requests one row at a time from the operators below them, so most likely that clustered index scan aborts after reading just a few rows, hence the low reads.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Tim Walker. SSCertifiable Points: 5173 More actions · Answer 13

That's very interesting Gail. I'll have a think about that, it certainly seems to answer the question.

Thanks for your help. I very much appreciate it.

Best Regards

.

Eric M Russell SSC Guru Points: 125562 More actions · Answer 14

Are you looking at logical or physical read counts? The two combined should sum near the same with two identical plans, but balance between them may differ depending on how many pages required by the query are cached in the buffer at time of execution.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho