Most SQL Server based apps use stored procedures exclusively, so the idea of forcing SQL Server to ignore plan caching is nasty.

According to the BOL, it is "unusual" to use this option.

But I think I have one...

My application has an end user designable reporting component. Put simply, the end user is allowed to create and design reports at runtime.  Users are allowed to either construct queries or browse from a predefined list.  Using the resultant dataset, they can then design a report.

I know some of you would be horrified by the fact that users are allowed to write their own queries.  Security, performance blah, blah the list goes on...  Basically when a user is creating  and testing these queries, the application does 2 things to protect the database.

  1. The sql connection that the application uses changes to a sql account that only has datareader priviliges.
  2. The application first adds SET PARSEONLY ON to the top of the statment. 
  3. If the sql is valid, the application adds SET ROWCOUNT 500 to the top of the statement and then runs it.

Anyway, these "queries" are stored in a db table and end up as "predefined" queries.

Execution of a predefined query is done through a storedprocedure.. Below is a very shortened example..

CREATE proc dbo.upQuery_Executer
@StartDate DateTime = NULL,
@EndDate DateTime = NULL,
@QueryName VARCHAR(255)
with recompile

FROM dbo.Query WHERE QueryName = @QueryName

EXEC sp_executesql @TSQL, N'@StartDate datetime, @EndDate datetime', @StartDate = @StartDate, @EndDate = @EndDate

Does adding "with recompile" here makes sense?  I would have thought so.
Or does the context change (sp_executesql) make the option useless?
Print | posted on Friday, February 09, 2007 9:20 AM



left by Hugo Kornelis at 2/9/2007 7:27 PM Gravatar
Hi David,

The only thing you achieve by adding this option is that the SELECT and the EXEC statement in the stored proc will be recompiled each time the proc executes. Using sp_executesql is just like submitting an ad-had query through SSMS: the query will be checked against the cache to see if it matches a query parsed and compiled earlier; if it does, the cached execution plan is used; otherwise the query is parsed and compiled first.

If you want to make sure that the SQL supplied by the end user (shudder - but I'll bite my tongue and keep shut) is recompiled every time, you'll have to add the "WITH RECOMPILE" option to the @TSQL. (And if the content of @TSQL is a multi-statement batch, or even if it's a single statement but includes the semicolon delimiter, you better make sure you add the option at the correct place!!)

Best, Hugo


left by rockmoose at 2/10/2007 5:34 AM Gravatar
Since it is user defined queries, and not stored procedures that are executed the WITH RECOMPILE is not an option.
(It is not user defined stored procs that are executed).

In this case I don't think it makes sense in any way.

Claes <rockmoose>


left by Gail at 2/12/2007 8:21 PM Gravatar
No, the recompile makes no sense here. the sp_executeSQL is submitted as either an ad-hoc or prepared statement and gets its own seperate execution plan. If you add recompile to the proc, you're forcing the SELECt .. FROM Query to be recompiled, not exactly optimal.

If you want to watch the compilation behaviour, run profiler and cache the cache miss, cache insert and recompile events. From that you should be able to judge if a proc needs a recompile or not.



left by Aleksandar Tosic at 4/21/2007 12:01 AM Gravatar
It does. As long as an user is able to execute a same query from the same connection more then once using different parameteres, it makes sense to use WITH RECOMPILE option.

I tested it as I had the similar problem and it reduced I/O by 20 times.

Comments have been closed on this topic.