Execution plan degradation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Execution plan degradation

Author

Topic

cgarcia
Starting Member

4 Posts

Posted - 2008-03-24 : 18:08:33

Hi all,

I am experiencing performance problems with one of my stored procedures. When the stored procedure is first compiled an executed, it behaves as expected (it usually takes 1 or 2 seconds to complete). But its performace it is degradated, so in 1 day, it usually takes 120 seconds to complete !!!. Once the stored procedure is compiled, its performance it is then the expected.

It is a complex stored procedure with two integer parameters with only one select, but composed by multiple views and sub-queries. We have been trying to break the query into small pieces using temporary tables but without success. The SQL Profiler shows an unusual number of reads when it goes wrong (more than a million reads).

I think the problem is in the execution plan. I know than compiling the stored procedure, the problem is fixed, but I do not know exactly when and why it starts to happen.

The stored procedure is running under the following configuration:

- Microsoft SQL Server Standard Edition (64-bit).
- Version: 9.00.1399.06
- RAM 16 MB
- 8 CPUs

Anyone has any ideas or possible solutions?

Thanks in advance,

Carlos.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-24 : 18:37:22

First, you should get to the latest service pack and security patch. I believe we are at build 3200 whereas yours is showing 1399.

Second, I'm sure you mean 16GB for RAM instead of 16MB. Are there other instances on the server?

And third, you can set the stored procedure to recompile each time it is executed. Check out BOL for details.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

cgarcia
Starting Member

4 Posts

Posted - 2008-03-24 : 18:55:23

Thank you tkizer.

1. Ok, I will check the last service pack version.
2. Of course it is 16GB for RAM.
3. I had already tried the WITH RECOMPILE option, but with bad performance results (> than 8 seconds)

Regards,

Carlos.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources