SQL Server - force NO recompile stored procedure_问答_开发者

I would like to know if exists some command to force NO RECOMPILE of a stored procedure. I have a procedure that take about 5 minutes to executed. But when I run directly in the Query windows it takes just few seconds. This sp have a temporary table.

My question is: Is there any wa开发者_Python百科y to force a stored procedure to avoid recompilation???

Note: I am using SQL Server 2005.

As was pointed out in the comments, this is almost certainly nothing to do with plan recompilation. If I had to hazard a guess, this is due a bad query plan being caused by parameter sniffing.

Assume that you have an ecommerce website where we can get different sales. We're going to have a lot more addresses in California than I will in Alaska, right? The physical operations that SQL Server is going to perform to read a lot of data (summarized sales in California) is going to be very different than they query to read a little bit of data (summarized sales in Alaska). Sometimes the cached plans are great for only one set of parameters and are horrible for all others. This is often referred to as parameter sniffing.

There's a fantastic article about Parameter Sniffing available on Simple Talk's website. So you can avoid reading that, you don't have too many options apart from specifying OPTION (RECOMPILE) at the statement level, specifying WITH RECOMPILE at the procedure level, or copying the procedure's parameters into local variables and using those to run your parameterized query.

Note that SQL Server's plans are cached by SET options as well as by query text. That is, if you have different SET options active in Management Studio, you can see different behaviour from what the application is seeing.

To check the SET options for each connection, look at the quoted_identifier, arithabort, ansi_null_dflt_on, ansi_defaults, ansi_warnings, ansi_padding, ansi_nulls and concat_null_yields_null columns of the sys.dm_exec_sessions dynamic management view. For my recent problem, ADO.NET had set ARITHABORT OFF, while Management Studio had it set ON.

To change the options for a query window in Management Studio, right-click in the query editor and select Query Options from the context menu, then go to the Advanced page for ARITHABORT and CONCAT_NULL_YIELDS_NULL, and the ANSI page for QUOTED_IDENTIFIER and the ANSI options. Alternatively just execute the necessary SET options in that query window.

Once you have the same environment set up, check for differences between the estimated execution plan and the actual execution plan. The estimated plan will be computed using the parameters and statistics available at that instant, whereas the actual plan will be whatever is in the cache. Chances are that the plans are different, and you either need to update statistics, guide it according to the more typical parameters, force a recompile each time, or rewrite the query to be more stable. For example, if you have optional parameters, consider using IF/ELSE statements rather than trying to be clever and saying 'WHERE @param = -1 or Column = @param', which will behave very differently if @param is not supplied. Or, use dynamic SQL to construct the text.

You should be aware that the statistics are best when the first column of the statistics, i.e. the first column of the index for index statistics, is the most selective and the most frequently updated. SQL Server produces a detailed histogram for the first column only - up to 200 values from that column with the number of rows in each range. For the other combinations of columns it just computes an average selectivity value, the number of unique combinations divided by the number of rows sampled. It also automatically updates the statistics only when a sufficient number of changes have occurred to the lead column. See http://blogs.technet.com/b/rob/archive/2008/05/16/sql-server-statistics.aspx for more information about when statistics are updated.