SQL Server Indexes Aren't Helping

Question

I have a table (SQL 2000) with over 10,000,000 records. Records get added at a rate of approximately 80,000-100,000 per week. Once a week a few reports get generated from the data. The reports are typically fairly slow to run because there are few indexes (presumably to speed up the INSERTs). One new report could really benefit from an additional index on a particular "char(3)" column.

I've added the index using Enterprise Manager (Manage Indexes -> New -> select column, OK), and even rebuilt the indexes on the table, but the SELECT query has not sped up at all. Any ideas?

Update:

Table definition:

ID, int, PK
Source, char(3)  <--- column I want indexed
...
About 20 different varchar fields
...
CreatedDate, datetime
Status, tinyint
ExternalID, uniqueidentifier

My test query is just:

select top 10000 [field list] where Source = 'abc'

Answer 1

For a table of that size your best bet is probably going to be partitioning your table and indexes.

Answer 2

Use the Show Execution Plan in SQL Query Analyzer to see if the index is used.

You could also try making it a clustered index if it isn't already.

Answer 3

You need to look at the query plan and see if it is using that new index - if it isnt there are a couple things. One - it could have a cached query plan that it is using that has not been invalidated since the new index was created. If that is not the case you can also trying index hints [ With (Index (yourindexname)) ].

10,000,000 rows is not unheard of, it should read that out pretty fast.

Answer 4

 select top 10000

How unique are your sources? Indexes on fields that have very few values are usually ignore by the SQL engine. They make queries slower. You might want to remove that index and see if it is faster if your SOURCE field only has a handful of values.