Suddenly one of the stored proc becomes very slow. When checked the query plan came to know that 2 tables are not using indexes. Used sp_compiled and also dropped and recreated the proc. But still the optimizer is not using the desired indexes. Does not want to force the index. Wanted to know the root cause of optimizer not using the index. please help.

Have you updated statistics for the 2 tables?

pdreyer has an excellent point about the statistics. You should probably also check to see if there are border cases in your data. Are there any combination of parameters the stored procedure accepts that cause a table scan to be preferable? Typically, RDBMS systems only use indexes at 4 - 7 % selectivity.

update statistics are actually run every weekend using a batch. But still there are no sign of any change with the query plan.

Can you add timing statements to find where in the proc it is suddenly taking the time and then give us the SQL at this point. Perhaps another batch is now locking a table when it wasn't before - have any batches been added/changed recently? Have you tried just running the proc and seeing if it runs slow at all times? Do the tables concerned always have a moderate amount of data in them or could one particular table be empty when the statistics are built and so affect stored proc plans?

Mike