SQL Server 2005 Query optimization issue : limit affected rows on a large volume table?

If you have a huge volume of table (say millions of rows) and a requirement to limit x number or sample of rows from a query every time you execute. To limit number of rows then you would think about [ select ... from (select top 100 * from X order by ... DESC) .... where ...] statement. But sometimes this will have degrade of performance due to the tablescan.

ON the basis of performance SQL Server typically returns rows in whatever order is most efficient, and data values have no effect on which rows are returned when you use either TOP or SET ROWCOUNT.  TOP is reproducibly faster than the SET ROWCOUNT statement, and it has an advantage availing from internal feature of the SQL Server if the table has no supporting indexes.

Further to this you can take help of "Limiting Result Sets by Using TABLESAMPLE" review 
MS189108 MSDN reference & http://blogs.netindonesia.net/kiki/articles/9103.aspx to go about it.

As a whole if you get a performance loss or if it is degrading then the order I go about is to documented in this HighCPU & TroubleshootSlowQueries blog posts on this site that are starting ones to see where it is lacking the performance. After all, if SQL Server isn't using an efficient execution plan, there must be something wrong with your table design or indexing strategy.

If you designed your system well, and it still doesn't perform well, you must be lacking appropriate hardware resources. Plan guides are most commonly used to specify RECOMPILE or OPTIMIZE FOR query hints. However, you can also advise SQL Server to use a particular type of JOIN, use a predefined execution plan. In this regard to see how SQL Server uses the execution plan caching refer to blogpost for further information.