Shulou(Shulou.com)06/01 Report--

The parameter sniff problem is the phenomenon that the execution plan generated by other parameters is reused, which leads to the non-optimization of the execution plan for the current parameters. Students who are familiar with data should know that the most typical problem in generating parameter sniff is the use of parameterized SQL (or parameterization in the stored procedure). If there is an uneven distribution of data, the execution plan generated under normal circumstances reuses the execution plan generated by normal parameters when more parameters are passed in the distribution data. The execution plan of this cache is not suitable for the current parameters.

This kind of situation, in the actual business, the frequency is still relatively high, because the stored procedure is generally written with parameterization, at this time, when encountered with uneven distribution of data parameters, the parameter sniff phenomenon appears, this kind of problem is still a headache.

I will not explain too much about the specific causes of parameter sniff, but it is too low to explain this.

Let me give a simple example to simulate this phenomenon to show how the parameterized stored procedure is written, what are the problems, and how to solve the parameter sniff problem.

Create a test environment first:

Create table ParameterSniffProblem (id int identity, OrderId int, OrederStatus int, CreateDate Datetime, Remark varchar) declare @ I int = 0while @ I

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.