SQL Execution (Enterprise Manager-QA) VS. .NET Execution




- April 20, 2015

Rest of the Story:

I have been chasing an issue for quite a while where the query would timeout when executed from an ASP.NET interface.  If I ran that exact same query through Query Analyzer the results would be returned in less than 2 seconds.  I have for a while been struggling with why it is different between those two interfaces.  I thought about connection pooling issues, command time outs and connection timeouts and was focused on that for a while.  Even after extending those values from the default the query would still time out.  It was often I would see an exception similar to --------------------------- SQL Exception Information:
ErrorId: -2
Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Line Number: 0
Procedure:
--------------------------- To put this post in the proper context think about one of your application search stored procedures.  The interface shows a dozen or more optional textboxes for user entry to narrow down the results.  The stored procedure in this case often has many parameters that could be used with the actual query.  This is the case for the query that I am discussing herein. When you execute a stored procedure for the first time the query processor constructs a query plan based on the the state of the statistics and the input parameters.  So, the query plan is created upon first execution and is cached in case some other invocation of the same stored procedure comes along before the cache is cleared.  So, when executing through the web interface SQL Server is using the same execution plan that was created by the prior user.  This can look significantly different between users and what parameters they provide before they run the search. So, to have SQL Server generate a new query plan upon each execution the "WITH RECOMPILE" can be used.  By adding this parameter SQL Server regardless of the parameters passed in it will create a new optimal plan.

i.e.
Create Procedure dbo.GetCustomers
@LastName varchar(200),
@FirstName varchar(200),
....  WITH RECOMPILE AS
BEGIN

  END

New in SQL Server 2005 it has a new RECOMPILE query hint option (see also OPTIMIZE FOR for can take advantage for local parameters also).  More information regarding these options can be found by researching 'parameter sniffing' problems' related to query execution.  So with 2005 instead of just using the WITH RECOMPILE (available in both 2000 and 2005) option at the stored procedure level you have more granular control at SQL level within the stored procedure.  By this I mean you can have some SQL statements within a stored procedure that use a common execution plan (ep) while others within the same stored procedure can create new optimal ep's upon each execution.
i.e. SQL 2005 option
Create Procedure dbo.GetCustomers
@LastName varchar(200),
@FirstName varchar(200),
....  AS
BEGIN
Query 1
Query 2 OPTION(RECOMPILE)
Query 3 OPTION(RECOMPILE)
Query 4    END
So when would use the recompile feature?  When you feel it would be inefficient to reuse a previously cached execution plan you should consider using it. The only aspect of this problem that still I have not found confirmation on is why it behaved differently in SQL Server Query Analyzer OR from within VS.NET.  I am tending to think that the optimal plan was generated each time from within those interfaces while when running from the ASP.NET web application it was using one that was cached on the server.  If anyone knows the answer to this I would very much like to know.