Diagnose cause of long running requests in IIS 7.0

asp.netiis-7latency

We are running an ASP.NET web application on IIS 7.0, Windows Server 2008 R2, with SQL Server 2008 R2 for DB.

On weekends, when the traffic is high, the request queue length in the IIS servers increase (up to 800 requests) and then drops, every minute or so.

I can see that the servers are handling some requests which, according to the 'Current Requests' view in IIS Manager, are long running (thier Time Elapsed value ranges from 20 to 50 seconds).

Those requests are not necessarily heavy queries, actually, I can't understand why they are taking so long.

Can it be because the client is closing the connection on his side?

Thanks,
Shlomi

Best Answer

It look's like it's the common issue with the connection pooling and to many open connections. On your DAL you should have something like this (it doesn't matter if it is mssql or mysql):

(I'm assuming that you are using DataReader)

    /// <summary>
    /// Selects a single record from the Media table.
    /// </summary>
    public virtual Media Select(int mediaID)
    {
        SqlParameter[] parameters = new SqlParameter[]
        {
            new SqlParameter("@MediaID", mediaID)
        };

        using (SqlDataReader dataReader = SqlClientUtility.ExecuteReader(connectionStringName, CommandType.StoredProcedure, "MediaSelect", parameters))
        {
            if (dataReader.Read())
            {
                return MakeMedia(dataReader);
            }
            else
            {
                return null;
            }
        }
    }

using guarantee that SqlDataReader will be properly disposed. This is just a basic example but usually you should have (you may have) a common class for all DAL operation that provide connection helpers and that implements IDisposable. I recommend having a base class that all DAL classes derive from.

If DAL is not the issue I would recommend to digg deeper on the database using SQL Server Profiler to evaluate query executions.

You can also use SQL Server Monitor to check average execution times and un-closed connections. If your DAL has a problem you'll see a lot of pending/waiting connections.

Related Topic