Weird index behavior

Question

Weird index behavior

Jim McLaren

SSC Journeyman

Points: 93
More actions
March 18, 2005 at 9:59 am

#85416

I am searching a table with the following index:
CREATE INDEX [idx_aka_ln_fn_source] ON [dbo].[AKA]([LastName],
[FirstName], [Source])
WITH FILLFACTOR = 100 ON [PRIMARY]
When I run select statements against the AKA table sometimes SQL server will use the index and sometimes it does not. For example, if I run
select * from aka where LastName = 'johnson' and firstname = 'jim'
then SQL Server does an index seek.
If I run
select * from aka where LastName = 'smith' and firstname = 'james'
then SQL Server does a Table Scan.
I know I can force it to use the index and will probably do that. But that solution will take significant time to implement. In the meantime, does anyone have any idea what causes this behavior?
TIA,
Jim

Viewing 7 posts - 1 through 6 (of 6 total)

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

Martha Sharkey SSC Enthusiast Points: 100 More actions · Answer 1

Is it possible that James Smith was inserted or updated after you created the index? FILLFACTOR = 100 means that all of your indexing capability was used up when you created the index and additional INSERT or UPDATE's won't be indexed.

PW-201837 SSC-Insane Points: 20805 More actions · Answer 2

>>FILLFACTOR = 100 means that all of your indexing capability was used up when you created the index and additional INSERT or UPDATE's won't be indexed.

Sorry, but that's completely incorrect.

100% FillFactor means that Sql Server needs to split an index page to create any new index entries.

The likely problem is either:

1) Out of date statistics

2) Index not selective enough

Jim McLaren SSC Journeyman Points: 93 More actions · Answer 3

I don't think the issue is what Martha said for two reasons.

The table is used only for reporting and is never written to once it is created.
When I run

select * from aka (index=idx_aka_ln_fn_source) where LastName = 'smith' and firstname = 'james'
Then it uses the index and returns in 1.5 seconds instead of over a minute.

PW,

Educate me on out of date statistics. I am not familiar with that. Thanks

PW-201837 SSC-Insane Points: 20805 More actions · Answer 4

UDPATE STATISTICS is a T-SQL command that you can run - see the BOL section for it.

Sql Server maintains statistics about data distribution in indexes/tables in order to determine the best query plan. In tables subject to continual data modification, these statistics get out of date, leading to inappropriate query plan generation.

However, if this is a static table, statistics are unlikely to be a problem. Do you recreate the index after populating the table ? (If no, 100% fillfactor is useless, because it only applies at index creation time).

Does the table have a clustered index ? If no, consider making this index clustered, since the first column (LastName) has many duplicated values.

Jim McLaren SSC Journeyman Points: 93 More actions · Answer 5

Thanks. I will read BOL.

After filling the table we don't update the index.

We have 51 databases that are constructed identically and we are querying all of them occasionally in a view. We don't have a clustered index on the db tables and will investigate if that will help.

Looks like it's going to be a long weekend. Will let you know what happens next week.

PW-201837 SSC-Insane Points: 20805 More actions · Answer 6

All your tables should benefit from a clustered index.

Your processing cycle will also benefit from dropping all indexes first, loading the static tables, then re-indexing at FF100% once the data is loaded (or if that isn't viable, then at least doing a DBCC DBREINDEX() in each table after load).