From: aspfun via SQLMonster.com on
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
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
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
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