Same query produces dramatically different plans with identical data but one different table name?

Question

Same query produces dramatically different plans with identical data but one different table name?

DB_Newbie2007

SSCarpal Tunnel

Points: 4449
More actions
January 26, 2016 at 2:31 pm

#323880

I was try to test the impact of adding a "covering query" and a "filtered query" on a query to help eliminate a large table scan. Attached is an "anonymized" using SQL Sentry Plan Explorer... sorry... but this still should be good to see the difference between the top and bottom queries?
1. Take Object16 (2nd query), make a copy (Object9).
2. Create Clustered index, foreign keys to new Object9 (same index/keys are Object16).
3. Copy data from Object16 into Object9.
4. Add non-clustered covering index, filtered index to Object9.
• Compare query plans (query with Object9 still using the clustered index).
5. Switch order of queries (original becomes 2nd query).
• Compare query plans (no change).
6. Add same non-clustered covering index, filtered index to Object16.
• Compare query plans (no change).
7. Update statistics, check index fragmentation.
• Compare query plans (no change).
The thing I am having problems with is why Object12 and Object14 in the first query look so much different (different = actual rows) than the same Object12 and Object14 in the second query, as well as why the query plans look so different, if the only change in the query was one table?
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)

Viewing 4 posts - 1 through 3 (of 3 total)

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

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

You did not attach anything. Can you attach the two versions of the query plans using just SSMS. I'm sure we can get to the bottom of this.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

Query plans will definitely help. As will table and index definitions.

Look for data value skew.

Also note that filtered indexes have ALL KINDS of limitations, gotchas, caveats, provisos, bugs, etc. They are a feature that COULD have been great, but sadly never got sufficient loving after initial release to become so. 🙁

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

Grant Fritchey SSC Guru Points: 398910 More actions · Answer 3

There are differences in the estimates. That's going to lead to differences in the plans. So, I'd focus on the statistics. Open them up and see if they're different. If they're the same... then this is weird. But I'll bet they're different.

"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