db-Berater GmbH
  • Home
  • Services
  • Workshops 
  • Referenzen
  • Blog
  • Download
  • Kontakt
Seite wählen
Forced Parameterization and its Impact on Interleaved Execution

Forced Parameterization and its Impact on Interleaved Execution

von Uwe Ricken | Nov. 13, 2026 | 300, DB Engine, Optimierung, SQL Blog

SQL Server offers several query optimization features designed to improve execution plan quality and overall query performance. Two of these features are Forced Parameterization and Interleaved Execution for Multi-Statement Table-Valued Functions (MSTVFs). At first...
Why I Don’t Use Query Hints Before Understanding What the Optimizer Is Doing

Why I Don’t Use Query Hints Before Understanding What the Optimizer Is Doing

von Uwe Ricken | Nov. 12, 2026 | 300, DB Engine, Optimierung, SQL Blog

Query hints are often treated as a shortcut to solve performance problems. A query doesn’t get the desired plan? Add a hint. Cardinality estimates look suspicious? Add another hint. Parameter sensitivity issues? Surely there’s a hint for that too. Over the...

Table Variable Deferred Compilation Disabled! So Why Does SQL Server Still Recompile?

von Uwe Ricken | Nov. 6, 2026 | 400, DB Engine, Optimierung, SQL Blog

When Microsoft introduced Table Variable Deferred Compilation (TVDC) in SQL Server 2019, table variables finally became capable of generating plans based on actual cardinalities instead of the notorious one-row estimate. If TVDC causes problems, Microsoft provide the...
Interleaved Execution: The Cure for MSTVFs That Never Cured Parameter Sniffing

Interleaved Execution: The Cure for MSTVFs That Never Cured Parameter Sniffing

von Uwe Ricken | Nov. 3, 2026 | 300, DB Engine, Optimierung, SQL Blog

When Microsoft introduced Interleaved Execution, many SQL Server professionals wondered whether this new optimization technique could eventually help address Parameter Sniffing as well. Instead of compiling a plan based on a fixed cardinality guess, the optimizer...

SQL Server Forced Parameterization: When It Improves Performance and When It Doesn’t

von Uwe Ricken | Okt. 20, 2026 | 300, Optimierung, SQL Blog

Many SQL Server performance tuning discussions focus on indexes, statistics, and query rewrites, while one database option often remains overlooked: FORCED PARAMETERIZATION. In environments flooded with ad hoc queries, SQL Server can spend a surprising amount of time...

Temporary Table or Table Variable, that is the question.

von Uwe Ricken | Dez. 28, 2024 | DB Engine, Optimierung, SQL Blog, Statistiken, Tipps und Tricks

Which technique do you prefer when you create a stored procedure and need to work with temporary data? We know there is no definitive answer to this, but there are workloads where a Table Variable is always preferable to a Temporary Table. Workload The stored...
« Ältere Einträge

db SQL Blog

  • Forced Parameterization and its Impact on Interleaved Execution
    SQL Server plan cache showing four entries under Forced Parameterization: one compiled plan for the MSTVF demo.get_c_custkey_list_by_nation (objtype = Proc, usecounts = 2), two separate Adhoc entries for the original queries containing the literals 'AUSTRIA' and 'PORTUGAL' (usecounts = 1 each), and one Prepared plan for the automatically parameterized query (usecounts = 2).
  • Why I Don’t Use Query Hints Before Understanding What the Optimizer Is Doing
    Extended Events session capturing the lifecycle of Interleaved Execution for a Multi-Statement Table-Valued Function (MSTVF). The trace starts with a recompilation event (recompilation_for_interleaved_exec), followed by statement execution events. Three interleaved_exec_status events document the progression through the Interleaved Execution framework: status 0 indicates eligibility detection, status 1 indicates that SQL Server executes the MSTVF during optimization to obtain actual cardinality information, and status 2 indicates that optimization resumes using the observed row count. The event sequence confirms that SQL Server pauses optimization, executes the MSTVF, and recompiles the query using the actual cardinality returned by the function.
  • Table Variable Deferred Compilation Disabled! So Why Does SQL Server Still Recompile?
  • Interleaved Execution: The Cure for MSTVFs That Never Cured Parameter Sniffing
    Extended Events output showing the compilation, execution, and recompilation phases of SQL Server Interleaved Execution for a Multi-Statement Table-Valued Function
  • MSTVFs: Better Estimates, Same Fundamental Problem
    Execution plan demonstrating Interleaved Execution for a Multi-Statement TVF with accurate cardinality estimation of 3,776 rows.
  • Update Statistics from Replicated Database
    Infrastructure of automated stats update from a subscriber to a publisher
  • SQL Server Forced Parameterization: When It Improves Performance and When It Doesn’t
  • Nonfoldable User Defined Functions
  • CTE vs temp table performance in SQL Server. Hidden costs and real workload impact!
  • Partition a big table with zero downtime
    Data Flow Between Application, Orders View, and Backend Tables
  • WHERE 1 = 1 – good practice?
  • Table Hint UPDLOCK and LOCK ESCALATION
  • Auto Update Statistics and Trivial Execution Plans
    Execution process of trivial plans with simple parameterization
  • When „Auto Update Statistics“ will not update your statistics
  • Is SWITCH PARTITION a bad idea?
  • Datenschutzerklärung
  • Impressum
  • Sitemap
  • Download
  • RSS

db Berater GmbH | | Büchenweg 4 | 64390 Erzhausen | Phone: +49 (0) 6150 5082955 | E-Mail: info@db-berater.de Amtgericht Darmstadt: HRB86305 |    Umsetzung