none
Query Execution plans - Tuning suggestion RRS feed

All replies

  • Hi Kenny shareNlearn,

     

    Thank you for your posting.

     

    In your execution plan which you provide , there are some key lookup. 

     

    When we were looking at the index scan and table scan section we were able to eliminate the scan which was replaced with an index seek, but this also introduced a Key Lookup which is something else you may want to eliminate to improve performance.

     

    A key lookup occurs when data is found in a non-clustered index, but additional data is needed from the clustered index to satisfy the query and therefore a lookup occurs.  If the table does not have a clustered index then a RID Lookup occurs instead.

     

    For more information about Key Lookup , please refer to Eliminating bookmark (key/rid) lookups.

     

    And in this link , it will advise to create a covering index to cover the query by including all of the columns that are needed to eliminate Key Lookup.  Please try following script to change your index.

    ALTER NONCLUSTERED INDEX [ix4_pcwdeptrans] ON [pcwdeptrans] (id)  INCLUDE (statecode)
    ALTER NONCLUSTERED INDEX [ix2_pcwdepreceipt] ON [pcwdepreceipt] (id,deposittransid)  INCLUDE (requesttime)
    ALTER NONCLUSTERED INDEX [ix2_pcwitemtotal] ON [pcwitemtotal] (id)  INCLUDE (statecode,createdate,userid)

     

    Hope it can help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, April 23, 2019 7:04 AM
  • Thanks for the detailed explanation.

    I have couple of doubts, 

    By the below mention you mean you tested in your environment ? thanks for clearing my confusion.

    1) When we were looking at the index scan and table scan section we were able to eliminate the scan which was replaced with an index seek, but this also introduced a Key Lookup 

    The keylookup from the query plans doesn't have any cost attached to it, except for one query (17%) rest i see the estimated no.of rows, actual executions and subtree cost are noraml ? is my understanding wrong... can you please shed some light to help me understand what are the key aspects and how do you analyze the query plans in general.

    2) Key Lookup which is something else you may want to eliminate to improve performance.

    NOTE: And does the amount of data has huge change in execution plans or just the cost ? what role does the amount of data/size of DB play in execution plans.


    Kenny ShareNlearn

    Tuesday, April 23, 2019 12:32 PM
  • Hi Kenny shareNlearn,

     

    Thank you for your reply.

     

    Firstly, by the above mention I didn't test in my environment. As we know, the  execution plan is related your actual environment, like your actual table structure ,actual data and so on. So the advice I give might not work, you need to have a try.

     

    Secondly, you said that 'The Key Lookup from the query plans doesn't have any cost attached to it'. I think you can do a simple example to compare the performance between  Key Lookup and no Key Lookup.  And in following article, you can see the difference clearly.  Eliminating bookmark (key/rid) lookups

     

    Thirdly, a Key Lookup occurs when SQL uses a nonclustered index to satisfy all or some of a query’s predicates, but it doesn’t contain all the information needed to cover the query. Key Lookups effectively join the nonclustered index back to the clustered index, on the clustered index key columns to retrieve the necessary information. Key Lookups are always tied to Nested Loops Joins, executing once for each row that needs to be retrieved.  So in my advice , I try to create an Index with included columns.

     

    Finally, when the optimizer determines the query plan, it assigns a cost to each possible plan and then chooses the plan with the lowest cost. The optimizer analyzes several factors to determine the cost of each query plan , such as a set of statistics(data),query filters ,actual environment and so on. So the amount of data might have huge change in execution plans.

     

    To analyze query plans in general, I mainly look at whether the index is being used effectively, for example 'Index seek'. Try to eliminate index scan ,table scan and Key Lookup. And then check the performance.

     

    By the way, you can 'SET STATISTICS IO ON; ' to display information regarding the amount of disk activity generated. Please compare the messages and you will see the difference. For more details , you can refer to it: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-statistics-io-transact-sql?view=sql-server-2017

     

    Hope it can help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, April 24, 2019 7:13 AM