Tuning - Index and execution plan

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Tuning - Index and execution plan

Author

Topic

geossl
Yak Posting Veteran

85 Posts

Posted - 2005-05-12 : 08:41:58

Dear All,

I added an index on a table of the testing database. According to the execution plan, the query immediately started to use the new index.

However, when I created the same index in the production database table, the query as displayed in the execution plan did not take to seek data with the new index but kept using the old one.

What is the reason behind this? Any remedy to solve it?

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-12 : 17:21:07

Are the statistics up to date? How distributed is the data for the first column in the index?

Tara

geossl
Yak Posting Veteran

85 Posts

Posted - 2005-05-12 : 21:56:24

I have updated the statistics but the situation is still the same.

Besides, the index is based on two columns, CatID and TypeID.
There are a total of 150000 records and there are only 4 different kind of CatID. TypeID is about 30 different type.

quote:
Originally posted by tduggan

Are the statistics up to date? How distributed is the data for the first column in the index?

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-13 : 12:18:35

quote:
Originally posted by geossl

Besides, the index is based on two columns, CatID and TypeID.
There are a total of 150000 records and there are only 4 different kind of CatID. TypeID is about 30 different type.

That's your problem then, CatID is not selective at all so SQL Server will not use that index. An index must good selectivity in order to be chosen for your execution plan. Highly selective is the best, 95% or greater. Yours is less than 1%.

Your test database must have a lot less rows in order for this index to be used. If TypeID is more selective, then I would change the composite index to TypeID, CatID (order changed).

Tara

geossl
Yak Posting Veteran

85 Posts

Posted - 2005-05-16 : 03:59:04

How to tell if the fields of index is selective enough?

quote:
Originally posted by tduggan

quote:
Originally posted by geossl

Besides, the index is based on two columns, CatID and TypeID.
There are a total of 150000 records and there are only 4 different kind of CatID. TypeID is about 30 different type.

That's your problem then, CatID is not selective at all so SQL Server will not use that index. An index must good selectivity in order to be chosen for your execution plan. Highly selective is the best, 95% or greater. Yours is less than 1%.

Your test database must have a lot less rows in order for this index to be used. If TypeID is more selective, then I would change the composite index to TypeID, CatID (order changed).

Tara

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-05-16 : 07:48:04

See if the index is used by execution plans when you run a query.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-16 : 12:38:29

NumberOfPossibleValues divided by RowCount: 4/150000 is approximately equal to 0, no selectivity.

Tara

geossl
Yak Posting Veteran

85 Posts

Posted - 2005-05-17 : 07:39:19

So in this case, it seems there is no other choice to increase the performance, isn't it?

quote:
Originally posted by tduggan

NumberOfPossibleValues divided by RowCount: 4/150000 is approximately equal to 0, no selectivity.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-17 : 12:23:55

Did you see this in a previous post of mine:

quote:

If TypeID is more selective, then I would change the composite index to TypeID, CatID (order changed).

Tara

SamC
White Water Yakist

3467 Posts

Posted - 2005-05-17 : 14:54:40

How about posting the DDL for the table and all indexes?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-17 : 15:37:35

Also posting the query would help.

Tara

ruan4u
Posting Yak Master

132 Posts

Posted - 2005-05-18 : 18:12:39

TDuggan: you said selectivity is a paramter for an index to be selected.But i have seen that by adding the right column in the select statement, the index and the plan behaves differently.What will be a DBA's advice to developer who will be writting querys to fulfil their programs.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-18 : 18:21:18

Only add indexes on columns that have at least good selectivity otherwise SQL Server will not use them. Since adding indexes impacts DML, you need to be careful on which ones you add. If you find that an index is never being used for your execution plans, then consider dropping them.

quote:

But i have seen that by adding the right column in the select statement, the index and the plan behaves differently.

I'm not sure what you mean. If the first column in an index has very little selectivity, the index will not be used. SQL Server finds it more efficient to scan in this case.

Tara

geossl
Yak Posting Veteran

85 Posts

Posted - 2005-05-30 : 09:17:43

quote:
Originally posted by SamC

How about posting the DDL for the table and all indexes?

The table structure is:

ProductID Int (Primary Key)
CatID Int (Indexed)
TypeID Int (Indexed)
ProcessTypeID int
Subject nText
PrcsDate DateTime (Indexed)
Remark nvarchar
DestinID Int
Cr_Date datetime

The query is something like:

select *
from tbl
WHERE CatID = 1
AND Subject LIKE 'TOY1234%'

It is over 160000 records.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-05-30 : 10:43:33

Nearly there....
"How about posting the DDL for the table AND..............ALL INDEXES?"

geossl
Yak Posting Veteran

85 Posts

Posted - 2005-05-31 : 07:37:30