В SQL Server 2012 была реализована поддержка объекта последовательности в качестве альтернативы свойству столбца идентификаторов при формировании суррогатного ключа. Недавно я узнал несколько удивительных вещей об особенностях кэширования последовательностей и идентификаторов; эти открытия послужили основой для новых рекомендаций и практических приемов, о которых пойдет речь в данной статье.
Обратите внимание, что приведенная здесь информация получена на тестах, проведенных на моем компьютере с SQL Server 2014 RTM и SQL Server 2012 SP2. Компания Microsoft может без предупреждения изменить принимаемые по умолчанию значения кэша, поэтому обязательно проведите собственное тестирование, прежде чем принимать решения об оптимальном размере кэша в своей среде.
Свойство CACHE последовательности
Свойство CACHE объекта последовательности определяет, насколько часто программе SQL Server необходимо записывать восстанавливаемое значение последовательности на диск. Например, вы создаете последовательность со значениями MINVALUE 1 и CACHE 50 (недокументированное значение по умолчанию). В ответ на запрос первого значения SQL Server записывает на диск восстанавливаемое значение 50 и хранит в памяти два члена, зависящие от размера последовательности — в одном хранится текущее (current) значение (1), а в другом число оставшихся (remaining) значений (49) до следующей записи на диск, связанной с кэшем. После первых 50 запросов члены памяти содержат значения current: 50 и remaining: 0. Следующий запрос приведет к записи на диск восстанавливаемого значения 100 и записи в память значений current: 51 и remaining: 49.
В случае чистого завершения SQL Server с помощью инструкции SHUTDOWN или остановки службы SQL Server из среды SQL Server Management Studio (SSMS), приложения Services или диспетчера настроек SQL Server, текущее значение записывается на диск. Но если процесс завершается не чисто, например при сбое питания, текущее значение не будет записано на диск, а процесс восстановления прочитает значение с диска. В этом случае может появиться пробел величиной до значения свойства кэша.
Не все понимают, что даже в отсутствие кэша ни идентификатор, ни последовательность не гарантируют, что удастся избежать пробелов между значениями. Получая запрос на новое значение, SQL Server блокирует внутренний ресурс, только чтобы увеличить значение, но затем немедленно снимает блокировку, чтобы позволить другим получить новые значения. SQL Server не ожидает, пока завершится транзакция, содержащая запрос. Поэтому в случае отмены транзакции изменение значения идентификатора или последовательности не отменяется. Следующий запрос значения поступит после пробела.
При использовании идентификатора или последовательности, независимо от значения кэша, приходится мириться с возможностью появления пробелов. Если это неприемлемо, то следует использовать альтернативное решение для создания ключей. Поэтому для определения подходящего размера кэша для конкретной среды необходимо задать себе вопрос: сколько значений вы готовы потерять единовременно в случае нечистого завершения процесса. Чем больше значение, тем лучше рабочие характеристики. Далее в этой статье будут представлены результаты сравнительного тестирования для разных значений кэша.
Значения кэша по умолчанию
Компания Microsoft не публикует значения кэша, используемые по умолчанию для последовательности и идентификатора, так как предпочитает иметь возможность изменять их без предупреждения по собственному усмотрению. В главе электронной документации по SQL Server, относящейся к команде CREATE SEQUENCE (msdn.microsoft.com/en-us/library/ff878091.aspx), содержится следующее замечание относительно значения кэша последовательности по умолчанию:
«Если кэш включен без указания размера кэша, то размер выбирается компонентом Database Engine». Однако пользователям не следует полагать, что этот выбор будет постоянным. Компания Microsoft может изменить метод расчета размера кэша без предупреждения.
Хотя значения кэша по умолчанию официально не опубликованы, их можно определить с помощью простого теста. Я продемонстрирую тест с типом INT, а затем приведу результаты, полученные мною для других типов.
Первый шаг теста — создать последовательность с кэшем по умолчанию и таблицу со столбцом идентификаторов в пользовательской базе данных (testdb), выполнив следующий программный код:
IF DB_ID(N'testdb') IS NULL CREATE DATABASE testdb; USE testdb; IF OBJECT_ID(N'dbo.Seq1', N'SO') IS NOT NULL DROP SEQUENCE dbo.Seq1; IF OBJECT_ID(N'dbo.T1', N'U') IS NOT NULL DROP TABLE dbo.T1; CREATE SEQUENCE dbo.Seq1 AS INT MINVALUE 1; CREATE TABLE dbo.T1 (keycol INT IDENTITY);
Следующий шаг — запросить пару значений, выполнив приведенный ниже программный код:
SELECT NEXT VALUE FOR dbo.Seq1; GO 2 INSERT INTO dbo.T1 DEFAULT VALUES; GO 2
Затем выполните следующий программный код, чтобы запросить значения последовательности и идентификатора:
SELECT current_value FROM sys.Sequences WHERE object_id = OBJECT_ID(N'dbo.Seq1'); SELECT IDENT_CURRENT(N'dbo.T1');
Вы получите 2 в качестве текущих значений как последовательности, так и идентификатора.
Чтобы вызвать нечистое завершение процесса SQL Server, откройте диспетчер задач (Ctrl+Shift+Esc), щелкните правой кнопкой мыши процесс SQL Server и выберите действие End task («Завершить задачу»).
Затем перезапустите процесс SQL Server (из SSMS, приложения Services или диспетчера настроек SQL Server) и вновь запросите текущие значения последовательности и идентификатора:
USE testdb; SELECT current_value FROM sys.Sequences WHERE object_id = OBJECT_ID(N'dbo.Seq1'); SELECT IDENT_CURRENT(N'dbo.T1');
Я получил текущие значения 50 для последовательности и 1001 для идентификатора. Значения по умолчанию для типа INT для последовательности — 50, а для идентификатора — 1000. Причина того, что текущее значение для идентификатора после перезапуска 1001, а не 1000, заключается в том, что первая запись в кэш происходит после второго запроса, тогда как для последовательности она происходит после первого запроса.
Аналогичный тест был выполнен для проверки значений по умолчанию для других типов. Результаты показаны на рисунке 1.
.jpg) |
| Рисунок 1. Значения кэша последовательности и?идентификатора |
Как мы видим, последовательность имеет значение 50 для всех типов, но примечательно, что идентификатор имеет различные значения по умолчанию в зависимости от типа.
Выполнение аналогичного теста с идентификатором в версиях SQL Server, предшествующих SQL Server 2012, показывает, что в этих версиях идентификатор не имеет кэша. Это объясняет, почему в SQL Server 2012 и более новых версиях стал появляться пробел 1000 после нечистого завершения процесса или переключения на другой ресурс в группе обеспечения доступности SQL Always On. Некоторые специалисты считают такое поведение ошибкой (см. публикацию «Failover or Restart Results in Reseed of Identity» на сайте Microsoft Connect (connect.microsoft.com/SQLServer/feedback/details/739013/failover-or-restart-results-in-reseed-of-identity)).
Однако на практике, как объяснялось выше, пробелы возможны даже без кэша. Тем не менее, специалисты Microsoft ввели флаг трассировки 272, чтобы дать возможность вернуться к поведению, свойственному версиям, предшествующим SQL Server 2012, в которых идентификатор не использует кэша. Но помните, что отключение кэша негативно отразится на производительности, как будет показано ниже.
Производительность и значения кэша
Производительность при создании суррогатных ключей с помощью идентификаторов и последовательностей может зависеть от нескольких факторов, в том числе от того, созданы значения в tempdb или пользовательской базе данных, от того, какой из двух элементов используется, и от значения кэша. Я выполнил тест производительности, чтобы определить влияние различных факторов и сравнить последовательность с идентификатором. Для подготовки среды к тестированию был использован программный код, приведенный в листинге 1.
В процессе подготовки создаются пользовательская база данных с именем testdb, вспомогательная функция с именем GetNums, которая возвращает последовательность целых чисел в запрошенном диапазоне, и последовательности как в пользовательской базе данных, так и в tempdb.
Затем я воспользовался программным кодом листинга 2, чтобы выполнить собственно тест производительности.
Программный код измеряет производительность при заполнении таблицы с 10 000 000 строк, без последовательности или идентификатора, с последовательностью, с идентификатором, как в пользовательской базе данных, так и в tempdb, с различными значениями кэша. Определяется объем операций с журналом (число записей и общий размер) путем обращения к функции fn_dblog до и после вставки.
Измерения и вставка выполняются в одной транзакции, чтобы избежать саморециркуляции журнала. Программный код также определяет число очисток журнала, вызванных вставками, запрашивая счетчик производительности Log Flushes/sec из представления sys.dm_os_performance_counters. Хотя судя по имени счетчика, он показывает число очисток в секунду, в действительности это просто общее число очисток, выполненных на данный момент. На рисунке 2 показаны результаты теста производительности на моем компьютере.
.jpg) |
| Рисунок 2. Результаты теста производительности |
Большинство столбцов не нуждаются в дополнительных пояснениях. Столбец normduration содержит длительность в мс за вычетом длительности вставки без формирования значений последовательности или идентификатора. Эта мера дает представление о времени, которое необходимо для создания 10 000 000 значений последовательности или идентификатора без учета времени собственно вставки.
Обратите внимание на несколько очень интересных особенностей. Производительность идентификатора и последовательности в tempdb весьма похожа при использовании одинаковых значений кэша. Кроме того, в tempdb производительность гораздо лучше, чем в пользовательской базе данных, особенно при малых значениях кэша. Дело в том, что в tempdb не нужно вносить в журнал относящиеся к кэшу операции записи на диск, поскольку после перезапуска ничего восстанавливать не нужно. В пользовательской базе данных необходимо делать запись в журнал для каждой относящейся к кэшу операции записи на диск.
Имеется любопытное различие в производительности между последовательностью и идентификатором в пользовательской базе данных при отсутствии кэша и при малых его значениях. Например, без кэша требуется менее двух минут для формирования 10 000 000 значений идентификатора, но почти час для создания такого же числа значений последовательности. И это несмотря на то, что объем записей в журнал одинаков.
Причина такого различия должна быть связана со способом очистки буфера журнала в двух случаях. Если при использовании идентификатора происходит сбой, то проблем после восстановления не возникает, если повторно используется значение, которое было вставлено, но не зафиксировано. Поэтому нет причин очищать буфер журнала после связанной с кэшем операции записи. Буфер журнала очищается после заполнения.
При использовании последовательности ситуация иная. Клиентское приложение может запросить новое значение последовательности и уже использовать его, даже если транзакция не зафиксирована. Если происходит сбой, не существует способа запросить новое значение последовательности от клиента. Поэтому SQL Server очищает буфер журнала после каждой операции записи на диск, связанной с кэшем. При использовании последовательности без кэша в пользовательской базе данных (см. рисунок 2) производится столько же (или больше) очисток журнала, сколько создано значений последовательности, тогда как идентификатор без кэша приносит гораздо меньше очисток.
Тестирование перед использованием
Если нужно где-то сохранить промежуточные данные и создать суррогатные ключи для строк, то везде, где только можно, задействуйте tempdb. Результаты при использовании как последовательности, так и идентификатора гораздо лучше в tempdb, чем в пользовательской базе данных. Нет операций внесения в журнал или очисток журнала, связанных с записью кэша на диск. Но помните, что даже в tempdb производительность при использовании идентификатора и последовательности снижается по мере уменьшения значения кэша. Поэтому, используя последовательность, необходимо указать большое значение кэша, такое как 10 000. В случае с идентификатором следует избегать установки флага трассировки 272. Это верно вообще, не только в tempdb. Помните, что в любом случае ни идентификатор, ни последовательность не гарантируют отсутствия пробелов между значениями.
В пользовательской базе данных, если нужно получить аналогичную производительность при преобразовании из идентификатора в последовательность, следует компенсировать принудительные очистки журнала. Это означает, что для последовательности необходимо использовать значение кэша, которое превышает значение, применяемое идентификатором для такого же типа. Например, если тип — INT, идентификатор использует значение кэша 1000; поэтому, чтобы получить аналогичную производительность при использовании последовательности, нужно большее значение, такое как 5000. В пользовательской базе данных следует избегать использования последовательности с NO CACHE или очень низкими значениями кэша, так как это приводит к низкой производительности из-за частых очисток журнала.
Информация, приведенная в этой статье, как уже говорилось, получена во время тестирования на моем компьютере с SQL Server 2014 RTM и SQL Server 2012 SP2. Компания Microsoft может изменить значения кэша по умолчанию без предупреждения, поэтому следует провести собственное тестирование, прежде чем принимать решение об оптимальном размере кэша для конкретной среды.
Листинг 1. Подготовка к тесту производительности
— Создание базы данных testdb IF DB_ID(N'testdb') IS NULL CREATE DATABASE testdb; ALTER DATABASE testdb SET RECOVERY SIMPLE; — Создание последовательности в testdb USE testdb; IF OBJECT_ID(N'dbo.Seq1', N'SO') IS NOT NULL DROP SEQUENCE dbo.Seq1; CREATE SEQUENCE dbo.Seq1 AS INT MINVALUE 1; — Создание вспомогательной функции GetNums в testdb IF OBJECT_ID(N'dbo.GetNums', N'IF') IS NOT NULL DROP FUNCTION dbo.GetNums; GO CREATE FUNCTION dbo.GetNums(@low AS BIGINT, @high AS BIGINT) RETURNS TABLE AS RETURN WITH L0 AS (SELECT c FROM (SELECT 1 UNION ALL SELECT 1) AS D(c)), L1 AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B), L2 AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B), L3 AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B), L4 AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B), L5 AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B), Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum FROM L5) SELECT TOP(@high — @low + 1) @low + rownum — 1 AS n FROM Nums ORDER BY rownum; GO — Создание последовательности в tempdb USE tempdb; IF OBJECT_ID(N'dbo.Seq1', N'SO') IS NOT NULL DROP SEQUENCE dbo.Seq1; CREATE SEQUENCE dbo.Seq1 AS INT MINVALUE 1; GO
Листинг 2. Тест производительности
— Для включения TF 272: DBCC TRACEON(272, -1), для отключения: DBCC TRACEOFF(272, -1) SET NOCOUNT ON; --USE tempdb; — для тестирования в tempdb USE testdb; — для тестирования в пользовательской базе данных testdb DECLARE @numrecords AS INT, @sizemb AS NUMERIC(12, 2), @logflushes AS INT, @starttime AS DATETIME2, @endtime AS DATETIME2; CHECKPOINT; BEGIN TRAN ALTER SEQUENCE dbo.Seq1 CACHE 50; — попытка с NO CACHE, 10, 50, 1000, 5000, 10000 IF OBJECT_ID(N'dbo.T', N'U') IS NOT NULL DROP TABLE dbo.T; — Stats before SELECT @numrecords = COUNT(*), @sizemb = SUM(CAST([Log Record Length] AS BIGINT)) / 1048576., @logflushes = (SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Log Flushes/sec' AND instance_name = 'testdb' — для тестирования в testdb -- AND instance_name = 'tempdb' — для тестирования в tempdb ) FROM sys.fn_dblog(null, null); SET @starttime = SYSDATETIME(); — Собственно выполнение SELECT CAST(n AS INT) AS n – тестирование без последовательности или идентификатора -- NEXT VALUE FOR dbo.Seq1 AS n — тестирование последовательности -- IDENTITY(INT, 1, 1) AS n — тестирование идентификатора INTO dbo.T FROM testdb.dbo.GetNums(1, 10000000) AS N OPTION(MAXDOP 1); — Последующая статистика SET @endtime = SYSDATETIME(); SELECT COUNT(*) — @numrecords AS numrecords, SUM(CAST([Log Record Length] AS BIGINT)) / 1048576. — @sizemb AS sizemb, (SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Log Flushes/sec' AND instance_name = 'testdb' — to test in testdb -- AND instance_name = 'tempdb' — to test in tempdb ) — @logflushes AS logflushes, DATEDIFF(ms, @starttime, @endtime) AS durationms FROM sys.fn_dblog(null, null); COMMIT TRAN CHECKPOINT;