2

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
2
  • 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 9, 2018 at 15:43
  • I’m voting to close this question because itshould really be asked on dba.stackexchange.com, but is too old to migrate.
    – TomTom
    Oct 24, 2022 at 8:09

2 Answers 2

0

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.

enter image description here

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

enter image description here

0

"CPU, memory and network usage are normal" - and they are absolutely IRRELEVANT for a database.

Well, not totally, but you basically say "my car does not go fast enough, and the brake lights are working".

Here is the point: Databases live and die by IO capacity. This is handled partially by memory (data stored in memory is not accessed from disc). And lock time is essential - which means things like indices.

I am pretty sure if you check the markers for disc IO (the only relevant is ms/per request btw) you see that those are through the roof and the disc subsystem is running wild.

If not you have bad transactional handling and have locks in place where one queyry block other queries. Use SSMS and the performance data warehoue feature of SQL Server to start analyzing.

Find your slow queries, fix them.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .