werid query performance issue

[mirthcyy]

We are using sql server 2005 enterprise 64 bit in our production. We
just updated a stored proc with minor changes. After that, our
customers are experiencing problems now and then.

The weird thing is that the new stored proc is always running much
faster than the old one when I tested on the production directly. And
the report using this stored proc normally runs much faster too using
the new one but will time out after a few days. There're no query
blockings when this happens.

After that, I changed it to the old version of the stored proc, the
report will show up slowly again (without timeout). And I applied the
new version again, the report will run fast.

Do you know what could be the reason the report using the new stored
proc will time out after a while? How could I fix the issue?

Thanks!

[Erland Sommarskog]

mirthcyy (mirt...@gmail.com) writes:
> We are using sql server 2005 enterprise 64 bit in our production. We
> just updated a stored proc with minor changes. After that, our
> customers are experiencing problems now and then.
>
> The weird thing is that the new stored proc is always running much
> faster than the old one when I tested on the production directly. And
> the report using this stored proc normally runs much faster too using
> the new one but will time out after a few days. There're no query
> blockings when this happens.
>
> After that, I changed it to the old version of the stored proc, the
> report will show up slowly again (without timeout). And I applied the
> new version again, the report will run fast.

Could be parameter sniffing. In any case, I have an article on my
web site may lead you on the right track:
http://www.sommarskog.se/query-plan-mysteries.html

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx