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
{
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.