SQL Performance

SEPTEMBER 25, 2019

Introduction. Performance spools are lazy spools added by the optimizer to reduce the estimated cost of the inner side of nested loops joins. Just before we get started, I want to stress an important point: There are two distinct types of nested loop join in execution plans. Nested Loops Join.

Performance 104

Performance Cache Servers Education 104

SQL Performance

NOVEMBER 4, 2021

First introduced in SQL Server 2017 Enterprise Edition, an adaptive join enables a runtime transition from a batch mode hash join to a row mode correlated nested loops indexed join (apply) at runtime. Introduction.

Servers 98

Servers Design Database Code 98

SQL Performance

NOVEMBER 22, 2019

Introduction. To be accessed more than once, the spool must appear on the inner side of a nested loops join operator. Each iteration of the loop should seek to a particular index spool key value provided by the outer side of the loop. That means that the join needs to be an apply , not a nested loops join.

Servers 77

Servers Cache Database Performance 77

SQL Performance

OCTOBER 7, 2020

Introduction. Rewinds are specific to operators on the inner side of a nested loops join or apply. Its raison d’être is to cache result rows from a plan subtree, then replay those rows on subsequent iterations if any correlated loop parameters are unchanged. Row Count Spool. Nonclustered Index Spool. Sort types.

Servers 106

Servers Cache Azure Performance 106