The links in this post contain affiliate links and I will receive a small commission if you make a purchase after clicking on my link.
When it comes to simple SQL queries, I never notice any performance difference in execution time when comparing stored procedures executed in SQL Server Management Studio (SSMS) and the actual .NET C# Application hosting on IIS.
However, when it comes to more complicated queries joined over many tables of many more records, I'm often times experiencing noticeable differences in execution time. A query that may only take 3 seconds in SSMS can take upwards of 2 minutes or more in my .NET application, which is obviously unacceptable. It's using the exact same SQL Server instance and both are simply executing a stored procedure.
So what is happening?
It all comes down to the execution plan that the stored procedure uses. Since the default option settings in SSMS is different than those in IIS, the same stored procedure end up choosing different execution plans. So how can I prove the it's using a different execution time.
First, Query that identifies any execution plans related to your stored procedure. Make sure you're executing this on your target database. You should see multiple execution plans. Replace MyProc with your stored procedure name.
select o.object_id, s.plan_handle, h.query_plan
from sys.objects o
inner join sys.dm_exec_procedure_stats s on o.object_id = s.object_id
cross apply sys.dm_exec_query_plan(s.plan_handle) h
where o.object_id = object_id('MyProc')
Second, to view the execution plan and the options, take the plan_handle from the first query and use it in this query. This will return a set of attributes. You'll want to find the value from the attribute 'set_options'.
select * from sys.dm_exec_plan_attributes (0x0500060085C0FC5940415E46040000000000000000000000)
From here, you can determine which options have been set for the execution plan based on the following by using a bitwise operation:
| Option | Value |
ANSI_PADDING
| 1 |
Parallel Plan
| 2 |
FORCEPLAN
| 4 |
CONCAT_NULL_YIELDS_NULL
| 8 |
ANSI_WARNINGS
| 16 |
ANSI_NULLS
| 32 |
QUOTED_IDENTIFIER
| 64 |
ANSI_NULL_DFLT_ON
| 128 |
ANSI_NULL_DFLT_OFF
| 256 |
NoBrowseTable Indicates that the plan does not use a work table to implement a FOR BROWSE operation.
| 512 |
TriggerOneRow Indicates that the plan contains single row optimization for AFTER trigger delta tables.
| 1024 |
ResyncQuery Indicates that the query was submitted by internal system stored procedures.
| 2048 |
ARITH_ABORT
| 4096 |
NUMERIC_ROUNDABORT
| 8192 |
DATEFIRST
| 16348 |
DATEFORMAT
| 32768 |
LanguageID
| 65536 |
UPON Indicates that the database option PARAMETERIZATION was set to FORCED when the plan was compiled.
| 131072 |
ROWCOUNT - Applies To: SQL Server 2012 to SQL Server 2016
| 262144 |
Third, to clear your execution plan cache, run the next system procedure. This will clear all cached execution plans, but they will be automatically regenerated the next time a stored procedure is run. This is useful if you want to confirm which execution plans belongs to SSMS and which execution plans belongs to the .NET IIS version.
sp_recompile
The Problem
The problem is if you compare the set_option values is that for me, ARITH_ABORT was not ON in the .NET IIS version. This surprisingly caused the big difference in execution time. To reach this conclusion, I compare the options that were ON in the two execution times to figure out the missing option.
The Solution
To solve the problem, make sure you have ARITH_ABORT turned ON before the stored procedure is executed. For me, this means in C#, I have to enable ARITH_ABORT when the connection is created. A pseudo code example below is a quick example in BOLDED RED:
SqlConnection mySqlConnection = new SqlConnection(myConnectionstring);
SqlCommand turnOnArithAbort = new SqlCommand("SET ARITHABORT ON", mySqlConnection);
turnOnArithAbort.ExecuteNonQuery();
SqlCommand actualStoredProcedureCall = new SqlCommand();
actualStoredProcedureCall.CommandText = "dbo.xmltext_import";
actualStoredProcedureCall.CommandType = CommandType.StoredProcedure;
actualStoredProcedureCall.Connection = mySqlConnection;
mySqlConnection.Open();
actualStoredProcedureCall.ExecuteNonQuery();
mySqlConnection.Close();This may not be the most elegant solution, as you'll be excuting SET ARITHABORT ON multiple times, but it works in my situation and effectively corrects all connection problems for my application without messing with the database server and other applications.
References: