How to use index hint in SQL?

Can you explain in detail how to utilize index hints in SQL? I'm interested in learning how they can help optimize query performance. Specifically, what types of scenarios would warrant the use of index hints, and what syntax should be followed to implement them effectively? Additionally, are there any potential drawbacks or limitations to consider when using index hints in SQL? I'd appreciate a comprehensive explanation that addresses these points.