One of my favorite SQL Server jobs is query tuning, especially on those really long running SQL queries.
Today I had the opportunity with one of the Stedman Solutions mentoring clients to tune a query that took just about 2 hours to run and to get the run time down to 53 seconds. Not only that, but the time it took to tune that query was less than the 2 hours that the query took to run.
This was a complex query that was generated by a search form in an application. After getting a good understanding of the issue, I discovered a table with around 7 million rows that had no indexes on it. I did some investigation on the usage of that table and added a sensible clustered index, and the query now runs much faster. 2 hour run time now taking 53 seconds to run. Given the needs of this query and the difficulty in changing the generated code, the 53 seconds was an acceptable solution for the client.
Another win for the Stedman Solutions SQL Mentoring offering.
