What makes the execution plan extremely bad? Should I use update statistics to solve this problem?

Consider this situation:

In most of the time, your stored procedures run well, but sometimes very poor, performance seems to have fallen from the ground up. Some people may say that the statistics are not updated in a timely manner, when you run it manually and view the execution plan, you will find that there is a big gap between the estimated number of rows and the actual number of rows, therefore, it is determined that the statistical information is inaccurate, which leads to incorrect execution plan generation.

However, it may not be...

A cache execution plan will be reused for stored procedures, parameterized statements using sp_executesql, and pre-compiled SQL statements. It is defined by a parameter sniffing, and parameter sniffing is not a problem, however, when the same stored procedure or parameterized statement is used to call the generated execution plan, some problems may occur. For example, if a parameterized query statement returns only one row of data, it may generate a simple lightweight execution plan, which is cached, it may only be a non-clustered index query + bookmarks query. However, if a query returns a large number of data rows, the execution plan generated earlier may not be suitable. Because the execution plan is cached in the memory, it may disappear from the memory for many reasons. If it just disappears, a query with a very low usage frequency will return a large number of data rows, then the generated execution plan will be cached. Even if only one row is returned for the subsequent query, the execution plan just cached will be used, which will lead to poor performance.

For example:

 Use  Tempdb  Go  --  Drop table tb_1  Create   Table Tb_1 (ID Int   Primary   Key   Identity , Name Varchar ( 200 ), DT Datetime   Default   Getdate (), Xx Int  )  Declare   @ I   Int   = 1  While   @ I  <  10000  Begin  Insert   Into Tb_1 (name) Select   Ltrim ( @ I  )  Set   @ I  + =  1 End  Create   Index Ix_name On  Tb_1 (name)  Go  Insert   Into Tb_1 (name) Select   '  99999  '  Go   5000

 

ExecSp_executesql n'Select top 10 * From tb_1 where name = @ name order by DT DESC', N'@ Name varchar (20)',@ Name='1'
 

IO: The table 'tb _ 1 '. Scan count 1,4 logical reads, Read 0 physically, read 0 in advance, read 0 in lob logic, read 0 in lob physical, and read 0 in lob preread.
Execution Plan:

Under normal circumstances, This name = '1' query returns only one row. When the name changes to another value, only a small number of rows are returned. If the name has an index, it is just an index search + key search, it will be very fast, From the above we can see that there are only four pages.

 

Suppose there is a very small numberName = 99999 will return a large number of rows, it should be better to use clustered index scanning, but because of SQL Server parameter sniffing, it will use the previously cached execution plan, this will make the efficiency worse, but since this name = 99999 is rarely executed, it seems acceptable, such:

 
ExecSp_executesql n'Select top 10 * From tb_1 where name = @ name order by DT DESC', N'@ Name varchar (20)',@ Name='99999'
 

IO: The table 'tb _ 1 '. Scan count 1,Read logic 10014 times, Read 0 physically, read 0 in advance, read 0 in lob logic, read 0 in lob physical, and read 0 in lob preread.
Execution Plan:

If no cache execution plan is usedWhat will it look like?

Select Top 10 * FromTb_1WhereName='99999' Order ByDTDesc
 

IO: The table 'tb _ 1 '. 1 scan count, 64 logical reads, 0 physical reads, 0 pre-reads, 0 lob logical reads, 0 physical reads, and 0 lob pre-reads.
Execution Plan:

 

However, if there is no execution plan for the statement in the cache at this time, or it may have just been removed from the memory, at this time, executing name = 99999 will generate a new execution plan and cache it, that is, the execution plan of the clustered index scan generated by the preceding T-SQL statement, which is a clustered index scan, in this case, other subsequent queries that only return one row of data will reuse this new execution plan, and the performance will be poor, such: