Hello everybody. I am experiencing the following problem: on a W2008R2/SQL 2008 x64 box I have a procedure that runs in 2 seconds when I am inside Sql Management Studio. When I call this procedure from inside a Web part (in a Windows Sharepoint Services 3.0 site), the duration increases so much that the
ado.net command raises a timeout exception. The web part is surely not responsible as long as it has been working for a long time and works perfectly with similar objects. It looks like the sql server engine behaves differently in the two situations. Any help is appreciated
The SQL Server engine probably isn't behaving differently, but your connection is either impacting this, or the config of the connection is. Trace the query in the 2 scenarios - are there any differences in the execution? If so, why? Different parameters, different logins, different ANSI settings in connection? If not then step up a level, check the difference between SSMS -> DB and Sharepoint -> DB, I don't know but one might be on the same box, and the other over a dial-up adsl line. That can make a lot of difference especially if the result set is heavy.
Try laso check statistics on the tables wheher they are up to date and if not, update them. Once I encountered similar problem on one db. When a SQL statement was executed from within SSMS it given results within a seccond. But when called remotely and the same statement was inside a stored proc, different (not optimal) plan was taken and the execution took more than 45 minutes. The problem was in out of date statistics. After statistics update it responded again correctly.
I've had this problem and mine was caused by different default ANSI options between SSMS and
ADO.Net. Tn our case I found that
ADO.Net sets Arithmetic Abort ON, while SSMS goes with the SQL Serve default, which is OFF. This is one of the settings that affects query plan re-use and it meant that when I tested a problem query I was getting a different query plan than the users. I fixed it by changing the SQL Server default to be on, which meant that at least I was diagnosing the plan that was running poorly. This was the right thing in our environment but your situation might be different. I suggest you compare the two connections using the Profiler to see if this is your problem. If the settings are different look them up in Books Online to see whether they affect query plan re-use to confirm that this is what is happening. Then you can run the query from SSMS with the same settings and see what's really going on in the query plan and compare it with fast plan you were seeing.
Hello everybody and thank you all for your cooperation. I have been a little busy in these days but I have continued to try to understand what is happening. This is the up to date situation. I have removed the "Sharepoint" element: with an
ADO.Net console application I have run the same procedure and the duration was still about a couple of minutes. The difference in the allocated memory is not the issue: I have seen that the query plans didn't show that situation on every run but the performance did not change: my conclusion is that the query with both plans would run fast but it seems as though there is something else that "hangs". At a certain moment (yesterday :) )I thought that the difference could lie in the fact that SSMS is a 32 bit application and both Sharepoint and the console program run x64, but I didn't manage to verify that because this morning every query runs fast and the problem has disappeared. This would lead me to say that it has been a "statistics" problem, as some of you suggested, but of course I have updated them since the beginning and I am quite sure that statistics cannot be the issue. One more thing: the system has not been rebooted since the 17th. If any of you have an idea...