to get rid of key lookups, do I add all the where clause columns to covered index or just create a new index on its own. Which one is better or best practised then the others

Arsh 2017-10-30 19:37:50

SDyckes2 2017-11-02 20:34:36
In general, the columns in the WHERE clause will be added to the index as columns in an order, not the INCLUDE. But you will want to take into consideration if the column is part of a predicate and/or is used in an Order By (Sort By).

When trying to remove a Key Lookup, you want to find the columns used in the Output List. These columns can then be added to the index as INCLUDE columns. You can find the list of columns if you hover over the Key Lookup (shown below) or if you highlight the Key Lookup and go to the Query Columns tab in Plan Explorer (PE). I would always consider updating an existing index before adding a new index, manage your index overhead as much as possible.

alt text

For the case of Object2 shown above, there are 12 columns from the Output List of the Key Lookup that would need to be added to an INCLUDE of the Index6 to build a covering index. This could negatively impact the performance if the cost of the additional writes was too high. If you hover over the thick black line from the Key Lookup, you will see the estimated size for each row is 2,740 B while the total estimated size of the data being returned is 7,322 MB.

If you then look at the Key Lookup Object3.Index4, there are only 5 columns that would need to be added to the Object3.Index3. This would be a much smaller impact and should eliminate the Key Lookup.

You can also use the Index Analysis tab in PE to build a theoretical index for an object. You must first collect an estimated or actual execution plan from the PE tool. Then you can choose the Index Seek Object2.Index6 and add the 12 columns as INCLUDE. The Hit Ratio score at the top of the index column will change for the better or worse as you add columns to the index, the higher the score the better up to 100. Once you have the index built, you can go to the bottom of the column and click on the to Script the Index. This pops open a new window with the index script, copy and run it in SSMS or schedule a job to run the script during a maintenance window.