use an Index correctly to optimize queries

I have an SQL database with millions of records and when I'm querying the data like

select * from ActCosts where ScenarioID= 456

The tables has 1,323,718 rows and it gives me like 50,000+ rows which is surprisingly taking more than 3 minutes. So I am now thinking of how I could improve this performance. I find one way is to create an index on the column "SomeID". I have created this index but the query takes the same time

Execution plan

Index Script

CREATE NONCLUSTERED INDEX [IX_ActCost_ScenarioID] ON [dbo].[ActCost] 
(
    [ScenarioID] 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)
GO

ALTER TABLE [dbo].[ActCost] ADD  CONSTRAINT [PK_ActCost] PRIMARY KEY CLUSTERED 
(
    [ActCostID] 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)
GO

Your query is no faster with the index because SQL Server has determined that it would be more efficient to do a Clustered Index Scan, than use the IX_ActCost_ScenarioID that you have defined and perform a Key Lookup to retrieve the extra data needed.

As you've only defined the index on ScenarioID, with no INCLUDE columns, each extra column you wish to return from the query would need to be retrieved from the Clustered Index. On 50,000+ rows, this simply wouldn't be efficient, hence the index is ignored.

Firstly, you need to ask yourself if you really need ALL columns to be returned. I somehow doubt you do (the ID columns for example), but I may be wrong. In any case, you should always define the column list rather than using *.

SELECT
 ActID, 
 ActCostTypeID,
 Description,
 Cost,
 
FROM dbo.ActCosts 
WHERE ScenarioID = 456;

Notes:

1. Don't avoid the schema prefix

1. Avoid using SELECT *

You then need to include the extra columns returned in IX_ActCost_ScenarioID:

CREATE NONCLUSTERED INDEX [IX_ActCost_ScenarioID] ON [dbo].[ActCost] ([ScenarioID] ASC)
INCLUDE (  );
GO

StackExchange Database Administrators Q#86748, answer score: 8