We are running a site with classic asp and ASP.NET MVC 4 (C#) side by side. During high traffic, database queries are running really slow in the asp pages. At the same time, in the same site, C# pages are always connecting normally to the same DB. CPU, memory and network usage are normal on both servers (powerful hardware/connection).
The site has been running the same setup and traffic load for years without any problems, this behavior started about a week ago. Does anyone know what could be wrong?
DB Server: SQL Server 2012 Web Edition
Web Server: Windows Server 2012 IIS 8.0
Connection string:
conn.connectionString = "Provider=SQLNCLI11;Persist Security Info=True;User ID=abc;Password=abc;Initial Catalog=sampledb;Data Source=192.168.10.11"
Sample loading times (ms) in iis server log:
2015-09-05 18:00:07 23642 /page.asp
2015-09-05 18:00:07 13547 /page.asp
2015-09-05 18:00:07 93 /ASP.NET
2015-09-05 18:00:07 11172 /page.asp
2015-09-05 18:00:07 78 /ASP.NET
2015-09-05 18:00:07 578 /ASP.NET
2015-09-05 18:00:07 10828 /page.asp
2015-09-05 18:00:07 32252 /page.asp
2015-09-05 18:00:07 13641 /page.asp
Sometimes, numbers are better for the asp pages:
2015-09-05 18:07:30 218 /page.asp
2015-09-05 18:07:30 3281 /page.asp
2015-09-05 18:07:30 46 /page.asp
2015-09-05 18:07:30 2375 /page.asp
2015-09-05 18:07:30 78 /page.asp
2015-09-05 18:07:30 46 /ASP.NET
2015-09-05 18:07:30 203 /ASP.NET
2015-09-05 18:07:30 2906 /page.asp
2015-09-05 18:07:30 1781 /page.asp
Asp queries are generally just slow, but sometimes we get an error:
Microsoft SQL Server Native Client 11.0 error '80040e31'
Query timeout expired
A test .asp page running six identical sql queries, with total page load time in seconds. One query is taking 13 seconds, the other ones are pretty much instant. Next run another query is running slow, sometimes they are all fast.
Query 1: 0
Query 2: 0,3554688
Query 3: 0,375
Query 4: 13,32813
Query 5: 13,32813
Query 6: 13,32813
set nocount on;
before executing each Stored Procedure solved the issue. The thing is that the ADO DB driver would expect a return ofROWCOUNT
from insert/update but on code I was ignoring it. It throws timeouts if ignored.