Understanding Query Performance Issues Caused by Local Variables in SQL Server by Mehdi Ghapanvari >>> https://lnkd.in/ebWRWQGC When you declare a variable inside a procedure, assign a value to it, and then use it in a query to filter results, the estimated number of rows will be wrong and can result in an incorrect memory grant. If the estimated number of rows is lower than the actual number of rows, an underestimation problem occurs, which can cause a spill to disk. This means that SQL Server has to write the data into tempdb and then read it from there, and this operation usually reduces query performance. Spill to disk is a costly operation, and you can learn more about it in Correct SQL TempDB Spills in Query Plans Caused by Outdated Statistics.
The use of "local variables" to filter data is one of the main reasons why many queries suddenly degrade in production. Because SQL Server compiles the execution plan without knowing the actual value stored in the variable, the query optimizer is forced to guess the number of rows, completely ignoring index statistics. - Incorrect memory and tempdb spill This behavior almost always causes incorrect estimates. If the estimate is too low, the server allocates little memory and forces operations to spill to tempdb via a "spill to disk" that slows performance. Conversely, excessive estimates waste resources that could be used for other processes. Moving the logic into stored procedure parameters or evaluating the "RECOMPILE" hint remain the most effective ways to allow the engine to see the actual value and optimize I/O.