yuvipoy (4/15/2014)
ChrisM@Work (4/15/2014)
95% of the cost of the query is a key lookup of table2. The query uses index IDX_SE_CD_MEAS_FK to seek matches on column TCOL1. Since the output requires a whole bunch of columns which don't exist in this index, SQL Server has to retrieve them from the table using the cluster keys collected in the nested loops join to IDX_SE_CD_MEAS_FK. You could get around this by adding those additional columns as INCLUDE columns to this index, or you could create a whole new index such as this:
CREATE INDEX ix_Suggestion ON TABLE2 (TCOL1) INCLUDE (TCOL2, TCOL3,TCOL9, TCOL4, TCOL5, TCOL6, TCOL7, TCOL8, TCOL10, TCOL12, TCOL11, TCOL13)
The end result is the same.SQL Server can get all the columns it needs from a single plan operator, rather than two, and this will diminish the reads for table2. What do you think will happen to the execution plan once you've done this?
how can i proceed on this?
what can i take this?
Read the recommended articles so that you understand what you are doing and why, and what the implications are of indiscriminately adding indexes to tables. Then either change an existing index so that it covers your query, or create a new index.
If you are curious and simply want to see the improvement that a covering index can make to the performance of your query, then create the index ix_Suggestion and run the query.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden