Производительность вставки SQL Server
Предположим, у меня есть следующая таблица с кластеризованным индексом по столбцу (скажем, a)
CREATE TABLE Tmp
(
a int,
constraint pk_a primary key clustered (a)
)
Затем, давайте предположим, что у меня есть два набора очень большого количества строк для вставки в таблицу.
- 1-й набор) значения последовательно увеличиваются (т. Е. {0,1,2,3,4,5,6,7,8,9,..., 999999997, 999999998, 99999999})
- 2-й набор) значения последовательно уменьшаются (т. Е. {99999999,999999998,999999997, ..., 3,2,1,0}
Как вы думаете, будет ли разница в производительности между вставкой значений в первый набор и второй набор? Если так, то почему?
Спасибо
3 ответа
SQL Server, как правило, будет пытаться сортировать большие вставки в порядке кластеризованного индекса до вставки в любом случае.
Однако если источником для вставки является табличная переменная, она не будет учитывать количество элементов, если только оператор не будет перекомпилирован после заполнения табличной переменной. Без этого предполагается, что вставка будет только одной строкой.
Скрипт ниже демонстрирует три возможных сценария.
- Источник вставки уже точно в правильном порядке.
- Источник вставки точно в обратном порядке.
- Источник вставки точно в обратном порядке, но
OPTION (RECOMPILE)используется для того, чтобы SQL Server компилировал план, подходящий для вставки 1 000 000 строк.
Планы выполнения
Третий имеет оператор сортировки, чтобы сначала получить вставленные значения в порядке кластерного индекса.
/*Create three separate identical tables*/
CREATE TABLE Tmp1(a int primary key clustered (a))
CREATE TABLE Tmp2(a int primary key clustered (a))
CREATE TABLE Tmp3(a int primary key clustered (a))
DBCC FREEPROCCACHE;
GO
DECLARE @Source TABLE (N INT PRIMARY KEY (N ASC))
INSERT INTO @Source
SELECT TOP (1000000) ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM sys.all_columns c1, sys.all_columns c2, sys.all_columns c3
SET STATISTICS TIME ON;
PRINT 'Tmp1'
INSERT INTO Tmp1
SELECT TOP (1000000) N
FROM @Source
ORDER BY N
PRINT 'Tmp2'
INSERT INTO Tmp2
SELECT TOP (1000000) 1000000 - N
FROM @Source
ORDER BY N
PRINT 'Tmp3'
INSERT INTO Tmp3
SELECT 1000000 - N
FROM @Source
ORDER BY N
OPTION (RECOMPILE)
SET STATISTICS TIME OFF;
Проверить результаты и очистить
SELECT object_name(object_id) AS name,
page_count,
avg_fragmentation_in_percent,
fragment_count,
avg_fragment_size_in_pages
FROM
sys.dm_db_index_physical_stats(db_id(), object_id('Tmp1'), 1, NULL, 'DETAILED')
WHERE index_level = 0
UNION ALL
SELECT object_name(object_id) AS name,
page_count,
avg_fragmentation_in_percent,
fragment_count,
avg_fragment_size_in_pages
FROM
sys.dm_db_index_physical_stats(db_id(), object_id('Tmp2'), 1, NULL, 'DETAILED')
WHERE index_level = 0
UNION ALL
SELECT object_name(object_id) AS name,
page_count,
avg_fragmentation_in_percent,
fragment_count,
avg_fragment_size_in_pages
FROM
sys.dm_db_index_physical_stats(db_id(), object_id('Tmp3'), 1, NULL, 'DETAILED')
WHERE index_level = 0
DROP TABLE Tmp1, Tmp2, Tmp3
STATISTICS TIME ON Результаты
+------+----------+--------------+
| | CPU Time | Elapsed Time |
+------+----------+--------------+
| Tmp1 | 6718 ms | 6775 ms |
| Tmp2 | 7469 ms | 7240 ms |
| Tmp3 | 7813 ms | 9318 ms |
+------+----------+--------------+
Результаты фрагментации
+------+------------+------------------------------+----------------+----------------------------+
| name | page_count | avg_fragmentation_in_percent | fragment_count | avg_fragment_size_in_pages |
+------+------------+------------------------------+----------------+----------------------------+
| Tmp1 | 3345 | 0.448430493 | 17 | 196.7647059 |
| Tmp2 | 3345 | 99.97010463 | 3345 | 1 |
| Tmp3 | 3345 | 0.418535127 | 16 | 209.0625 |
+------+------------+------------------------------+----------------+----------------------------+
Заключение
В этом случае все три из них в конечном итоге использовали одинаковое количество страниц. тем не мение Tmp2 фрагментирован на 99,97% по сравнению с только 0,4% для двух других. Вставка в Tmp3 потребовалось больше всего времени, так как для этого сначала потребовался дополнительный шаг сортировки, но эти единовременные затраты необходимо сопоставить с выгодой будущих проверок по таблице минимальной фрагментации.
Причина по которой Tmp2 настолько сильно фрагментирован, можно увидеть из запроса ниже
WITH T AS
(
SELECT TOP 3000 file_id, page_id, a
FROM Tmp2
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%)
ORDER BY a
)
SELECT file_id, page_id, MIN(a), MAX(a)
FROM T
group by file_id, page_id
ORDER BY MIN(a)
При нулевой логической фрагментации страница со следующим наивысшим значением ключа будет следующей наивысшей страницей в файле, но страницы будут точно в том порядке, в котором они должны быть.
+---------+---------+--------+--------+
| file_id | page_id | Min(a) | Max(a) |
+---------+---------+--------+--------+
| 1 | 26827 | 0 | 143 |
| 1 | 26826 | 144 | 442 |
| 1 | 26825 | 443 | 741 |
| 1 | 26824 | 742 | 1040 |
| 1 | 26823 | 1041 | 1339 |
| 1 | 26822 | 1340 | 1638 |
| 1 | 26821 | 1639 | 1937 |
| 1 | 26820 | 1938 | 2236 |
| 1 | 26819 | 2237 | 2535 |
| 1 | 26818 | 2536 | 2834 |
| 1 | 26817 | 2835 | 2999 |
+---------+---------+--------+--------+
Строки поступали в порядке убывания, поэтому, например, значения 2834–2536 были помещены на страницу 26818, затем была назначена новая страница для 2535, но это была страница 26819, а не страница 26817.
Одна из возможных причин, по которой вставка Tmp2 заняло больше времени, чем Tmp1 потому что строки вставляются в обратном порядке на странице каждой вставки Tmp2 означает, что массив слотов на странице необходимо переписать так, чтобы все предыдущие записи были перемещены вверх, чтобы освободить место для нового поступления.
Это связано с последовательным размещением страниц, как это делается для кластерного индекса. С первым они естественно сгруппировались бы. Но во-вторых, я думаю, что вам придется продолжать перемещать местоположения страниц, чтобы они последовательно поднимались. Однако я действительно понимаю SQL-сервер только на концептуальном уровне, так что вам придется тестировать.
Чтобы ответить на этот вопрос, вам нужно только посмотреть, как кластеризация влияет на данные и как они логически упорядочены. При кластеризации по возрастанию более высокие числа добавляются в конец таблицы; вставки будут очень быстрыми. При вставке в обратном направлении он будет вставлен между двумя другими записями (прочитайте разделение страницы); это приведет к более медленным вставкам. Это на самом деле имеет и другие негативные последствия (читайте о коэффициенте заполнения).