Application timeout | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Application timeout

I’m having issues where one of our web services is having a problem with a stored procedure timing out. Now, you are probably saying, check the timeout, tune to query, etc, but here is the rub.
The procedure when called from the web service maxes out the cpu. This can be seen both on the server and while watching sp_who2. The IO columns barely move, but the cpu counter grow rapidly. Now, take the output from profiler and the query executes in less than a second. How can the execution vary this much between the call from the .Net web service and running the same thing locally?
BTW, I also put together a quick VB.Net app to just call the stored proc and the same thing happens. The code for this is below:
Dim strCnn As String = "Data Source=MACDB;Initial Catalog=BMBEventProcessing;User Id=TETATest;Password=test;"
Dim cn As New SqlClient.SqlConnection(strCnn)
Dim strCmd As String = "getAndUpdateNewSEA"
Dim cmd As New SqlClient.SqlCommand(strCmd, cn)
cmd.Parameters.AddWithValue("@SubscriberId", 14)
cmd.Parameters.AddWithValue("@EventTypeId", 1)
cmd.Parameters.AddWithValue("@RowCount", 1)
cmd.Parameters.AddWithValue("@StatusComment", "CHH")
cmd.Parameters.AddWithValue("@StatusTypeId", 2)
cmd.CommandType = CommandType.StoredProcedure
cn.Open()
cmd.ExecuteNonQuery()
cn.Close()
Profiler shows:
exec getAndUpdateNewSEA @SubscriberId=14,@EventTypeId=1,@RowCount=1,@StatusComment=’CHH’,@StatusTypeId=2
When I cut and paste this into Management Studio, the procedure instantly returns a result.

DId you check the network connectivity between web adn SQL server?
I guess it could be a network related,,,,

]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |