1

My company is currently in the process of migrating our client-facing web server. This has been an ongoing project for several months(namely because we’re making a very, very, big leap in technology) and we are now less than a week away from our migration window closing. We have hit a wall with one of our client-side processes that needs to be fixed before we can go live with the new server. Everything else seems to be working correctly though.

In short, one of our stored procedures keeps timing out. It works fine on our live server(completes in <1 second), however when we try to run the process on the new server, it times out after 30 seconds. Through testing and tracing we have pinned down the exact procedure that is timing out, but have no explanation as to why it works in our production environment and not on our replacement server.

Some possible explanations we have:

Network Latency - Unlikely at this point, but possible because the production server and it’s replacement are in two different physical locations with low bandwidth between them.

Something to do with MSDTC - We encountered some errors with this process that involved MSDTC. We have corrected those errors and verified that the connection between the new server and the SQL server is working correctly via DTCPing.

Something to do with AD DS - Our new server is part of our domain. The old one is not joined to a domain, nor is the SQL server. However we have not seen any authentication errors, or Networking errors during troubleshooting of this particular issue.

What i have tried:

After googling SQL server timeouts and finding several similar cases, i tried recompiling the affected stored procedure with an sp_recompile query, and resetting stats with an sp_resetstats query. neither solution has yielded any change.

I have traced the transaction(via SQL Server profiler) on both the servers and they are both making all the same calls up to this one point. what’s even more baffling is that the same procedure that keeps timing out is successfully called earlier in the trace on both servers.

Unfortunately i cannot post the trace files here because they contain sensitive information.

Relevant details of our servers and setup:

Note: None of these servers are running in virtualized environments, each one has dedicated hardware.

Note: Both the Production and new servers are connected to the same SQL database.

Production Web Server:

  • NAME: SVR23

  • SYSTEM: Dell PowerEdge 2950

  • OS: Windows Server 2003

  • IIS: 6.0

  • CPU ARCH: x86 (32-bit)

  • LOCATION: Site 2
    SQL Server:

  • NAME: CAP1

  • SYSTEM: Dell PowerEdge R710

  • OS: Windows Server 2008R2, SP1

  • SQL SERVER: 2008

  • CPU ARCH: x64 (64-bit)

  • LOCATION: Site 2
    New Web Server:

  • NAME: SVR23A

  • SYSTEM: Dell PowerEdge R440

  • OS: Windows Server 2016

  • IIS: 10.0

  • CPU ARCH: x64 (64-bit)

  • LOCATION: Site 1

Any help or insight would be greatly appreciated.

@Dell_Technologies

6 Spice ups
2

What version of SQL are you running on your new server/

1 Spice up
3

Did you extend the timeout? Just to see if it ever would work at all.

Did you use SQL management studio to inject the same routine and use the procedure debugger in the management studio to see what happens?

You can debug procedures pretty detailed with a step by step debugger integrated in it, but it’s rather unknown, a SQL pro should know it though.

It helped me out before finding a minor issue that caused a procedure to eventually fail.

1 Spice up
4

What Carl may be alluding to is that the SP may contain deprecated commands

2 Spice ups
5

We have not extended the timeout yet, that is our next logical step. It involves recompiling a dll, which i need our 1st shift guy for. Unless there’s a way to override that behavior.

As for the SQL version, we are using the same SQL Server. The SQL server is physically separate from the Web Server. The application itself is very old though, it uses classic ASP and Visual Basic.

6

I know i tagged SQL Server 2005 in the Original post, that’s because 2008 didn’t show up.

7

If it is classic asp, it should be easy to just adjust the connection of the adodb object, it holds the timeout. Yes, there could be a DLL involved.

I would try the exact syntax from the web directly in SQL studio next, there you have more control over timeouts.

1 Spice up
8

So the new server MSSQL version is the same as the old one ?

Are the compatibility levels set the same ?

Edit: Just reread and see they are both using the same SQL server - the same DB as well or a test one ?

Maybe it is responding but the new website is not processing it correctly can you run a wireshark or fiddler to look at the traffic ?

1 Spice up
9

Just to be precise: SQL Server stored procedures do not have timeouts in and of themselves. They either run to completion, throw an exception or simply take so long that your application or whatever gives up on it.

You need to investigate your wait stats on the SQL Server to see what, if anything is going on there. A full discussion is way beyond the scope of a forum post so best place to start is with Paul Randal. He has a blog post here with a script to gather your waits and group them in descending order. The post contains very detailed information and links. Brent Ozar has a easier-to-swallow blog post here covering the same subject. Use these tools with the associated documentation on both sites to see what may be happening inside SQL Server and could point you in the direction of a bottleneck.

My gut feeling based on your post is it is somewhere between SQL Server and the Web server.

2 Spice ups
10

Unless you like waiting 30 seconds for results, I wouldn’t worry so much about the timeout. I would look for the cause of the process taking >30 seconds in on environment and 1 second in another.

I suspect that there’s something locking a table and that the query is just sitting and waiting for access to data, then giving up.

I would also look for connections to other servers. Is there somewhere in this process that they refer very specifically to a server?

select * 
from invoices 
join customers on cstif = inv_cst_id
from mydatabase

Would be way different than

select * 
from invoices 
join liveserver.livedb.dbo.customers on cstif = inv_cst_id
from mydatabase

Ultra simple example, but if one portion of the process is hitting a server it can’t reach, it would possibly time out.

1 Spice up
11

Not sure if you already looked at these, however here are two timeout settings to look at:

MSDTC Transaction Timeout settings (example for 3600 seconds = 1 hour):
Select Start>Settings>Control Panel/Administrative Tools/Component Services
Expand the Tree > Component Services>Computers>My Computer
Right click on My computer and select the Properties menu.
Select the Options tab and you should see the Transaction Timeout field.
Change this to 3600 (1 hour which is the maximum).
If already set at 3600 you can change to 0 to turn off the timeout.
After adjusting, Restart the COM+ application by the following steps:
Right click on My Computer and select Stop MSDTC
Right click on My Computer and select Start MSDTC
SQL Server Remote Connection Timeout settings:
SQL Server Management Studio - expand the Tree to locate the Server.
Right click on the Server and select the Properties menu.
Click the Connections option.
In the Remote Server Connections section of the menu - Update the Remote Query Timeout option.
This option can be set from 0 (no timeout) to 3600 = 1 hour.
You must restart the SQL Server after you adjust the Remote Query Timeout option for update to take effect.

1 Spice up
12

We managed to work it out. It turns out that this particular process had 2 SQL transactions happening on one page. Also the first transaction needed to complete before the second one. IIS was set to run ASP applications in a multi-threaded environment. We think that multi-threaded behavior was causing both transactions to execute at the same time, thus causing a lock condition within SQL that caused a timeout even when the timeout was increased. Our solution was to disable Multi-Threading for that application within IIS. Thanks to everyone for your help and insight.

2 Spice ups