SQL Server Performance

Application timeout

Discussion in 'SQL Server 2005 General DBA Questions' started by chumeniuk, Dec 9, 2010.

  1. chumeniuk New Member

    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.
  2. satya Moderator

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

Share This Page