You know, sometimes query analyzer in MSSQL 2000 just doesn't get things right. I had a query that was taking a very long time to execute in MSSQL. The query on average would take 30-60 seconds to complete execution. The query was running a LIKE search over a text data type. Not ideal by any means, but the execution was mind boggling slow.
Figuring my problem was in the fact that I'm doing a LIKE operation on a text data type over hundreds of thousands of records, I figured that was the main culprit. So one of the very first things I tried was setting up a Full Text Index on the data, so I could do a FTS over the data instead. Much to my chagrin, this really showed no noticeable performance increase (maybe like 2-3 seconds faster, but not nearly enough to warrant making radical changes to implement FTS.)
So where was my problem? I really started studying the execution plan. Everything I looked at seem to imply that the LIKE operator was the problem, and it was mandatory (especially since FTS provided no real performance increase.)