29

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?

Sanushi Salgado
  • 1,195
  • 1
  • 11
  • 18
user1494292
  • 399
  • 1
  • 5
  • 14

3 Answers3

34

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • thank you for your prompt reply, can you please guide me to create a covering non clustered index, what keys to be included in that index can you help me out mate on this – user1494292 Aug 06 '12 at 10:35
  • 1
    CREATE NONCLUSTERED INDEX [MST_IDX_FOR_REC_TYPE] ON [dbo].[MASTER_RECORD_TYPE] ( [Record_Type_Code] 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 Now index scan has been turn up into index seek of 100% – user1494292 Aug 06 '12 at 10:59
  • 1
    @user1494292: OK - so now you have the **index seek** - which is the most efficient (and fastest) way to fetch (a few rows of) data – marc_s Aug 06 '12 at 11:04
1

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.

Mike Perrenoud
  • 66,820
  • 29
  • 157
  • 232
  • hi mate, thank for your prompt reply, even though if i use select 1 from Master_record_type then also same index scan cost it was throwing – user1494292 Aug 06 '12 at 10:33
0

In your query, you have used column RECORD_TYPE_CODE which is not part of clustered index and also not included in any non-clustered index too. So SQL Optimizer will decide to scan the clustered index to do comparison of where clause predicate.

Why is there a scan on my clustered index?

Community
  • 1
  • 1
sundar
  • 1,760
  • 12
  • 28