Classic asp site, randomly slow DB connection

Question

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

Very old question, but anyway. I recently had an issue with "random" DB query timeouts and that turns out to be a problem with Stored Procedures that have Update/Insert statements. Setting `set nocount on;` before executing each Stored Procedure solved the issue. The thing is that the ADO DB driver would expect a return of `ROWCOUNT ` from insert/update but on code I was ignoring it. It throws timeouts if ignored. — Vetras, Jul 09 '18 at 15:43

score 0 · Answer 1 · answered Sep 12 '15 at 21:52

Install SQL Server Management Studio on your Web Server (or another PC in the same network) and run some queries your application is running.

Enable your Client Statistics. These might tell you what is going on.

The more you run it, the more statistics it will collect and show you

Classic asp site, randomly slow DB connection

1 Answers1