Post reply

Execution Plan

  • April 3, 2014 at 4:28 pm

    #291914

    Hi,

    I saw Index scan in execution plan. The table has primary key. We are selecting that PK data based on where condition by using and & and clauses on the remaining columns.

    I have the first where clause columnn is integer datatype. SO I created non-clustered index on that column. Now clustered Index scan become Index seek and key lookup.

    Which is better scan or creating index and change to key lookup? please let me know

  • April 3, 2014 at 4:34 pm

    #1703133

    Why dont you add the column(s) in the key lookup to your index as included columns ?

    This will most likely still allow an index seek and remove the key lookup altogether.

  • April 3, 2014 at 4:58 pm

    #1703142

    Key lookup is bad, instead you can avoid this by using a covering index by including all those referred columns in the index itself.

    --

    SQLBuddy

  • April 4, 2014 at 3:19 am

    #1703214

    It is true that Key Lookup can be avoided by converting your index to covering index. But, you should be careful! If you include too much columns you will actually create a copy of your table. If you do this several times for several indexes you can end up with index size which is more time larger than the table itself. And performance for DML queries against your table can be dramatically reduced.

    So, just simply adding included columns in an index to avoid Key Lookup is not always a solution. You can use it for to improve a query which is very frequently used and if you don't need to include more than 2-3 columns.

    ___________________________
    Do Not Optimize for Exceptions!

  • April 4, 2014 at 3:53 am

    #1703223

    sqlbuddy123 (4/3/2014)

    Key lookup is bad, instead you can avoid this by using a covering index by including all those referred columns in the index itself.

    Key lookup might be bad. You should evaluate and decide whether or not the key lookup is a problem. If it is, then and only then should you look for solutions

    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

  • April 4, 2014 at 5:52 am

    #1703255

    If you have the clustered index key value in the WHERE clause and you're still getting a scan, the suggestion is that you must have one of two situations, your statistics are badly out of date on that index, so it doesn't appear useful to the optimizer, or, you have some type of function on the column that is causing the scan. If you can post the query we might be able to help.

    But, your current situation, with a key lookup instead of a scan, might be better. How fast is the query running and how many reads are you seeing now?

    "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

  • April 7, 2014 at 9:29 pm

    #1704014

    I am sorry. I couldn't able to compare the timing.

  • April 7, 2014 at 10:56 pm

    #1704033

    Undo whatever you did so you can run the code below to get a baseline (as Gail and Jeff stated, your keylookup may not be a bad thing - you need to test it). Try running:SET STATISTICS IO ON;

    exec [query or your procedure here]

    SET STATISTICS IO OFF;

    Go to this wonderful site[/url] and paste the output into the window and click the magic button to view your reads...

    Make your desired index changes (or remove any functions, etc in your WHERE clause predicates), then run the code again.

    Go back to the website above, and compare your reads

    If timing is an issue, add TIME to the SET statement above (ex:)SET STATISTICS IO, TIME ON;

    exec [query or your procedure here]

    SET STATISTICS IO, TIME OFF;

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • April 13, 2014 at 9:44 pm

    #1705627

    Hey,

    Providing the output in to that website means I am breaking the company rules right

  • April 13, 2014 at 10:44 pm

    #1705629

    ?

    It's just the output of reads...in a more readable format, there's no actual "data"...

    Hardly something to be worried about.

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • April 14, 2014 at 8:51 pm

    #1705970

    sqlbuddy123 (4/3/2014)

    Key lookup is bad, instead you can avoid this by using a covering index by including all those referred columns in the index itself.

    --

    SQLBuddy

    To reiterate what has already been said and to add some other items to consider... Depending on what's most important to you, covering indexes can be really bad. They're great for query performance but ...

    1. They're tough on Inserts, Updates, Deletes, Merges

    2. All non-clustered indexes are basically a duplication of data sorted in a different manner. That means that you can easily have tables that are (for example) 10% data and 90% index. If you have 20GB of data, that kind of ratio can start making things tough on disk space.

    3. Because of item 2 above, backups will also take more disk (if you backup to disk first) and tape space.

    4. Because of item 2 and 3, restores can suddenly take a whole lot more time.

    5. Because of item 2 above, you can also expect your log file to be larger.

    6. Because of item 2 above, other nightly maintenance, such as index reorgs/rebuilds and statistics updates can suddenly take a whole lot longer and the reorgs will also cause the log file to grow.

    Before you use a covering index, check the items above and make sure the tradeoff is actually worth avoid a couple of key lookups. Like Gail suggested, key lookups are not something you always want to avoid.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • April 15, 2014 at 9:14 am

    #1706151

    Jeff, it's a statement made in general as we didn't have the specifics. It's only a suggestion to consider. We can make it a long academic discussion if needed.

    --

    SQLBuddy

  • April 15, 2014 at 11:00 am

    #1706187

    Hi,

    Correct me if I am wrong. I think covering Index is nothing but adding columns that are participating in the where clause right? What about the include columns. What is the main difference for covering index and included columns?

    Thanks,

    Ramana

  • April 15, 2014 at 11:10 am

    #1706197

    ramana3327 (4/15/2014)

    Hi,

    Correct me if I am wrong. I think covering Index is nothing but adding columns that are participating in the where clause right? What about the include columns. What is the main difference for covering index and included columns?

    Thanks,

    Ramana

    You have that backwards. Your key columns (the main columns of your index) should reference your WHERE clause predicates and your SELECT columns are the ones you would INCLUDE to cover.

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Viewing 14 posts - 1 through 13 (of 13 total)

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