Уровень совместимости базы данных ms sql для 1с
Изменения внутри оптимизатора могут быть при смене версии, при этом, не только главной версии, но и в рамках SP, и даже CU. Могут изменяться внутренние правила трансформации, внутренние пороги и коэффициенты стоимости, и другие недокументированные вещи. Это может приводить к разным планам и к разной производительности независимо от уровня совместимости.
Если вы хоть раз переезжали на новую версию сервера, вы сами знаете, как это бывает.
Немного отдельный разговор про 2014 версию, там уровень совместимости также управляет фундаментальным изменением – сменой версии компонента оценивающего количество строк от которого очень много что зависит. Включение новой логики может сказаться как позитивно, так и негативно.
В 2016 есть еще одна интересная особенность, касающаяся Trace Flag-ов оптимизатора, которые раньше не были выключены по умолчанию и включались отдельно при помощи TF 4199.
Поэтому, некоторые исправления включались отдельным флагом (4199) или комбинацией флагов (4199 + . ) .
Начиная с 2016 эта ситуация меняется. Все исправления сделанные до 2016 версии и включаемые ранее Tf 4199 - теперь будут включены по умолчанию (при условии compatibility level 130). А TF остается регулировать те, что будут добавлены уже в самом 2016.
Вот табличка оттуда, как это будет выглядеть:
Но в 2016 будет проще, там будет механизм Query Store (QS), при помощи которого будет осуществляться контроль и мониторинг планов.
Одна из идей использования примерно такая:
- обновились до 2016 оставили уровень совместимости предыдущего или пред-предыдущего сервера, в зависимости от. (да, есть момент, что уже после обновления планы могу отличаться, но не так драматично)
- включили QS
- включили новый уровень совместимости (130)
- мониторите, и если выявлена регрессия - смотрите новый план и старый план (который записал QS)
- дальше по желанию, например, можете форсировать старый план пока разбираетесь с проблемой, как только разберетесь оставите новый, либо по старинке при помощи TF, либо как угодно
Клиент-серверный режим работы
Что означают понятия «клиент» и «сервер»? Клиентом является запрашивающая машина (обычно ПК), сервером — машина, которая отвечает на запрос. Оба термина (клиент и сервер) могут применяться как к физическим устройствам, так и к программному обеспечению.
Важно! На видео выбрана кодировка не для 1С (видео не мое)
Обучающий ролик (внимание, анлийский язык, ролики не мои!)
Перед установкой
Для работы MS SQL Server подходят большинство современных компьютеров.
Обязательно понадобятся (они идут в дистрибутиве):
- SQL Server Performance Dashboard Reports
- SQL Server Best Practices Analyzer
Параметры установки
Важно! На видео выбрана кодировка не для 1С (видео не мое)
После установки
Оставьте для работы только протоколы TCP/IP и SHARED MEMORY
В версии Express экземпляр по умолчанию отличается от других версий, после имени компьютера нужно указывать именованный экземпляр SQLEXPRESS. А также по умолчанию выключен протокол TCP/IP, нужный для работы с 1С:Предприятие 8.
Обновите экземпляр сервера до актуального релиза
Обновите версию MS SQL Server 2005 до последней.
Для повышения быстродействия выключите проверку сертификатов
Перенесите tempdb на быстрый независимый массив/диски
ALTER DATABASE tempdb
ALTER DATABASE tempdb
Выключите антивирус на сервере СУБД
После всех настроек посмотрите на рекомендации SQL Server 2005 Best Practices Analyzer
Например данный скриншот говорит о том, что у этого сервера можно повысить производительность, правильно выполним разметку HDD и затем оптимизировав размещение баз данных.
Регламентное обслуживание
Рекомендуется также выполнять следующие регламентные операции:
Обновление статистики базы данных
Очистка процедураного кэша СУБД
(после обновления статистики)
Реиндексация базы данных
(раз в сутки в не рабочее время)
Если вручную, то
Через графический интерфейс:
Используйте анализа фрагментированности для снижения нагрузки
Для больших баз данных нужно уменьшать ненужные операции по дефрагментации тех индексов, для которых это не требуется.
Функция таблицы динамического управления sys.dm_db_index_physical_stats возвращает процент фрагментации в столбцеavg_fragmentation_in_percent. Если значение в этом столбце превышает 25%, то для восстановления исходных параметров производительности рекомендуется выполнить дефрагментацию/реиндексацию этого индекса.
Еще проще, воспользовшись отчетом:
Настройте резервное копирование
Уменьшение размера базы (лога) данных
CHECKPOINT; /*Записывает все данных из буферного кэша в текущей базе данных на диск*/
После развертывание баз данных 1С рекомендую также настроить бесплатные онлайн-сервисы контролирующие производительность и надежность системы.
В этой статье описывается просмотр и изменение уровня совместимости базы данных в SQL Server с помощью среды SQL Server Management Studio или Transact-SQL.
Перед изменением уровня совместимости базы данных проанализируйте, как это повлияет на имеющиеся приложения. Дополнительные сведения см. в разделе Уровень совместимости инструкции ALTER DATABASE (Transact-SQL).
Permissions
Необходимо разрешение ALTER на базу данных.
Использование среды SQL Server Management Studio
Просмотр или изменение уровня совместимости базы данных:
После соединения с соответствующим экземпляром Компонент SQL Server Database Engine в обозревателе объектов нажмите имя сервера.
Раскройте узел Базы данных и в зависимости от типа восстанавливаемой базы данных выберите пользовательскую базу данных или раскройте узел Системные базы данных и выберите системную базу данных.
Щелкните базу данных правой кнопкой мыши и выберите Свойства.
Откроется диалоговое окно Свойства базы данных .
На панели Выбор страницы выберите пункт Параметры.
Текущий уровень совместимости будет указан в списке Уровень совместимости .
Чтобы изменить уровень совместимости, выберите в списке другой параметр. Доступные параметры для разных версий Компонент Database Engine указаны на странице Уровень совместимости инструкции ALTER DATABASE (Transact-SQL).
Использование Transact-SQL
Просмотр уровня совместимости базы данных
Установите соединение с компонентом Компонент Database Engine.
На стандартной панели выберите пункт Создать запрос.
Скопируйте приведенный ниже пример в окно запроса и нажмите кнопку Выполнить. В этом примере возвращается уровень совместимости базы данных AdventureWorks2012 .
Изменение уровня совместимости базы данных
Установите соединение с компонентом Компонент Database Engine.
На стандартной панели выберите пункт Создать запрос.
Скопируйте приведенный ниже пример в окно запроса и нажмите кнопку Выполнить. В этом примере изменяется уровень совместимости базы данных AdventureWorks2012 на 120 , т. е. на значение уровня совместимости для SQL Server 2014 (12.x).
Сертификация на совместимость позволяет компаниям обновлять и модернизировать базу данных SQL Server в локальной среде, облаке и пограничной зоне, устраняя риски, связанные с совместимостью приложений.
Одно и то же Компонент Database Engine лежит в основе как SQL Server, так и База данных SQL Azure (включая Управляемый экземпляр). Это общее Компонент Database Engine означает, что пользовательскую базу данных можно легко перемещать между локальным сервером SQL Server и База данных SQL Azure. При этом код приложения, выполняемый в базе данных как Transact-SQL, продолжит работать так же, как в исходной системе.
Для каждого нового выпуска SQL Server уровень совместимости по умолчанию устанавливается в зависимости от версии Компонент Database Engine. Однако уровень совместимости предыдущих версий сохраняется, чтобы совместимость существующих приложений не терялась. Таблицу совместимости можно просмотреть здесь. Поэтому если приложение было сертифицировано для работы с определенной версией SQL Server, оно сертифицировано для работы на уровне совместимости по умолчанию этой версии.
Например, в SQL Server 2016 (13.x); по умолчанию использовался уровень совместимости базы данных 130. Так как уровни совместимости определяют функциональное поведение и способ оптимизации запросов Transact-SQL, база данных, сертифицированная для работы в SQL Server 2016 (13.x);, неявно сертифицирована на уровне совместимости базы данных 130. Эта база данных может работать "как есть" в более поздней версии SQL Server (например, SQL Server 2019 (15.x)) и в База данных SQL Azure при условии, что сохраняется уровень совместимости базы данных 130.
Это фундаментальный принцип модели непрерывной интеграции Microsoft База данных SQL Azure. Компонент Database Engine постоянно улучшается и обновляется в Azure, но так как для существующих баз данных сохраняется текущий уровень совместимости, они продолжают работать, как было задумано, даже после обновления базового Компонент Database Engine.
Этот же принцип используют SharePoint Server 2016 и SharePoint Server 2019 для сертификации на SQL Server и Управляемый экземпляр SQL Azure, что позволяет развертывать любые Компонент SQL Server Database Engine, которые могут использовать поддерживаемые уровни совместимости баз данных для этих версий SharePoint Server. Дополнительные сведения см. в статьях Требования к оборудованию и программному обеспечению для SharePoint Server 2016и Требования к оборудованию и программному обеспечению для SharePoint Server 2019.
Управление рисками при обновлении с помощью сертификации на совместимость
Сертификация на совместимость — эффективный подход к модернизации базы данных. Выполняя сертификацию на уровне совместимости, разработчики устанавливают технические требования для поддержки приложения в SQL Server и База данных SQL Azure, но отделяют жизненный цикл приложения от жизненного цикла платформы баз данных. Это позволяет компаниям обновлять Компонент SQL Server Database Engine в соответствии с политиками жизненного цикла, а также использовать улучшения в плане масштабируемости и производительности, не зависящие от кода, а подключающимся приложениям — сохранять свое функциональное состояние посредством обновлений.
Возможность негативного воздействия на функциональность и производительность — основной фактор риска при любом обновлении. Сертификация на совместимость позволяет уверенно управлять такими рисками.
В плане поведения Transact-SQL любое изменение означает, что приложение необходимо снова сертифицировать, чтобы подтвердить правильность его работы. Однако уровень совместимости базы данных обеспечивает обратную совместимость с более ранними версиями SQL Server только для указанной базы данных, а не всего сервера. Сохранение уровня совместимости базы данных гарантирует, что существующие запросы приложения будут работать одинаково до и после обновления Компонент Database Engine. Дополнительные сведения о поведении Transact-SQL и уровнях совместимости см. в разделе Использование уровня совместимости для обеспечения обратной совместимости.
Если говорить о производительности, то, поскольку с каждой новой версией в оптимизатор запросов вводятся усовершенствования, в разных версиях Компонент Database Engine можно было бы ожидать различий в планах запросов. Различия в планах запросов при обновлении обычно приводят к возникновению риска, если некоторые изменения могут негативно сказаться на определенном запросе или рабочей нагрузке. В свою очередь, этот риск является побуждающей причиной для повторной сертификации, из-за которой могут откладываться обновления и возникать проблемы с жизненным циклом и поддержкой. Именно в целях устранения рисков, возникающих при обновлении, улучшения оптимизатора запросов ограничиваются уровнем совместимости по умолчанию нового выпуска (другими словами, самым высоким уровнем совместимости, доступным для новой версии). Сертификация на совместимость включает в себя защиту формы плана запроса: сохранение уровня совместимости базы данных сразу после обновления Компонент Database Engine означает, что модель оптимизации запросов в новой версии остается такой же, как и до обновления, и форма плана запроса не должна изменяться. Дополнительные сведения см. в разделе Для чего нужна форма плана запроса?.
Для существующего приложения, которое уже сертифицировано для заданного уровня совместимости, обновите Компонент SQL Server Database Engine и сохраните предыдущий уровень совместимости базы данных. В этом сценарии нет необходимости в повторной сертификации приложения. Дополнительные сведения см. в разделе Уровни совместимости и обновления ядра СУБД далее в этой статье.
Если вы разрабатываете новое приложение или существующему приложению требуется использовать новые возможности, такие как интеллектуальная обработка запросов, или какие-либо новые инструкции Transact-SQL, спланируйте повышение уровня совместимости до последнего доступного в SQL Server и проведите повторную сертификацию приложения для работы на этом уровне. Дополнительные сведения о повышении уровня совместимости базы данных см. в разделе Рекомендации по обновлению уровня совместимости базы данных.
Для чего нужна форма плана запроса?
Форма плана запроса относится к визуальному представлению различных операторов, составляющих план запроса. Сюда входят такие операторы, как поиск, сканирование, соединение и сортировка, а также связи между ними, указывающие поток данных и порядок операций, которые должны быть выполнены для получения необходимого результирующего набора. Форма плана запроса определяется оптимизатором запросов.
Чтобы сохранить предсказуемую производительность запросов во время обновления, необходимо использовать одну и ту же форму плана запроса. Это можно сделать, не изменяя уровень совместимости базы данных сразу после обновления несмотря на разные версии базового экземпляра Компонент Database Engine. Если в экосистеме выполнения запросов больше ничего не изменилось (например, доступные ресурсы или механизм распространения данных в базовых данных не претерпели значительных изменений), производительность запроса должна остаться прежней.
Однако сохранение формы плана запроса — это не единственный момент, который может повлиять на производительность после обновления. При перемещении базы данных в более новый экземпляр Компонент Database Engine и внесении изменений в среду могут появиться факторы, которые немедленно затронут производительность запроса, даже если у плана запроса будет одна форма в разных версиях. Эти изменения могут включать в себя новый Компонент Database Engine с большим или меньшим объемом доступных ресурсов памяти и ЦП, изменения параметров конфигурации сервера или базы данных, а также изменения механизма распространения данных, влияющие на создание плана запроса. Поэтому важно понимать, что поддержка уровня совместимости базы данных обеспечивает защиту от изменений в форме плана запроса, но не защищает от других факторов среды (некоторые из них являются инициированными пользователем изменениями), влияющих на производительность запросов.
Преимущества сертификации на совместимость
У сертификации базы данных как подхода, основанного на совместимости, а не на конкретных версиях, есть ряд прямых преимуществ.
- Отделение сертификации приложения от платформы. Так как Компонент Database Engine является общим, приложениям, которым необходимо просто выполнять запросы Transact-SQL, не требуются отдельные процессы сертификации для Azure и локальной среды.
- Снижение рисков при обновлении вследствие того, что во время модернизации платформы баз данных циклы обновления на уровне приложений и на уровне платформы баз данных можно разделить, чтобы сократить перерывы в работе и улучшить управление изменениями.
- Обновление без изменения кода. Если при обновлении до новой версии SQL Server или База данных SQL Azure сохранить уровень совместимости исходной системы, то изменения кода не потребуются. Кроме того, не нужно будет выполнять повторную сертификацию до тех пор, пока приложению не потребуется использовать усовершенствования, доступные только на более высоком уровне совместимости базы данных.
- Улучшение управляемости и масштабируемости без внесения изменений в приложения за счет использования усовершенствований, не ограниченных определенным уровнем совместимости базы данных. В SQL Server к ним относятся, например:
- дополнительные улучшения возможностей мониторинга и устранения неполадок с помощью новых системных динамических административных представлений, расширенных событий и автоматической настройки;
- Улучшенная масштабируемость, например с помощью автоматической архитектуры Soft-NUMA, Ускоренного восстановления баз данных, или оптимизированных для памяти метаданных tempdb.
Для новых баз данных по-прежнему задается уровень совместимости по умолчанию версии Компонент Database Engine. Однако при обновлении базы данных с любой более ранней версии SQL Server до новой версии SQL Server или База данных SQL Azure она сохраняет свой существующий уровень совместимости.
Перед переносом базы данных в новую версию SQL Server или База данных SQL Azure убедитесь в том, что уровень совместимости базы данных по-прежнему поддерживается. Таблицу поддержки уровня совместимости базы данных можно просмотреть здесь.
При обновлении базы данных с уровнем совместимости ниже допустимого (например, уровнем 90, который был уровнем по умолчанию в SQL Server 2005 (9.x)) устанавливается минимальный допустимый уровень совместимости (100).
Чтобы определить текущий уровень совместимости, запросите столбец compatibility_level в представлении sys.databases.
Уровни совместимости и обновления ядра СУБД
Чтобы обновить Компонент Database Engine до последней версии с сохранением уровня совместимости базы данных, существовавшего до обновления, и ее состояния поддержки, рекомендуется выполнить статическую функциональную проверку контактной зоны кода приложения в базе данных (объекты программирования, включая хранимые процедуры, функции, триггеры и пр.) и в приложении (используя трассировку рабочей нагрузки для записи кода, отправляемого приложением).
Это легко сделать, использовав Помощник по миграции данных (Майкрософт) (DMA). Отсутствие в выходных данных DMA ошибок, связанных с отсутствием или несовместимостью функциональных возможностей, позволит избежать снижения функциональности приложения в новой конечной версии. Если требуется внести изменения, чтобы гарантировать, работоспособность базы данных в новой версии, DMA позволит точно определить, где требуется внести изменения, и какие обходные решения доступны. Дополнительные сведения см. в разделе Общие сведения о Помощнике по миграции данных.
Эта функциональная проверка особенно важна при переводе базы данных с устаревшей версии (например, SQL Server 2008 R2 или SQL Server 2012 (11.x)) на новую версию SQL Server или База данных SQL Azure, так как код приложения может использовать неподдерживаемую Transact-SQL, не защищенную уровнем совместимости базы данных. Но при переходе с более новой версии (например, SQL Server 2016 (13.x);) на SQL Server 2019 (15.x) или База данных SQL Azure, нет причин волноваться о неподдерживаемых Transact-SQL. Дополнительные сведения о неподдерживаемых Transact-SQL см. в разделе Использование уровня совместимости для обеспечения обратной совместимости.
DMA поддерживает уровень совместимости базы данных 100 и выше. SQL Server 2005 (9.x) как исходная версия не поддерживается.
Microsoft рекомендует проводить минимальное необходимое тестирование для проверки успешности обновления с сохранением прежнего уровня совместимости базы данных. Вам следует определить, что является минимальным необходимым тестированием, с учетом особенностей вашего приложения и сценария.
Microsoft обеспечивает защиту формы плана запроса в указанных ниже случаях.
- Новая (конечная) версия SQL Server работает на оборудовании, сопоставимом с оборудованием, на котором работала предыдущая (исходная) версия SQL Server.
- Один и тот же поддерживаемый уровень совместимости базы данных используется как в конечной версии SQL Server, так и в исходной версии SQL Server.
- Одна база данных и рабочая нагрузка используются в целевой системе SQL Server и в исходной системе SQL Server.
Любая регрессия формы плана запроса (по сравнению с исходной версией SQL Server) в описанных выше ситуациях требует принятия мер. Обратитесь в службу поддержки клиентов Майкрософт.
Читайте также: