>In addition to Naomi's question about fragmentation....how slow is "slower"....can you give some numbers?
For the Web, the SLA for one application I maintain is under 2 seconds. For my applications, I am targeting at 0.2. If a hit on a Web site requires more than two second, than I will try to see if there could be enhancements. If not, I will try to see if our robot could prepare some of that so when come times to make the option available on the Web, it will be faster because the robot would have prepared some mostly used options. Then, there could be some usage of pivot tables so we can query on them instead on the big transactional tables.
>Also, how many rows are in the table being queried?
In this one, it is already a pivot table build by a robot in order to accelerate the queries. There are 2.1 million records. The SQL command uses four where clauses, which are two integer fields and one date field used twice for the in between equivalence. All the fields in the SQL command where clause are indexed. To optimize, I created an index which combines those three fields. It helped but I only succeeded to drop it from 2.8 to 2.6 seconds. This is why I created another thread to know if those included fields in an index could really make a difference and how can I interpret the execution plan to really identify which fields the execution plan is trying to tell me to include in the index.