Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Our application uses the .net sql driver, and the queries end up looking something like this in profiler:

sp_executesql N'query where @param = ?, and param2 = ?', param, param2, param3, etc

When copying and pasting the query from Profiler into sql server management studio, the query will run in less than a minute vs 15 - 20 minutes executing from the application.

As far as I can tell they are both using the same execution plan, so I'm not sure what would be different.

To add to the strangeness, we also have a testing sql server which is basically a copy of the production server. In our testing environment with the same code and mostly the same data (a few days out of date from production), the query runs in less than a minute in our application as well as in sql server management studio. Once again, profiler is capturing the exact same execution plan for all of them.

The only thing I've discovered makes the query run correctly is to run sp_updatestats on the database, which we run every morning at 5:00 am. What's strange is by 7:00 am the query will still running slow again. If I run sp_updatestats again, the query will finish in less than a minute. Once again, all execution plans look the same.

I must be missing something. Any ideas?

share|improve this question
    
Have you checked that auto create statistics and auto update statistics for the database(s) are enabled? They probably are, but worth checking. –  Peter Schofield Apr 25 '11 at 20:10
add comment

2 Answers

Does your query involve a table with an ascending datetime or datetime2 column, and one of the parameters is a datetime or datetime2 which is typically looking for a recent value?

Your comments regarding behavior after updating statistics suggests you are experiencing the problem with frequently out of date statistics that Gail Shaw describes here: http://sqlserverpedia.com/blog/sql-server-bloggers/statistics-row-estimations-and-the-ascending-date-column/

As Gail mentions, the most straightforward resolution is a more frequent update of statistics. Ideally target those more frequent updates to the statistics that need them only-- see Update Statistics.

In the case of very large tables, a filtered index might also be useful, depending on the table size and update and read patterns.

share|improve this answer
add comment

Currently in the process of trying to upgrade my SQL Skills, so take this with a grain of salt. But perhaps this is a Parameter Sniffing issue?:

Parameter sniffing is the process whereby SQL Server creates an optimal plan for a stored procedure by using the calling parameters that are passed the first time a stored procedure is executed. By “first time”, I really mean whenever SQL Server is forced to compile or recompile a stored procedures because it is not in the procedure cache. Every subsequent call to the same store procedure with the same parameters will also get an optimal plan, whereas calls with different parameter values may not always get an optimal plan.

-- http://www.simple-talk.com/sql/t-sql-programming/parameter-sniffing/

As I said in my disclaimer, I have a lot to learn here, but if I understand this correctly if this is the case I think if you pass OPTION RECOMPILE to the query and the problem goes away this might have been your problem.

share|improve this answer
    
I'll give the recompile option a try, but the problem isn't with different sets of parameters. In all examples, all parameters are exactly the same, they are just running differently, even though the execution plans are the same. –  Gaidin Mar 25 '11 at 18:36
    
Yeah, the recompile options doesn't resolve the issue either, and I'm still seeing the exact same execution plan. –  Gaidin Mar 25 '11 at 20:16
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.