November 5, 2014 at 7:56 pm
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
November 6, 2014 at 1:46 am
In the absence of an Actual execution plan, any suggestions would be guesses. Can you post it please, as a .sqlplan attachment? Thanks.
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
November 6, 2014 at 3:05 am
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
November 6, 2014 at 9:38 am
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!
November 6, 2014 at 8:49 pm
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
November 6, 2014 at 10:56 pm
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?
π
November 7, 2014 at 12:00 am
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.
November 7, 2014 at 1:04 am
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.
π
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply