I, too, have only had to use an index hint one time. At my last company, we had a stored procedure that accepted about 16 parameters and performed a dynamic search based off of the input parameters. Depending on which parameters you passed, the optimizer would select a different plan. Once cached, the reuse of plans for this SP would end up with the wrong plan being run for the parameter combination being passed in. I ran through a number of tests to find the 'middle road' plan that worked best for all of the common parameter combinations and we used a hint to force that plan.

This was just a band-aid though because the right fix involved re-engineering the SP and some of the table design and I did not have the resource bandwidth to make that kind of change as we had a customer waiting on the fix.

Another option for both Gus's and my example woudl be to create the procedure WITH RECOMPILE to force the SP to generate a new query plan for each execution. We chose not to go this route because of how frequent the SP was called.