По материалам статьи Joe
Chang Large
Data Operations in SQL Server
Перевод Виталия Степаненко
- Обзор
формул оценки затрат ресурсов для построения плана
выполнения
Тестовая среда
Тестовые запросы
Запросы обновления
Запросы удаления
Заключение
Пример, представленный в этой статье, показывает, что формулы внутренних затрат ресурсов SQL Server для операций ввода-вывода основаны на времени доступа к диску. Величины затрат ресурсов на операции ввода-вывода были зафиксированы несколько лет назад. Они неточно отражают производительность современных дисков, как и действительные затраты ресурсов на операции в памяти. Кроме того, модель затрат ресурсов на выполнение запросов SQL Server не совсем аккуратно отражает затраты ресурсов на операции ввода-вывода для Insert, Update и Delete (IUD). Для операций с небольшим количеством строк разница между внутренней моделью затрат ресурсов SQL Server и действительными затратами на выполнение запросов незначительно влияет на эффективность плана выполнения. В операциях с большим количеством строк план по умолчанию может работать существенно медленнее, чем план выполнения для определенного количества операций ввода-вывода.
Обзор формул оценки затрат ресурсов для построения плана выполнения
Для начала, может оказаться полезным обзор формул оценки затрат ресурсов для построения плана выполнения SQL Server, рассмотренных в статьях серии 'Анализ производительности'. Формула оценки затрат ресурсов на операцию Index Seek показана ниже. Затраты ресурсов на ввод-вывод зависят от количества физической памяти.
|
Затраты ресурсов на ввод-вывод (I/O Cost)
= Затраты ресурсов процессора (CPU Cost) = 0.000079600 + 0.000001100 на каждую следующую строку Формула оценки затрат ресурсов для множества операций Bookmark Lookup показана ниже. Затраты ресурсов на ввод-вывод (I/O Cost) = Затраты ресурсов процессора (CPU Cost) = 0.0000011 на строку |
Множество операций Bookmark Lookup - это не точное количество строк, а часть этого количества, обычно >95% от большого количества строк. Для обеих операций Index Seek и Bookmark Lookup базовые затраты ресурсов на ввод-вывод зависят от конфигурации памяти компьютера. Если объем памяти меньше или равен 1 гигабайту, то базовые затраты ресурсов для Index Seek составляют 0.063285. Если объем памяти больше 1 гигабайта, то затраты ресурсов на ввод-вывод составляют 0.003203425. Все другие затраты ресурсов на ввод-вывод не зависят от системной памяти (если только не используется неявным образом поиск по индексу). Ниже показана формула оценки затрат ресурсов на операцию Table Scan, хотя она также может применяться для сканирования кластерных и некластерных индексов.
|
Затраты ресурсов на ввод-вывод (I/O Cost) = 0.0375785 + 0.000740741 на каждую следующую страницу Затраты ресурсов процессора (CPU Cost) = 0.0000785 + 0.0000011 на каждую следующую строку |
Для всех операций изменения содержимого таблицы - Insert, Update и Delete (IUD) - затраты ресурсов на ввод-вывод примерно соответствуют формулам, приведенным ниже. Затраты ресурсов процессора точно соответствуют формуле. Общие затраты ресурсов на операции Update и Delete включают затраты на поиск по индексу или на сканирование таблицы, но они могут быть не включены в отдельные значения затрат ресурсов на ввод-вывод и затрат ресурсов процессора.
|
Затраты ресурсов на ввод-вывод (I/O Cost) ~ 0.01002 - 0.01010 (>100 строк) Затраты ресурсов процессора (CPU Cost) = 0.000001 на каждую строку |
Интересный момент заключается в том, что затраты ресурсов на ввод-вывод для операций IUD фиксированные, несмотря на количество строк и страниц. Сравните это с затратами ресурсов на ввод-вывод для операций Index Seek, Bookmark Lookup и Table Scan, которые зависят от количества страниц.
Похоже, не существует никакой документации от Microsoft о единицах измерения затрат ресурсов плана выполнения SQL Server. Единица измерения может быть некой величиной времени или загрузки процессора. Статья 'Параллельные планы выполнения' показывает, что единица измерения является скорее категорией времени, чем загрузки процессора. В качестве основного доказательства этого утверждения можно привести то, что план выполнения с параллельными операциями требует меньше затрат ресурсов, чем эквивалентный план с непараллельными операциями. Логическим объяснением может быть то, что параллельные операции на двухпроцессорных компьютерах выполняются за половину времени плюс некоторые затраты времени на объединение результатов от каждого процессора. Если бы единицей измерения для плана выполнения выступала загруженность процессора, то параллельная операция показала бы более высокие затраты ресурсов. Это связано с тем, что разделение задачи между двумя процессорами не сократит общую сумму циклов процессора, и еще понадобятся дополнительные затраты ресурсов на объединение результатов.
Если предположить, что затраты ресурсов плана выполнения SQL Server измеряются единицами времени, то можно сделать следующее предположение, что единицей измерения является секунда. Значения затрат ресурсов не зависят от типа процессора и они не изменились при переходе с SQL Server 7.0 на 2000. Можно предположить, что затраты ресурсов были настроены на некой системе, когда SQL Server 7.0 был в разработке, где-то между 1995 и 1997 годами. Обоснованием этого периода является то, что среда, на которой были настроены единицы затрат ресурсов SQL Server, была базой данных с объемом данных, намного превышающим доступную системную память. В этом случае операции SQL часто сопроаождались бы операциями дисковыми ввода-вывода.
Для современного диска со скоростью 15K затраты ресурсов ввода-вывода для Index Seek в 0.0063285 секунды (или 6.33мс) являются достаточно произвольным временем доступа. Хотя этот результат и является чрезмерно низким для дисков со скоростью вращения 7200 RPM, доступных в середине 1990-х годов. Возможно, что был установлен коэффициент кэша буфера в 50% для затрат ресурсов на ввод-вывод для операций Index Seek и Bookmark Lookup для систем с памятью меньше 1 гигабайта и 75% для систем с памятью больше 1 гигабайта. Однако нет нужды ограничиваться только двумя возможными вариантами единиц измерения затрат ресурсов на ввод-вывод. Дополнительные затраты ресурсов на ввод-вывод в 0.0007407 на каждую страницу могут быть объяснены как время последовательного ввода-вывода (0.74мс), подразумевая, что скорость последовательной передачи данных диска равна 1350 операций ввода-вывода в секунду по 8KB каждая или 169 операций ввода-вывода в секунду по 64KB.
Вообще-то, память системы не может быть надежным показателем для коэффициента кэша буфера. Лучшей стратегией для настройки затрат ресурсов на ввод-вывод могло быть использование недавней истории коэффициента кэша буфера. Для операций с большим количеством строк также имеет смысл определить, сколько из необходимого количества данных уже находится в памяти.
Сервером тестовой среды является двухпроцессорный Xeon 2.4GHz/512K кэш, системная шина 533MHz и 2 гигабайта памяти на материнской плате с чипсетом ServerWorks GC-LE. Файл данных тестовой базы данных находится на двух 18-гигабайтных SCSI U160 дисках со скоростью вращения 15K, все log файлы находятся на одном SCSI диске со скоростью вращения 10K, а файл данных базы tempdb находится на диске IDE/ATA. Модель восстановления базы данных установлена в SIMPLE и 4 гигабайта дискового пространства заранее распределены для данных и лога. Кроме того, максимальный уровень параллельности был ограничен в большинстве тестов одним процессором. Результаты являются измерениями времени выполнения единственного запроса. Существуют некоторые отличия между измерениями, т.к. нет никакого простого способа определить содержание кэша буфера. Была сделана попытка произвести оценку с повторяющимися значениями.
Следующий скрипт создает тестовую таблицу и заполняет ее тестовыми данными. Таблица содержит 8 столбцов типа integer по 4 байта каждый, столбец типа char из 10 байт, столбец типа decimal из 5 байт, и 3 других столбца по 8 байт каждый, в сумме составляющие 71 байт. Вместе с метаданными все это требует примерно 81 байт на строку, позволяя 99 строкам поместиться в каждую 8-килобайтную страницу с коэффициентом заполнения 99%, оставляя свободными 77 байт.
CREATE TABLE M3C_00 (
ID int NOT NULL,
ID2 int NOT NULL,
ID3 int NOT NULL,
ID4 int NOT NULL,
ID5 int NOT NULL,
ID6 int NOT NULL,
SeqID int NOT NULL,
DistID int NOT NULL,
Value char(10) NOT NULL,
randDecimal decimal (9,4) NOT NULL,
randMoney money NOT NULL,
randDate datetime NOT NULL,
seqDate datetime NOT NULL )
|
Следующий скрипт заполняет таблицу 10 миллионами строк. Столбец ID - это последовательность от 1 до 10 миллионов, но он не объявлен явно как столбец типа identity. Столбцы с ID2 по ID6 не важны в этих сериях тестов, и в них могут быть использованы любые значения. Значения столбца SeqID последовательны, а значения столбца DistID разнесены по таблице. В этом примере таблица имеет 10 миллионов строк. Первые 100 000 строк имеют значение столбца SeqID, равное 1; следующие 100 000 имеют значение столбца SeqID, равное 2, и т.д. Первая строка имеет значение столбца DistID, равное 1; следующая строка со значением столбца DistID - строка 101, и т.д. Все строки с определенным SeqID являются смежными строками. Все строки с определенным значением DistID разделены 100 строками, что приводит к тому, что каждое отдельное значение DistID находится в отдельной 8-килобайтной странице. Таблица не кластерная, поэтому т.к. нет гарантии определенного размещения строк, то строки вставляются последовательно.
BEGIN TRANSACTION
DECLARE @I int, @rowCnt int, @p int, @sc1 int, @dv1 int
SELECT @I = 1, @rowCnt = 10000000, @p =100, @sc1 = 100000
SELECT @dv1 = @rowCnt/100000
WHILE @I <= @RowCnt BEGIN
INSERT M3C_00 (ID, ID2, ID3, ID4, ID5, ID6, SeqID, DistID,
Value, randDecimal, randMoney, randDate, seqDate)
VALUES ( @I, @I, @I/2, @I/4, @I/10, @I/20,
(@I-1)/@sc1 + 1, -- Последовательные значения
(@I-1)%(@dv1) + 1, -- Разнесенные значения
CHAR(65 + 26*rand())+CHAR(65 + 26*rand())+CHAR(65 + 26*rand())
+CONVERT(char(6),CONVERT(int,100000*(9.0*rand()+1.0)))+CHAR(65 + 26*rand()),
10000*rand(), 10000*rand(),
DATEADD(hour,100000*rand(),'1990-01-01'), DATEADD(hour,@I/5,'1990-01-01') )
SET @I = @I+1
END
COMMIT TRANSACTION
CHECKPOINT
|
Этот скрипт требует 17 минут 45 секунд для выполнения, вставляя в среднем 9380 строк в секунду. Таблица M3C_00 заканчивается на 105 264 странице со степенью заполнения 95%. Несмотря на генерацию таблицы каждый раз, когда нужен свежий набор данных, исходный набор данных сохранен в таблице M3C_00 и копия исходного набора создается при помощи команды SELECT INTO, показанной ниже.
SELECT ID,ID2,ID3,ID4,ID5,ID6,SeqID,DistID,Value,randDecimal,randMoney,randDate,seqDate INTO M3C_01 FROM M3C_00 CHECKPOINT |
Эта таблица имеет 101 012 страниц, примерно 99 строк на страницу со степенью заполнения 99%, и размера чуть больше 789 мегабайт. Команда SELECT INTO выполняется примерно 28 секунд, вставляя в среднем 357 000 строк в секунду. Следующие 2 индекса создаются на столбцах SeqID и DistID. Каждый индекс занимает 18 554 страницы или 145 мегабайт.
CREATE INDEX IX_M3C_01_Seq ON M3C_01 (SeqID) WITH SORT_IN_TEMPDB CHECKPOINT CREATE INDEX IX_M3C_01_Dist ON M3C_01 (DistID) WITH SORT_IN_TEMPDB CHECKPOINT |
Первый индекс требует примерно 30 секунд для создания с использованием памяти в 256 мегабайт, а второй требует 60 секунд.
Первые протестированные запросы - это команды Select, показанная ниже. Первая пара запросов Select использует столбец SeqID. Вторая пара запросов использует столбец DistID. Первый запрос в каждой паре - базовая команда Select без индексных хинтов. Второй запрос каждой пары использует хинт для указания определенного индекса для построения плана выполнения.
-- Последовательные строки, сканирование таблицы (table scan) SELECT AVG(randMoney) FROM M3C_01 WHERE SeqID = 91 -- Последовательные строки, поиск по индексу (index seek) и bookmark lookup SELECT AVG(randMoney) FROM M3C_01 WITH(INDEX(IX_M3C_01_Seq)) WHERE SeqID = 91 -- Разнесенные строки, сканирование таблицы (table scan) SELECT AVG(randMoney) FROM M3C_01 WHERE DistID = 91 -- Разнесенные строки, поиск по индексу (index seek) и bookmark lookup SELECT AVG(randMoney) FROM M3C_01 WITH(INDEX(IX_M3C_01_Disr)) WHERE DistID = 91 |
Оценочное и действительное количество строк для обоих поисковых аргументов (SARG) составляет 100 000 строк. План выполнения запроса по умолчанию (без хинтов) для обоих поисковых аргументов является сканированием таблицы. Хинт заставляет план выполнения использовать индекс для поисковых аргументов, который требует bookmark lookup для получения значения столбца randMoney. Сейчас статистика SQL Server содержит только информацию об области значений столбцов, а не о расположении строк. Таким образом, SQL Server не может знать, сколько страниц потребуется в действительности. Т.к. запросы с разными поисковыми аргументами имеют одинаковое оценочное (и действительное) количество строк, то план выполнения и оценочные затраты ресурсов одинаковы для обоих поисковых аргументов. Планы выполнения для первой пары запросов показаны ниже.
Сканирование таблицы использует 101 012 страниц, а Bookmark Lookup - 100 000 строк, так что дейтвительное время выполнения запроса в основном зависит от затрат на страницу для сканирования таблицы и от затрат на строку для bookmark lookup. Затраты ресурсов на сканирование таблицы показаны ниже.
Оценочные затраты на ввод-вывод равны 37.4 и оценочная загрузка процессора равна 5.50, но оценочные затраты ресурсов на всю операцию равны 85.86. Рассчитывая затраты на ввод-вывод и загрузку процессора по формуле из предыдущей главы, получаем 74.86 для затрат на ввод-вывод и 11.00 для загрузки процессора. Это в точности соответствует общему значению затрат на ввод-вывод и загрузку процессора, равному 85.86. Каждый раз значения затрат на ввод-вывод и загрузку процессора точно равны половине ожидаемого значения, но общие затраты точно равны ожидаемому значению.
Затраты на поиск по индексу показаны ниже. Затраты на ввод-вывод, равные 0.140240, точно равны базовым затратам из предыдущей главы плюс затраты на дополнительные 185 страниц, подразумевая, что каждая страница индекса содержит примерно 541 строку. Загрузка процессора, равная 0.110000, соответствует 100 000 строк для общих затрат на ввод-вывод и загрузку процессора, равных 0.250619.
Затраты на bookmark lookup показаны ниже. Затраты на ввод-вывод, равные 311.86, на 99.8% соответствуют затратам на ввод-вывод для единственной строки для Bookmark Lookup (0.0031249), умноженным в 100 000 раз.
Для тестирования больше применялись не изменение системной памяти или размера тестовых данных, а изменение настроек максимума памяти сервера. Разные времена выполнения, измеряемые с помощью Profiler с установленным максимумом памяти сервера в 256M и 1,154M, показаны ниже. Счетчики STATISTICS IO и Perfmon disk подтверждают, что вся таблица читается с диска с 256 мегабайтами серверной памяти, плана выполнения с Index Seek и Bookmark Lookup по столбцу SeqID, который требует очень небольшого количества чтений диска. При оценке времени выполнения для памяти в 1,154 мегабайт и данные, и индексы находились в памяти.
|
Запрос SELECT |
Последовательные |
Последовательные |
Разнесенные |
Разнесенные |
|
256M памяти |
Index + BL |
Сканирование таблицы |
Index + BL |
Сканирование таблицы |
|
Время запроса (сек) |
0.3 |
10.5 |
167 |
10.5 |
|
Строк или страниц / сек |
333,333(R) |
9,620(P) |
599(R) |
9,620(P) |
|
Диск.чтений / сек |
Низкое |
~1,200 |
~600 |
~1,200 |
|
Ср.байт / 1 чтение |
|
64K |
8K |
64K |
|
1154MB памяти |
Index + BL |
Сканирование таблицы |
Index + BL |
Сканирование таблицы |
|
Время запроса (сек) |
0.266 |
1.076 |
0.373 |
1.090 |
|
Строк или страниц / сек |
376,000 |
93,877 |
268,000 |
92,672 |
Оба сканирования таблицы требуют около 10.5 секунд для выполнения, так что расположение строк не влияет на время сканирования таблицы. Количество операций ввода-вывода для каждого физического диска составляло примерно 600/сек. при 64Кб на 1 чтение при скорости передачи 37.5Мб/сек., почти максимальная скорость для винчестера Seagate ST318451 (современное поколение дисков со скоростью вращения 15K поддерживает последовательную скорость передачи данных >50MB/сек.)
План выполнения с поиском по индексу для последовательных строк показывает некоторую начальную активность диска (меньше, чем 200 операций ввода-вывода) при конфигурации памяти в 256 мегабайт, но после этого выполняется в памяти. План выполнения с поиском по индексу для разнесенных строк был вынужден получать каждую 8-килобайтную страницу из 101 012-страничной таблицы с диска в случае, когда максимальная память диска ограничена 256 мегабайтами. Каждый диск в среднем выполнял 300 операций ввода-вывода в секунду, выбирая 8KB за 1 чтение. Может показаться, что 300 операций ввода-вывода в секунду - это довольно много для диска со скоростью вращения 15K. Однако средняя длина очереди была равна 40 на диск, и данные были распределены на пространстве около 0.8 гигабайт из 32 гигабайт дискового пространства. Большая длина очереди позволяет диску менять последовательность операций ввода-вывода, а небольшое использование дискового пространства уменьшает среднюю дистанцию, которую проходит головка диска - оба этих фактора увеличивают производительность ввода-вывода.
При конфигурации памяти в 1154 мегабайта, т.е. с достаточным количеством памяти для хранения в ней всей таблицы и обоих индексов, как только данные загружаются в память, дисковая активность у всех четырех запросов прекращается. Скорость сканирования таблицы, находящейся в памяти, составляющая примерно 93 000 страниц в секунду, является ожидаемой производительностью процессора Xeon 2.4GHz (с максимальной степенью параллелизма, равной 1), где затраты на 1 страницу при сканировании таблицы составляют примерно 25K циклов процессора. И план Index Seek, и план Bookmark Lookup требуют гораздо меньших затрат, чем сканирование таблицы, когда все данные находятся в памяти. Bookmark Lookup для последовательных строк работает примерно в 4 раза быстрее, чем табличное сканирование, а для разнесенных строк работает почти в 3 раза быстрее, чем табличное сканирование.
Формула расчета затрат для плана выполнения (>1 гигабайта) оценивает поиск по индексу, сопровождаемый bookmark lookup, как в 3.6 раза более затратный (по времени выполнения), чем табличное сканирование. Затраты плана выполнения не учитывают такие факторы, как находятся ли данные уже в памяти, могут ли данные поместиться в памяти, хотя эти факторы имеют огромное влияние на затраты ресурсов на выполнение запроса. Для SQL Server вполне оправданно не учитывать расположение данных, потому что статистика SQL Server содержит эту информацию.
Когда все данные должны читаться с диска, действительные затраты на табличное сканирование, использующее последовательное чтение с диска, оказываются в 16 раз меньше, чем затраты на план выполнения с поиском по индексу и с bookmark lookup с операциями ввода-вывода для произвольно расположенных данных. Сравните это с преимуществом в 3.6 раза, которое было предсказано формулой расчета затрат плана выполнения. Использованный здесь диск со скоростью вращения 15K RPM имеет примерно в 2 раза большую производительность произвольного ввода-вывода, чем диск со скоростью вращения 7200RPM, доступный 8 лет назад, но он еще имеет и в 8 раз большую производительность последовательного чтения по сравнению со старыми дисками. Вполне возможно, что старый диск 7200RPM действительно выдал бы именно такой результат, который был предсказан формулой плана выполнения.
Если количество дисков увеличить, то и последовательная, и произвольная производительность операций ввода-вывода увеличится примерно на одно и то же значение. Когда есть достаточно дисков для достижения уровня последовательной передачи данных в 800Mб/сек. (в 10 раз больше, чем текущая скорость передачи данных), производительность табличного сканирования ограничится скоростью работы процессора. Однако есть хорошие основания полагать, что дальнейшее увеличение количества дисков увеличит производительность произвольного ввода-вывода до 10 000 операций в секунду.
Те же самые четыре типа запросов были протестированы для команды Update. Первая пара запросов обновляет последовательный блок из 100 000 строк. Вторая пара запросов обновляет 100 000 строк, где каждая строка находится в отдельной 8-килобайтной странице. Команда Checkpoint выполняется сразу после команды Update. Сложение времени выполнения команд Update и Checkpoint определяет действительное время, необходимое для записи изменений на диск. Даже если сессия Query Analyzer может показывать, что команда выполнена, сложение времени выполнения двух команд является более точной оценкой реальных затрат.
-- Последовательные строки, поиск по индексу UPDATE m SET randMoney = 1.0 FROM M3C_01 m WHERE SeqID = 91 GO CHECKPOINT GO -- Последовательные строки, сканирование таблицы UPDATE m SET randMoney = 1.0 FROM M3C_01 m WITH(INDEX(0)) WHERE SeqID = 91 GO CHECKPOINT GO -- Разнесенные строки, поиск по индексу UPDATE M3C_01 SET randMoney = 5000.0 WHERE DistID = 91 GO CHECKPOINT GO -- Разнесенные строки, сканирование таблицы UPDATE m SET randMoney = 1.0 FROM M3C_01 m WITH(INDEX(0)) WHERE DistID = 5 GO CHECKPOINT GO |
План выполнения для первой пары команд Update показан ниже. Заметьте, что план без хинтов использует поиск по индексу, чтобы найти строки для обновления. Индекс может только определить строки для обновления, но не загружает в действительности страницу данных в память. Нигде не видно, что первый план действительно учитывает затраты на получение страницы данных, которую нужно изменить. Перед тем, как SQL Server сможет записать информацию на страницу, она сначала должна быть загружена в буферный кэш. Хинт INDEX(0) во втором плане - это указание на использование сканирования таблицы.
Index Seek требует таких же затрат, как и в запросе Select. Операции Top и Compute Scalar добавляют к загрузке процессора 0.010000 каждая. Операция Table Update требует 0.010068 затрат ввода-вывода и 0.100000 загрузки процессора, а также 0.0000001 затрат на строку. Как обсуждалось ранее, план выполнения показывает те же затраты ввода-вывода, несмотря на количество вовлеченных строк или страниц. Index Seek не может гарантировать, что строки обновляются по порядку.
Ниже показаны окна детализации команды Update, использующей хинт для сканирования таблицы. Данные детализации затрат на сканирование таблицы такие же, что и у команды Select. Здесь операция Top добавляет 4.809997 затрат, хотя даже затраты ввода-вывода и загрузка процессора - такие же, что и у предыдущей операции Update. Оператор Table Update имеет такую же структуру затрат в обоих случаях.
Конечный результат формул затрат плана выполнения состоит в том, что план по умолчанию, использующий поиск по индексу, менее затратен, чем план со сканированием таблицы в 238 раз. Такой результат получается полностью из-за разницы в затратах между поиском 100 000 строк по индексу высокой плотности и табличным сканированием 101 012 страниц и 10М строк. Следующая таблица показывает действительное время выполнения команды Update и следующей за ней Checkpoint для конфигураций памяти 256M и 1154M.
|
Запрос UPDATE |
Последовательные |
Последовательные |
Разнесенные |
Разнесенные |
|
256M памяти |
Поиск по индексу |
Сканирование таблицы |
Поиск по индексу |
Сканирование таблицы |
|
Время запроса (сек) |
1.3 | 12.6 | 476.6 | 10.5 |
|
Время контрольной точки (сек) |
0.4 | 0.6 | 14.5 | 8 |
|
Строк / сек |
57,471 | 7,576 | 203 | 2,778 |
|
1154MB памяти |
Поиск по индексу |
Сканирование таблицы |
Поиск по индексу |
Сканирование таблицы |
|
Время запроса (сек) |
0.8 | 1.3 | 0.9 | 1.5 |
|
Время контрольной точки (сек) |
0.2 | 0.1 | 23 | 23 |
|
Строк / сек |
100,000 | 71,429 | 4,184 | 4,082 |
План выполнения с поиском по индексу действительно работает быстрее, когда все изменяемые строки находятся на соседних страницах, но он может работать гораздо медленнее, если изменяемые строки разнесены по многим страницам. Т.к. статистика SQL Server не может определить степень распределения изменяемых строк, то формула затрат плана выполнения не должна подразумевать, что строки находятся на смежных страницах. Нет никаких оснований ожидать, что строки из значения обычного некластерного индекса хранятся на соседних страницах. Однако SQL Server всегда выбирает план выполнения с поиском по индексу, если существует индекс на поисковом аргументе, т.к. план выполнения не учитывает затраты на получение страниц данных, также, как и операция Bookmark Lookup. Очевидно, что действительные затраты на самом деле также включают затраты на получение строки данных. Это расхождение может приводить к появлению очень медленного плана выполнения по сравнению с методом сканирования таблицы в случае отсутствия индексов.
Простое изменение условия SET в запросе UPDATE на randMoney = randMoney + 1.0 даст в результате план, показанный ниже. Теперь план выполнения по умолчанию использует сканирование таблицы, а план выполнения с индексом на поисковом аргументе использует операцию bookmark lookup.
Запросы удаления показаны ниже. Первые два запроса используют строки из последовательно расположенных страниц, а вторая пара использует строки, разнесенные по всем страницам, как это было в других примерах.
-- Последовательные строки, поиск по индексу DELETE M3C_01 WHERE SeqID = 91 GO CHECKPOINT GO -- Последовательные строки, сканирование таблицы DELETE m FROM M3C_01 m WITH(INDEX(0)) WHERE SeqID = 71 GO CHECKPOINT GO -- Разнесенные строки, поиск по индексу DELETE M3C_01 WHERE DistID = 91 GO CHECKPOINT GO -- Разнесенные строки, сканирование таблицы DELETE m FROM M3C_01 m WITH(INDEX(0)) WHERE DistID = 27 GO CHECKPOINT GO |
Без хинтов план выполнения использует поиск по индексу. Затраты ввода-вывода на чтение страницы данных и строки не учитываются планом выполнения, таким же, как план выполнения команды Update. Использование запросом сканирования таблицы порождает гораздо более затратный план. После операции Table Delete производится операция Spool, после которой производятся операции Index Delete для двух индексов этой таблицы.
Операции поиска по индексу и сканирования таблицы являются аналогами предыдущих запросов Select и Update. Операции Top в каждой из двух команд Delete также идентичны операциям в соответствующих командах Update. В отличие от Update, команда Delete не имеет операции Compute Scalar. Однако поиск по индексу требует 0.250619 затрат, Top добавляет 0.010000, получая в сумме 0.260619, Table Delete добавляет 0.110068, однако общие затраты операции Table Delete получаются равными 0.380687, т.е. в них каким-то образом попали дополнительные 0.010000. Операция Table Delete имеет фиксированную величину затрат ввода-вывода в 0.010068, независимо от количества используемых страниц и строк.
Затраты на операции Table Spool объяснить сложнее. Операции в обоих запросах показывают затраты ввода-вывода в 0.931 и загрузку процессора в 0.0188, но Spool вверху показывает общие затраты в 0.768492, а Spool внизу показывает общие затраты в 1.149179. Обе операции Spool показывают полные затраты в 1.149179.
Верхний Spool для плана выполнения команды Delete с использованием поиска по индексу.
Нижний Spool для плана выполнения команды Delete с использованием поиска по индексу.
Затраты на Table Delete и верхний Table Spool действительно добавляются к полным затратам в 1.149179, но тогда нет никакого видимого объяснения, как затраты ввода-вывода и загрузка процессора для нижнего Table Spool приводят к тем же полным затратам.
Каждый Index Delete показывает затраты ввода-вывода в 0.0100125 и загрузку процессора в 0.100000, что несколько меньше, чем затраты ввода-вывода, и равно загрузке процессора для Table Delete. Обе операции Index Delete показывают полные затраты, равные 1.2591919.
Верхний Index Delete.
Нижний Index Delete.
Последняя операция SQL - это Sequence, которая показывает нулевые затраты ввода-вывода и загрузку процессора, равную 0.200000. Общие затраты равны 2.7183836, предыдущие операции внесли 2.5183836 из общих затрат, оставляя по 1.2591919 на каждый из Index Delete.
Это не очевидно, но вполне может быть, что затраты на операции, предшествующие операциям Table Spool, делятся поровну в общих затратах каждой операции Table Spool. Поэтому значения затрат добавляются не точно, а достаточно приблизительно.
Следующая таблица показывает измеренное время на выполнение команды Delete для обоих планов выполнения и обоих поисковых аргументов. План выполнения, использующий поиск по индексу, не учитывает затраты на получение строк и страниц данных, но измеренные затраты ясно показывают, что эти затраты входят в затраты реального плана выполнения. Неясно, почему сканирование таблицы, которое удаляет последовательные строки, требует большего времени, чем сканирование таблицы, удаляющее разнесенные строки. Когда все данные находятся в памяти, план выполнения с поиском по индексу оказывается немного быстрее сканирования таблицы. Операция Checkpoint выполняется очень быстро, если были изменены только несколько страниц данных, и выполняется медленнее, если все страницы были изменены, что ожидаемо.
|
Запрос Delete |
Последовательные |
Последовательные |
Разнесенные |
Разнесенные |
|
256M памяти |
Index |
Сканирование таблицы |
Index |
Сканирование таблицы |
|
Время запроса (сек) |
4.8 | 88.52 | 282 | 41 |
|
Время контрольной точки (сек) |
8.4 | 4.52 | 8.4 | 14 |
|
Строк / сек |
7,576 | 1,075 | 340 | 1,800 |
|
1154MB памяти |
Index |
Сканирование таблицы |
Index |
Сканирование таблицы |
|
Время запроса (сек) |
4.1 | 6.4 | 4.2 | 5.3 |
|
Время контрольной точки (сек) |
3.7 | 3.9 | 28.6 | 28.6 |
|
Строк / сек |
12,821 | 9,708 | 3,048 | 2,949 |
Ниже показан план выполнения команды Delete для этой же таблицы без индексов. Затраты на выполнение операций внутри команды такие же, как у таких же операций в рассмотренных выше планах выполнения.
Ниже показаны измеренные затраты времени на выполнение запроса и команды Checkpoint для удаления 100 000 строк из таблицы с 10M строк и 101 012 страниц.
|
Запрос Delete, без индекса |
Последовательные |
Разнесенные |
|
256M памяти |
Сканирование таблицы |
Сканирование таблицы |
|
Время запроса (сек) |
11.5 | 26 |
|
Время контрольной точки (сек) |
0.1 | 4 |
|
Строк / сек |
8,621 | 3,300 |
|
1154MB памяти |
Сканирование таблицы |
Сканирование таблицы |
|
Время запроса (сек) |
1.9 | 1.5 |
|
Время контрольной точки (сек) |
0.2 | 22 |
|
Строк / сек |
47,619 | 4,255 |
Как и ожидалось, операция Delete на таблице без индексов (которая должна использовать сканирование таблицы) работает быстрее, чем операция Delete, которая использует план выполнения со сканированием таблицы и должна также удалять данные из индексов.
Однако разница в затратах между этими двумя операциями, учитывая затраты на удаление данных из индексов, небольшая, кроме необычного поведения при удалении последовательных строк, включающем сканирование таблицы с диска.
Есть мнение, что намного быстрее обновлять или удалять большое количество строк, удаляя существующие индексы, обновляя или удаляя данные из таблицы без индексов, а затем пересоздавая индексы, чем обновлять или удалять данные из таблицы с индексами. Теперь видно, что причиной такого мнения является то, что план выполнения не учитывает затраты на получение страниц данных и использует план выполнения с поиском по индексу тогда, когда план выполнения со сканированием таблицы гораздо более эффективен. Время построения двух индексов, использованных в примерах, составляет в сумме около 90 секунд (30 секунд для SeqID и 60 секунд для DistID). Команды Update и Delete на таблице с индексами с использованием хинта сканирования таблицы в большинстве случаев работают быстрее, чем удаление индексов перед командами Update или Delete и последующее пересоздание этих индексов.
На сервере с 256 мегабайтами памяти время выполнения запроса Delete несколько больше, чем время выполнения запроса Select, для последовательных строк, и примерно в 2.5 раза больше для разнесенных строк. Время выполнения команды Checkpoint очень незначительно для последовательных строк, где задействовано небольшое количество страниц (1010 страниц данных и 185 страниц индексов) и более существенно, если требуется записать на диск большое количество страниц.
Несколько важных заключений могут быть сделаны после
рассмотрения планов выполнения SQL Server и действительных
затрат на выполнение запросов для конфигурации с данными в
памяти и конфигурации с интенсивным обменом с диском.
Есть
хорошие основания полагать, что единицами измерения значений
затрат плана выполнения SQL Server являются секунды, и что эти
значения по крайней мере частично основаны на операциях с
диском. Значения затрат были зафиксированы еще во время SQL
Server 7.0 и с тех пор не обновлялись. Оптимизатор запросов
SQL Server недооценивает скорость последовательной передачи
данных современных дисков по сравнению с произвольным дисковым
вводом-выводом. Когда данные находятся в памяти, затраты
Bookmark Lookup на строку меньше, чем затраты сканирования
таблицы на страницу. Время выполнения запроса будет сильно
зависеть от того, находятся ли данные в памяти или необходимы
дисковые операции. Для операций с интенсивной работой с диском
также важна производительность диска при последовательных и
произвольных операциях ввода-вывода. Современные технологии
позволяют диску с частотой вращения 7200RPM иметь такую же
производительность при последовательной передаче данных, как у
диска с частотой вращения 15K, но однако вдвое меньшую
производительность при произвольных операциях
ввода-вывода.
В принципе, можно получить намного лучший
план выполнения в определенных случаях, если можно определить,
что данные уже находятся в памяти. Даже менее аккуратная
оценка с использованием Buffer Cache Hit Ratio и оценка
размера данных, относящаяся к Target Server Memory, должны
давать лучший план выполнения, чем жестко фиксированные
оценки, основанные только на системной памяти.
Наиболее
важным заключением является то, что операции SQL Server
Insert, Update и Delete могут неправильно рассчитывать полные
затраты на операции ввода-вывода. Это может приводить к тому,
что команды Update и Delete начинают использовать план
выполнения с поиском по индексу в случаях, когда он гораздо
менее эффективен, чем план выполнения со сканированием
таблицы. Необязательно удалять индексы перед выполнением
массивных удалений и затем пересоздавать их. Простое указание
хинта сканирования таблицы является лучшим решением во многих
случаях.