Troubleshoot UPDATE performance issues with narrow and wide plans in SQL Server

https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/troubleshoot-performance-narrow-vs-wide-plans

Troubleshoot performance issues with narrow and wide plans in SQL Server - SQL Server

 

• SQL Server chooses a wide plan update when the following criteria are met at the same time:
  • The impacted number of rows is greater than 250.
  • The memory of leaf index is at least 1/1000 of the max server memory setting.
• Wide plans boost performance at the expense of consuming extra memory.
• If the expected query plan isn't used, it may be due to stale statistics (not reporting correct data size), max server memory setting, or other unrelated issues like parameter-sensitive plans.
• The duration of UPDATE statements using a wide plan depends on several factors, and in some cases, it may take longer than narrow plans.
• Trace flag 8790 will force a wide plan; trace flag 2338 will force a narrow plan.

 

https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/troubleshoot-performance-narrow-vs-wide-plans

https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-ver16

https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql?view=sql-server-ver16#tf2338