Search code examples
sql-serverindexingquery-optimizationclustered-index

How to reduce clustered index scan cost by using SQL query

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:

enter image description here

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?

Solution

  • 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