Заметки: MS SQL

Про ошибку в названии заметки я уже как-то писал. Напомню суть: платформа не переваривает даты позже 3999 года (на практике начинает стрелять с 3999-12-01). Теоретически такие даты вообще нельзя записать, но если очень хочется из-за ошибок в прикладном коде (и в коде самой платформы) это всё-таки возможно и время от времени случается.

Обычные симптомы — перестают работать какие-то отчёты, не проводятся какие-то документы, падает пересчёт итогов. В общем, страдает любой код, который трогает записи с проблемными датами.

Способ лечения, который я рамочно описал в заметке по ссылке выше — рабочий, но сравнительно медленный: нужно настроить ТЖ, собрать и распарсить результаты. Между тем, платформа падает на первом же обращении к битой дате, а их может быть много и валяться они могут в разных таблицах. То есть может потребоваться несколько подходов к снаряду: проверили, получили ошибку, исправили, проверили, получили следующую...

Желая решать проблему как-нибудь побыстрее, несколько лет назад я написал запрос для PostgreSQL. Общая идея:

1. Ищем в базе данных все поля с датами.
2. Строим мегазапрос к таким полям (ищем даты, которые выходят за лимит 1С).

То есть этот запрос генерирует другой запрос, да. Выполняем его и получаем полную картинку проблемы: список таблиц с некорректными датами. Дальше уже дело техники — смотрим, что за таблицы и решаем, как быть. В нашем случае проблемные даты иногда возникают в итогах и оборотах, так что их можно просто удалять и пересчитывать итоги через штатные инструменты.

Почему действуем на уровне СУБД? Ну, средствами 1С такой номер не отколоть — напомню, платформа падает при попытке потрогать проблемные записи. Это касается любого взаимодействия, включая чтение. Кроме того, в данном случае работать напрямую быстрее и удобнее.

На днях я переписал этот запрос для MS SQL. Вышло длиннее (в силу особенностей этой СУБД), но идея та же.

Если будете использовать, имейте в виду, что:

• Поле _Fld626 в тексте запроса — разделитель для фреша. В вашей базе может называться по-другому или вообще отсутствовать.
• Запрос написан для базы со сдвигом в 2000 лет. Если в вашей базе сдвига нет, нужно скорректировать условие (см. функцию DATEADD()).
• Трюк с выводом в XML (см. FOR XML) я добавил, чтобы не дать SSMS обрезать текст мегазапроса (показалось быстрее, чем возиться с приведением типов). Побочный эффект: в получившемся мегазапросе нужно заменить > на >, прежде чем выполнять его.

Разбирали на днях с коллегой проблему. Ничего особенно серьёзного, очередное расследование вида «какого черта этот запрос ведет себя странно?».

Упрощая, суть: читаем таблицу базы данных и кладем результат во временную таблицу. Если срабатывает определённое условие, нужно, чтобы временная таблица всё равно создавалась, но была пустой (независимо от того, есть строки в исходной таблице или нет).

Запрос был примерно такой:

SELECT
    Table.Field1 AS Field1
FROM
    Table AS Table
WHERE 
    &Parameter

Если нужно было отбирать записи из исходной таблицы во временную, в параметр передавался TRUE; если временную таблицу нужно было получить пустой — передавался FALSE.

Несмотря на кажущуюся простоту, такой трюк — проблема для производительности, если таблица, которую читает запрос — большая.

Причина в том, как СУБД работают с параметризованными запросами. И MS SQL, и PostgreSQL строят план выполнения запроса на основе его текста, и в примере выше значение параметра не повлияет на принятие решения, нужно читать таблицу или нет.

Таким образом, при выполнении такого запроса обе СУБД педантично прочитают всю таблицу (ну, или её индекс), даже если параметр равен FALSE. В последнем случае каждая прочитанная запись будет отброшена и алгоритм будет работать корректно, однако мы будем тратить ресурсы на бессмысленное чтение данных и забивать буферный кэш, замедляя систему в целом и активно работая на глобальное потепление :)

Решение тут простое — вставлять TRUE/FALSE в тело запроса как константу, не используя параметр. Либо использовать оператор TOP, так текст запроса будет даже проще:

SELECT TOP 0
    Table.Field1 AS Field1
FROM
    Table AS Table

Тут на уровне SQL мы получим что-то вроде «SELECT TOP 0 ... FROM Table» (для MS SQL) и «SELECT ... FROM Table LIMIT 0» (для PostgreSQL). В итоговом плане будет оператор чтения, но исполнитель фактически не запросит ни одной строки, так что реального сканирования данных не случится (ура).

P.S. Если не критично получать во временной таблице корректные типы колонок, можно вообще вот так:

SELECT TOP 0
    UNDEFINED AS Field1

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

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

Внешне он выглядит так: вы обновляете конфигурацию базы, и при попытке реструктуризации выскакивает ошибка "Таблица метаданных истории данных cодержит не уникальные записи, которые должны быть удалены".

При этом платформа не предлагает никакого понятного способа найти такие записи — иди туда, не знаю куда, сделай то, не знаю что.

Проблему можно решить, порывшись в базе данных. Таблица, на которую ссылается ошибка — _DataHistoryMetadata. В ней лежат версии метаданных каждого объекта, для которого ведётся история. Это позволяет платформе понимать, какие реквизиты были у объекта на любой момент времени, в течении которого для объекта велась история.

Как это работает? Ну, когда меняется состав реквизитов объекта (например, реквизит в справочнике добавили), платформа запоминает его метаданные: конкретно, добавляет в _DataHistoryMetadata новую запись и сохраняет в ней актуальный список реквизитов объекта, а также номер версии этого списка (например, в при включении истории для объекта сохраняется первая версия метаданных, при добавлении какого-нибудь реквизита — вторая и так далее).

Ещё платформа ставит в созданной записи отметку, что именно эта версия объекта — самая актуальная, после чего снимает этот флаг с той версии, которая была помечена актуальной до этого.

Так вот, проблема в том, что платформа иногда забывает сделать последний шаг и в таблице появляется сразу две версии, помеченные как актуальные. Конфигуратор понимает это, но сделать ничего не может.

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

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

1. get-issues.sql проверяет, что проблема есть: ищет версии метаданных, которые одновременно помечены как актуальные.
2. fix-issues.sql снимает признак актуальности с тех версий, которые на самом деле устарели.

Оба запроса написаны для Microsoft SQL Server. Если вы используете PostgreSQL, то вот они же для этой СУБД.

Запросы потребуют небольшой адаптации под конкретную базу: в них используется поле _fld626, в котором хранится разделитель данных. В вашей таблице _DataHistoryMetadata это поле может называться иначе, поэтому нужно обновить его имя на актуальное. Ошибиться будет трудно — у таблицы только одно поле с префиксом _fld.

P.S. Напоминаю, что лицензионное соглашение запрещает ковыряться в базе данных в обход средств платформы, так что на такие эксперименты можно идти, только если других вариантов не осталось.

Порылся в сети по поводу типов дат в MS SQL Server и в целом вопроса «почему 1С до сих пор носится со своим смещением» больше не имею. Люди пишут о целой пачке проблем с DATETIME2:

1. Недоступна базовая математика. Без дополнительных финтов ушами не выйдет посчитать разницу между двумя датами, прибавить к дате день и так далее.
2. Стандартные функции по-прежнему возвращают старый добрый DATETIME (например, DATEADD). Если данные хранятся в DATETIME2 — потребуется конвертация.
3. Поля с этим типом неважно индексируются, так как каждое значение DATETIME2 хранится задом наперед (сначала время, потом дата). В итоге СУБД промахивается с оценкой количества строк, которое может вернуть запрос, и строит для него неэффективный план выполнения.

Подробнее о всем этом можно прочитать на Towards Data Science или, например, на SQL Server Central.

В MS SQL Server поля DATETIME не могут хранить даты раньше 1753-го года. Например, если попытаться записать в базу 01.01.0001 — получим ругань на out-of-range value. Я считал это забавным для такой почтенной СУБД рудиментом, пока случайно не наткнулся на причину.

Если вкратце, в 1752-м году Великобритания внедрила у себя Григорианский календарь, и в процессе у них из летосчисления пропало одиннадцать дней. Это породило проблему: вот хочет юзер посчитать разницу в днях между 1653-м и 1753-м годом — что делать будем? Учтем потеряшек? Проигнорируем? Сделаем какие-то хинты или настройки?

Видимо, чтобы не городить неоднозначные механизмы, разработчики СУБД решили вопрос радикально — усечением доступного диапазона дат. А для тех, для кого это проблема, есть DATETIME2, который никаких ограничений не имеет.

Что касается 1С, то изначально платформа использовала DATETIME, а чтобы не иметь головной боли с хранением дат раньше 1753-го года — придумала специальный костыль. В двух словах: когда платформа пишет даты в БД, то тихой сапой прибавляет к каждой две тысячи лет, а когда читает — вычитает обратно. То есть в 1С пользователь видит 01.01.2000, а в БД на самом деле хранится 01.01.4000.

Любопытно, почему 1C до сих пор не выкинула эту штуку? Сейчас платформа использует DATETIME2 и фокус по смещением в общем-то не нужен. Конечно, тут могут быть какие-то подводные камни или просто разумная осторожность, но среди разработчиков самой СУБД сомнений не заметно:

Your great great great great great great great grandfather should upgrade to SQL Server 2008 and use the DateTime2 data type, which supports dates in the range: 0001-01-01 through 9999-12-31.

Joe Stefanelli (SQL Server developer)

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

Задержки вида PageIOLatch (я про них сегодня писал) легко встретить в любых системах, в том числе небольших. С задержками PageLatch дела обстоят наоборот — их трудно заметить, пока пользователей меньше нескольких тысяч.

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

Возможных причин две.

Горячее место в индексе

Такой расклад иногда называют хотспотом. Он возникает, когда мы массово пытаемся писать что-то на последнюю страницу индекса; в первую очередь речь идёт об индексе с монотонно возрастающим ключом — например, любые индексы по полям ссылочного типа (начиная с последних версий 8.2, платформа выдает последовательные GUID — это снижает фрагментацию диска и делает ключ индекса монотонно возрастающим).

Что такое «индекс с монотонно возрастающим ключом»? Для платформы 1С это, например, индексы регистров по периоду. Конечно, они растут не вполне монотонно — однако тут скорее важно, что данные мы всегда будем писать в конец. Сюда же относятся индексы по номеру документа и индексы по коду справочника — в обеих случаях мы выдаем некий новый номер, который пишется в конец индекса.

Решается проблема хотспота только архитектурно — нужно добиться того, чтобы данные записывались в разные места индекса, а не только в конец.

Системные страницы tempdb

Когда мы создаём или удаляем таблицу в базе данных, в ней обновляется ряд служебных страниц — IAM (Index Allocation Map), PFS (Page Free Space), GAM (Global Allocation Map), SGAM (Shared Global Allocation Map) и другие.

Почему это важно для tempdb? Для платформы 1С это база, в которой регулярно создается огромное количество таблиц. Служебные страницы при этом обновляются настолько часто, что при этом возникают задержки семейства PageLatch.

Вообще-то MS SQL Server умеет оптимизировать обновления системных страниц для tempdb, благодаря чему это делается реже, но иногда даже этого не достаточно. Особенно если в tempdb создаются таблицы с индексами — а это очень частый кейс для приложений на 1С.

У проблемы есть несколько возможных решений. Первый — если СУБД старше 2016-й, можно отключить смешанные экстенты через флаг трассировки 1118. При этом исчезнет необходимость сразу в двух служебных страницах — GAM и SGAM. Соответственно, ожиданий на их обновлении не будет. Экстент — это восемь страниц данных, т.е. 64 килобайта; если он содержит страницы одной таблицы — это нормированный экстент, если нескольких — смешанный.

Второй подход — разбивать tempdb (с 2016-й версии СУБД она, кстати, по умолчанию разбивается на восемь файлов). Дело в том, что служебные страницы ведутся в разрезе файлов; если их будет несколько — ожидания на обновлениях служебных страниц будут ниже.

Третий вариант — уменьшать количество временных таблиц с целью снизить нагрузку на tempdb. То есть переписывать наиболее частотные запросы так, чтобы они работали приемлемо быстро без временных таблиц.

Когда мы читаем страницу данных с диска в буферный кэш, она на очень короткое время блокируется (её нельзя писать, а в некоторых случаях — ещё и читать). Ожидание на завершении этого действия — и есть задержка PageIOLatch.

Причиной может быть вымывание буферного кэша — из-за небольшого объёма доступной ОЗУ или не оптимальных запросов, которые читают миллионы строк, а возвращают три. В итоге СУБД регулярно не находит нужных страниц в кэше и вынуждена снова и снова читать их с диска.

В общем, если в системе есть существенные задержки PageIOLatch — их скорее всего можно снизить, используя более быстрые диски, наращивая объём ОЗУ и оптимизируя запросы.

Для чего может пригодиться дата запуска MS SQL Server? Например, мы разбираем задержки в работе СУБД и хотим определить, в течении какого периода времени наполнялась DMV-шка sys.dm_os_wait_stats. Её данные (как и любой другой DMV, впрочем) хранятся в оперативной памяти как раз с момента запуска СУБД.

Дату запуска сервера можно получить из DMV-шки sys.dm_os_sys_info:

SELECT sqlserver_start_time FROM sys.dm_os_sys_info

Есть и другой способ, связанный с tempdb. Эта база данных создаётся при запуске сервера, и дата её создания вполне может считаться датой запуска сервера. Значение можно вытащить из sys.databases:

SELECT create_date FROM sys.databases WHERE name = 'tempdb'

Нюанс: дату запуска сервера как точку начала сбора данных DMV-шек нужно рассматривать с осторожностью. Накопленная статистика могла быть очищена вручную — например, вот так:

DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR)

Каждый раз, когда SQL-запрос может запуститься, но ожидает другого ресурса — он записывает сведения о причине задержки. Доступ к ним можно получить через представление sys.dm_os_wait_stats.

Для анализа представления можно использовать готовые скрипты:

• Скрипт из статьи Яна Стерка «Открытие скрытых данных для оптимизации производительности приложений», опубликованной в MSDN Magazine ещё в 2008-м году. Выводит список типов задержки, упорядоченный по времени — от самых частотных до наиболее редких.
• Cкрипт из статьи Пола Рэндала про анализ причин задержек в работе сервера MS SQL (на Хабре, кстати, есть перевод). Фильтрует задержки, которые возникают на сервере всегда и которые обычно можно игнорировать. Кроме того, к каждой задержке добавляется ссылка на страницу, где эта задержка детально описана (например, вот описание CXPACKET).