June 8, 2007 at 4:15 pm
In the last week our production environment has started causing some grief. We have reports that are timing out (60 seconds ). SqlClient timeout...
When I run the exact same query (same userid) in SSMS the result set returns in < 1 second.
SS2005 on Server 2003.
If the server is rebooted or SQL restarted the issue goes away for awhile (heavy usage? still testing).
Has anybody ran into a similiar issue?
thanks
Daryl
June 11, 2007 at 8:50 am
We had a very similar issue at the place where I used to work. We would find that the SP in question would run slowly from the client, but fast from query analyzer or SSMS. We could often recompile the stored procedure (through a drop/add) and that would fix it. Then it would run fine for a random amount of time, then start running slowly again.
Mike Dillon has since posted this article: http://www.sqlservercentral.com/columnists/mdillon/2863.asp which seems to pinpoint the problem you're seeing. Basically it's that the SP gets run for a small amount of data, and the query plan gets cached using that data sampling. Then the SP gets run with a large amount of data, but uses that cached query plan, which doesn't really fit. You then get bad performance.
Make sure you read the comments on the article.
Hope this helps,
Rick Todd
June 11, 2007 at 10:50 am
Thanks for the great link Rick.
I had finally tracked the problem down (using trace) that the application sql was running an execution plan (@84) and the SSMS was using an execution plan (@5.2). I did not realize SQL would use a separate execution plan (or store multiple execution plans ) for a single sproc.
From the link: I had not heard of [procedure cache] (which I am exploring).
Instead of sp_recompile I added [WITH RECOMPILE] into the stored procedure itself. The execution plan when to (4.8). Performance restored.
thanks again
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply