When we face performance issues with out application, one of the first things we do is try to isolate if the problem is the code, the queries or the database itself. The most frustrating part of this is that the same query, executed in code or executed in a SQL tool such as the Query Analyzer of SQL Server Management Studio, can have radically different timings.
We are currently facing an issue where the same query executed in the code takes (on average) 15-20 seconds. The same query on one of our development laptops (against the same database-server) will take the same amount of time, whereas another development laptop will run the query in under 3 seconds.
I'm looking for reasons why the same query, run against the same database at the same time of day, would have such radically different timings depending on where it's run from (and why a previously good performing query would suddenly turn into a slower-than-a-snail-riding-a-tortoise monstrosity without any changes to the database). Does a tool such as SQL Server Management Studio execute queries differently as opposed to our code?
Best Answer
Different execution plans.
When you run the query from different environments many things must be the same in order to reuse the cached query plan.
Including the query text for adhoc queries, various
SEToptions and the default schema of the logged in user if the query relies on implicit name resolution.This is usually a sign of a parameter sniffing issue where the optimum plan generated can vary according to the value of the parameters passed in. When you run the query in SSMS if it can not reuse the cached plan the application is using you get a new plan compiled that is suited for the parameter values under test.
See Slow in the Application, Fast in SSMS? Understanding Performance Mysteries for more.