April 14, 2014 at 10:29 pm
Sure will follow ANSI 92 standard and thanks for the suggestion.
How about the execution plan
April 15, 2014 at 2:06 am
yuvipoy (4/14/2014)
Sure will follow ANSI 92 standard and thanks for the suggestion.How about the execution plan
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?
Before randomly throwing indexes at the database, you should upgrade your understanding of them. SSC has this excellent starting point [/url]by David Durant. Read it. You will gain far more than a little extra knowledge about indexing.
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
April 15, 2014 at 11:25 pm
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?
April 16, 2014 at 1:55 am
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
April 22, 2014 at 2:48 am
After doing the recommendation i now see that the query is using index seek with 84%.
So my question is index seek is better than index scan
previously my query gave index scan which consume 80% now it is index seek
Thanks!
May 5, 2014 at 1:08 am
yuvipoy (4/22/2014)
After doing the recommendation i now see that the query is using index seek with 84%.So my question is index seek is better than index scan
previously my query gave index scan which consume 80% now it is index seek
Thanks!
The above index seek will improve my query performance ?
May 5, 2014 at 8:37 am
yuvipoy (5/5/2014)
yuvipoy (4/22/2014)
After doing the recommendation i now see that the query is using index seek with 84%.So my question is index seek is better than index scan
previously my query gave index scan which consume 80% now it is index seek
Thanks!
The above index seek will improve my query performance ?
Not necessarily. "It Depends". Look at the properties of the Index Seek in the execution plan and see how many times the Index Seek is being executed. Just as an example, 40,000 seeks can be a whole lot worse than a single table scan.
--Jeff Moden
May 6, 2014 at 2:27 am
yuvipoy (5/5/2014)
yuvipoy (4/22/2014)
After doing the recommendation i now see that the query is using index seek with 84%.So my question is index seek is better than index scan
previously my query gave index scan which consume 80% now it is index seek
Thanks!
The above index seek will improve my query performance ?
As Jeff pointed out, it depends. Test the query with and without the index by timing several executions.
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
Viewing 8 posts - 46 through 52 (of 52 total)
You must be logged in to reply to this topic. Login to reply