前面我们询问了参数嗅探可能是好之啊或是死的。当数排的遍布不全匀的时段参数嗅探就是坏的作业。例如,考虑“Status”列在Orders表中生出一起10M行。该列有7个例外的值,如下分布:

回到目录

Status Number of Rows
Open 314
Pending Approval 561
Approved 28,990
Paid 17,610

Shipped

817,197

Closed

7,922,834

Cancelled

1,032,886

概念

Microsoft.Practices.Unity.Interception是一个拦截器,它隶属于Microsoft.Practices.Unity组成之中,主要成就AOP的法力,而实现AOP方法阻碍(页为切面)的根基就算是IoC,我们得配备相关接口或者项目的IoC方式,然后在生目标时,使用Unity的方式开展动态生产目标,这时,你的Interception拦截器也会自作用!

 

连带技术

IoC: 控制反转(Inversion of
Control,英文缩写为IoC)是一个至关重要的面向对象编程的原理来减计算机程序的耦合问题。
控制反转一般以于称呼依赖注入(Dependency
Injection,简称DI)。即将部署中的靶子,动态注入及路先后中,使程序的之一意义可动态进展切换。

AOP:
面向切面编程(也于面向方面编程):Aspect
Oriented
Programming(AOP),是软件开发遭遇之一个热门。利用AOP可以对作业逻辑的顺序部分进行隔离,从而让业务逻辑各部分中的耦合度退,提高程序的但重用性,同时加强了支付的频率。

貌似用其来促成:日志记录,性能统计,安全控制,事务处理,颇处理等等。

   
如果查询status是“Open”的数时使用参数嗅探,那么优化器很可能选择一个蕴含index
seek 和 key
lookup的实施计划。这个计划在缓存中有益重用。当其他用户执行查询closed状态的下,相同的行计划给引用,这即特别可能是一个不幸,因为今天以进行8M单键值查找操作。

持久化方式

此时此刻自家的Project.UnityCaching组件支持Microsoft.Practices.EnterpriseLibrary.Caching和Redis两栽多少持久化的方法,前者为是Microsoft.Practices.
EnterpriseLibrary企业库底一律片,主要实现内存持久化(也得兑现公文持久化),主要用当单台WEB服务器上;后者可以独自安排至同样高抑基本上宝服务器上,主要实现分布式缓存,它是鹏程之百般数额的一个主旋律。

键名组成

缓存解决方案名_取名空间_方法名,其中缓存解决方案名好好去安排,对许config中之CacheProjectName节点,而键对应之值采用字典类型,字典的键对应方法的各国参数的重组。

   
另外的应用参数嗅探的坏情况是用无相等的谓词使用参数。请看下的询问:

论及到的相关程序集

IoC基础库程序集

 统计 1

Redis基础库程序集

 统计 2

Project.Frameworks自封装程序集

 统计 3

SELECT
    Id ,
    CustomerId ,
    TransactionDateTime ,
    StatusId
FROM
    Billing.Transactions
WHERE
    TransactionDateTime BETWEEN @FromDateTime AND @ToDateTime
ORDER BY
    TransactionDateTime ASC;

布置文件被开展对IoC,AoP的配备

统计 4统计 5

  <configSections>
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=5.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
    <section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler, log4net" />
    <section name="cachingConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Caching.Configuration.CacheManagerSettings, Microsoft.Practices.EnterpriseLibrary.Caching, Version=5.0.505.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
    <section name="unity" type="Microsoft.Practices.Unity.Configuration.UnityConfigurationSection, Microsoft.Practices.Unity.Configuration"  />
    <section name="RedisConfig" type="Redis.Client.RedisConfigInfo, Redis.Client"/>
  </configSections>

  <RedisConfig WriteServerList="127.0.0.1:6379" ReadServerList="127.0.0.1:6379" MaxWritePoolSize="60" MaxReadPoolSize="60" AutoStart="true" LocalCacheTime="180" RecordeLog="false">
  </RedisConfig>

  <cachingConfiguration defaultCacheManager="ByteartRetailCacheManager">
    <cacheManagers>
      <add name="ByteartRetailCacheManager" type="Microsoft.Practices.EnterpriseLibrary.Caching.CacheManager, Microsoft.Practices.EnterpriseLibrary.Caching, Version=5.0.505.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" expirationPollFrequencyInSeconds="600" maximumElementsInCacheBeforeScavenging="1000" numberToRemoveWhenScavenging="10" backingStoreName="NullBackingStore" />
      <!--
          expirationPollFrequencyInSeconds:过期时间(seconds)
          maximumElementsInCacheBeforeScavenging:缓冲中的最大元素数量
          numberToRemoveWhenScavenging:一次移除的数量
      -->
    </cacheManagers>
    <backingStores>
      <add type="Microsoft.Practices.EnterpriseLibrary.Caching.BackingStoreImplementations.NullBackingStore, Microsoft.Practices.EnterpriseLibrary.Caching, Version=5.0.505.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" name="NullBackingStore" />
    </backingStores>
  </cachingConfiguration>

  <unity xmlns="http://schemas.microsoft.com/practices/2010/unity">
    <sectionExtension type="Microsoft.Practices.Unity.InterceptionExtension.Configuration.InterceptionConfigurationExtension, Microsoft.Practices.Unity.Interception.Configuration" />

    <container>
      <!--泛型类型注入,数据仓储服务注入,可以由ef,linq,ado,memory,file,nosql等方式实现-->
      <register type="IRepository.Core.IUnitOfWork,IRepository.Core" mapTo="NLayer_IoC_Demo.Entity.backgroundTestIoCEntities,NLayer_IoC_Demo.Entity" />
      <!--使用redis作为持久化存储-->
      <!--<register type="IRepository.Core.IRepository`1,IRepository.Core" mapTo="Redis.Data.Core.RedisRepository`1,Redis.Data.Core" />-->
      <!--使用SQLSERVER作为持久化存储-->
      <register type="IRepository.Core.IRepository`1,IRepository.Core" mapTo="NLayer_IoC_Demo.DATA.backgroundRepositoryBase`1,NLayer_IoC_Demo.DATA" />
      <register type="IRepository.Core.IExtensionRepository`1,IRepository.Core" mapTo="NLayer_IoC_Demo.DATA.backgroundRepositoryBase`1,NLayer_IoC_Demo.DATA" />

      <!-- AOP注入 -->
      <extension type="Interception" />
      <register type="NLayer_IoC_Demo.BLL.IUserService,NLayer_IoC_Demo.BLL" mapTo="NLayer_IoC_Demo.BLL.UserService,NLayer_IoC_Demo.BLL" >
        <!--接口拦截-->
        <interceptor type="InterfaceInterceptor" />
        <!--缓存注入-->
        <interceptionBehavior type="Project.UnityCaching.CachingBehavior,Project.UnityCaching"/>
      </register>

      <register type="NLayer_IoC_Demo.BLL.OrderService,NLayer_IoC_Demo.BLL" >
        <!--接口拦截-->
        <interceptor  type="VirtualMethodInterceptor" />
        <!--虚方法注入,生成派生类进行拦截,让我们省去了建立接口的时间,不错的选择-->
        <!--Transparent Proxy Interceptor -->
        <!--它是注入所有的virutal method ,method,interface,但它的性能太差了-->
        <!--缓存注入-->
        <interceptionBehavior type="Project.UnityCaching.CachingBehavior,Project.UnityCaching"/>
      </register>

      <register type="Project.InterceptionBehaviors.IHandle, Project.InterceptionBehaviors" mapTo="Project.InterceptionBehaviors.StandardHandle, Project.InterceptionBehaviors">
        <!--接口拦截-->
        <interceptor type="InterfaceInterceptor" />
        <interceptionBehavior  type="NLayer_IoC_Demo.BLL.AOP.LoggerBehavior,NLayer_IoC_Demo.BLL" />
      </register>

    </container>
  </unity>

  <connectionStrings>
    <add name="backgroundTestIoCEntities" connectionString="metadata=res://*/background.csdl|res://*/background.ssdl|res://*/background.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=.;initial catalog=backgroundTestIoC;persist security info=True;user id=sa;password=zzl123;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

    <add name="DefaultConnection" connectionString="Data Source=(LocalDb)\v11.0;Initial Catalog=aspnet-NLayer_IoC_Demo-20141024091423;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnet-NLayer_IoC_Demo-20141024091423.mdf" providerName="System.Data.SqlClient" />
  </connectionStrings>

View Code

  

下时的相干注意要

  1. Unity组件中引入了劳动一定器ServiceLocator的定义,它可使我们无引用目标程序集,而自动在bin目录自动去稳定。
  2. 对Redis方式的缓存来说,进行缓存的实体类需要为声称也Serializable特性。
  3. 实现IoC,AoP时,只援引得之程序集,而无用拿富有Microsoft.Practices.Unity组件都引入,在程序进行编译时,这些用之顺序会活动添加到UI项目之BIN目录。
  4. 措施阻碍这块有三种,但TransparentProxyInterceptor由于特性最差,我们连无提倡用,项目面临我们最主要运用VirtualMethodInterceptor对于虚方法的掣肘与InterfaceInterceptor对于接口的遏止,两种植方法各起好处,如果实现方式于单一,可以直接下虚方法注入,这样好省写接口的代码量。

回去目录

     如果查询利用参数嗅探编译,使用值“2014-07-01″
和“2014-08-01″,那么优化器基于统计估计行数并且大概估量行数为20000。然后创建基于此量行数的计划而在缓存中。后来之履好应用完全不同的参数。例如,用户执行查询用时间参数“2012-01-01″
和“2014-01-01″。结果集大体发生61000执行,但是依据前的行数的计划给用,并且颇可能无是一个好的尽计划。

 

   
那么,我们能举行来什么来影响参数嗅探?  

    我用显示一些因自己事先用存储过程实例的艺:

 

CREATE PROCEDURE
    Marketing.usp_CustomersByCountry
(
    @Country AS NCHAR(2)
)
AS

SELECT
    Id ,
    Name ,
    LastPurchaseDate
FROM
    Marketing.Customers
WHERE
    Country = @Country;
GO

  

  

这里是一个“Country”列的遍布情况:

Country Number of Rows
BE 70

CL

55

CN

29,956

DK

74

EG

64

IL

72

MT

83

PT

75

TR

63

UK

28,888

US

40,101

VE

78

 

    
正而所显现,一共12个不等之价,其中老三单凡是比较多之行数,然而其余的行数非常少。这是一个最好的分红不净匀情况没有,生产条件中恐怕特别麻烦见到。这里正可以来得自身之观…

     在座谈使得之缓解方案之前,先押一下问题…

    
首先参数赋值为IL。当存储过程首不好用“IL”参数执行时,生成计划包含了一个找“Country”的目录。对于此指定的执行就是颇有协助的优化器估计行数是72,完全标准。

    
下次存储过程执行时,使用参数为“US”。数据中起40,101实践,并且这种气象下之特等实践计划是动聚集索引围观,可以避多“key
lookups”。但是计划已在内存中,就见面引用。不幸之是,这个计划包含了目录查找和“key
lookup
”而非是聚集索引围观,这虽是一个特别不同的尽计划。此时咱们来看索引查找操作符的特性被估计行数是72,然后实际却是40000+。这虽是实施计划错误引起的估算行数错误。如果我们查阅SELECT
的“Parameter List”
属性,就可知觉察因所在。由于编译1是“IL”,而运作时是“US”。

    那么现在我们发现了问题,接下为咱看一下或许的缓解方案…
Solution #1 – sys.sp_recompile

   
很简单就是使用系统存储过程sys.sp_recompile从缓存中移除指定的执行计划或者有计划引用的指定表和视图。这就是说下次储存过程更实施时需要再编译,新的尽计划用为创造。

   
记住我们的最主要问题是价值的分布。因此根据相同效新的参数还编译存储过程用开创指定的推行计划,但是大部分时节这并无解决问题,因为新的计划还只是对此次的值是好的,当遇到任何不同分布的参数值时仍是糟糕的计划。我建议当查问中淋的价值绝大多数情况下是只有一值的早晚可以设想再编译的方法来解决问题,比如当where背后的status
状态吧1底挤占99%的数据值时,一般情形便是好的计划。

Solution #2 – WITH RECOMPILE

假设您免喜前是赌博式的道,那么WITH
RECOMPILE很符合您。与之前靠传递让指定执行之参数值不同,这种艺术而你得告知优化器编译在各级一个储存过程被编译计划。

ALTER PROCEDURE
    Marketing.usp_CustomersByCountry
(
    @Country AS NCHAR(2)
)
WITH
    RECOMPILE
AS

SELECT
    Id ,
    Name ,
    LastPurchaseDate
FROM
    Marketing.Customers
WHERE
    Country = @Country;
GO

  

 

   
每一样涂鸦参数嗅探被运用时,意味着执行将取优化器提供的超级实践计划。既然新的计划每次执行还被创造,那么SQLServer将不见面把计划坐缓存中。

立是一个没错的缓解方案,因为每次执行存储过程都产生一个超级的计划,消除了随机赌博式的副作用。但是缺点是历次编译都不能不经昂贵的优化过程。这是急需凑数的CPU处理过程。如果系统就处在PCU高负荷并且存储过程往往执行,那么这种方法是未得体的。另一方面,如果CPU使用率相对较逊色而且存储过程只有是有时执行,那么就便是一个带动吃您最佳的解决方案。

Solution #3 – OPTION (RECOMPILE)

举凡一个及前者相似的化解方案,但是也起有限单第一的不同点。首先,这个查询参数对发生题目的查询语句而不是满存储过程。

ALTER PROCEDURE
    Marketing.usp_CustomersByCountry
(
    @Country AS NCHAR(2)
)
AS

SELECT
    Id ,
    Name ,
    LastPurchaseDate
FROM
    Marketing.Customers
WHERE
    Country = @Country
OPTION
    (RECOMPILE);
GO

 

  只对一个语句的重编译节省了大量的资源。

  其次,“WITH RECOMPILE”发生在编译时,而“OPTION (RECOMPILE)” 发生在运行时。整个例子中运行时执行这个语句时,暂停执行,重新编译该查询,生成新的执行计划。而其他部分则使用计划缓存。运行时编译带来的好处就是使优化器能预先知道所有的运行时值,甚至不需要参数嗅探。优化器知道参数的值,局部变量和环境设置,然后使用这些数据编译查询。多数情况下,运行时编译生成的计划要比编译时生成的计划好很多。

为此,你应有考虑动用“OPTION (RECOMPILE)” 而非是“WITH
RECOMPILE”,因为其使了再度少之资源长生了再次好的计划。但是如果留意这种办法还是殊占CPU的。

Solution #4 – OPTIMIZE FOR

    另一样翻看询选项“OPTIMIZE
FOR”也足以缓解参数嗅探问题。该选项指示优化器使用一定的同样法参数而不是实在的参数来编译查询。实际上就是重写参数嗅探。注意,这个选项只有当查问必须叫还编译的时节才会为利用。选项本身不会见惹重编译。

ALTER PROCEDURE
    Marketing.usp_CustomersByCountry
(
    @Country AS NCHAR(2)
)
AS

SELECT
    Id ,
    Name ,
    LastPurchaseDate
FROM
    Marketing.Customers
WHERE
    Country = @Country
OPTION
    (OPTIMIZE FOR (@Country = N'US'));
GO

  

     还记得“Sales. Orders”表底情也?99%底尽会下“Pending
Approval”作为参数。而休是采用sys.sp_recompile(重编译),综上所述,如果要生一样差实行已然使用这个参数,俺么使用OPTIMIZE
FOR
将会见是其一种植状态的更佳选择,并且指示优化器无论实际参数在生同样赖执行时凡呀还利用该参数(如齐例被的US)。

     通过行使“OPTIMIZE FOR
UNKNOWN”可以禁止参数嗅探。这个选项指示优化器将参数设为位置,实际上就是禁用了参数嗅探。如果存储过程有差不多只参数,那么你会分别针对各个一个参数进行选择处理(禁用)。

ALTER PROCEDURE
    Marketing.usp_CustomersByCountry
(   
    @Country AS NCHAR(2)
)
AS

SELECT
    Id ,
    Name ,
    LastPurchaseDate
FROM
    Marketing.Customers
WHERE
    Country = @Country
OPTION
    (OPTIMIZE FOR (@Country UNKNOWN));
GO

  

Solution #5 – 最佳方案

   
到目前为止你或注意到了,有点儿个我们愿意达到有互相冲突的目的。一个凡吗每个执行创建最优良的计划,另一个凡最最小化编译避免资源的荒废。“WITH
RECOMPILE”方式完成了第一个目的,但是它们需要每个执行更编译。另一方面,sys.sp_recompile方式只有重复编译了相同浅存储过程,但是非会见呢每个执行有最佳计划。

   
那么最佳的化解方案就是是平衡马上半种植冲突之对象。这种平衡考虑就分手参数值到不同之组,每组有差的优化计划,并且转变不同的优化计划。每个计划才受编译一差,然后打这点以来每个执行还见面收获最佳计划,因为计划基于参数值产生,所以合理的分组导致变化对应组的计划。

    听起如魔法吧?让咱看一下夫魔术如何贯彻…

   
首先我们得拿价值分成不同之组。这是重中之重部分,并且产生许多方式去分组。这里自己以用国作为参数,将一般性国家以及无寻常国家分成两组。如果该邦的行数占及了表行数的1%以上自拿该定义也常见国家。假定SQLServer已经定义了通常国家,通过统计国家列字段。SQLServer
通常用普通的参数值作为图形统计的条款。

   
因此我们拿日常国家插入到“CommonCountries”表的“Country”,然后去不寻常国家…

CREATE TABLE
    Marketing.CommonCountries
(
    RANGE_HI_KEY        NCHAR(2)    NOT NULL ,
    RANGE_ROWS          INT         NOT NULL ,
    EQ_ROWS             INT         NOT NULL ,
    DISTINCT_RANGE_ROWS INT         NOT NULL ,
    AVG_RANGE_ROWS      FLOAT       NOT NULL ,

    CONSTRAINT
        pk_CommonCountries_c_RANGEHIKEY
    PRIMARY KEY CLUSTERED
        (RANGE_HI_KEY ASC)
);
GO


INSERT INTO
    Marketing.CommonCountries
(
    RANGE_HI_KEY ,
    RANGE_ROWS ,
    EQ_ROWS ,
    DISTINCT_RANGE_ROWS ,
    AVG_RANGE_ROWS
)
EXECUTE ('DBCC SHOW_STATISTICS (N''Marketing.Customers'' , ix_Customers_nc_nu_Country) WITH HISTOGRAM');
GO


DECLARE
    @RowCount AS INT;

SELECT
    @RowCount = COUNT (*)
FROM
    Marketing.Customers;

DELETE FROM
    Marketing.CommonCountries
WHERE
    EQ_ROWS < @RowCount * 0.01;
GO

  

发明底询问内容如下:

RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS
CN 0 29956 0 1
UK 0 28888 0 1
US 0 40101 0 1

 

    
这样理解极了。这三只凡是平常国家的事例。当然这是比较简单的例子,实际条件可能而复杂的基本上,有时甚至要提出有算法来区分普通和无普通的价值。可以下自家这种统计的结果。也可采取某种监视机制来追踪使用结果以及计划。又要需要出同效仿自己之统计体制。无论如何,多数时节是索要支出一个算法来区分值为歧之组。

   
那么我们好为此之国度之分组分别生成优化计划。这种方法需要创造不同存储过程,而存储过程除了名字他几乎都是如出一辙的。

   
在实例中,我创建“Marketing.usp_CustomersByCountry_Common”和“Marketing.usp_CustomersByCountry_Uncommon”两单存储过程。如下:

CREATE PROCEDURE
    Marketing.usp_CustomersByCountry_Common
(
    @Country AS NCHAR(2)
)
AS

SELECT
    Id ,
    Name ,
    LastPurchaseDate
FROM
    Marketing.Customers
WHERE
    Country = @Country;
GO


CREATE PROCEDURE
    Marketing.usp_CustomersByCountry_Uncommon
(
    @Country AS NCHAR(2)
)
AS

SELECT
    Id ,
    Name ,
    LastPurchaseDate
FROM
    Marketing.Customers
WHERE
    Country = @Country;
GO

  

 

   
接下去我们修改一个原有之贮存过程,这个蕴藏过程成为一个路由。它的办事就是是价差参数值并根据值的分组确定实施哪一个对应的存储过程。

ALTER PROCEDURE
    Marketing.usp_CustomersByCountry
(
    @Country AS NCHAR(2)
)
AS

IF
    EXISTS
        (
            SELECT
                NULL
            FROM
                Marketing.CommonCountries
            WHERE
                RANGE_HI_KEY = @Country
        )
BEGIN

    EXECUTE Marketing.usp_CustomersByCountry_Common
        @Country = @Country;

END
ELSE
BEGIN

    EXECUTE Marketing.usp_CustomersByCountry_Uncommon
        @Country = @Country;

END;
GO

  

 

随即是一个大好的解决方案:

   
首次于一般国家作为参数使用,路由于存储过程调用普通存储过程。一旦第一次受实践下,计划给生产在缓存中。多亏了参数嗅探,从此后,只要普通国家之仓储过程让实施还见面用是计划。然后,同样不常用国家呢是要此…

   
因此,我们呢每个参数值都提供了优良的计划,并且每个计划才受编译一次于。通常来写就发生2届3组值,因此最好多2交3个编译。这就算是魔法之庐山真面目。

    缺点:

  
当然就就是一个佳之法,需要小心的是该方案的掩护资产。一旦数据产生了转,算法必须去保护修改来重新适应。如上面的事例,需要各个一段时间去又创设普通国家的阐明。

总结:

   
参数嗅探能是好的也得以是充分的政工。既然在SQLServer中默认使用,只要她是好之,我们尽管应用。我们的目的是因不同状况识别参数嗅探,然后使文中提到的方法来化解不好的参数嗅探问题。

   
今后我会选择有现实生产问题来显示一下各种参数嗅探以及对应的衍生问题的处理方案。