Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Without actual query plans your question can attract guesses in the best case. Could you please check whether that query produces the same plan here and there? Also, try to compare timing using the same query tool, you have too many factors in the equation. –  dezso Sep 13 '13 at 8:11
    
How would we check the query plan for a query that is run from our application code? That is the reason why we run the code in SSMS in the first place - so we can see what's going on. –  JDT Sep 13 '13 at 8:19
    
start the built-in profiler to see what is going on: SSMS=>Extras=>SQL Server Profiler –  DrCopyPaste Sep 13 '13 at 8:50
    
@JDT - You can also get it from the plan cache. Example Query –  Martin Smith Sep 13 '13 at 8:59
add comment

1 Answer

up vote 2 down vote accepted

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 SET options 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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.