SQL.RU
 client/server technologies
 Главная | Документация | Статьи | Книги | Форум | Блоги | Опросы | Гостевая | Рассылка | Работа | Поиск | FAQ |

Вопрос на злобу дня - Просмотр кластеризованного индекса - Часть III

ПУБЛИКАЦИИ  

По материалам статьи Itzik Ben-Gan: Quaere Verum - Clustered Index Scans - Part III

Почему SQL Server имеет тенденцию использовать просмотр в порядке распределения, если используются подсказки NOLOCK или TABLOCK?

С подсказкой TABLOCK ответ очевиден - когда таблица полностью заблокирована, изменения в данных не разрешены, и поэтому не возможно никакое перемещение данных, что делает безопасным использование просмотра в порядке распределения, с гарантией сохранения последовательности.
Возможны случаи, когда на основании оценки стоимости SQL Server может решить установить блокировку на таблицу или индекс (rowset-блокировка) без подсказки в запросе. В таких случаях, поведение будет таким же, как и при использовании подсказки TABLOCK. Вы можете запустить трассировку, по которой можно выяснить, какие блокировки в данный момент установлены.
В случае с подсказкой NOLOCK (или при установке уровня изоляции сеанса READ UNCOMMITTED) SQL Server понимает, что нет никаких гарантий целостности данных. Имейте в виду, что, даже имея дело с "грязными данными", это не подразумевает, что будут видны незавершённые изменения (которые потом могут быть откачены) или промежуточные состояния изменений данных в транзакции. Это также означает, что в простом запросе, который просматривает все данные таблицы/индекса, SQL Server может потерять позицию просмотра, или может обработать одну и ту же строку дважды.
Я хотел бы сделать короткое отступление, прежде чем приступить к демонстрации проблем несогласованности данных.
Мои исследования на SQL Server 2000 показали, что просмотр в порядке распределения происходил тогда, когда была использована подсказка NOLOCK или TABLOCK и это не зависело от размера таблицы. Однако, в SQL Server 2005, даже с подсказками, просмотр в порядке индекса использовался до тех пор, пока размер таблицы не превышал 64 страницы; после превышения этого числа, и если была указана одна из подсказок, использовался просмотр в порядке распределения. Возможно, причины изменения этого поведения в SQL Server 2005 в том, что стоимость инициализации неупорядоченного просмотра довольно высока. Неупорядоченный просмотр может использоваться на больших индексах, где велика вероятность того, что выгода от этого превысит его стоимость. Чтобы увидеть это воочию, Вы можете изменять размер таблицы, изменяя число итераций цикла в скрипте, который использовался для создания и заполнения таблицы T1. При числе итераций равном 300, таблица занимает примерно 100 страниц. Если Вы изменяете число итераций до достаточно маленькой величины (например, до 100), размер таблицы будет меньше 64 страниц, и после этого SQL Server 2005 будет использовать просмотр в порядке индекса, даже если будут указаны подсказки TABLOCK или NOLOCK.
Далее будет продемонстрировано то, что при использовании подсказки NOLOCK можно обработать дважды одну и ту же строку. Я обновлю таблицу T1 так, что бы кластеризованный индекс по полю col1 был создан как уникальный индекс с опцией IGNORE_DUP_KEY. Это означает, что в col1 не может существовать дубликатов, а также то, что попытка вставки дубликата ключа не будет обрывать транзакцию и генерировать ошибку, генерироваться будет только предупреждение. В бесконечном цикле будем вставлять в col1 строки со случайными значениями так, чтобы цикл завершался, как только будет получен сигнал из другого сеанса. Сигналом послужит исчезновение глобальной временной таблицы с именем ##DupsNotFound, создаваемой заранее. Во втором сеансе, также в бесконечном цикле, будем делать запрос к T1 с подсказкой NOLOCK, копируя из неё данные во временную таблицу с именем #T. Будем проверять, появляется ли повторно во временной таблице какое-нибудь значение поля col1 (это означает, что одна и та строка читалась повторно в T1), и если это случится, удалим глобальную временную таблицу ##DupsNotFound. Исчезновение глобальной временной таблицы ##DupsNotFound послужит сигналом обоим сеансам, чтобы они остановили свои бесконечные циклы, поскольку в таком случае наши подозрения подтвердятся.
В одном сеансе нужно выполнить представленный ниже код, который пересоздаст таблицу T1 и начнёт вставку строк:

SET NOCOUNT ON; USE testdb; GO IF OBJECT_ID('dbo.T1', 'U') IS NOT NULL DROP TABLE dbo.T1; GO CREATE TABLE dbo.T1 ( col1 INT NOT NULL, filler CHAR(2000) NOT NULL DEFAULT('a') ); CREATE UNIQUE CLUSTERED INDEX idx_cl_col1 ON dbo.T1(col1) WITH IGNORE_DUP_KEY; GO IF OBJECT_ID('tempdb..##DupsNotFound', 'U') IS NOT NULL DROP TABLE ##DupsNotFound; GO CREATE TABLE ##DupsNotFound(col1 INT); GO WHILE OBJECT_ID('tempdb..##DupsNotFound', 'U') IS NOT NULL INSERT INTO dbo.T1(col1) SELECT 1 + ABS(CHECKSUM(NEWID()) % 1000000000);

В другом сеансе, переключившись в текстовый режим вывода, нужно выполнить показанный ниже код, который читает данные в T1 и проверяет, читались ли они повторно:

SET NOCOUNT ON; USE testdb; GO WAITFOR DELAY '00:00:05'; WHILE OBJECT_ID('tempdb..##DupsNotFound', 'U') IS NOT NULL BEGIN IF OBJECT_ID('tempdb..#T', 'U') IS NOT NULL DROP TABLE #T; SELECT col1 INTO #T FROM dbo.T1 WITH (NOLOCK); SELECT col1, COUNT(*) AS cnt FROM #T GROUP BY col1 HAVING COUNT(*) > 1; IF @@ROWCOUNT > 0 DROP TABLE ##DupsNotFound; END

Вы, возможно, догадались, что причина использования в коде 5-ти секундной задержки состоит в том, чтобы таблица T1 стала достаточно большой, и SQL Server стал выбирать просмотр в порядке распределения.
По истечении нескольких секунд у меня получилось следующее:

col1 cnt ----------- ----------- 782866256 2 783744406 2

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

[В начало]

Какие отличия между просмотром в порядке распределения и просмотром в порядке индекса?

Это зависит от уровня логической фрагментации индекса. Чем выше фрагментация, тем просмотр в порядке распределения быстрее просмотра в порядке индекса.
Чтобы это продемонстрировать, сначала нужно выполнить представленный ниже код, который изменит таблицу T1 и запишет в неё 50000 строк. Это займёт некоторое время. В сценарии происходит вставка строк (после создания индекса) со случайными значениями поля col1, находящимися в диапазоне от 1 до 1000000, что порождает высокий уровень фрагментации:

SET NOCOUNT ON; USE master; GO IF DB_ID('testdb') IS NULL CREATE DATABASE testdb; GO USE testdb; GO IF OBJECT_ID('dbo.T1', 'U') IS NOT NULL DROP TABLE dbo.T1; GO CREATE TABLE dbo.T1 ( col1 INT NOT NULL, filler CHAR(2000) NOT NULL DEFAULT('a') ); CREATE CLUSTERED INDEX idx_cl_col1 ON dbo.T1(col1); GO DECLARE @i AS INT; SET @i = 1; WHILE @i <= 50000 BEGIN INSERT INTO dbo.T1(col1) VALUES(1 + ABS(CHECKSUM(NEWID()) % 1000000)); SET @i = @i + 1; END

Проверяем уровень фрагментации:

SELECT avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats ( DB_ID('testdb'), OBJECT_ID('dbo.T1'), 1, NULL, NULL );

У автора получилось более 99% фрагментации. Включите в SSMS опцию "Discard results after execution" (Отбросить результаты после выполнения), чтобы не учитывалось время, которое требуется для генерации вывода результатов.
Выполните показанный ниже код, чтобы после очистки кэша данных получился просмотр в порядке индекса:

DBCC DROPCLEANBUFFERS; SELECT * FROM dbo.T1;

Этот код выполнился на системе автора 33 секунды. Далее, выполните изменение этого кода, используя подсказку NOLOCK, после чего просмотр должен исполняться в порядке распределения:

DBCC DROPCLEANBUFFERS; SELECT * FROM dbo.T1 WITH (NOLOCK);

Этот код выполнялся у автора 11 секунд, что в 3 раза быстрее просмотра в порядке индекса! Выполним ещё одну модификацию кода, чтобы просмотр исполнялся в порядке распределения, используя для этого подсказку TABLOCK:

DBCC DROPCLEANBUFFERS; SELECT * FROM dbo.T1 WITH (TABLOCK);

Этот код тоже выполнился 11 секунд, так же, как и с подсказкой NOLOCK. После этого, выполните представленный ниже код, который пересоздаёт индекс, вследствие чего уровень логической фрагментации будет небольшим:

ALTER INDEX idx_cl_col1 ON dbo.T1 REBUILD WITH (SORT_IN_TEMPDB = ON, MAXDOP = 1);

Повторно запустите использованные выше тестовые запросы. На компьютере автора просмотр в порядке индекса исполнялся 7 секунд (без подсказок оптимизатору), и те же 7 секунд понадобились для просмотра в порядке распределения (подсказка NOLOCK или TABLOCK).

[В начало]

Заключение и хорошая практика

Самый главный совет, который Вы вынесете после прочтения этой статьи, соответствует стандарту ANSI SQL, который говорит, что запрос без предложения ORDER BY не гарантирует возвращения данных в каком либо порядке. Если нужно гарантировать, что данные будут возвращены в требующемся порядке, нужно указать это в предложении ORDER BY. Независимо оттого, что мы знаем (или думаем, что знаем) о внутренней организации SQL Server, продемонстрированные примеры должны послужить поводом для очередного переосмысления того, что мы делаем. Могут существовать такие методы доступа, о которых мы не знаем, которые могут использоваться только в определённых обстоятельствах, а также в будущих версиях SQL Server могут появиться новые методы доступа, или даже в следующих сервисных пакетах.
Другой урок, который можно вынести из этой статьи, это то, что никогда не стоит доверять вслепую чему-то, не проверив это, включая то, что может казаться простыми и очевидными.

Теперь о том, что касается технических выводов и хорошей практики…

  • Создание кластеризованного индекс для таблицы не гарантирует, что данные в файле будут храниться в порядке ключа этого индекса. Перемещение данных, вызванное разбиением страницы, изменением значений ключа индекса и расширение динамических полей, неизбежно приведёт к логической фрагментации. При создании или пересоздании индекса существующей таблицы, SQL Server постарается сделать его непрерывным (уменьшая фрагментацию), но нет никаких гарантий полного отсутствия фрагментации.

  • Просмотр в порядке распределения данных на листовом уровне индекса возможен, если определена подсказка NOLOCK или TABLOCK (или если SQL Server решит использовать rowset - блокировку с учётом реальной стоимости операции); во всех других случаях SQL Server будет использовать просмотр в порядке индекса.

  • Стоит быть в курсе того, как осуществляется чтение данных с подсказкой NOLOCK (или чтение с уровнем изоляции read uncommitted). Это не только вопрос о "грязном" чтении, или о данных в промежуточном состоянии транзакции; это скорее вопрос о возможности получения одной и той же строки дважды, или о том, что SQL Server может потерять позицию просмотра в течение сканирования.

  • В таблице c кластеризованным индексом при возрастании уровня логической фрагментации просмотр в порядке распределения становится более эффективным, чем просмотр в порядке индекса.

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

Автор надеется, что Вы, после прочтения этой статьи, не броситесь опрометчиво делать следующие вещи:

  • Избавитесь от всех кластеризованных индексов, и будете работать только с кучами; использование кластеризованных индексов даёт много преимуществ, но это уже тема для другой статьи.

  • В дальнейшем, Вы будете везде использовать подсказку NOLOCK; помните, что NOLOCK имеет серьезные проблемы с согласованностью данных.

  • В дальнейшем, Вы будете везде использовать подсказку TABLOCK; хотя это и гарантирует непротиворечивость данных и повышение производительности просмотра, это также может привести к серьезным проблемам с блокировками.

[В начало]

Перевод: Александра Гладченко  2007г.

Rambler's Top100 Рейтинг@Mail.ru  Administrator: Обратная связь 
Copyright: SQL.Ru 2000-2013