How does including a SQL index hint affect query performance?

Question

Say I have a table in a SQL 2005 database with 2,000,000+ records and a few indexes. What advantage is there to using index hints in my queries? Are there ever disadvantages to using index hints in queries?

Answer 1

First, try using SQL Profiler to generate a .trc file of activity in your database for a normal workload over a few hours. And then use the "Database Engine Tuning Advisor" on the SQL Server Management Studio Tools menu to see if it suggests any additional indexes, composite indexes, or covering indexes that may be beneficial.

I never use query hints and mostly work with multi-million row databases. They sometimes can affect performance negatively.