x

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
more ▼

asked Nov 18, 2010 at 08:39 AM in Default

jpp gravatar image

jpp
21 1 1 1

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.
Nov 19, 2010 at 03:34 PM Mister Magoo
(comments are locked)
10|1200 characters needed characters left

6 answers: sort voted first

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.
more ▼

answered Nov 18, 2010 at 08:44 AM

Kev Riley gravatar image

Kev Riley ♦♦
53.9k 47 49 76

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.
Nov 18, 2010 at 08:58 AM jpp

@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).
Nov 18, 2010 at 09:11 AM Oleg
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.
Nov 18, 2010 at 09:24 AM jpp
@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.
Nov 18, 2010 at 01:22 PM Oleg
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?
Nov 19, 2010 at 03:21 AM jpp
(comments are locked)
10|1200 characters needed characters left
Is it collecting a lot of data that is being shipped across your network? Can you post the code for the procedure please?
more ▼

answered Nov 18, 2010 at 08:43 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

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)
Nov 18, 2010 at 08:51 AM jpp
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Nov 19, 2010 at 09:00 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

Excellent answer! It can be the parameter sniffing issue. Try to add option(recompile) or option(optimize for unknown)
Nov 19, 2010 at 02:42 PM Håkan Winther

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.
Nov 20, 2010 at 12:40 AM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Nov 19, 2010 at 11:52 PM

David Wimbush gravatar image

David Wimbush
5.1k 29 31 34

(comments are locked)
10|1200 characters needed characters left
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.
more ▼

answered Nov 18, 2010 at 11:03 AM

Håkan Winther gravatar image

Håkan Winther
15.7k 35 37 48

Thank you Hakan, but as long as only this procedure (among many) times out, I guess it can't be anything dealing with network or connection issues.
Nov 18, 2010 at 12:42 PM jpp
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1850
x24
x18

asked: Nov 18, 2010 at 08:39 AM

Seen: 3593 times

Last Updated: Nov 18, 2010 at 08:40 AM