- 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.
- 基于C語言的程序設計
- 基于LabWindows/CVI的虛擬儀器設計與應用
- Hands-On Data Science with SQL Server 2017
- Associations and Correlations
- RPA(機器人流程自動化)快速入門:基于Blue Prism
- 基于企業網站的顧客感知服務質量評價理論模型與實證研究
- Hands-On Dashboard Development with QlikView
- Mastering Ansible(Second Edition)
- 貫通開源Web圖形與報表技術全集
- Photoshop CS4數碼照片處理入門、進階與提高
- Practical AWS Networking
- 從零開始學ASP.NET
- 從祖先到算法:加速進化的人類文化
- 運動控制系統
- 開放自動化系統應用與實戰:基于標準建模語言IEC 61499