Assigning an index to use by a stored proc

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 Development (2000)

Assigning an index to use by a stored proc

Author

Topic

PGG123
Yak Posting Veteran

55 Posts

Posted - 2004-06-17 : 13:37:01

I have this stored proc that's taking too long to execute. It's a select query and I looked at the execution plan and it's using the wrong index. The table has a clustered index and a few non-clustered indexes. The clustered index is on two fields that are not being used by my query. My query is filtering based on the primary key, which is not part of the clustered index. I cannot change the indexes, but I want my query to use the PK index. Can I explicitly tell the stored proc to use the PK index?

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-17 : 13:39:07

Yes you can with index hints. But it is not recommended doing it. You need to figure out why the index isn't being used. Have you run DBCC DBREINDEX recently, how about UPDATE STATISTICS?

Tara