Why index scan instead of index seek?

Question

Post reply

Why index scan instead of index seek?

ufrufr

SSC Veteran

Points: 283
More actions
November 5, 2014 at 7:56 pm

#292187

Hi all,

I got a table with 2M records, table size is about 12GB.

A usual select query is executed to extract records where created_date <=DATEADD(day, -60, getdate()) , approx 12k records will be extracted in the resultset.

Only pk and clustered were declared on this table originally.

User complained that this query ran very long, thus i planned to add a non-clustered index on column "created_date".

However, the execution plan didnt change after index added (i.e. still 100% cost on index scan)

Do you have idea why there was no index seek occurred? Please advise.

Appreciated your great help.

Thanks,

Stan

Viewing 8 posts - 1 through 7 (of 7 total)

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

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

In the absence of an Actual execution plan, any suggestions would be guesses. Can you post it please, as a .sqlplan attachment? Thanks.

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

Koen Verbeeck SSC Guru Points: 259015 More actions · Answer 2

Koen Verbeeck

SSC Guru

Points: 259015

November 6, 2014 at 3:05 am

#1756912

Also provide table DDL and the CREATE INDEX statement and the SELECT query as well.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

ScottPletcher SSC Guru Points: 100262 More actions · Answer 3

If you (almost) always provide created_date in the WHERE clause when querying that table, change the clustered index on the table to be on created_date. That will 100% solve those types of performance issues.

SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

ufrufr SSC Veteran Points: 283 More actions · Answer 4

Sorry guys, tied up on other stuffs yesterday, here is the schema of table:

CREATE TABLE [dbo].[Testing_table](

[id] [int] IDENTITY(1,1) NOT NULL,

[message] [nvarchar](max) NULL,

[message_text] [text] NULL,

[name] [varchar](10) NULL,

[created_date] [datetime] NOT NULL,

CONSTRAINT [PK_Testing_table] PRIMARY KEY CLUSTERED

(

[id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[Testing_table] ADD CONSTRAINT [PK_Testing_table] PRIMARY KEY CLUSTERED

(

[id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [idx_created_date] ON [dbo].[Testing_table]

(

[created_date] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

Eirikur Eiriksson SSC Guru Points: 182631 More actions · Answer 5

Quick thoughts, first is that the non-clustered index is not a covering index and will require an additional key lookup to satisfy the query output. Therefore the server simply deems the scan a better option.

Secondly, if there is a clear cut-off point, that is the [created_date] is ever increasing, a scan will make more of a sense.

Question, after adding the non-clustered index, is the scan still on the clustered index?

😎

ufrufr SSC Veteran Points: 283 More actions · Answer 6

Eirikur Eiriksson (11/6/2014)

Quick thoughts, first is that the non-clustered index is not a covering index and will require an additional key lookup to satisfy the query output. Therefore the server simply deems the scan a better option.

Secondly, if there is a clear cut-off point, that is the [created_date] is ever increasing, a scan will make more of a sense.

Question, after adding the non-clustered index, is the scan still on the clustered index?

😎

Yeah Eirikur, 100% cost on the clustered index.

Eirikur Eiriksson SSC Guru Points: 182631 More actions · Answer 7

ufrufr (11/7/2014)

Eirikur Eiriksson (11/6/2014)

Quick thoughts, first is that the non-clustered index is not a covering index and will require an additional key lookup to satisfy the query output. Therefore the server simply deems the scan a better option.

Secondly, if there is a clear cut-off point, that is the [created_date] is ever increasing, a scan will make more of a sense.

Question, after adding the non-clustered index, is the scan still on the clustered index?

😎

Yeah Eirikur, 100% cost on the clustered index.

Just noticed that you have the GETDATE function in the WHERE clause, change this into a variable as it is a nondeterministic function.

😎