Insert query times out in C# web app, runs fine from SQL Server Management Studio

Question

I'm attempting to get an insert query to run from my C# web application. When I run the query from SQL Server Management Studio, the insert query takes around five minutes to complete. When run from the application, it times out after thirty minutes (yes minutes, not seconds).

I've grabbed the actual SQL statement from the VS debugger and run it from Mgmt Studio and it works fine.

All this is running from my development environment, not a production environment. There is no other SQL Server activity while the query is in progress. I'm using SQL Server 2008 R2 for development. MS VS 2010 Express, Asp.Net 4.0. SQL Server Mgmt Studio 10.

There is a similar question to this that was never answered: SQL server timeout 2000 from C# .NET

Here's the SET options from: dbcc useroptions

Option                  MgtStudio      Application
----------------------- -------------- --------------
textsize                2147483647     -1
language                us_english     us_english
dateformat              mdy            mdy
datefirst               7              7
lock_timeout            -1             -1
quoted_identifier       SET            SET
arithabort              SET            NOT SET
ansi_null_dflt_on       SET            SET
ansi_warnings           SET            SET
ansi_padding            SET            SET
ansi_nulls              SET            SET
concat_null_yields_null SET            SET
isolation level         read committed read committed

Only textsize and arithabort are different.

Any ideas why there is such a difference in query execution time and what I may be able to do to narrow that difference?

I'm not sure how useful including the query will be, especially since it would be too much to include the schema. Anyway, here it is:

INSERT INTO GeocacherPoints
            (CacherID,
             RegionID,
             Board,
             Control,
             Points)
SELECT z.CacherID,
       z.RegionID,
       z.Board,
       21,
       z.Points
FROM   (SELECT CacherID,
               gp.RegionID,
               Board=gp.Board + 10,
               ( CASE
                   WHEN (SELECT COUNT(*)
                         FROM   Geocache g
                                JOIN GeocacheRegions r
                                  ON ( r.CacheID = g.ID )
                         WHERE  r.RegionID = gp.RegionID
                                AND g.FinderPoints >= 5) < 20 THEN NULL
                   ELSE (SELECT SUM(y.FinderPoints) / 20
                         FROM   (SELECT x.FinderPoints,
                                        ROW_NUMBER() OVER (ORDER BY x.FinderPoints DESC, x.ID) AS Row
                                 FROM   (SELECT g.FinderPoints,
                                                g.ID
                                         FROM   Geocache g
                                                JOIN Log l
                                                  ON ( l.CacheID = g.ID )
                                                JOIN Geocacher c
                                                  ON ( c.ID = l.CacherID )
                                                JOIN GeocacheRegions r
                                                  ON ( r.CacheID = g.ID )
                                         WHERE  YEAR(l.LogDate) = @Year
                                                AND g.FinderPoints >= 5
                                                AND c.ID = gp.CacherID
                                                AND r.RegionID = gp.RegionID) x) y
                         WHERE  y.Row <= 20)
                 END ) Points
        FROM   GeocacherPoints gp
               JOIN Region r
                 ON r.RegionID = gp.RegionID
        WHERE  gp.Control = 21
               AND r.RegionType IN ( 'All', 'State' )
               AND gp.Board = @Board - 10) z
WHERE  z.Points IS NOT NULL
       AND z.Points >= 1 

Answer 1

ARITHABORT is often misdiagnosed as the cause.

In fact since version 2005 when ANSI_WARNINGS is on (as it is in both your connections) ARITHABORT is implicitly on anyway and this setting has no real effect.

However it does have a side effect. To allow for the cases where ANSI_WARNINGS is off the setting of ARITHABORT is used as one of the plan cache keys which means that sessions with different settings for this cannot share each other's plans.

The execution plan cached for your application cannot be reused when you run the query in SSMS except if they both have the same plan cache key so it gets a new plan compiled that "sniffs" the parameter values that are currently under test. The plan for your application was likely compiled for different parameter values. This issue is known as "parameter sniffing".

You can retrieve and compare both execution plans with something like

SELECT usecounts, cacheobjtype, objtype, text, query_plan, value as set_options
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
CROSS APPLY sys.dm_exec_query_plan(plan_handle) 
cross APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
where text like '%INSERT INTO GeocacherPoints (CacherID,RegionID,Board,Control,Points)%' 
and attribute='set_options' and text not like '%this query%'

The parameters section in the XML tells you the compile time value of the parameters.

See Slow in the Application, Fast in SSMS? Understanding Performance Mysteries for more.

Execution Plans

You've supplied the estimated execution plans rather than the actual execution plans but it can be seen that only the first query plan is parameterised and it was compiled for the following values.

        <ParameterList>
          <ColumnReference Column="@Dec31" ParameterCompiledValue="'2013-12-31'" />
          <ColumnReference Column="@Jan1" ParameterCompiledValue="'2013-01-01'" />
          <ColumnReference Column="@Board" ParameterCompiledValue="(71)" />
        </ParameterList>

The second execution plan uses variables rather than parameters. That changes things significantly.

DECLARE @Board INT
DECLARE @Jan1 DATE
DECLARE @Dec31 DATE

SET @Board=71
SET @Jan1='January 1, 2013'
SET @Dec31='December 31, 2013'

INSERT INTO GeocacherPoints

SQL Server does not sniff the specific value of variables and generates a generic plan similar to using the OPTIMIZE FOR UNKNOWN hint. The estimated row counts in that plan are much higher than in the first plan.

You don't state which is the fast plan and which is the slow plan. If the one using variables is faster then likely you need to update statistics you may well be encountering the issue described here Statistics, row estimations and the ascending date column if the one using parameters is faster then you will be able to achieve variable sniffing and get it to take account of the actual variable values by using the OPTION (RECOMPILE) hint.

Answer 2

If you are using SqlCommand and you don't specify a value for CommandTimeout, it will automatically timeout after 30 seconds.