Index seek and index scan

Question

Index seek and index scan

krishna83

Ten Centuries

Points: 1003
More actions
June 7, 2017 at 9:38 am

#402145

If I create cluster index on a column I have observed index scan on a column in execution plan.
If I create non cluster index on a column I observed table scan .
Here How to convert access of column from index scan to index to seek.
What is the type of index I need to create here?

Viewing 15 posts - 1 through 15 (of 17 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Why is it important for you to do this? Often an index scan is the more appropriate operation. We'll need, at the very minimum, table DDL and the query you ran, please.

John

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 2

No one can advise you on what index is needed without seeing your queries.

Use the stairways - http://www.sqlservercentral.com/stairway/72399/ on indexing, this will help you understand how to create indexes to cover you queries.
Creating indexes won't necessarily address what your query is looking at or for.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

A seek operation requires that there is a value being searched for, and the column that is being searched is the left-most column of the index. That's the absolute minimum to get a seek

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

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

krishna83 - Wednesday, June 7, 2017 9:38 AM
If I create cluster index on a column I have observed index scan on a column in execution plan.
If I create non cluster index on a column I observed table scan .
Here How to convert access of column from index scan to index to seek.
What is the type of index I need to create here?

A covering index, but a seek may not be appropriate.
Can you post the actual (as opposed to estimated) execution plan as a .sqlplan attachment?

^{“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

Rechana Rajan SSCertifiable Points: 7717 More actions · Answer 5

krishna83 - Wednesday, June 7, 2017 9:38 AM
If I create cluster index on a column I have observed index scan on a column in execution plan.
If I create non cluster index on a column I observed table scan .
Here How to convert access of column from index scan to index to seek.
What is the type of index I need to create here?

Share your query here if possible. You saw Index Scan and table Scan because Clustered Index is basically table itself.

DimUser Ten Centuries Points: 1043 More actions · Answer 6

scans aren't always a bad option. It's a myth that seeks are always better than scans.
also, I believe there is a "threshold" in terms of number of rows, below which the query optimiser will always produce a table scan as it is the better option.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

DimPerson - Thursday, June 8, 2017 5:53 AM
also, I believe there is a "threshold" in terms of number of rows, below which the query optimiser will always produce a table scan as it is the better option.

No, the threshold you may be thinking about is about rows fetched from a non-covering index, and hence necessitating a lot of lookups.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

krishna83 Ten Centuries Points: 1003 More actions · Answer 8

Please find attached execution plan and post your suggestions here

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

krishna83 - Friday, June 9, 2017 1:49 AM
Please find attached execution plan and post your suggestions here

Thanks for posting the plan, it's a huge help.
You don't appear to have any ordinary indexes on your tables - is this correct?
According to this plan, the query will return about 18 million rows - does this sound about right? (what application consumes this huge output?)
Do you have a maintenance plan in place for e.g. statistics?

^{“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

krishna83 Ten Centuries Points: 1003 More actions · Answer 10

It's dataware house application.
Every day I collect database statistics and rebuilding existing indexes if require.
I am fresher to SQL server plz help how to go ahead on this.

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

krishna83 - Friday, June 9, 2017 3:59 AM
It's dataware house application.
Every day I collect database statistics and rebuilding existing indexes if require.
I am fresher to SQL server plz help how to go ahead on this.

There's plenty to do here for sure. Can you answer a couple of those questions? In particular, does the query really return 18 million rows? This is important - you really don't want seeks if you're returning most or all of the rows from a table and the plan indicates you are doing exactly that.

^{“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

DimUser Ten Centuries Points: 1043 More actions · Answer 12

there's a question here in that is this causing a performance problem, or are you just worried about scans vs. seeks?

you look as though you may be missing a clustered index, however if performance is a problem, I'm not sure this is where you are getting your problem.
the number of rows being returned may not be ideal, so you may wish to reconsider the data you are choosing; maybe try and be a little more selective if possible, instead of returning all the data? I don't know what your requirements are.
if performance is your problem then you may wish to reconsider sorting 100,000,000 rows, as this is consuming 53% of your total resource?
Also, you seem to be experiencing an implicit conversion, this will always kill performance.

If you are simply worried about seeks or scans, then maybe reconsider rewriting your query, if possible. But as already said, if you are returning all, or most of the data, maybe you want the scans.

krishna83 Ten Centuries Points: 1003 More actions · Answer 13

The above query has retrieving 2010899 rows .Exection time 11:25 sec

krishna83 Ten Centuries Points: 1003 More actions · Answer 14

I raised this question to improve performance of the query.It would be more help full if you give based on execuiton plan,