Website Slowdown

Question

I'm seeing a large amount of slowdown on a website I'm working on toward the end of each working day. There seems to be an issue with the database as I'm seeing a large number of connections in the Activity Monitor that seems to be sitting there doing nothing. The Task State is blank which suggests it's a running query but they never seem to disappear. When I run the SQL Profiler I can compare the SPID and see the EventClass is an "ExistingConnection" and the TextData reads:

-- network protocol: TCP/IP
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed

This doesn't make a lot of sense to me and there seems to be hundreds of these entries all with different SPID's that never do anything. Any ideas?

Answer 1

I'd check web application is using well known connection pooling solution, recheck that it is closing connections (if there is some reason not to use a standard connection pooling solution) and then if there are other users of the database I'd want to know that it is the web application causing the issue, I'm not sure how to test that in your case. If there was not much data and I did not understand the DB well enough I would set up a dedicated temporary DB to see if the issue continued. Obviously that would not work in many situations but anything to partition the problem.

Answer 2

• A connection doing nothing consumes some memory but not much else

• A connection doing nothing means your app may not be closing them

• The output you give is "Existing connections" in profiler and is run for each connection. It's irrelevant

We need more info. Like what batch jobs or processes or backups or index maintenance is running at the end of each working day?