How do I find what code is consumming my SQL Server connection pool?

I have rewritten the below based on the answers.

I have a website that causes HIGH CPU issues on the database server to the point where the server becomes unavailable. Recycling the app pool fixes the issue. According to the server administrator http://www.microsoft.com/downloads/details.aspx?FamilyID=28bd5941-c458-46f1-b24d-f60151d875a3&displaylang=en shows there are threads that are active for about an hour.

The interactions with the database are very simple and worked prior to us adding web forms routing to the application.

They only consists of code like this throughout the application. Yes, this code is not perfect, but its not this code that is an issue as prior to us adding routing, there were no problems.

  private string GetPublishedParagraphs()
  {
    string query, paragraphs = "";
    try
    {

      m_sql_connection = new SqlConnection(m_base_page.ConnectionString());
      query = "select * from PublishedParagraphs where IDDataContent_page='" + m_IDDataContent_page + "'";

      SqlDataAdapter da = new SqlDataAdapter(query, m_sql_connection);
      DataSet ds = new DataSet();
      da.Fill(ds, "paragraph");

      if (ds.Tables["paragraph"].Rows.Count > 0)
        paragraphs = (string)ds.Tables["paragraph"].Rows[0]["paragraphs"];

      ds.Dispose();
     da.Dispose();
   }
   finally
   {
     开发者_StackOverflow中文版m_sql_connection.Close();
   }

   paragraphs = paragraphs.Replace("™", "™");

   return paragraphs;
}

The connection string looks like:

server_name; User ID=server_user; Password=server_password

We have meticulously checked that every call to the database Open() is followed by a Close(). We have measured there are no open connections by viewing them as we run the application locally and the connection count does not increase via:

SELECT SPID,
       STATUS,
       PROGRAM_NAME,
       LOGINAME=RTRIM(LOGINAME),
       HOSTNAME,
       CMD
FROM  MASTER.DBO.SYSPROCESSES
WHERE DB_NAME(DBID) = 'TEST' AND DBID != 0 

(However, if we don't Close connections, there is a leak)

The difference between our application from when it worked is the addition of asp.net routing via web forms. This calls the database too, but again closes connections after they are open.

We are not sure what else we can check for. Any ideas fellow programmers?

ANSWER

We found the problem via Query Profiler. This showed us a query with high usage. Tracing the query back to the code showed an infinite loop calling the database over and over. It was difficult to find as the loop was initiated by a bot calling a page on the website that no longer existed.

• In the code you are showing, the ds and da .Dispose go in the finally block. Better yet, use the using () {} structure which ensures object disposal

• the pattern of build your own string as a query isn't just a gaping security hole, it is also very inefficient. Use a stored procedure and a parameter instead.

• the query for processes is overly restrictive. If you have a resource issue that is causing connections to be refused, it won't be limited to a single database. About the only thing I would restrict is the current command --> where spid != @@spid

• REALLY need some error messages and context - where are they being seen? Tell us more and we can help!

Good luck!

• First, great additional information! Thanks for the followup.

• I would suggest that if you're so sure that the code you have posted has nothing to do with the problem that you remove it from the question. However, the problems aren't an issue of merely being "imperfect". Proper disposal of memory intensive objects - ones that the initial developers recognized as intensive enough to include the dispose() method - ones that interact with the database - while you are having unexplained problems with database isn't a small issue, in my opinion anyways.

• I did some googling and found this. While I wouldn't go and say that this is the problem, it did get me to thinking. When "threads that are active for about an hour", is that being measured on the db server or on the web server? I'm not familiar with the tool, but are you able to post logs from this tool?

• On the webserver, are you able to monitor the routing code's actions? Is the routing code written / setup in such a way as to protect against infinite loops - see the question and answers here text.

• In the earlier version of my answer, I said to you that looking only @ connections for a particular database was too restrictive for your task. The clarifications to your question do not indicate that you have corrected this query. I would suggest:

    SELECT 
        is_being_blocked = sp.blocked
        , sp.cpu
        , DB_NAME ( dbid )
        , sp.status
        , LOGINAME=RTRIM(sp.LOGINAME)
        , sp.HOSTNAME
        , sp.Hostprocess
        , sp.CMD
    FROM  SYSPROCESSES sp
    WHERE spid != @@SPID
    ORDER BY 
        sp.blocked ASC
        , sp.cpu DESC

• Logs - what are the SQL Server Logs saying in the time span 10 minutes before and 10 minutes after you restart the web app?

• Have you tried and is this issue repeatable in development?

• Please tell us what the below statement means in terms of your application - an error message or other: "the server becomes unavailable"

• I highly suggest that, you startup a trace of sql server using profiler. According to what you are saying in this question, this is what I would trace saving to table ( on another sql server ) or saving to file ( on another machine NOT the sql server box ). This trace is for finding a problem that is severely hampering production. It's not something that you would want running on a regular basis.

I would capture these events

* Errors and Warnings - all of them
* Security Audit 
** Audit Login
** Audit Logout
* Sessions
** Existing Sessions
* TSQL
** SQL: Stmt Starting
** SQL: Stmt Completed
** Prepare SQL
** Exec Prepared SQL

I wouldn't use any filters other than the presets.

Have you tried running the "sp_who2" query in SQL Server Management Studio to see how many active database connections there are as the code looks fine.

You might want to change the scope of the m_sql_connection variable from class scope to member scope. Perhaps that could be your issue?

what do you mean by "running out of application pool?" Do you mean the connection pool?

If your database seems to be getting overworked, it could also be because a user has free reign over your m_IDDataContent_page variable. This data access code is vulnerable to sql injection.