SQL Server Stored Procedure RPC VS SSMS

Question

I have a stored procedure that takes 1 parameter. When I run the stored procedure from SQL Server Management Studio, it runs in 2-4 seconds. When I call it with a console application, it takes 30+ seconds. The SQL Server is remote and both SSMS and my application are being run from my local machine so I don't think it's a networking issue.

I've ran the SQL Server Profiler to try to track down the issue and one thing I'm seeing is that when it's run from SSMS it starts the statement, recompiles it, then starts it over again, then completes it, like this:

SP:StmtStarting
SP:Recompile
SQL:StmtRecompile
SP:StmtStarting
SP:StmtCompleted

The 2 recompile entries have an EventSubClass of "2 - Statistics changed"

From the app I only see entries for SP:StmtStarting & SP:StmtCompleted, no recompile entries.

I'm calling exactly the same stored procedure with the same parameter value. Why does SSMS recompile based on statistics but my console app does not?

SQL Server's Relational Engine will optimize a "good" query plan and save it in the Plan Cache. The plan will be cached optimized for the initial set of parameters used and the current table statistics. If the number of records in a table change drastically or the actual parameter values change drastically then this can cause the cached plan to become stale. Google Updating Table Statistics and Parameter Sniffing. There is tons of info out there on this issue. — Ross Bush, Dec 21 '18 at 14:20
are you using an ORM in your console application ? If yes which one? any idea about the result volume ? can you mention how do you call the proc in your console? — MNF, Dec 21 '18 at 14:38
@RossBush yeah that was my initial though. What I'm confused about though is that SSMS and my app are getting different results when I run them with the same parameter value within a minute of each other. If that plan is obsolete shouldn't both SSMS and my app be using the same bad execution plan? Why does SSMS have the entries for recompile but when running from my app it does not? — chris-crush-code, Dec 21 '18 at 15:03
@MNF I made a stripped down C# console app to reproduce it. All it does i use the SqlConnection, SqlCommand, and SqlDataReader classes from System.Data & System.Data.SqlClient namespaces. Then I'm just doing while(reader.read()) to loop through the rows of the first result set returned but I'm not actually doing anything with the values since I made it just to reproduce the stored procedure call issues. It returns ~20 rows in the first result set and then several thousand in the second. Both SSMS and the app return the same # of rows though. The profiler shows about 100 times more reads though — chris-crush-code, Dec 21 '18 at 15:08
When testing performance of stored procedures, I always clear the plan cache, perhaps the profiler is doing the same thing in order to get a fair result. You have to send in a recompile command yourself, the client drivers are not going to magically do that. I would be curious as to what result you get from your console app if you add WITH RECOMPILE to your stored procedure. Are you sending over many parameters to filter a where clause, are there big logic branches based on your parameters in the procedure? — Ross Bush, Dec 21 '18 at 15:11
Possible duplicate of [sql runs fast in ssms slow in asp.net](https://stackoverflow.com/questions/10175422/sql-runs-fast-in-ssms-slow-in-asp-net) — Tab Alleman, Mar 05 '19 at 15:36

score 0 · Accepted Answer · answered Mar 05 '19 at 19:18

After researching and troubleshooting it appears to be entirely due to SET_ARITHABORT_ON. SSMS defaults this to 'ON' while the .net sql client defaults it to 'OFF' so it was going with 2 different execution plans, although I'm not entirely sure why the two plans are so drastically different.

I overrode the OpenConnection() method to open the connection set it to ON and my application then had the same performance as SSMS. I hope this helps anyone else who stumbles upon this.

SQL Server Stored Procedure RPC VS SSMS

1 Answers1