I used the built-in performance tuning tool and built indexes for my database. Since then I changed some of the queries from "select top 10..." to "select top 50".
My question is do I need to rebuild my SQL server database indexes?
+3
A:
You don't need to rebuild your indexes if all you've changed is top 10 for top 50. The queries are exactly equal regarding execution plan (and thus will benefit from existing indexes), the only difference is that you are retrieving some extra rows from the resultset.
If you are noticing a big performance drop after that change, the problem must lie elsewhere. For example, I've seen the need to rebuild a clustered index for performance reasons after adding data in an order different to the natural order of the index. But this is because of the new data, not because a top 10 changed to top 50.
EDIT: Sam Saffron comments that execution plans can change between top 10 and top 50 queries, to make sure this isn't happening to you check both execution plans and see if they vary.
Vinko Vrsalovic
2010-10-21 23:56:32
and what about without the "top" command?
Rana
2010-10-22 00:01:32
If you have many rows, the time it'll take to fetch them all will be longer of course, but the query time should be equal, after all, all you are changing is the amount of data to bring, not how to select that data.
Vinko Vrsalovic
2010-10-22 00:13:00
@Vinko, I have seen query plans change radically in some cases when you switch from top 10 to top 50.
Sam Saffron
2010-10-22 00:29:15
@Sam: really? I haven't. It would be interesting to find out when...
Vinko Vrsalovic
2010-10-22 04:26:35
@Vinko It hits a threshold at some point, if the rows are really wide and the index is really narrow, it can be too expensive to go -> index and then do bookmark lookups when you do a `select *`. Once you hit a certain threshold a table scan can be cheaper, and I have seen cases where that number is pretty low - less then you would expect.
Sam Saffron
2010-10-22 04:35:29
A:
Though you do not need to rebuild indexes just because querying patterns have changes, you may have to rethink indexes.
For example, say you have a table with a non-clustered index on a particular column. Whenever you need to pull out a row with data that is not in the non-clustered index, SQL will have to go to the underlying table to pull out the data.
When you are pulling 1 row it usually will be cheapest to go to the non-clustered index and then hit the main table, however at some point if your are pulling lots of rows it may become cheaper just to scan the whole table.
bottom line If querying patterns change you may want to review your indexes, it is possible that an index that worked great for a top 10 query, will no longer be picked for a top 50 query.
Sam Saffron
2010-10-22 00:26:38