SQL CPU High - SqlConnection not being closed - Related?

2
votes

I have a ASP.net based application.

The CPU on the SQL Server box is constantly ~90 - 100%

There are a lot of inneficient queries, which I am currently working on, however, looking at the code from a previous coder, he never seemed to close (or dispose) the SqlConnection

When I run the folloing query, I get around 450 connections that are "Awaiting Command"

SELECT Count(*) FROM MASTER.DBO.SYSPROCESSES WHERE DB_NAME(DBID) = 'CroCMS' AND DBID != 0 AND cmd = 'AWAITING COMMAND'

Is this likely to be causing a problem?

I read this and it seems to relate: http://www.pythian.com/news/1270/sql-server-understanding-and-controlling-connection-pooling-fragmentation/

We are also getting a lot of timeouts, specifically when replication is enabled.. I'm not sure if this is related.. Have disabled replication (transactional) for now and it seems ok.. (This server is a subscriber to our in office Database server)

Would disposing of the SQL connection object help?

2
c#sql-serverperformancesqlconnection

2 Answers

2
votes

Yes, dispose them. Otherwise ignore them for now. Possibly the pool is as large because the statements are slow. I would more suggest:

  • Fixing the statements.
  • Check the applicaion that it only uses one connection PER REQUEST (i.e. not open multiple at the same time).

If the problem does not get better after optiomizing SQL - you can revisit the pool.

1
votes

You should always dispose the command object when your done with it. that way the connection pooling can be used better.

easist is to use the using statment. 

using (
                var sqlCommand = new SqlCommand(
                    "storedprocname",
                    new SqlConnection("connectionstring"))
                    { CommandType = CommandType.StoredProcedure })
            {
          // do what you should.. setting params executing etc etc. 

}