Question
I have a query that acts strangely. Most of the time it runs very quickly, in just under a few seconds. But on occasion, it takes over 20 minutes to run. This is rare, but it happens periodically. I have checked and verified that it is the exact same query each time (no difference in the code or parameters). In addition, I rebuild the indexes in my database once a week to ensure that their is minimal fragmentation and all statistics are kept up-to-date. Can you offer a suggestion on how to prevent this problem?
Answer Assuming you have ruled out things like blocking locks, out-of-date statistics, or other SQL Server-related issues, you may have run across a query that causes the Query Optimizer to have fits. I have run across these myself from time-to-time.
When the Query Optimizer gets a query to optimize, it uses a sophisticated algorithm, along with gathering other information, such as index and column statistics, to determine the optimum way to execute the query. And in most cases it makes the correct decision. But the Query Optimizer is not perfect and occasionally makes the wrong decisions, even for queries that are normally optimized correctly.
The best way to identify if the Query Optimizer is the actual culprit is to perform two steps. First, capture a query plan for the query in question when it is runs fast. Second, capture a query plan for the query in question when it runs slow. This is not always easy, but if you use Profiler to captures a query’s execution plan, and you are patient, it is possible.
If the two execution plans are identical (for the fast and slow running query), then we know the Query Optimizer is not at fault, and we then need to look for other sources of the problem. But if they are different (and assuming the queries are identical), then most likely the Query Optimizer is at fault.
In this case, if you carefully analyze the differences between the two query plans, you most likely will find that the query plan for the slow performing query is not using indexes correctly, and one or more table scans may be used instead, or one or more joins in the query are being performed sub-optimally.
Now that you have identified the cause of the problem (Query Optimizer is not optimizing the query correctly all the time), how do you prevent the problem? The solution I use is to use Query Hints. Hints can be used to override what the Query Optimizer recommends. For example, if you noticed that the slow query didn’t use an available index like it normally does, you can add a hint to your query to force the Query Optimizer to use the index. This will ensure that the Query Optimizer will always correctly run the query.
]]>