Why is a Table Scanned and Read So Much More than Others

Question

Post reply

Why is a Table Scanned and Read So Much More than Others

souLTower

SSCarpal Tunnel

Points: 4026
More actions
March 3, 2016 at 7:19 am

#325331

This is more of a theoretical question. I have 2 tables which joined together represent a user and transactions. I have a third table with information about the transaction. There is a 1-1 relationship between the transaction and information table. The information table is small, 1000 records. The transaction table is 13M records.
In troubleshooting a performance issue I see that the information table is being scanned 500K times with a logical read of 1.6M times. Comparatively this is roughly 10 times more than the other tables.
I can see no reason for this. Regardless of how I change the query I get the same numbers. I've fixed my performance issue but am puzzled by this.
Thanks for any thoughts
ST

Viewing 10 posts - 1 through 9 (of 9 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Is the information table indexed to support your queries?

Can you provide an actual execution plan?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Bill Talada SSChampion Points: 11954 More actions · Answer 2

Theoretically...

We don't know the precise algorithms used by the query optimizer but I have seen similar behavior. If you only have 1000 rows, then likely they fit on a few pages. It is nothing for a CPU to scan a few 8K pages instead of sql server resorting to more complex index usage and row lookups processing, etc. Even covering indexes are often ignored over going directly to the source when the data is small.

souLTower SSCarpal Tunnel Points: 4026 More actions · Answer 3

Yes, the index supports the query. Unfortunately the example is too far in the weeds to send an execution plan. It's just a curiosity.

On a related note, what does it mean in an execution plan when the estimated rows is different than the actual number of rows? I know that the sorting memory is allocated based on the estimate and I don't think I'm having a huge issue with it. Is there a trick for bringing these values closer together?

Y.B. SSChampion Points: 11649 More actions · Answer 4

souLTower (3/3/2016)
Yes, the index supports the query. Unfortunately the example is too far in the weeds to send an execution plan. It's just a curiosity.
On a related note, what does it mean in an execution plan when the estimated rows is different than the actual number of rows? I know that the sorting memory is allocated based on the estimate and I don't think I'm having a huge issue with it. Is there a trick for bringing these values closer together?

Could be that your statistics are out of date.

SELECT quote FROM brain WHERE original = 1
0 rows returned

Bill Talada SSChampion Points: 11954 More actions · Answer 5

Statistics are maintained on the distribution of values in an index, for some values - not all. Since an actual distribution of values may follow a strange curve instead of a level line, it is impossible for sql server to know if your precise value appears often or rarely.

souLTower SSCarpal Tunnel Points: 4026 More actions · Answer 6

Thanks for the responses. I always appreciate the expertise.

ST

ScottPletcher SSC Guru Points: 101091 More actions · Answer 7

What is really critical is how the information table is clustered. Insure the table has the best clustering index to reduce rows to be read as much as possible based on common criteria in SELECTs.

Don't automatically assume that it's ok to scan an entire table just because it's only 1,000 (or 2,000) rows. Firstly, tables can grow, sometimes surprisingly quickly. And secondly, that result set could be part of loop joins later, magnifying the effect of additional rows.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Grant Fritchey SSC Guru Points: 398881 More actions · Answer 8

Without seeing the execution plan to understand how the optimizer is resolving the query, it's really hard to know. It's possible that it chose a Nested Loops when a Hash might be a better choice. Just guessing.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Gazareth One Orange Chip Points: 27737 More actions · Answer 9

Grant Fritchey (3/3/2016)
It's possible that it chose a Nested Loops ... Just guessing.

That'd be my guess too.