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