Why a Table Scan when there's an Index ?

Question

Why a Table Scan when there's an Index ?

homebrew01

SSC Guru

Points: 55548
More actions
November 28, 2008 at 11:28 am

#73117

Why does the execution plan show a table scan on my Leads_DST table ?? I have an index on the List_ID field
However, if I replace @list_Id with an actual value, then it shows an index seek.
What will the query use at run time with the variable ?
USE [ComcastVOIP_Production]
GO
/****** Object: Table [dbo].[CorpsSource] Script Date: 11/28/2008 13:24:29 ******/
SET ANSI_NULLS ON
GO
CREATE TABLE [dbo].[CorpsSource](
[PK_ID] [int] IDENTITY(1,1) NOT NULL,
[Corp] [int] NOT NULL,
[FTA] [int] NOT NULL,
[Division] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Region] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Market] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Rpt_Group] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Corp_Descr] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TSPhone_Num] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TSPhone_Ext] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CSPhone_Num] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CSPhone_Ext] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OpenTime] [datetime] NULL,
[CloseTime] [datetime] NULL,
[Callable] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Callback_Ind] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Leads_DST](
[UNIQUE_ID] [decimal](10, 0) NULL,
[CORP] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LAST_Name] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FIRST_Name] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PHONE_ORG] [varchar](11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ACCOUNT_NUMBER] [varchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FRAN_TX_AR] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OPERATOR_CODE] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ORDER_ENTRY_DATE] [datetime] NULL,
[TECH_CODE] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ORDER_FINALIZE_DATE] [datetime] NULL,
[LIST_ID] [int] NULL
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [List_ID] ON [dbo].[Leads_DST]
([LIST_ID] ASC) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [Unique_ID] ON [dbo].[Leads_DST]
([UNIQUE_ID] ASC) ON [PRIMARY]
DECLARE @list_id integer
SELECT UNIQUE_ID
, LIST_ID
, CONVERT(int,l.[CORP])
, CONVERT(int,l.[FRAN_TX_AR])
, ''
, PHONE_ORG
, FIRST_Name
, LAST_Name
, Account_Number
, Order_Entry_Date
, Operator_Code
, Order_Finalize_Date
, Tech_Code
FROM Leads_DST l
, CorpsSource c
WHERE l.List_ID = @list_id
AND CONVERT(int,l.CORP) = c.corp
AND CONVERT(int,l.FRAN_TX_AR) = c.FTA

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Can you show us the Execution Plan? in XML(.sqlplan) format?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

Oops sorry, I don't think that you can get XML format in SQL 2000.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Elisabeth Rédei Hall of Fame Points: 3873 More actions · Answer 3

Hi,

It is because the value of @list_id is not known at compile time so the optimizer has to revert to do an estimate of how many rows will be returned. For = operator that is 10% of the table which is also in the hood of when the optimizer is likely to choose a scan anyway to avoid the cost of (bookmark) lookups (getting the data for the rest of the columns in the SELECT list).

If the value is sent in as a parameter to a stored procedure, the optimizer can do something called "parameter sniffing" and use available statistics to estimate the number of rows returned for that particular value.

(Even if it doesn't matter for you at the moment but as an aside, your converts on the lefthand side of the = operator is likely to prevent indexes from being used as well.)

/Elisabeth

elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei

Sergiy SSC Guru Points: 110208 More actions · Answer 4

Any reason why both indexes on Leads_DST are nonclustered?

Make the index on LIST_ID clustered and you "parameter sniffing" problem will go away.

_____________
Code for TallyGenerator

homebrew01 SSC Guru Points: 55548 More actions · Answer 5

Sergiy (11/28/2008)
Any reason why both indexes on Leads_DST are nonclustered?
Make the index on LIST_ID clustered and you "parameter sniffing" problem will go away.

This is a system I inherited and I'm trying to help performance. For some reason there are hardly any clustered indexes on the tables, almost all non-clustered. Before I create clustered indexes I would have to decide somehow which is the best field to use.

Sergiy SSC Guru Points: 110208 More actions · Answer 6

Look at you WHERE clause:

WHERE l.List_ID = @list_id

AND CONVERT(int,l.CORP) = c.corp

AND CONVERT(int,l.FRAN_TX_AR) = c.FTA

Here is the answer.

1st, you may safely change datatype for CORP and FRAN_TX_AR to int.

If anybody will try to enter non-integer value in any of these columns system will fail anyway.

2nd, your clustered index is written: (List_ID, CORP, FRAN_TX_AR)

Order of columns probably needs to be different, but without knowing the data and business logic can't tell which is right.

But even if you just make existing index on List_ID clustered it would be enough.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 7

homebrew01 (11/28/2008)
However, if I replace @list_Id with an actual value, then it shows an index seek.

Trick it...

WHERE l.List_ID > 0

AND l.List_ID = @list_id

AND CONVERT(int,l.CORP) = c.corp

AND CONVERT(int,l.FRAN_TX_AR) = c.FTA

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

homebrew01 SSC Guru Points: 55548 More actions · Answer 8

Sergiy (11/28/2008)
Look at you WHERE clause:
WHERE l.List_ID = @list_id
AND CONVERT(int,l.CORP) = c.corp
AND CONVERT(int,l.FRAN_TX_AR) = c.FTA
Here is the answer.
1st, you may safely change datatype for CORP and FRAN_TX_AR to int.
If anybody will try to enter non-integer value in any of these columns system will fail anyway.

Do you mean to change the table itself ??

Sergiy (11/28/2008)
2nd, your clustered index is written: (List_ID, CORP, FRAN_TX_AR)
Order of columns probably needs to be different, but without knowing the data and business logic can't tell which is right.
But even if you just make existing index on List_ID clustered it would be enough.

I'm not sure how this table is used in other applications. What are the risks of picking the "wrong" (or less than ideal) column to be the clustered index ?

I'm new at this company and am still finding my way around the applications. I'm trying to improve performace carefully so I don't accidentally cause other problems.

Sergiy SSC Guru Points: 110208 More actions · Answer 9

homebrew01 (12/1/2008)
Do you mean to change the table itself ??

Yep.

Table is just another piece of executable code compiled when CREATE TABLE statement was executed.

If the code is wrong it needs to be changed.

I'm not sure how this table is used in other applications. What are the risks of picking the "wrong" (or less than ideal) column to be the clustered index ?
I'm new at this company and am still finding my way around the applications. I'm trying to improve performace carefully so I don't accidentally cause other problems.

If it would be "replace one clustered index with another" then there would be a good reason for being cautious. But because there is no clustered index at all making one of the indexes clustered is pretty much no harm exersise.

Only situation when it may cause some performance issues is when you have heavy transactional load on this table for UPDATEs, DELETES and INSERTs affecting order of records in the clusterd index.

Any way it's not gonna cause any operational problems. And if anybody notices decrease in performance it's easy to make the index non-clustered.

But most likely it's a chance for you to become "the day hero" by fixing long coming performance issue. 😉

_____________
Code for TallyGenerator