CODEWITHSUNDEEP
sql-serversql-server-2008
user3093010
user3093010

Reputation: 365

Index not found in query

This query execute in 8 s.:

SELECT segment,
    [segstart]
FROM dbo.CmsCallHistory WITH (INDEX(CmsCallHistory_SGSTRT))
WHERE segstart between '2015-09-20' and '2015-09-30'

But this query ~3 minutes

SELECT segment,
    [segstart]
FROM dbo.CmsCallHistory
WHERE segstart between '2015-09-20' and '2015-09-30'

Data rows count ~120k

In second query execution plan I can't see index, but only Table Scan cost 98%: Second query

In first query execution plan I see that segstart index is used: First query

Columns total: ~20

Rows total: 40296998

So what's wrong ?

Upvotes: 1

Views: 884

Answers (2)

singhswat
singhswat

Reputation: 906

Based on second execution plan I would suggest to create a Covered Index. This will get rid of KeyLookUpID from the execution plan and it should resolve the issue. Also refer this for covered index

CREATE NONCLUSTERED INDEX IX_Name
ON dbo.CmsCallHistory (segstart)
INCLUDE (segment.. include your other columns from select statement);
GO

Upvotes: 1

Devart
Devart

Reputation: 121922

DROP INDEX CmsCallHistory_SGSTRT ON dbo.CmsCallHistory
GO

CREATE CLUSTERED INDEX CmsCallHistory_SGSTRT ON dbo.CmsCallHistory (segstart)
GO

--DBCC FREEPROCCACHE
--GO

SELECT segment, [segstart]
FROM dbo.CmsCallHistory
WHERE segstart BETWEEN '2015-09-20' AND '2015-09-30'

Upvotes: 1

Related Questions