Statistics again and we see same query with two different plans
Posted by rahmanagoro on October 25, 2012
Following on from my earlier post on the importance of keeping statistics up to date, I have come across another issue recently and this time, I have the luxury of actually showing the query plans too. Please note that I have used SQL Sentry plan explorer which you can download for free from http://www.sqlsentry.com
I had a call from one of my clients today complaining about bad performance on their database. The irony of this particular case is that I can see it myself by actually running the query on the database server and I could see that its taking a lot of time to complete for a fairly trivial query that shouldn’t really take that long to complete. I was lucky in this particular case that I also had a UAT system to run the query on as well, as it was a report which wasn’t really changing data, it made rerunning the report slightly less intrusive.
I proceeded onto UAT, ran the query and he pesto it completed within 9 seconds as opposed to 3 hours which it was taking on production. In my normal fashion, I started taking a look at the query plans and one thing which became obvious is the fact that the query plans are somewhat different on the 2 systems. Although the way the joins in the query have been written isn’t the best I have seen in my career but these things do happen. I looked through one of the tables and used the SQL Sentry query plan too to observe that the estimated rows on the table was somewhat wrong. I can also see that the wait stats on the query was CXPACKET waits, the query uses parallelism but I can’t really say it’s a bad thing.
The section of the plan below shows the table with the right number of row estimates for the statistics.
The query below shows the same query but with the statistics somewhat wrong.
Although looking at the query, the optimizer also recommended that an index be created on the table, I was hesitate to create the index as the query was working fine before, so why would it start to behave badly all of a sudden. After updating the statistics on the table, cleared the procedure cache and reran the query everything was fine once more. This just proves the importance of having up to date statistics and also the fact that when the optimizer recommends that you create indexes, it’s not always the answer to every performance problem.