1

Issue:

Trying to get the same (or better) performance with a separate SQL Server 2017 and IIS 7.5 server than with having both on the same machine.

Trying to move toward scalability for future growth.

Using WebSurge to test the speen on the same box that's running both IIS and SQL Server 2017 Developer with 32gb of ram on 6 cores and 12 logical processors, there are no major performance issues with the machine. We can run the code below on WebSurge at a rate of 4000 requests per second with great speed.

The Problem:

Once we run the same code on a separate IIS box and a separate SQL Server on the same gigabit connection on the local LAN, we can only get about 350 requests per second on the IIS running on its own box. Note: SQL Server is using <1% CPU on the box it is running on with these 350 requests, so it is not an issue with the query or SQL Server performance, as the same code runs over 4000 with IIS on the same machine.

The LAN cables are plugged into the same switch and PING is <1ms at all times from IIS to SQL Server.

What are we doing wrong where the completed requests slow down so drastically, but when run again on the same IIS/SQL Server box... it is over 4000 per second.

We are planning on a creating a large service soon were web traffic will well exceed 4000 requests per second so we will need additional servers, load balancers etc. We are currently trying to see why we can only handle 350 requests per second from an IIS box, not on the same machine as the server.

I'll also point out that the CPU is running nowhere above 50% in either scenario.

SQL Server only consumes approx 10% when running this code.

<%
rangedb="Provider=SQLOLEDB;" & _ 
        "Data Source=10.10.1.220,1433;" & _ 
        "Initial Catalog=xxxxxxx;" & _ 
        "Network=DBMSSOCN;" & _ 
        "User Id=xxxxxx;" & _ 
        "Password=xxxxxx" 

set cnn = Server.CreateObject("ADODB.Connection")
set rst = Server.CreateObject("ADODB.RecordSet")

cnn.open rangedb

sqltext = "SELECT page_content,template_id FROM master_templates_data where  (template_id='165' or template_id='166' or template_id='167' or template_id='168' or template_id='169' or template_id='170' or template_id='171' or template_id='172' or template_id='173' or template_id='182' or template_id='183' or template_id='184' or template_id='185' or template_id='186' or template_id='187' or template_id='188' or template_id='189')"

rst.CursorLocation=2
rst.CursorType=0
rst.LockType=1
rst.Open sqltext,cnn

dataArray = rst.GetRows()

rst.close

cnn.close
%>

We have looked all over the Internet and cannot find a reason for this.

Any help would be appreciated!

(Yes, we know classic ASP is not the way to go for a project like this but we would like to solve this issue for our current situations)

If there is a better alternative such as MySQL and PHP/Linux we would be open to how the performance would be better.

This is our first time posting here so please be kind :)

Additionally, we have tried to run SELECT 1 as the query and it boosts to about 800 rps on the separate IIS box, but jumps to 14,000 on the box where IIS and SQL reside together.

Trying to find where the breakdown in speed is happening since we are on a gigabit intranet, boxes are plugged into the same switch, and ping is <1ms

No virus or firewalls are activated currently for this testing to rule those out as well.

UPDATE:

I went ahead and plugged 2 computers directly into each other and also had them on the same switch and ran a throughput test and I was able to achieve just below 1gb speed with data transfer (no file transfer) so the connection is up to par it appears.

I also reran the web server test and got around 900 requests per second processed with IIS being on a separate box using another computer.

It seems like IIS ASP and SQL on 2 different machines is running 4 times slower using the same script and software as when running on the local machine.

The DB is pulling from a solid state drive so the drive speed should not be the issue as it would be the issue locally as well (the driver resides on the SQL server machine, not a file server)

  • 1
    The answer in this question seems like it might be relevant: serverfault.com/questions/906984/… – Rob Pearson Jun 3 '18 at 22:48
  • 1. copy a large file. does it reach max expected throughput? 2. change the test program to do the same number of data retrieval operations but keeping the same connection alive. if that makes a huge difference, focus on pooling metrics in profiler. – dlatikay Jun 4 '18 at 9:52
  • I updated my question with some additional results. We are achieving the 1gb we expect, but when IIS is on a separate machine from the SQL server, it runs at around 900 requests per second rather than the 4000 when IIS is on the same machine as the SQL server. – Bob Kreitz Jun 4 '18 at 13:23
  • Are the requests that you are testing with being run in serial? – Rob Pearson Jun 4 '18 at 15:27
  • Web Surge is what we have been using. When we run it against a remote production server we can see it start to max out the processors as we increase the load. – Bob Kreitz Jun 4 '18 at 18:21
0

You should make sure the duplex settings on your nic's all match up. Always must have the same duplex setting, whether it's AUTO everywhere, or 1 Gbps Full duplex. This means checking the duplex setting on the nic's (network cards) of each server, as well as any hardware between them, like a switch.

Next, I would run wireshark on both of the boxes and get traces of the network activity going on while the tests are running, and see if anything unusual pops up, or if the two do not match up properly.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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