Stored procedure times out when ran from code, but not from query tool

sql server stored procedure timeout problem
fast in ssms slow in application
ssrs report running slow query fast
dapper parameter sniffing
stored procedure taking long time to execute than sql query
sqlcmd slower than management studio
arithabort performance
update query plan stored procedure

I am trying to figure out why a stored procedure call takes seconds in a SQL server express query window, but when I run call the stored procedure in code the query TIMES OUT. We are using sql server 2008. I know it is hard to say exactly what is going on without seeing the stored procedure. I'm just hoping this is a known issue. Any guidance is much appreciated.

SQL query that calls "STORED_PROCEDURE_X" and runs in 2 seconds in SQL server express query window:

EXEC STORED_PROCEDURE_X '07/01/2010', '07/31/2010', 0, '', 'true','', 'Top 20'

Code that calls "STORED_PROCEDURE_X" and TIMES OUT:

SqlConnection connSQL = null;
SqlCommand sqlCmd = null;
SqlDataAdapter sqlDataAdpater = null;
DataTable returnData = null;

try
{
    returnData = new DataTable();
    connSQL = new SqlConnection(sqlConnection);
    sqlCmd = new SqlCommand("STORED_PROC_X", connSQL);
    if (connSQL.State == ConnectionState.Closed)
    {
        connSQL.Open();
    }
    sqlCmd.CommandType = CommandType.StoredProcedure;
    sqlCmd.CommandTimeout = 600;
    sqlCmd.Parameters.Add("@StartDate", SqlDbType.NVarChar).Value = "07/01/2010";
    sqlCmd.Parameters.Add("@EndDate", SqlDbType.NVarChar).Value = "07/31/2010";

    sqlCmd.Parameters.Add("@AuditType", SqlDbType.Int).Value = "0";

    sqlCmd.Parameters.Add("@SortBy", SqlDbType.NVarChar).Value = "";

    sqlCmd.Parameters.Add("@IsClaimDepartment", SqlDbType.NVarChar).Value = "true";
    sqlCmd.Parameters.Add("@IdsList", SqlDbType.NVarChar).Value = "";
    sqlCmd.Parameters.Add("@ReportType", SqlDbType.NVarChar).Value = "Top 20";
    sqlDataAdpater = new SqlDataAdapter(sqlCmd);
    sqlDataAdpater.Fill(returnData);
    if (connSQL.State == ConnectionState.Open)
    {
        connSQL.Close();
    }
    return returnData;
}
catch (Exception ex)
{
    LogErrorMessages("ExecuteStoredProcedure", ex.Message);
    throw ex;
}

Exception Received:

System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

So, running a stored proc that returned 30 records took me 00:00 second in the management console but when loading it in .net took me about 40 seconds, more than the default 30 sec TimeOut.

I just modify the stored proc and rerun the ALTER PROCEDURE... code without changing anything and the problem was solved instantly. I don't have more details about the source of this error, but at least it's a very quick fix

https://social.msdn.microsoft.com/Forums/sqlserver, Run your query for the most common set of parameters If not, you may still have parameter sniffing issues on other combinations, or you may not. WITH RECOMPILE is the quick band-aid, multiple stored procedures is the as actual parameters and as part of IF statements that put blocks of code in). And failing that, have the stored procedure call a stored procedure, call a UDF, call a UDF, call a stored procedure, call a UDF to query the view. To sum up, the following run fast from QA, but slow when put into a stored procedure: The original:

What helped me always a lot was to Add the "with recompile" Option to the Procedure.

http://technet.microsoft.com/en-us/library/ms190439.aspx

Slow query that times-out my application, any advice?, It turns out that this particular process had 2 SQL transactions happening on one page. .com/sqlservertip/1370/identifying-deprecated-sql-server-code-with-​profiler/. Reply We have not extended the timeout yet, that is our next logical step. Just to be precise: SQL Server stored procedures do not have timeouts in and of  when tool times stored ssrs slow runs run query not make how from faster fast code but application asp.net sql-server-2008 stored-procedures SQL Server: Query fast, but slow from procedure

Assuming that you are passing the same parameters from your code as when you are testing in SSMS and your SSMS test is exactly the same in terms of data type usage I would have thought this is likely to be a parameter sniffing issue.

Do you have access to SQL Profiler (doesn't come with Express edition) to get both actual execution plans? If not you can follow the advice in this answer to get the plans.

[SOLVED] Odd SQL Timeout Error, Increasing the timeout has no effect (eg. increasing it from 30 secs to 60 secs just (NB It isn't always this stored proc that fails but that is the example I gave above​. Again the query ran fine in Management Studio however. When you select a Stored Procedure window from the Database Explorer the Run button will be shown. Click on this to show the Run Procedure window. Running Oracle Packages. Oracle Stored Procedures can be contained inside packages. The Run Stored Procedure window cannot run such procedures (this is because AQT cannot get the information on the parameters for these procedures).

I had a similar issue with stored procedures running slower than the same query in the query window. I had tried everything from coding for parameter sniffing (local variables), removing clustered indexes and using only non-clustered, etc. It still took 22 seconds minuimum to retrieve a varchar field, using a nvarchar parameter.

I originally thought it was just the difference between nvarchar and varchar and changed the database field to nvarchar. I lost another full day, moving 50 million records to a new table and re-indexing. Still took over 22 seconds.

Finally, I changed any key fields in the tables from nvarchar to varchar, plus all the parameters and wow; back dowen to less than 1 second.

I strongly believe this is a bug in SQL Server, that has never got corrected. How can you run a query directly in the query window, or call sql from vb.net or c# code and get results in less than 1 second; then run the same query, using parameters, in a stored procedure and get such horrendus results?

Short answer: Stay away from nvarchar data types at all costs.

Also, learn to use the merge statement to move data into large tables to avoid timeouts. Set recovery mode to simple, while running a large merge query; then put back to full recovery.

Boy, did I learn alot this week. Over 80 hours of education that I did not need. But, LinkToMeet.com, now has over 150 million members and all is fine.

QUERY TIMES OUT FROM ADO BUT RUNS FINE IN QUERY AN , All other SP's that were run against the database worked okay but not this one. We then ran SP_UPDATESTATS and the SP began to work as expected again. If you post the code from the stored proc I can probably fix it for you. This happens when a stored proc has a borderline execution plan and sometimes the query  you want to export the results of a stored procedure to a file; you are running a Stored Procedure contained inside an Oracle Package. Copy to SQL Window. From the Run Procedure window, you can use Copy to SQL Window to create an SQL script to run the procedure. This is the recommended method when wishing to run a procedure from the SQL window.

Are you using any transactions in your stored procedure? Uncommitted transactions will cause this exact error message.

Stored Procedure Timeout Question : SQLServer, I manually ran those same procedures in SSMS and they executed in NoBrowseTable Indicates that the plan does not use a work table to options by going to Tools > Options > Query Execution > SQL Server > Advanced. point out that adding SET ARITHABORT ON to your procedure is not a solution. It was all good but 2 of my stored procedures started timing out intermittently today with: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated. If I execute the sp manually from management studio it's still all good.

SQL Server stored procedure runs fast in SSMS and slow in , If your application does not use stored procedures, but submits SQL One complication here, though, is that the SQL Server tools spew out SET You should not forget that one possible reason that the procedure ran slow in the When you tested the query three hours later in SSMS, the blocker had completed its work. If you're not running a trace that will capture the exec event (and don't have a default trace running) then you'll have to have an application level auditing mechanism in place to capture the running of stored procedures. If you don't have either of those going then you're not going to be able to go back in time to see when the procs have run.

Slow in the Application, Fast in SSMS?, We have ran oracle sql queries in oracle directly, once as a SQL statement and But when we execute the SQL statement or stored procedure in Pega, the DBA The SQL never finishes before having stuck threads, connection timeout, and a not displayed for delegated decison table and data types Idea Low Code App  When SQL server finshes to complete execution of these procedures, it gives ten lines showing any row changes caused by all these stored procedures. What i want to do is that after Execution of all stored procedures SQL Server also gives in output window the execution time for each Stored Procedures. Can i do this? I am sure that for achieving

Oracle SQL query runs fast directly but dies executed through Pega , Intimidating Databases and Code the exact same query (whether a straight up ad hoc query, stored procedure, The query is just a pig there, but not on TEST. result in different execution plans and therefore different execution times. of differences highlighted, but one ought to jump out pretty quick. I am facing-out a very surprising problem in SQL Server. The problem is when the query run in SSMS window it will return result in ~1 sec and when the query run through a stored procedure it will execute in ~57 sec. For example :

Comments
  • I don't know if it would help, but I'd build the command completely before opening the connection.
  • @AllenG: plus I'd put SqlConnection and SqlCommand into using(...) { ...... } blocks, too
  • One thign I notice: when you execute from SSMS, you are not supplying Unicode string - from your code, you are. What is the stored proc expecting?? VARCHAR or NVARCHAR?? Also : why aren't you specifying maxa lengths for your NVARCHAR parameters in your code??
  • ALtering the procedure saved me too..... Interested to see why? Any expert opinion?
  • Seems like the compiled sproc was corrupted in some way.
  • This can improve the procedure’s processing performance. straight from the source. Good on ya', mate.
  • Also, that is essentially what the top voted answer does, right?
  • @GibralterTop Yes, except that "with recompile" prevents parameter sniffing so that you don't have to drop and recreate every time you encounter issues
  • Yes these type conversions from NVARCHAR could cause problems using indexes as well.
  • After few days I again got this exception so alter is not a solution.
  • How on earth do you conclude that this is a better solution than what was already suggested here?
  • sir in our code, we can set command timeout to 0 so that code give not give error until stored procedure complete