How to force an index on a t-sql request ?

[ChevalierBayard]

Hello,

I'm currently dealing with severe performances issues on SQL-Server 2000.

The database concerned as several table but 2 bigs ones (between 150.000 and 600.000 lines as it concerns the same Database structure on different Servers).

We've seen with my coders that adding an index solves problems of timeout on a stored procedure execution.

The problem is that we have different stored proc attacking these two tables and we have only accelerated a few of them with the new index.

I know in Oracle we can specify the index to use for a request and hope that exists also in SQL Server.

Can someone give me the T-SQL syntax for this ?

Cheers,
Chev

[rmiao]

You can use index hint in your query like

select * from table with index = ... where ...

Check books online for details.

[Rawhide]

rmiao is right (as usual). But be aware that there are dangers in using index hints. The index you specify may be the correct index to use today, but will it be tomorrow?

If your statistics are missing or out of whack, then forcing the index to use the bad index can cause some major problems. You're better off determining why the index you want to be used is not being used and make changes accordingly.

Try running DBCC DBREINDEX and UPDATE STATISTICS first!! Then use the estimated execution plan in Query Analyzer to determine where there are problems with the query and why.

I could probably suggest more if I knew your table structure and the procedure in question.