Post reply

Performance Issue - understanding query plans

  • February 7, 2013 at 4:06 pm

    #1585690

    nick.mcdermaid (2/7/2013)

    Thanks for your advice Scott.

    I did change the PK to

    RowNum_Start,SRC_System, SRC_Application

    and also

    SRC_System, SRC_Application,RowNum_Start

    (Yes I know you can create a clustered index that isn't based on the primary key)

    In both cases it was still stuck on the old query plan unless I removed SRC_System from the join. I do hear what you're saying about don't get into the habit of just clustering on the primary key every time, that's good advice.

    There are probably more benefits to be had there but unfortunately my time to optimise this has run out. I'll learn some more next time I have this kind of issue (there will definitely be a next time).

    Just changing the PK won't help, it must be clustered properly to get real performance gains.

    I'd need more info/stats to know the best order for the columns, including cardinality and which column(s) are (almost) always specified in joins.

    >> In both cases it was still stuck on the old query plan unless I removed SRC_System from the join <<

    Yes, because if it's not the clus index, every column used must be in that index or SQL will revert to a table scan. For the clus index, SRC_System would just be a Predicate added to the clus index seek.

    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".

Viewing post 31 (of 30 total)

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