Post reply

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

  • 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)

  • January 26, 2016 at 3:05 pm

    #1854568

    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

  • January 26, 2016 at 3:24 pm

    #1854575

    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

  • January 27, 2016 at 8:42 am

    #1854743

    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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

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

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