weird index issue

we have weekly refresh of our db, right after refresh, we find one function in application is hanging, then we find out the script of that function.
we decide to add an index to one table which is main part of that involved script, then the problem went away. After the problem gone, even we drop that index, we will not have any problem untill next week after the refresh, the same problem come back again.
then we will do the same, add index, then drop.
Any one has any idea?
It looks like that it does not matter if sql use the added index, but put the table into memory by adding the index that really matters?
Thanks.

Thanks for your reply.
it is a nonclustered index. We have a existing similar index already. to drop it is to test if that really make things work.
db refresh is something about the failover, not restore from db backup
thanks

[quote user="dba_boston"]
it is a nonclustered index. We have a existing similar index already.
[/quote]
What does this mean??? you're creating and additional index with the same defination which is already there???
[quote user="dba_boston"]
db refresh is something about the failover, not restore from db backup
[/quote]
Can you please confirm as what does this failover means???

what the size of the table , size of the transaction% on that table.?

It could be statistics issue, because when you create an index statistics of that column will be updated.
Next time instead of creating an index try to update the statistics and see what happens...
Did you see the query plan after adding the index? is it using the new index and is there any query plan change after dropping the index?

What do you mean by "refresh of our db"? Are you doing a back-up and then a restore from that back-up?