SQL Server indexes not working

Question

I've set some indexes on an a test server and on my own machine, and it worked great. But when I tried to put the data on a production server (copying all the test data through import/export wizard, and after setting the primary keys, and then the FK and indexes), the indexes seems not to be working.

They are there, but the queries are really, really slow, as the indexes weren't there at all. What can I be missing? I noted that the production indexes are a little bit more fragmented than the test indexes.

I've tried to rebuild and reorganize the indexes, but it didn't work. I'm using SQL Server 2005 (90).

Answer 1

I 've set some indexes on an a test server and on my own machine, and it worked great. But when I tried to put the data on a production server

First question is if the production data has the same amount of data as your test server, usually a production box has a lot more data and when things are fast on a test box they are not always fast on a production box because the queries are not written in a way so that indexes will be used.

Look at the execution plan and see if you see seeks or scans

In general indexes won't be used if the queries are not SARGable or if the index selectivity is too low

Also are statistics created and maintained on the production box?

Take a look at Your testbed has to have the same volume of data as on production in order to simulate normal usage to get some ideas on how to check for SARGable queries