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

Question

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

Viewing 5 posts - 16 through 19 (of 19 total)

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

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

Eric M Russell (8/20/2015)
The two combined should sum near the same with two identical plans

The logical reads should be the same with identical plans. The physical reads may not.

It's not an either-or situation, if a query needs a single page and that page is not in memory, stats IO will show 1 physical read, 1 logical read (page read from disk to memory, page read from memory to satisfy the query). If the query is run again immediately, there'll be 1 logical read, 0 physical 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

Eric M Russell SSC Guru Points: 125522 More actions · Answer 2

GilaMonster (8/20/2015)
Eric M Russell (8/20/2015)
The two combined should sum near the same with two identical plans
The logical reads should be the same with identical plans. The physical reads may not.
It's not an either-or situation, if a query needs a single page and that page is not in memory, stats IO will show 1 physical read, 1 logical read (page read from disk to memory, page read from memory to satisfy the query). If the query is run again immediately, there'll be 1 logical read, 0 physical reads

That's right, it's the ratio between the two, and logical reads should be the same. I don't know why I was thinking page counts between the two were exclusive earlier. Anyway, I just wanted to confirm if he is looking at physical page reads, which can vary depending on cache.

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

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

Eric M Russell (8/20/2015)
GilaMonster (8/20/2015)
Eric M Russell (8/20/2015)
The two combined should sum near the same with two identical plans
The logical reads should be the same with identical plans. The physical reads may not.
It's not an either-or situation, if a query needs a single page and that page is not in memory, stats IO will show 1 physical read, 1 logical read (page read from disk to memory, page read from memory to satisfy the query). If the query is run again immediately, there'll be 1 logical read, 0 physical reads
That's right, it's the ratio between the two, and logical reads should be the same. I don't know why I was thinking page counts between the two were exclusive earlier. Anyway, I just wanted to confirm if he is looking at physical page reads, which can vary depending on cache.

Profiler shows logical reads and should be consistent each time given the same plan and data,

I think physical reads can be really misleading in query optimisation. I've lost count of the number of times I've had to tell people that a query isn't good just because it runs fast (the second time)!

Thanks

Tim

.

Eric M Russell SSC Guru Points: 125522 More actions · Answer 4

Tim Walker. (8/21/2015)
Eric M Russell (8/20/2015)
GilaMonster (8/20/2015)
Eric M Russell (8/20/2015)
The two combined should sum near the same with two identical plans
The logical reads should be the same with identical plans. The physical reads may not.
It's not an either-or situation, if a query needs a single page and that page is not in memory, stats IO will show 1 physical read, 1 logical read (page read from disk to memory, page read from memory to satisfy the query). If the query is run again immediately, there'll be 1 logical read, 0 physical reads
That's right, it's the ratio between the two, and logical reads should be the same. I don't know why I was thinking page counts between the two were exclusive earlier. Anyway, I just wanted to confirm if he is looking at physical page reads, which can vary depending on cache.
Profiler shows logical reads and should be consistent each time given the same plan and data,
I think physical reads can be really misleading in query optimisation. I've lost count of the number of times I've had to tell people that a query isn't good just because it runs fast (the second time)!
Thanks
Tim

Sometimes the issue is not necessarily query optimization. If a query plan is much slower on occasion, and a baseline comparison of query stats shows that logical reads remains fairly constant while physical reads has increased dramatically, then that could indicate a problem with memory pressure.

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

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

Eric M Russell (8/21/2015)
Tim Walker. (8/21/2015)
Eric M Russell (8/20/2015)
GilaMonster (8/20/2015)
Eric M Russell (8/20/2015)
The two combined should sum near the same with two identical plans
The logical reads should be the same with identical plans. The physical reads may not.
It's not an either-or situation, if a query needs a single page and that page is not in memory, stats IO will show 1 physical read, 1 logical read (page read from disk to memory, page read from memory to satisfy the query). If the query is run again immediately, there'll be 1 logical read, 0 physical reads
That's right, it's the ratio between the two, and logical reads should be the same. I don't know why I was thinking page counts between the two were exclusive earlier. Anyway, I just wanted to confirm if he is looking at physical page reads, which can vary depending on cache.
Profiler shows logical reads and should be consistent each time given the same plan and data,
I think physical reads can be really misleading in query optimisation. I've lost count of the number of times I've had to tell people that a query isn't good just because it runs fast (the second time)!
Thanks
Tim
Sometimes the issue is not necessarily query optimization. If a query plan is much slower on occasion, and a baseline comparison of query stats shows that logical reads remains fairly constant while physical reads has increased dramatically, then that could indicate a problem with memory pressure.

That's true, but a common cause of that is a badly behaved query elsewhere trashing the cache by reading a load of irrelevant data into it.

I favour the simple approach of trying to keep everything as efficient as possible. Minimise the logical reads everywhere, get a good cache hit ratio, minimise my physical reads.

If I've still got a problem after that, only then is it time to spend some money on hardware. Usually memory.

Thanks

Tim

.