SQL Server Performance and Update Statistics

Question

We have a site in development that when we deployed it to the client's production server, we started getting query timeouts after a couple of hours.

This was with a single user testing it and on our server (which is identical in terms of Sql Server version number - 2005 SP3) we have never had the same problem.

One of our senior developers had come across similar behaviour in a previous job and he ran a query to manually update the statistics and the problem magically went away - the query returned in a few miliseconds.

A couple of hours later, the same problem occurred.So we again manually updated the statistics and again, the problem went away. We've checked the database properties and sure enough, auto update statistics isTRUE.

As a temporary measure, we've set a task to update stats periodically, but clearly, this isn't a good solution.

The developer who experienced this problem before is certain it's an environment problem - when it occurred for him previously, it went away of its own accord after a few days.

We have examined the SQL server installation on their db server and it's not what I would regard as normal. Although they have SQL 2005 installed (and not 2008) there's an empty "100" folder in installation directory. There is also MSQL.1, MSQL.2, MSQL.3 and MSQL.4 (which is where the executables and data are actually stored).

If anybody has any ideas we'd be very grateful - I'm of the opinion that rather than the statistics failing to update, they are somehow becoming corrupt.

Many thanks

Tony

Answer 1

Disagreeing with Remus...

Parameter sniffing allows SQL Server to guess the optimal plan for a wide range of input values. Some times, it's wrong and the plan is bad because of an atypical value or a poorly chosen default.

I used to be able to demonstrate this on demand by changing a default between 0 and NULL: plan and performance changed dramatically.

A statistics update will invalidate the plan. The query will thus be compiled and cached when next used

The workarounds are one of these follows:

• parameter masking
• use OPTIMISE FOR UNKNOWN hint
• duplicate "default"

See these SO questions

• Why does the SqlServer optimizer get so confused with parameters?
• At some point in your career with SQL Server does parameter sniffing just jump out and attack?
• SQL poor stored procedure execution plan performance - parameter sniffing
• Known issue?: SQL Server 2005 stored procedure fails to complete with a parameter
• ...and Google search on SO

Now, Remus works for the SQL Server development team. However, this phenomenon is well documented by Microsoft on their own website so blaming developers is unfair

• How Data Access Code Affects Database Performance (MSDN mag)
• Suboptimal index usage within stored procedure (MS Connect)
• Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005 (an excellent white paper)