Performance Tuning from a High Level

We had a report which ran “forever”. Seriously it was a long time. After restoring production backups on to a test instance, I ran the same sql. Not good. I could have grown the coconuts whose water I was drinking. There were some indexing opportunities and that made a difference on my test sql server, though on the production box, when running the stored procedure on the newly indexed tables, the wait was not acceptable. What was interesting is that in profilier for the production procedure execution, even stopping the after a few minutes (test was down to a few seconds at this point), the reads were over 10x what I saw on test for a properly completed run. On the other hand, running the procedure innards resulted in a good execution time and same reads as test. This made me think about parameter sniffing. Sure enough, after adding local variables which were set to the input parameter values we had a speedy execution.