Why there are so many connections?
in [Server Configuration]
|
Prev: ANN: Database Workbench 4.0.2 released - including Free Lite Editions
Next: SQL05 Cluster: Striped tempdb reverting to original non-striped?
From: aspfun via SQLMonster.com on 9 Jun 2010 09:54 In my asp.net app, I use ado.net and store procedures. Last night about 10 pm, I ran app and then ran sp_who2. I found that there are so many SPID were generate. Is it some problem in my app? Status:sleeping login: sa hostname: intranet1 dbname: live command:AWAITING COMMAND programname: .Net SqlClient Data Provider SPID CPUTime DiskIO LastBatch 58 0 0 06/08/2010 22:44 60 0 0 06/08/2010 22:44 62 0 1 06/08/2010 22:44 63 0 0 06/08/2010 22:44 64 47 37 06/08/2010 22:48 69 15 0 06/08/2010 22:48 70 0 2 06/08/2010 22:48 71 0 0 06/08/2010 22:48 57 0 0 06/08/2010 22:49 59 0 0 06/08/2010 22:49 68 0 0 06/08/2010 22:49 72 32 40 06/08/2010 22:49 73 0 14 06/08/2010 22:50 77 0 0 06/08/2010 22:50 78 0 0 06/08/2010 22:50 79 0 0 06/08/2010 22:50 80 0 5 06/08/2010 22:51 81 0 0 06/08/2010 22:51 82 15 1 06/08/2010 22:51 83 0 0 06/08/2010 22:51 84 0 0 06/08/2010 22:51 85 0 0 06/08/2010 22:51 86 0 6 06/08/2010 22:51 87 0 0 06/08/2010 22:51 88 0 0 06/08/2010 22:51 89 0 0 06/08/2010 22:51 74 15 0 06/08/2010 22:52 90 16 4 06/08/2010 22:52 91 0 0 06/08/2010 22:52 92 0 0 06/08/2010 22:52 61 0 0 06/08/2010 22:55 93 0 5 06/08/2010 22:55 65 0 9 06/08/2010 22:57 75 0 0 06/08/2010 22:57 76 0 0 06/08/2010 22:57 94 0 0 06/08/2010 22:57 66 0 0 06/08/2010 23:00 67 0 0 06/08/2010 23:00 95 16 15 06/08/2010 23:00 -- Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/201006/1
From: Erland Sommarskog on 9 Jun 2010 17:18 aspfun via SQLMonster.com (u53138(a)uwe) writes: > In my asp.net app, I use ado.net and store procedures. Last night about > 10 pm, I ran app and then ran sp_who2. I found that there are so many > SPID were generate. Is it some problem in my app? Maybe. Maybe not. It's difficult to tell without further knowledge. But what is plausible is that you have a leak in the connection pool. How you do you use connections? Do you open a connection each time you run a query? In such case, do you close it and dispose it explicitly? Or declare it with Using? If you only open the connection, it might get closed at the end. But the connection object will live until garabge collection sets in. Then again, if there are lots of simultaneous requests to the web server, I guess it's perfectly normal. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: aspfun via SQLMonster.com on 10 Jun 2010 10:08 Erland Sommarskog wrote: >> In my asp.net app, I use ado.net and store procedures. Last night about >> 10 pm, I ran app and then ran sp_who2. I found that there are so many >> SPID were generate. Is it some problem in my app? > >Maybe. Maybe not. It's difficult to tell without further knowledge. > >But what is plausible is that you have a leak in the connection pool. >How you do you use connections? Do you open a connection each time you >run a query? In such case, do you close it and dispose it explicitly? >Or declare it with Using? > >If you only open the connection, it might get closed at the end. But >the connection object will live until garabge collection sets in. > >Then again, if there are lots of simultaneous requests to the web >server, I guess it's perfectly normal. > Here is more code. I using store procedure in which using transaction to insert data in 5 tables. Dim myDSN As String = ConfigurationManager.AppSettings("ConnectionString") //connectionstring store in web.config file Dim myConn As New SqlConnection(myDSN) Dim mySQLCommand As New SqlCommand("sUM_approve", myConn) mySQLCommand.CommandType = CommandType.StoredProcedure mySQLCommand.CommandTimeout = 5000 ... Using myConn Try myConn.Open() mySQLCommand.ExecuteNonQuery() myConn.Close() Return True Catch ex As SqlException Return False Finally myConn.Close() End Try End Using -- Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/201006/1
From: Erland Sommarskog on 10 Jun 2010 16:29
aspfun via SQLMonster.com (u53138(a)uwe) writes: > Dim myConn As New SqlConnection(myDSN) > Dim mySQLCommand As New SqlCommand("sUM_approve", myConn) > mySQLCommand.CommandType = CommandType.StoredProcedure > mySQLCommand.CommandTimeout = 5000 > .. > Using myConn > Try > myConn.Open() > mySQLCommand.ExecuteNonQuery() > myConn.Close() > Return True > Catch ex As SqlException > Return False > Finally > myConn.Close() > End Try > End Using I don't think this is correct. You are using "Using", but I think you need to create the connection in the Using clause itself. And I think you should create the Command there too. Here is some code that I have around: Using cn As SqlConnection = setup_connection(), _ cmd As SqlCommand = cn.CreateCommand() ' The query to run. cmd.CommandText = _ "SELECT C.CustomerID, C.CompanyName " & _ "FROM Northwind.dbo.Customers C " & _ "WHERE C.CustomerID IN (SELECT id.custid FROM @custids id)" cmd.CommandType = CommandType.Text ' Add the table parameter. cmd.Parameters.Add("@custids", SqlDbType.Structured) cmd.Parameters("@custids").Direction = ParameterDirection.Input ' When we use CommandType.Text, we must specify the name of ' the table type. cmd.Parameters("@custids").TypeName = "custid_list_tbltype" ' We pass our data table as the parameter value. cmd.Parameters("@custids").Value = custid_list ' Time to run the command. To keep the code brief we use a ' DataAdapter.Fill, although this may not be the most efficient. Using da As new SqlDataAdapter(cmd), _ ds As new DataSet() da.Fill(ds) PrintDataSet(ds) End Using End Using Note that declare the connections and the command in the Using clause. Please note that this is more a question of .Net programming than an SQL Server question, and you may get more accurate answers in an ASP .Net forum. This is certainly outside my area of expertise. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx |