SQL Server Performance and Update Statistics

Question

Hi

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

Is not that the statistics are outdated. What happens when you update statistics all plans get invalidated and some bad cached plan gets evicted. Things run smooth until a bad plan gets again cached and causes slow execution.

The real question is why do you get bad plans to start with? We can get into lengthy technical and philosophical arguments whether a query processor shoudl create a bad plan to start with, but the thing is that, when applications are written in a certain way, bad plans can happen. The typical example is having a where clause like (@somevaribale is null) or (somefield= @somevariable). Ultimately 99% of the bad plans can be traced to developers writing queries that have C style procedural expectation instead of sound, set based, relational processing.

What you need to do now is to identify the bad queries. Is really easy, just check sys.dm_exec_query_stats, the bad queries will stand out in terms of total_elapsed_time and total_logical_reads. Once you identified the bad plan, you can take corrective measures which depend from query to query.

Answer 2

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)