Порядок сортировки кластерного индекса SQL Server 2008+

Поддерживает ли порядок сортировки кластерного индекса SQL Server 2008+ производительность вставки?

Тип данных в конкретном случае является integer а вставленные значения возрастают ( Identity ). Следовательно, порядок сортировки индекса будет противоположным порядку сортировки вводимых значений.

Я предполагаю, что это будет иметь влияние, но я не знаю, может быть, SQL Server имеет некоторые оптимизации для этого случая, или это внутренний формат хранения данных, безразличный к этому.

Обратите внимание, что вопрос касается производительности INSERT , а не SELECT .

Обновить
Чтобы быть более ясным в вопросе: что происходит, когда значения, которые будут вставлены ( integer ), находятся в обратном порядке ( ASC ) для упорядочения кластерного индекса ( DESC )?

Есть разница. Вставка из кластерного порядка вызывает массированную фрагментацию.

При запуске следующего кода кластеризованный индекс DESC генерирует дополнительные операции UPDATE на уровне NONLEAF.

 CREATE TABLE dbo.TEST_ASC(ID INT IDENTITY(1,1) ,RandNo FLOAT ); GO CREATE CLUSTERED INDEX cidx ON dbo.TEST_ASC(ID ASC); GO CREATE TABLE dbo.TEST_DESC(ID INT IDENTITY(1,1) ,RandNo FLOAT ); GO CREATE CLUSTERED INDEX cidx ON dbo.TEST_DESC(ID DESC); GO INSERT INTO dbo.TEST_ASC VALUES(RAND()); GO 100000 INSERT INTO dbo.TEST_DESC VALUES(RAND()); GO 100000 

Два оператора Insert выражают точно такой же План выполнения, но при просмотре оперативной статистики различия проявляются против [nonleaf_update_count].

 SELECT OBJECT_NAME(object_id) ,* FROM sys.dm_db_index_operational_stats(DB_ID(),OBJECT_ID('TEST_ASC'),null,null) UNION SELECT OBJECT_NAME(object_id) ,* FROM sys.dm_db_index_operational_stats(DB_ID(),OBJECT_ID('TEST_DESC'),null,null) 

Существует дополнительная операция по созданию капота, когда SQL работает с индексом DESC, который работает против IDENTITY. Это связано с тем, что таблица DESC становится фрагментированной (строки вставлены в начале страницы), и для поддержки структуры B-дерева возникают дополнительные обновления.

Наиболее заметным в этом примере является то, что кластерный индекс DESC становится более 99% фрагментированным. Это воссоздает такое же плохое поведение, как использование случайного GUID для кластерного индекса. Приведенный ниже код демонстрирует фрагментацию.

 SELECT OBJECT_NAME(object_id) ,* FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('dbo.TEST_ASC'), NULL, NULL ,NULL) UNION SELECT OBJECT_NAME(object_id) ,* FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('dbo.TEST_DESC'), NULL, NULL ,NULL) 

ОБНОВИТЬ:

В некоторых тестовых средах я также вижу, что таблица DESC больше подвержена WAITS с увеличением [page_io_latch_wait_count] и [page_io_latch_wait_in_ms]

ОБНОВИТЬ:

Некоторое обсуждение возникло в связи с тем, что является точкой нисходящего индекса, когда SQL может выполнять обратные проверки. Прочитайте эту статью об ограничениях обратного сканирования .

Порядок значений, вставленных в кластеризованный индекс, безусловно, влияет на производительность индекса, потенциально создавая много фрагментации, а также влияет на производительность самой вставки.

Я построил тестовую кровать, чтобы посмотреть, что произойдет:

 USE tempdb; CREATE TABLE dbo.TestSort ( Sorted INT NOT NULL CONSTRAINT PK_TestSort PRIMARY KEY CLUSTERED , SomeData VARCHAR(2048) NOT NULL ); INSERT INTO dbo.TestSort (Sorted, SomeData) VALUES (1797604285, CRYPT_GEN_RANDOM(1024)) , (1530768597, CRYPT_GEN_RANDOM(1024)) , (1274169954, CRYPT_GEN_RANDOM(1024)) , (-1972758125, CRYPT_GEN_RANDOM(1024)) , (1768931454, CRYPT_GEN_RANDOM(1024)) , (-1180422587, CRYPT_GEN_RANDOM(1024)) , (-1373873804, CRYPT_GEN_RANDOM(1024)) , (293442810, CRYPT_GEN_RANDOM(1024)) , (-2126229859, CRYPT_GEN_RANDOM(1024)) , (715871545, CRYPT_GEN_RANDOM(1024)) , (-1163940131, CRYPT_GEN_RANDOM(1024)) , (566332020, CRYPT_GEN_RANDOM(1024)) , (1880249597, CRYPT_GEN_RANDOM(1024)) , (-1213257849, CRYPT_GEN_RANDOM(1024)) , (-155893134, CRYPT_GEN_RANDOM(1024)) , (976883931, CRYPT_GEN_RANDOM(1024)) , (-1424958821, CRYPT_GEN_RANDOM(1024)) , (-279093766, CRYPT_GEN_RANDOM(1024)) , (-903956376, CRYPT_GEN_RANDOM(1024)) , (181119720, CRYPT_GEN_RANDOM(1024)) , (-422397654, CRYPT_GEN_RANDOM(1024)) , (-560438983, CRYPT_GEN_RANDOM(1024)) , (968519165, CRYPT_GEN_RANDOM(1024)) , (1820871210, CRYPT_GEN_RANDOM(1024)) , (-1348787729, CRYPT_GEN_RANDOM(1024)) , (-1869809700, CRYPT_GEN_RANDOM(1024)) , (423340320, CRYPT_GEN_RANDOM(1024)) , (125852107, CRYPT_GEN_RANDOM(1024)) , (-1690550622, CRYPT_GEN_RANDOM(1024)) , (570776311, CRYPT_GEN_RANDOM(1024)) , (2120766755, CRYPT_GEN_RANDOM(1024)) , (1123596784, CRYPT_GEN_RANDOM(1024)) , (496886282, CRYPT_GEN_RANDOM(1024)) , (-571192016, CRYPT_GEN_RANDOM(1024)) , (1036877128, CRYPT_GEN_RANDOM(1024)) , (1518056151, CRYPT_GEN_RANDOM(1024)) , (1617326587, CRYPT_GEN_RANDOM(1024)) , (410892484, CRYPT_GEN_RANDOM(1024)) , (1826927956, CRYPT_GEN_RANDOM(1024)) , (-1898916773, CRYPT_GEN_RANDOM(1024)) , (245592851, CRYPT_GEN_RANDOM(1024)) , (1826773413, CRYPT_GEN_RANDOM(1024)) , (1451000899, CRYPT_GEN_RANDOM(1024)) , (1234288293, CRYPT_GEN_RANDOM(1024)) , (1433618321, CRYPT_GEN_RANDOM(1024)) , (-1584291587, CRYPT_GEN_RANDOM(1024)) , (-554159323, CRYPT_GEN_RANDOM(1024)) , (-1478814392, CRYPT_GEN_RANDOM(1024)) , (1326124163, CRYPT_GEN_RANDOM(1024)) , (701812459, CRYPT_GEN_RANDOM(1024)); 

Первый столбец является первичным ключом, и, как вы можете видеть, значения перечислены в произвольном порядке (ish). Перечисление значений в случайном порядке должно также сделать SQL Server:

  1. Сортировка данных, предварительная вставка
  2. Не сортировать данные, что приводит к фрагментированной таблице.

Функция CRYPT_GEN_RANDOM() используется для генерации 1024 байта случайных данных для каждой строки, чтобы позволить этой таблице потреблять несколько страниц, что, в свою очередь, позволяет нам видеть эффекты фрагментированных вставок.

После запуска указанной выше вставки вы можете проверить фрагментацию следующим образом:

 SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('TestSort'), 1, 0, 'SAMPLED') ips; 

Запуск этого экземпляра SQL Server 2012 Developer Edition показывает среднюю фрагментацию 90%, что указывает на то, что SQL Server не сортировал во время вставки.

Мораль этой конкретной истории, вероятно, будет «когда усомниться, соберите, если это будет полезно». Сказав, что добавление и предложение ORDER BY в инструкцию insert не гарантируют, что в этом порядке будут вставляться вставки. Подумайте, что произойдет, если вставку идет параллельно, в качестве примера.

В непроизводственных системах вы можете использовать флаг трассировки 2332 в качестве опции в инструкции insert для принудительного SQL Server для сортировки ввода до его вставки. @PaulWhite имеет интересную статью, оптимизируя запросы T-SQL, которые изменяют данные, охватывающие это, и другие детали. Имейте в виду, что флаг трассировки не поддерживается и не должен использоваться в производственных системах, поскольку это может аннулировать вашу гарантию. В непроизводственной системе для вашего собственного образования вы можете попробовать добавить это в конец INSERT :

 OPTION (QUERYTRACEON 2332); 

После того, как вы добавили к вставке, посмотрите на план, вы увидите явный вид:

введите описание изображения здесь

Было бы замечательно, если бы Microsoft сделала это поддерживаемым флагом трассировки.

Пол Уайт дал мне понять, что SQL Server автоматически вводит оператор сортировки в план, когда он считает, что он будет полезен. Для примера запроса выше, если я запустил вставку с 250 элементами в предложении values , сортировка не будет выполнена автоматически. Однако в 251 элементе SQL Server автоматически сортирует значения до вставки. Почему обрезание 250/251 строк остается для меня загадкой, за исключением того, что она жестко закодирована. Если я SomeData размер данных, вставленных в столбец SomeData всего на один байт, обрезание по- прежнему составляет 250/251 строк, хотя размер таблицы в обоих случаях составляет всего одну страницу. Интересно, глядя на вставку с SET STATISTICS IO, TIME ON; показывает вставки с одним байтом. Значение SomeData занимает в два раза больше времени при сортировке.

Без сортировки (т.е. 250 строк вставлены):

  Время и время компиляции SQL Server: 
    Время CPU = 0 мс, прошедшее время = 0 мс.
 Время и время компиляции SQL Server: 
    Время CPU = 16 мс, прошедшее время = 16 мс.
 Время и время компиляции SQL Server: 
    Время CPU = 0 мс, прошедшее время = 0 мс.
 Таблица «TestSort».  Число сканирования 0, логическое чтение 501, физическое чтение 0, 
    read-ahead читает 0, lob логическое чтение 0, физическое чтение lob 0, lob 
    read-ahead читает 0.

 (Затронуто 250 строк)

 (Затронуты 1 ряд)

  Время выполнения SQL Server:
    Время CPU = 0 мс, прошедшее время = 11 мс. 

С сортировкой (т.е. 251 строка вставлена):

  Время и время компиляции SQL Server: 
    Время CPU = 0 мс, прошедшее время = 0 мс.
 Время и время компиляции SQL Server: 
    Время CPU = 15 мс, прошедшее время = 17 мс.
 Время и время компиляции SQL Server: 
    Время CPU = 0 мс, прошедшее время = 0 мс.
 Таблица «TestSort».  Число сканирования 0, логическое считывание 503, физическое чтение 0, 
    read-ahead читает 0, lob логическое чтение 0, физическое чтение lob 0, lob 
    read-ahead читает 0.
 Таблица «Рабочий стол».  Число сканирования 0, логическое чтение 0, физическое чтение 0, 
    read-ahead читает 0, lob логическое чтение 0, физическое чтение lob 0, lob 
    read-ahead читает 0.

 (Затронуто 251 ряд строк)

 (Затронуты 1 ряд)

  Время выполнения SQL Server:
    Время CPU = 16 мс, прошедшее время = 21 мс.

Как только вы начнете увеличивать размер строки, сортированная версия, безусловно, становится более эффективной. При вставке 4096 байтов в SomeData сортированная вставка почти на два раза быстрее на моей тестовой установке, чем несортированная вставка.


В качестве примечания, если вы заинтересованы, я сгенерировал предложение VALUES (...) , используя этот T-SQL:

 ;WITH s AS ( SELECT v.Item FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v(Item) ) , v AS ( SELECT Num = CONVERT(int, CRYPT_GEN_RANDOM(10), 0) ) , o AS ( SELECT v.Num , rn = ROW_NUMBER() OVER (PARTITION BY v.Num ORDER BY NEWID()) FROM s s1 CROSS JOIN s s2 CROSS JOIN s s3 CROSS JOIN v ) SELECT TOP(50) ', (' + REPLACE(CONVERT(varchar(11), o.Num), '*', '0') + ', CRYPT_GEN_RANDOM(1024))' FROM o WHERE rn = 1 ORDER BY NEWID(); 

Это генерирует 1000 случайных значений, выбирая только первые 50 строк с уникальными значениями в первом столбце. Я скопировал и ввел вывод в INSERT выше.

Пока данные поступают по кластерному индексу (независимо от того, является ли это восходящим или нисходящим), тогда не должно быть никакого влияния на производительность вставки. Причиной этого является то, что SQL не заботится о физическом порядке строк на странице для кластерного индекса. Порядок строк хранится в так называемом «Record Offset Array», который является единственным, который должен быть переписан для новой строки (которая в любом случае была бы сделана независимо от порядка). Фактические строки данных будут просто записываться один за другим.

На уровне журнала транзакций записи должны быть идентичными независимо от направления, чтобы это не создавало дополнительного влияния на производительность. Обычно журнал транзакций – это тот, который генерирует большинство проблем с производительностью, но в этом случае их не будет.

Вы можете найти хорошее объяснение физической структуры страницы / строки здесь https://www.simple-talk.com/sql/database-administration/sql-server-storage-internals-101/ .

Так что в основном до тех пор, пока ваши вставки не будут генерировать разбиение на страницы (и если данные поступают в порядке кластеризованного индекса, независимо от порядка его не будет), ваши вставки будут иметь незначительное влияние на производительность вставки.

Основываясь на приведенном ниже коде, вставка данных в столбец идентификации с отсортированным кластеризованным индексом более интенсивна в ресурсах, когда выбранные данные упорядочены в противоположном направлении отсортированного кластерного индекса.

В этом примере логические чтения почти удваиваются.

После 10 запусков отсортированный восходящий логический считывает среднее значение 2284, а отсортированный нисходящий логический считывает среднее значение 4301.

 --Drop Table Destination; Create Table Destination (MyId INT IDENTITY(1,1)) Create Clustered Index ClIndex On Destination(MyId ASC) set identity_insert destination on Insert into Destination (MyId) SELECT TOP (1000) n = ROW_NUMBER() OVER (ORDER BY [object_id]) FROM sys.all_objects ORDER BY n set identity_insert destination on Insert into Destination (MyId) SELECT TOP (1000) n = ROW_NUMBER() OVER (ORDER BY [object_id]) FROM sys.all_objects ORDER BY n desc; 

Подробнее о логических чтениях, если вы заинтересованы: https://www.brentozar.com/archive/2012/06/tsql-measure-performance-improvements/

  • Почему сортировка записей в таблице не соответствует кластерному индексу?
  • Каковы последствия преобразования индексов кучи в кластеризованные индексы на SQL Server?
  • Индексы SQL Server - некластерный указатель на HEAP
  • Определить, какую структуру / алгоритм индексирования использует SQL Server?
  • Лучшая практика для индексирования внешних ключей на большой таблице
  • Низкая статистика Логические чтения и чтение с высоким профилировщиком
  • Стратегия индексирования SQL Server
  • Разница между первичным ключом (кластером) и уникальным кластеризованным индексом в SQL Server
  • Давайте будем гением компьютера.