Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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
share|improve this question
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 '11 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 '11 at 19:10

3 Answers 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.

share|improve this answer
    
+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 '11 at 6:56

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

share|improve this answer

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.

share|improve this answer
1  
Yes. Rason for not 100% stored procedures: read the manual. No real advantages. Not for 15 years. –  TomTom Feb 9 '11 at 6:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.