Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question
    
Check for saturated database connection limits. Are inefficient database queries causing too much network I/O? Is disk I/O high at the time? Is the application pool restarting what is causing it to suddenly drop? If so, why is it restarting? Due to memory limits being hit? –  Mufasa Oct 3 '11 at 15:50

1 Answer 1

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.