The usecount value shown in sp_cacheobjects (described in the previous post) for the procedure will not increase since the original plan is not being reused. It will not be saved and certainly won’t be reused. This option will cause a new plan to be generated just for this one execution. First, we can ascertain whether recompiling will actually help performance by executing the procedure with the RECOMPILE option:ĮXEC get_sales_range 66666 WITH RECOMPILE WHERE SalesOrderID < we want to make sure we get a new plan every time we run this query, with potentially vastly different values for we can add the RECOMPILE hint as shown:įor stored procedures, we have three options. Using FORCED parameterized (discussed in the previous article), this query will be parameterized. For individual statements, such as with PREPARED plans that have been autoparameterized, we can add the RECOMPILE hint to a query. If inappropriate use of parameter sniffing is a problem, a simple solution is to just tell SQL Server to come up with a new plan. However, most people use the term ‘recompile’, so that’s what I’ll use here. Usually, we call the act of coming up with a new plan ‘recompiling’, but it probably should be called ‘reoptimizing’. So, what can we do when parameter sniffing is a problem? We can force SQL Server to come up with a new plan. If a plan is based on an initial value that causes the optimizer to generate a plan appropriate for that value, and then the same plan is used for a different value, the plan may no longer be optimal. I ended the discussion with a look at an inappropriate reuse of a plan, which can happen when SQL Server applies parameter sniffing in the wrong situations. We looked at three types of query plans: adhoc, prepared, and procedure. In my previous article, I talked about SQL server diagnostics, including the various options that SQL Server has for reusing a query plan. In these posts I’ll share with you how I look at some of the features or aspects of SQL Server, sometimes along with a bit of historical perspective I’ve seen a lot of changes over the years (and decades!) and versions of this incredible product. SQL Server has been around over 30 years, and I’ve been working with SQL Server for almost as long. Be sure to read Kalen's first post on this topic. Public override void ScalarExecuted( DbCommand command, DbCommandInterceptionContext interceptionContext)īase.This is part of a SQL Server Internals Plan Caching series. ReaderExecuted( command, interceptionContext) Public override void ReaderExecuted( DbCommand command, DbCommandInterceptionContext interceptionContext)īase. NonQueryExecuted( command, interceptionContext) Public override void NonQueryExecuted( DbCommand command, DbCommandInterceptionContext interceptionContext)īase. Public override void NonQueryExecuting( DbCommand command, DbCommandInterceptionContext interceptionContext) Public override void ReaderExecuting( DbCommand command, DbCommandInterceptionContext interceptionContext) Public override void ScalarExecuting( DbCommand command, DbCommandInterceptionContext interceptionContext) Static void UpdateCommand( DbCommand command) Private static readonly Regex ReplaceTableRegex = new (?AS \(?(?! WITH \((?\. )\)))) " Public class HintInterceptor : DbCommandInterceptor / This hint interceptor need be registered in DBConfiguration to be used! Throw new InvalidOperationException( "No surrounding HintScope created ") Public static DbSet With( this DbSet set, params string hint) where T : class Public string GetTableHint( string table) Public void AddTableHint( string table, string hints) Add( "ARITHABORT ON ") ĭictionary TableHints = new Dictionary() Public void AddDefaultPerformanceOptions()ĮnvironmentOptions.
0 Comments
Leave a Reply. |