1

We have a business web application in ASP.NET + SQL Server 2008.

In the beginning, SQL Server and IIS were on the same machine. Now we bought another machine. Current configuration is IIS machine plus SQL Server machine, and they are connected by a 1gb LAN connection.

With this configuration our web application is slower than before. Max bandwidth is 1-2% of network, about 15mbps.

When we use another threads to the same SQL Server from the same IIS machine, network use is higher. So this is no problem with SQL Server.

Ho we can make higher bandwidth for this SQL connection?

Specs:

  • .Net 3.5
  • SQL Server 2008 Standard
  • file transfer can use 100% of LAN SQL
  • connection by TCP/IP protocol SQL
  • logins Pool tested with enable and
2
  • 2
    Do you use the SQL server's name or IP address within your web app to connect to the SQL server? It could be a slowdown caused by a problem resolving the SQL server's name, and using it's IP would tell you that.
    – KJ-SRS
    Jan 14, 2011 at 17:31
  • I would seriously consider this ^^ option, I recently switched from using the sql server name to the ip address and definitely noticed a speed increase.
    – jonezy
    Feb 15, 2011 at 19:10

4 Answers 4

3

Using Profiler, examine the queries running on the SQL instance.

If the application isn't coded well, you may be in a 'death-by-1000 cuts' scenario where there are hundreds or thousands of small, possibly insignificant queries returning single rows instead of as a set, or a JOINed result. Every time the application wants data for one of these sets of queries, separating the tiers introduces a huge amount of extra latency over a network connection. This is pretty counter-intuitive if you haven't seen this situation before.

Running Profiler on a server with relatively high query volume can be difficult, but if it's that high volume, you can turn it on for a few times, 1-2 minutes at a time, and get a good sample. Just whatever you do, don't run Profiler over a network connection. Either run Profiler on the SQL Server box via RDP, or set up the trace to run on the instance itself (preferred).

I suppose you could throw hardware at this to reduce the network latency. IMO, that really isn't scalable, but if you're in a pinch with a huge application that needs to be fixed up, it is an option, at least temporarily.

1
  • +1. THe one thing different now is the addition of a smaller pipe (irrelevant) and possibly a small amount of network latency. "Death by 1000 cuts" seems like a very likely culprit.
    – TomTom
    Feb 9, 2011 at 6:56
1

Are they good quality servers with decent network cards? cheap network cards (like cheap RAID cards) are a false economy. And all the latest drivers? You have to start at one end and work your way through verifying everything has a clean configuration. Then start troubleshooting.

And have you allocated enough memory to your processes?

Always look for things that are 'different'. My latest weird one is a high end server that was running slowly. Plenty of CPU, memory, no disk activity etc. Task Manager showed 120,000 handles?! A printer driver was leaking a handle every 2 seconds..

0

When both the web server and SQL Server reside on the same machine, by default shared memory is used for the protocol. If you move the SQL Server to another box the communication has to go via TCP/IP.

Basically when using shared memory the client application can read tabular data streams (TDS) directly from SQL Server's memory. When using the TCP/IP protocol the TDS has to be converted to TCP/IP on the server, sent to the client, and converted back into TDS, which takes time.

I can't seem to find a good reference for recent versions, but read through this, it hasn't changed that much since.

If your app runs on the same machine as your SQL Server, consider using the shared memory Net-Library if you aren't already. Shared memory Net-Library-based connections are often considerably faster than other types of connections. Keep in mind what I said earlier, though: always thoroughly test a solution and compare it with viable alternatives before assuming that it is inherently better or faster. The proof is in the pudding.

and this blog post gives a brief overview too. A more technical explanation can be found over at simple-talk

-1

Start by loading up the performance monitor sql counters on the sql box and having a look.

a .net app? chances are that it is badly written with adhoc queries all over the place.

Are you using 100 percent stored procs? If not, there had better be a good reason.

Having the SQL server on the same box probably masked some of the bad design choices previously made in the app. Now, they are out in plain view.

SQL queries can be blindingly fast on a 56k modem, if only the result set is returned. But, not if you are doing things like using client side cursors where you have to haul everything back across the wire.

1
  • 2
    Yes. Rason for not 100% stored procedures: read the manual. No real advantages. Not for 15 years.
    – TomTom
    Feb 9, 2011 at 6:55

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .