Stored Procedure is slow, takes 3 times longer in code than SSMS

Question

I am facing a problem related “Cannot find table 0”. Initially I have no idea to find the root problem of this exception. Then I came to know that this problem arose due to the error “Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding”. (i.e) The execution of the Stored procedure in the SQL server 2005 takes time more than 3 seconds (default query timeout is 3 seconds). So I executed the same stored procedure with same parameters in SSMS (SQL Sever Management Studio). But it took only 1 second.

In the middle of time, we run the same source and SQL code in another server (Backup server). But there is no error. (i.e) The whole process (I have a do while loop which loops 40 times , consisting 4 for loops which loops approximately 60 times.Each time it will access 3 stored procedures) took 30 minutes in my system but in backup server only 3 minutes has been taken which means there is no timeout. But all the source are same. So now I came to an end that there is a technical issue involving.

I tried the following things in my source.
•   In Asp.net, I added the “SQLCommand Timeout = 0”. But it is a failure.
•   I used “SET ArithAbort ON” and “With Recompile”. It is also a failure.
•   Then I used “Parameter Sniffing”. (i.e) I used local variables in stored procedures. But all went in a wrong direction.

The Ado.net code for accessing the stored procedure in asp.net is like the following:

 Public Function retds1(ByVal SPName As String, ByVal conn As SqlConnection, Optional ByVal ParameterValues() As Object = Nothing) As DataSet
    dconn = New SqlConnection(ConfigurationManager.ConnectionStrings("webriskpro").ConnectionString)
    Try
        sqlcmd = New SqlCommand
        ds = New DataSet
        If dconn.State = ConnectionState.Open Then dconn.Close()
        sqlcmd = New SqlCommand(SPName, dconn)
        sqlcmd.CommandType = CommandType.StoredProcedure
        sqlcmd.CommandTimeout = 0
        dconn.Open()
        SqlCommandBuilder.DeriveParameters(sqlcmd)
        If Not ParameterValues Is Nothing Then
            For i As Integer = 1 To ParameterValues.Length
                sqlcmd.Parameters(i).Value = ParameterValues(i - 1)
            Next
        End If
        da = New SqlDataAdapter(sqlcmd)
        da.SelectCommand.CommandTimeout = 0
        da.Fill(ds)
    Catch ex As Exception
        send_prj_err2mail(ex, SPName, "")
    Finally
        dconn.Close()
    End Try
    Return ds
End Function

The execution plan of the error giving procedure is

Hope you guys understood my problem. Please get me back with some ideas!!

Answer 1

You will need to debug the application to find out what the root cause is. In another thread you mention:

I have checked the process in SQL Server Profiler. Since the loop has no limit, we don't know how many times it iterates.

You need to write out to a text file how many times the code loops, it may be a small number in Backup compared to a large number on PROD or your local PC. You should also log out how long each part of the operation takes, sample code using a StopWatch:

Dim sw as New StopWatch()
sw.Start()

...long operation...

sw.Stop()
File.Write("Operation XYZ took " & sw.EllapsedMilliseconds / 1000 & " seconds")

I am suspicious on the code here, it doesn't look like it was very well written. For example checking if the connection is open after instantiating it smells of poor resource management. Its like the author of this code found the connection was left open and wasn't closed somewhere else:

dconn = New SqlConnection(ConfigurationManager.ConnectionStrings("webriskpro").ConnectionString)
...
If dconn.State = ConnectionState.Open Then dconn.Close() 

I'd recommend that you use the Using statement which guarantee's the connection will be closed, eg:

Using cn As New SqlConnection(ConnectionString)
cn.Open()

  Using cmd As New SqlCommand("GetCustomerByID", cn)
    Try
        With cmd
            .Connection = cn
            .CommandType = CommandType.StoredProcedure
            .Parameters.Add("@CustomerID", SqlDbType.Int, 4)
            .Parameters("@CustomerID").Value = CustomerID
        End With

        da = New SqlDataAdapter(cmd)
        da.Fill(ds, "Customer")
    Catch ex As Exception

    End Try
  End Using
End Using

Although you know that If the connection is closed before Fill is called, it is opened to retrieve data, then closed. If the connection is open before Fill is called, it remains open. So you don't even need to explicitly open it in your case.

Again you will need to debug the application to find out what the root cause is. Add heaps of logging to help diagnose the problem. Debugging the problem should shed light and give you some clues as to the root cause of this performance bottleneck.

Good luck!!

Edit: For any of the Connection Pooling idea's raised here to work you will need to use ConnectionPooling, so remove Pooling=false from your connection string:

<add name="webriskpro" 
     connectionString="Data Source=TECH01\SQL2005;Initial Catalog=webriskpro1;User ID=sa;Password=#basix123; <strike>pooling=false</strike>;connection

timeout=600;"/>

Edit 2: Some research to help you work out the problem

1. Run SQLDiag which comes as a part of the product. You can refer books online for more details. In brief, capture Server Side trace and blocker script.

2. Once trace is captured, look for "Attention" event. That would be the spid which has received the error. If you filter by SPID, you would see RPC:Completed event before "Attention". Check the time over there. Is that time 30 seconds? If yes, then client waited for 30 second to get response from SQL and got "timed out" [This is client setting as SQL would never stop and connection]

3. Now, check if the query which was running really should take 30 seconds?

4. If yes then tune the query or increase the timeout setting from the client.

5. If no then this query must be waiting for some resources (blocked)

6. At this point go back to Blocker Script and check the time frame when "Attention" came

If you can execute a sp_who2 while the queries are timing out, you can use the BlkBy column to trace back to the holding the lock that everyone else is waiting on.

sp_who3 is useful too as it includes the actual query.

When you see which queries/transaction is locking/blocking your database till complete you may need to rewrite them or run them at an other time to avoid blocking other processes.

An extra point to dig into is the auto increment size of your transaction log and database. Set them on a fixed size instead of a percentage of the current files. If files are getting larger than the time it takes to allocate enough space and that's longer than your transaction timeout then your db will come to a halt.

Answer 2

A few ideas:

• Check the primary server for resource starvation issues: memory, disk space, lots of other processes running and using resources, et cetera.
• Check the primary server for configuration issues: database is configured to use only a small amount of memory, system swap file is too small, et cetera.
• Check the primary server for hardware issues: memory errors, failing RAID, failing drive, et cetera.

Hope that helps!

Answer 3

I think the exception is because of the connection opening time.

You need to specify the timeout for the connection or delete the pooling=false from the connection string.

Give like this,

<add name="db1" connectionString="Data Source=your server;Initial Catalog=dbname;User ID=sa;Password=*****;connection timeout=600;"/>

The default size of the pooling is 100. If you would like to change you then you can.

Since in the backup server there will be no applications running, the speed of the system will be high and there will be no interrupts.

But in your local system, you might have worked on some other applications.So that could be one of the reason. The next time when you run, close all the opened application then check in the sql profiler.

Since the same code is working fine in the backup server, i dont think the loops(60*40 times) will not be the reason for slowing down.

Best of luck!!

Answer 4

If the error occurs only at high loads, it is likely that connections aren't being returned to the pool, and remain associated with the transaction for longer than they should. If transaction speed isn't a problem, try the following query:

SqlConnection.ClearPool(connection);

Note : For every operation in SQL we must open a connection so give this query after opening the connection

conn.Open()

cmd.ExecuteNonQuery()

SQLConnection.ClearPool(conn)