官术网_书友最值得收藏!

  • Learn T-SQL Querying
  • Pedro Lopes Pam Lahoud
  • 230字
  • 2021-06-24 14:38:15

Parameter sniffing

Given that query plan reuse is so important, why wouldn't SQL Server parameterize every query that comes in by default? One of the reasons is to avoid query performance issues that may result from parameter sniffing. Parameter sniffing is something SQL Server does in order to optimize a parameterized query. The first time a stored procedure or other parameterized query executes, the input parameter values are used to drive the optimization process and produce the execution plan, as discussed in the Query optimization essentials section.

That execution plan will then be cached and reused by subsequent executions of the procedure or query. For most queries, this is a good thing because using a specific value will result in a more accurate cost estimation. In some situations, however, particularly where the data is skewed in some way, the parameters that are sent the first time the query is executed may not represent the typical use case of the query, and the plan that is generated may perform poorly when other parameter values are sent. This is a case where reusing a plan might not be a good thing.

Parameter sniffing is a very common cause of plan variability and performance issues in SQL Server. We will discuss this behavior in more detail in Chapter 6, Easily Identified T-SQL Anti-Patterns, and Chapter 7, Discovering T-SQL Anti-Patterns in Depth.

主站蜘蛛池模板: 嘉祥县| 米泉市| 上杭县| 广宁县| 石渠县| 芜湖县| 鹤岗市| 宽甸| 叙永县| 三河市| 汉川市| 漯河市| 墨竹工卡县| 土默特左旗| 驻马店市| 盐池县| 察隅县| 西乌| 密云县| 全椒县| 沅江市| 海原县| 平山县| 堆龙德庆县| 巨野县| 福贡县| 吴江市| 新竹市| 会东县| 乐东| 新野县| 沁水县| 锦屏县| 惠东县| 庆元县| 出国| 富阳市| 望谟县| 拉萨市| 兴山县| 永春县|