- 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.
- Ansible Configuration Management
- 高效能辦公必修課:Word圖文處理
- 中文版Photoshop CS5數碼照片處理完全自學一本通
- 3D Printing with RepRap Cookbook
- AWS:Security Best Practices on AWS
- Java開發技術全程指南
- 網上生活必備
- 條碼技術及應用
- Visual C++編程全能詞典
- 運動控制器與交流伺服系統的調試和應用
- INSTANT Autodesk Revit 2013 Customization with .NET How-to
- Windows Server 2008 R2活動目錄內幕
- Mastering GitLab 12
- 嵌入式GUI開發設計
- 簡明學中文版Photoshop