How to reduce clustered index scan cost by using SQL query

Question

How can I reduce the clustered index scan cost of below mentioned query

DECLARE @PARAMVAL varchar(3)  set @PARAMVAL = 'CTD' select * from MASTER_RECORD_TYPE where RECORD_TYPE_CODE=@PARAMVAL 

if I run the above query it was showing index scan 99 %

Please find here below my table particularities:

here below i have pasted my index for the table:

CREATE TABLE [dbo].[MASTER_RECORD_TYPE] ADD  CONSTRAINT [PK_MASTER_REPORD_TYPE] PRIMARY KEY CLUSTERED  (     [Record_Type_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, FILLFACTOR = 80) ON [PRIMARY] GO 

kindly advise how can i reduce index scan cost?

Answer 1

First of all - if you search for RECORD_TYPE_CODE you should make sure to have an index on that column.

Besides that mainly two things:

• don't use SELECT * - that'll always have to go back to the clustered index to get the full data page; use a SELECT that explicitly specifies which columns to use

• if ever possible, try to find a way to have a covering nonclustered index, e.g. an index that contains all the columns needed to satisfy the query

If you have such a covering nonclustered index, then the query optimizer will most likely use that covering index (instead of the actual clustered index which is the full table data) to fetch the results

Answer 2

You need to try and use a covered index. But the problem you're going to have is that you're using SELECT *. Do you really need the entire record?

Either way, add RECORD_TYPE_CODE to another index and it will help with the query because at least that field can be read off of an index page.