Один з доступних алгоритмів з'єднання двох таблиць в SQL Server це вкладені цикли (Nested Loops). Залежно від обраного оптимізатором порядку з'єднання таблиць, одна з таблиць вибирається як зовнішня (по ній відкривається зовнішній цикл), друга як внутрішня (для кожного рядка з зовнішньої таблиці виконується внутрішній цикл по другій таблиці), під час з'єднання, всередині циклів перевіряється умова з'єднання , такий підхід називається «наївний» алгоритм вкладених циклів. Якщо ж по внутрішній таблиці доступний індекс за умовою сполуки, то необов'язково виконувати внутрішній цикл перевірки по кожному рядку другої таблиці, замість цього, можна передати в якості аргументу пошуку значення з зовнішньої таблиці, а всі рядки, що будуть знайдені у внутрішній таблиці з'єднати з рядком з зовнішньої таблиці.
Пошук по внутрішній таблиці - це випадковий доступ, SQL Server починаючи з версії 2005 має оптимізацію, звану batch sort (не плутати з оператором Sort в Batch Mode для колоночного індексів). Ідея оптимізації полягає в тому, щоб перед тим, як отримати дані з внутрішньої таблиці, упорядкувати ключі пошуку з зовнішньої, перетворивши тим самим випадковий доступ в послідовний.
Операція batch sort, хоч я знаю в плані запиту, як окремий оператор, натомість, ви можете спостерігати в операторі Nested Loops властивість Optimized = true. Якби можна було побачити batch sort як окремий оператор в плані, це б виглядало приблизно так: 
В даному «псевдо-плані», ми читаємо дані з некластерного індексу ix_CustomerID в порядку ключа цього індексу CustomerID, після чого, нам необхідно виконати Key Lookup в кластерний індекс (тому що індекс ix_CustomerID не є покриває для запиту, за яким одержаний даний «псевдо-план»). Key Lookup - це операція пошуку по ключу кластерного індексу, випадковий доступ, і щоб перетворити його в послідовний сервер може виконати batch sort по ключу кластерного індексу.
Детальніше по batch sort ви можете почитати в моєму англомовному блозі, в статті Batch Sort and Nested Loops .
Ця оптимізація дає гарне прискорення при достатній кількості рядків, ви можете ознайомитися з результатами тестування ефекту цієї оптимізації в блозі одного з розробників оптимізатора Крейга Фрідмана OPTIMIZED Nested Loops Joins .
Однак, якщо рядків менше, ніж очікувалося, то додаткові витрати CPU на побудову цієї сортування можуть красти її вигоди, збільшувати споживання процесора і сповільнювати виконання. До появи підказки DISABLE_OPTIMIZED_NESTED_LOOP Microsoft пропонувала в такому випадку скористатися прапором трасування 2340, він відключає цю оптимізацію. Тепер, в цьому немає необхідності і ми можемо використовувати хинт DISABLE_OPTIMIZED_NESTED_LOOP.
приклад
Розглянемо приклад:
use tempdb; go - Створимо тестову таблицю (SalesOrderID - кластерний ПК) create table dbo.SalesOrder (SalesOrderID int identity primary key, CustomerID int not null, SomeData char (200) not null); go - Заповнимо її тестовими даними with n as (select top (1000000) rn = row_number () over (order by (select null)) from sys.all_columns c1, sys.all_columns c2) insert dbo.SalesOrder (CustomerID, SomeData) select rn% 500000, str (rn, 100) from n; - Створимо некластерние індекс create index ix_c on dbo.Salesorder (CustomerID); go - За замовчуванням оптимізація batch sort включена (Nested Loops: Optimized = true) select * from dbo.SalesOrder with (index (ix_c)) where CustomerID <тисяча; - Відключаємо її хинти DISABLE_OPTIMIZED_NESTED_LOOP (Nested Loops: Optimized = false) select * from dbo.SalesOrder with (index (ix_c)) where CustomerID <тисячі option (use hint ( 'DISABLE_OPTIMIZED_NESTED_LOOP')); go
результат: 
Зверніть увагу на різний порядок рядків при виведенні, тому що у нас не заданий явний порядок за допомогою ORDER BY, сервер повертає рядки в порядку їх обробки. У першому випадку, ми послідовно читаємо з індексу ix_c, але для оптимізації випадкових читань з кластерного індексу, сортуємо по ключу кластерного індексу SalesOrderID. У другому випадку, сортування не відбувається і читання йдуть в порядку ключів CustomerID в некластерние індексі ix_c.
Відмінність від прапора 2340
Незважаючи на те, що в документації прапор 2340 вказаний як еквівалент хинта DISABLE_OPTIMIZED_NESTED_LOOP, це не зовсім так. 
Розглянемо наступний приклад, в якому за допомогою недокументовані команди UPDATE STATISTICS ... WITH PAGECOUNT, я обману оптимізатор, сказавши, що таблиця займає більше сторінок, ніж насправді. Байдуже використовувати саме цю команду, можна було просто зробити таблицю ширше, але я хочу заощадити час. Після цього, подивимося на три запити:
- без будь-яких підказок (MAXDOP, додано для збереження простої форми плану);
- з хинти DISABLE_OPTIMIZED_NESTED_LOOP;
- з прапором трасування 2340.
- Імітуємо широку таблицю update statistics dbo.SalesOrder with pagecount = 100000; go set showplan_xml on; go - 1. Без підказок select * from dbo.SalesOrder with (index (ix_c)) where CustomerID <1000000 option (maxdop 1); - 2. Хінт select * from dbo.SalesOrder with (index (ix_c)) where CustomerID <1000000 option (use hint ( 'DISABLE_OPTIMIZED_NESTED_LOOP'), maxdop 1); - 3. Прапор трасування select * from dbo.SalesOrder with (index (ix_c)) where CustomerID <1000000 option (querytraceon 2340, maxdop 1); go set showplan_xml off; go
В результаті ми отримаємо такі плани:
У всіх трьох планах, Nested Loops має властивість Optimized = false. Справа в тому, що, збільшивши ширину таблиці, ми збільшили вартість доступу до даних. При досить високій вартості, сервер може вирішити замість неявній batch sort, використовувати явний оператор сортування Sort, що він і робить в даному випадку. Ми спостерігаємо це в плані першого запиту.
У другому запиті, ми використовували хинт DISABLE_OPTIMIZED_NESTED_LOOP, який відключає неявний batch sort, але він також прибирає і явну сортування окремим оператором. У третьому плані, ми бачимо, що незважаючи на додавання прапора 2340, оператор сортування присутній.
Таким чином, хинт відрізняється від прапора тим, що відключає оптимізацію по перетворенню випадкового доступу в послідовний повністю, незалежно від того, як сервер вирішить її реалізувати, за допомогою неявної сортування batch sort або за допомогою окремого оператора Sort.
В наступного замітці ми поговоримо про хинти FORCE_LEGACY_CARDINALITY_ESTIMATION і FORCE_DEFAULT_CARDINALITY_ESTIMATION.
PS Плани в цій замітці можуть залежати від обладнання, так, якщо у вас не виходить відтворити приклади, спробуйте збільшити або зменшити розмір стовпчика SomeData char (200) у визначенні таблиці dbo.SalesOrder.