Man I really need to spend some time and learn how to use Optimizer Hints. A user reported that a query was running slow. I started troubleshooting the issue by first generating an Graphical Execution plan. The main problem was exposed right away. 95% of the process was a table scan on a table that was 7 million rows.
I then fed the query into the Database Engine Tuning Advisor (DETA). The DETA suggested I added a covering index on the 7 million row table for the two columns in my select. With a little more investigation I realized that the PK for that table covers these two columns. Why is the optimizer not using the PK index in the execution plan?
So I set out to rebuild the index and update statistics. No dice. The Optimizer still used the Table Scan vs. the Index Seek. So I started engaging support for the application and after throwing around a few emails, letting them know I had no idea what I was talking about, they finally sent back a query with the Optimizer Hint WITH (INDEX(Index_Name)).
The query went from taking 30 seconds + (sometimes 2 minutes or more) to executing in just under 2 seconds!! Another interesting fact is that the same query ran in 9 seconds on a test server, while using the Table Scan. However that server is a lot more beefier with faster procs and a lot more RAM.
So anyway, all that to say this, I really need to look into Optimizer Hints. Here is a link from http://www.sql-server-performance.com with some more optimizer hint information.
JustSQL 