SQL Server Read ismore

Question

Post reply

SQL Server Read ismore

Viewing 8 posts - 46 through 52 (of 52 total)

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

yuvipoy SSChampion Points: 10469 More actions · Answer 1

Sure will follow ANSI 92 standard and thanks for the suggestion.

How about the execution plan

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

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.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

yuvipoy SSChampion Points: 10469 More actions · Answer 3

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?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

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.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

yuvipoy SSChampion Points: 10469 More actions · Answer 5

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!

yuvipoy SSChampion Points: 10469 More actions · Answer 6

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 ?

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 7

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

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)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

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.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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