Решение проблемы скачкообразного увеличения ID в SQL Server

Быстрый ответ

Если вы заметили пропуски в значениях идентификаторов в SQL Server, вполне вероятно, что причина в опции IDENTITY_CACHE. Пропуски в распределении инкрементных идентификаторов возникают при каждом перезагрузке сервера. Для их предотвращения в SQL Server 2017 и более новых версиях можно отключить кэширование идентификаторов:

ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF;

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

Для более глубокого понимания проблемы и возможных способов её решения рекомендуем ознакомиться со всей статьей.

Подробнее о кэшировании – понимание механизма кэша идентификаторов

Вы, возможно, слышали о том, что белки накапливают запасы орехов на зиму? Аналогичным образом SQL Server сохраняет значения идентификаторов пачками. Тем не менее, после перезагрузки или сбоя сервера сохранённые значения исчезают, что и вызывает пропуски в значениях.

Вопрос размера – размер кэша и версии серверов

• В старых версиях SQL Server, например, в SQL Server 2012, действовало схожее с экономией белки правило: на столбец с инкрементными идентификаторами кэшировалось 1000 значений.
• В новых версиях сервера, начиная с SQL Server 2017, размер кэша идентификаторов стал настраиваемым с помощью команды ALTER DATABASE SCOPED CONFIGURATION.

Подробнее об этом расскажет наш спикер на видео

След утечек – трассировочные флаги и последовательности

• Применение трассировочного флага 272 позволяет лучше понять механизм распределения идентификаторов. Эта функция помогает объяснить происхождение скачков после перезапуска сервера.

Использование последовательностей – альтернативные стратегии для непрерывного увеличения

В поиске решения – последовательности как спасение

Беспокоитесь из-за проблем с инкрементами? Генератор последовательностей при включённой опции NOCACHE может стать вашим спасением.

Альтернативные подходы: попытки исправить ситуацию

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

• DBCC CHECKIDENT позволяет корректировать значения идентификаторов после перезапуска сервера.
• Создайте хранимую процедуру, автоматически корректирующую идентификаторы при старте сервера.

Но помните: бережное применение указанных методов предотвращает вызов дополнительных проблем с индексацией и производительностью.

Дежавю: какие трудности возникают в устаревших системах

Пользуетесь SQL Server 2008R2 и столкнулись с похожей проблемой? К сожалению, в данном случае трассировочный флаг 272 не окажет помощи. Здесь можно попробовать:

• Вручную переустанавливать значения после перезапуска сервера.
• Использовать стратегии на уровне приложения для обхода проблемы с пропусками в идентификаторах.

Визуализация

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

Обычное поведение:

🪜 [ 1, 2, 3, 4, ... ]

Нежелательные пропуски:

🪜 [ 1, 2, ... , ❓ 1001, 1002, ... ] // А вот и пропуски!

Загадочное поведение:

• SQL Server резервирует ID группами, и при перезапуске некоторые из резервированных значений могут быть потеряны.

Перезапуск 🔄 = Пропавшие ступени [3, 4, ... 1000]
Результат   = 🪜 [ 1, 2, ❌, 1001, 1002, ... ]

Практические вопросы – реальные последствия скачков

Когда пропуски становятся проблемой

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

Балансировка помежду быстродействием и надёжностью

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

Безопасность данных и порядок работы

Управление идентификаторами может казаться простым решением, однако необходимо помнить о вопросах безопасности данных и возможных конфликтах при многопользовательском использовании базы данных.

Диагностика проблем – методы устранения неполадок

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