A few days ago a developer came to me with a query that was executing slowly on a staging server. On this server it took 16 long seconds to execute while on other servers it took about 1 second.
I did a quick schema compare and found them to be identical. So I ran the query against each server asking for the actual execution plan. Result, different execution plans! I saved the execution plan as a .sqlplan file and opened it up with SQL Sentry’s Plan Explorer. This is a great tool that breaks down an execution plan into something far more comprehensible than we’re provided with in SSMS.
By comparing this with the original plan I could see a big difference in the actual and estimated number of rows for a particular join.
This resulted in the optimizer deciding to go with a clustered index scan for this join rather than a more efficient index seek. Clearly the statistics for this table were out of date. Here’s what I did;
UPDATE STATISTICS tablename;
This took just a few seconds to execute and the execution of this query went down to milliseconds!