Подтвердить что ты не робот

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

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

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

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

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

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

4b9b3361

ОТВЕТЫ

Ответ 1

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

При запуске следующего кода кластеризованный индекс 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]

UPDATE:

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

Ответ 2

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

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

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:

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

Функция 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, всего на один байт, обрезание по-прежнему составляет 250/251 строк, хотя размер таблицы в обоих случаях составляет всего одну страницу. Интересно, что просмотр вставки с помощью SET STATISTICS IO, TIME ON; показывает, что вставки с однобайтовым значением SomeData занимают в два раза больше времени при сортировке.

Без сортировки (т.е. 250 строк):

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 16 ms, elapsed time = 16 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
Table 'TestSort'. Scan count 0, logical reads 501, physical reads 0, 
   read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob 
   read-ahead reads 0.

(250 row(s) affected)

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 11 ms.

С помощью сортировки (то есть 251 строки вставлены):

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 15 ms, elapsed time = 17 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
Table 'TestSort'. Scan count 0, logical reads 503, physical reads 0, 
   read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob 
   read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, 
   read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob 
   read-ahead reads 0.

(251 row(s) affected)

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 21 ms.

Как только вы начнете увеличивать размер строки, сортированная версия, безусловно, становится более эффективной. При вставке 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 выше.

Ответ 3

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

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

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

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

Ответ 4

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

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

После 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/