I am having an ongoing issue in a production vendor application, and I don’t know what to look at next.
I have 2 copies of the exact same database.
1 copy has indexes that have been freshly rebuilt with next to zero fragmentation.
1 copy has indexes that were rebuilt 7 days prior.
When running the exact same query against these 2 databases, in the freshly reindexed DB, the query runs in 10 seconds. In the week old indexed DB, it just under 4 mins.
Initially to get around this issue, we began rebuilding our indexes every 2 weeks. However, it has gotten to the point that after a Sunday morning rebuild, by Monday afternoon, the queries are back to running long within the app, sometimes not returning at all.
The highest fragmentation level on the slow running database, with the tables involved in the query, is 30%, most other indexes are less than 1% fragmented.
I have moved both databases to the same server instance, to use same SQL version (SQL 2005 ENT SP4 CU3) with the same hardware.
Looking at the execution plans of each, it appears that in the slow running database, the most expensive part of the query is a Hash Match (76%), while at the same point in the Indexed database, it is a nested loop at 0%.
I am not a programmer by any means, but I don’t understand how indexes that are not overly fragmented can cause such an issue. And also don’t understand how it can occur so fast after a complete Index rebuild.
I’ve attached the execution plans to this post. Any assistance would be great appreciated.