asked
Sql Server 2008 strange timeout
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
Any chance you could post the procedure - for one thing it seems very odd that it takes 2 seconds to return only 22 rows. It may be worth examining what that query is doing.
Is it collecting a lot of data that is being shipped across your network? Can you post the code for the procedure please?
Hi Fatherjack, the procedure sends back only 22 rows of normal data (no big fields). The only peculiar thing is that it is poorly written. It uses a lot of joins of the type -- SELECT ... FROM TABLE1 JOIN ( SELECT a , b , c FROM X WHERE d = ... ) AS TABLE2 ON TABLE1.field12 = TABLE2.Field2 JOIN ( SELECT a , b , c FROM Y WHERE d = ... ) AS TABLE3 ON TABLE1.field13 = TABLE3.Field2 .... As I said though, inside SQL Management Studio it only takes 2 seconds to run. The base tables have no more than 2000 rows. More than changing the query though I am interested in other issues I might have to face similar to this (in other words if I were sure that it depends only on the "ugly" T-SQL I wouldn't care much about it)
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.
@jpp Since you get a command timeout rather then connection timeout, Kevan's answer is very probably spot on. Different session level ANSI settings can be to blame. Default timeout for SqlConnection is 15 seconds, you are OK here. SqlCommand instance default timeout is 30 seconds, and from what you describe, command timeout occurs because the proc takes longer than that time to actually execute. Just to check how bad is your problem, you can set the CommandTimeout of your SqlCommand instance to either something somewhat greater than 30 or to 0, which will instruct the engine NOT to timeout the query, so you can get your code working and measure execution time. Checking the execution plans should help because it is possible that you will see the differences between the 2. If you can capture the poorly performing plan, you can save it and then force the proc to use it. This will allow you to reveal that the problem has nothing to do with network, but is rather caused by the execution plan (in case if you will start getting poor execution times dfrom SSMS as well).
@jpp Connection timeouts can be specified in the connection string in the web.config, but this is seldom needed. On the other hand, it can prove to be pretty useful to drop a line in the appSettings to store the CommandTimeout. This way, the value can be read before the first call to the database is made and then used as a CommandTimeout of any instance of the SqlCommand objects used by the application. This way, it is much easier to troubleshoot the problems like you have without the need to recompile the binary. Just make a change to the web.config file, which by the way has a great side-effect of recycling the worker process (you don't have to do the nasty **iisreset**), so the changes are immediately available to the very next request to the site.
Hi Kev, the query was run from inside the same box (which was hosting SQL-SSMS and WSS-Web part). Nothing changed when I tried to use a different box (WSS3.0 x32, same SQL server, same LAN). Following your advice I tried to use SSMS using the same account used by WSS, but nothing changed: the query inside SSMS run in 2 seconds. Furthermore other queries run normally.
Hi Oleg. I will try to raise the commandtimeout parameter. It will take a little time 'cause I will have to rebuild, transfer redeploy etc. and I can't do it now, but I will let you know as soon as I manage to do it. I guess however that you an Kev are right. Maybe it really depends on different procedure compilation and execution plans. Just to give you another clue I have tried to add a completely useless condition ( WHERE TABLEn.field < 100000 with TABLEn.field actually varying from 1 to 30) and the web part started behaving correctly. I thought the execution plans were reused but probably this is not the case and the issue is just finding the reason.
Hi Oleg. I have extended the command timeout and the query works even though it takes a couple of minutes to complete (I mean, as expected, there are no errors in the query itself). I have compared the execution plans and both of them are serial (no parallelism) but they are actually different. Furthermore the procedure run from within SSMS uses 128 Mb memory while the procedure run from the web part is given only 6 Mb. I think this is enough to explain the different performances, but I don't know how I can control that. Do you have suggestions before diving into manuals?
Make sure that SQL server responds to TcpIP protocol, by default the Shared memory protocol is enabled, and you may need to activate the TcpIp protocol.
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.
Excellent answer! It can be the parameter sniffing issue. Try to add option(recompile) or option(optimize for unknown)
Yah, it could be also parameter sniffing issue, tha thteoptmizer chooses different plan. But in my situation it was really statistics. As in the query was used a single stored proc parameter directly and it didn't worked even after clearing query plan cache. The issue was, it was query selecting from several tables with a few of milions of rows. and to each of that tables every day is added a few thousands of rows. The queery worked with rows from only a few last days, but the statistics were not auto updated as not significat amount of rows were added to the tables. The result was query optimizer coose wrong order for joins and processing. Only I din't find why it worked with the query directly and not from withing the stored proc.
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...
@jpp - can you reproduce this behaviour on a testbed? I would like to suggest you query the plan cache to find out if the two queries are in fact the same this will let you also see if the ANSI settings are all the same. This would be easier on a testbed as you can clear the plan cache, run the queries and only have them in cache. I would not suggest doing this on a running system without knowing about the performance implecations.
