Значение уровня совместимости базы данных в SQL Server
Пересказ статьи Glenn Berry. The Importance of Database Compatibility Level in SQL Server
До SQL Server 2014 уровень совместимости базы данных ваших пользовательских баз, как правило, не являлся требующим внимания аспектом, по крайней мере, с точки зрения производительности. В отличии от уровня файлов базы данных (который автоматически апгрейдится, когда вы восстанавливаете или присоединяете базу данных более низкого уровня к экземпляру, запущенному на более новой версии SQL Server, и который никогда не опускается к более низкому уровню), уровень совместимости базы данных может быть изменен до любого поддерживаемого уровня с помощью простой команды
ALTER DATABASE SET COMPATIBILITY LEVEL = xxx
Вы не привязаны к какому-то конкретному поддерживаемому уровню совместимости и можете изменить его по вашему желанию на любой другой из поддерживаемых. Во многих случаях большинство пользовательских баз данных никогда не изменяют свой уровень совместимости после перехода к новой версии SQL Server. Это обычно не вызывает никаких проблем до тех пор, пока вам не понадобятся новые возможности, доступные на последнем доступном уровне совместимости базы данных.
Для SQL Server 2012 и ниже уровень совместимости базы данных, главным образом, использовался для контроля над тем, доступны или нет новые возможности, введенные в конкретной версии SQL Server, и доступны или нет не поддерживаемые уже старые возможности. Уровень совместимости базы данных также использовался как метод поддержания обратной совместимости приложений со старыми версиями SQL Server. Если у вас не было времени, чтобы выполнить полное тестирование регрессии на новейшем уровне совместимости, вы могли просто использовать прежний уровень совместимости до тех пор, пока выполните тестирование и, при необходимости, модификацию ваших приложений.
В таблице 1 показаны основные версии SQL Server и их уровни совместимости, поддерживаемые и принимаемые по умолчанию.
Таблица 1: Версии SQL Server и поддерживаемые ими уровни совместимости
Когда вы создаете новую пользовательскую базу данных в SQL Server, уровень совместимости базы данных будет установлен в уровень совместимости по умолчанию для этой версии SQL Server. Так, например, новая пользовательская база данных, которая создается в SQL Server 2017 получит уровень совместимости базы данных 140. Исключение возникает, если вы измените уровень совместимости системной базы данных model на другой поддерживаемый уровень, тогда новая пользовательская база данных будет наследовать этот уровень совместимости от базы данных model.
Если вы восстанавливаете полный бэкап базы данных, который был сделан на более старой версии SQL Server, на экземпляре, который запущен на более новой версии SQL Server, то уровень совместимости базы данных останется прежним, каким он был на старой версии SQL Server, если уровень совместимости старой базы данных не ниже, чем минимальный поддерживаемый уровень совместимости новой версии SQL Server.
Например, если вы восстанавливаете бэкап базы данных с SQL Server 2005 на экземпляре SQL Server 2017, уровень совместимости для восстановленной базы данных будет установлен в 100. Вы получите то же самое поведение, если отсоедините базу данных с более старой версии SQL Server, а затем присоедините её к более новой версии SQL Server.
Такое поведение не ново, но кое-что новое и важное может еще произойти, когда вы переводите пользовательскую базу данных на уровень совместимости 120 или новее. Эти дополнительные изменения, которые могут оказать существенное влияние на производительность, видимо, недостаточно известны и поняты широкому сообществу пользователей SQL Server. Я по-прежнему встречаю многих профессионалов в области баз данных и организации, выполняющих то, что я называю "апгрейд вслепую". Это когда они переходят с SQL Server 2012 или более ранних версий на SQL Server 2014 или новее (особенно SQL Server 2016 и SQL Server 2017), не выполняя сколь-нибудь серьезного тестирования падения производительности, чтобы выяснить, как их рабочая нагрузка будет вести себя на новом естественном уровне совместимости, и доступны ли дополнительные конфигурационные параметры, способные оказать положительный эффект.
Здесь был введен новый оценщик кардинального числа (CE), т.е. изменилась оценка числа строк. Во многих случаях большинство ваших запросов стало выполняться быстрей при использовании нового оценщика, но зачастую имелось небольшое число запросов, которые демонстрировали существенное падение производительности с новым оценщиком кардинального числа. Использование уровня совместимости базы данных 120 означает, что вы будете использовать "новый" CE до тех пор, пока не установите флажок трассировки уровня экземпляра или не будете использовать хинт в запросе, чтобы отменить его действие.
Joe Sack еще в апреле 2014 года написал классическую работу “Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator” (Оптимизация ваших планов запросов с оценщиком кардинального числа SQL Server 2014), которая объясняет основание и поведение сделанных изменений. Если вы наблюдаете падение производительности некоторых запросов с новым СЕ, SQL Server имел немного вариантов, чтобы устранить проблемы с производительностью, вызванные новым СЕ. Joe подробно описал эти возможности, которые, по существу, сводились к флажкам трассировки или хинтам в запросах для контроля над тем, какой оценщик кардинального числа применяется оптимизатором запросов. Или же вы решали вернуться обратно к уровню совместимости базы данных 110 и ниже.
Я использую "новый" СЕ в кавычках потому, что теперь нет единственного "нового" СЕ. Каждая следующая версия SQL Server, начиная с SQL Server 2014, имеет свой СЕ, и изменения оптимизатора запросов привязаны к уровню совместимости базы данных. Новая более точная терминология, соответствующая SQL Server 2016 и далее, использует СЕ120 для уровня совместимости 120, СЕ130 - для уровня совместимости 130, СЕ140 - для уровня совместимости 140, и СЕ150 - для уровня совместимости 150.
Если вы имеете SQL Server 2016 или новее, использование уровня совместимости базы данных 130 будет применять СЕ130 по умолчанию, и будут доступны и все другие изменения, касающиеся производительности. Эффекты глобальных флажков трассировки 1117, 1118 и 2371 будут действовать при уровне совместимости базы данных 130. Вы также получите эффект глобального флажка трассировки 4199 для всех заплаток (hotfix), которые были поставлены до SQL Server 2016 RTM.
SQL Server 2016 также ввел конфигурационные опции уровня базы данных, которые дают вам возможность контролировать некоторое поведение и которые ранее конфигурировались на уровне экземпляра сервера, с помощью команды ALTER DATABASE SCOPED CONFIGURATION. Двумя наиболее уместными для темы настоящей статьи конфигурационными опциями уровня базы данных являются ESTIMATION и QUERY_OPTIMIZER_HOTFIXES.
LEGACY_CARDINALITY ESTIMATION включает унаследованный СЕ (СЕ70) вне зависимости от установки уровня совместимости базы данных. Это эквивалентно флажку трассировки 9481, но влияет только на соответствующую базу данных, а не на весь экземпляр. Это позволяет вам установить уровень совместимости базы данных в 130 для того, чтобы получить другие функциональные возможности и выгоды с точки зрения производительности, и при этом использовать унаследованный СЕ уровня базы данных (если он не переписывается хинтом в запросе).
Опция QUERY_OPTIMIZER_HOTFIXES эквивалентна флажку трассировки 4199 на уровне базы данных. SQL Server 2016 делает доступными все заплатки оптимизатора запросов, поставленные до SQL Server 2016 RTM, когда вы используете уровень совместимости базы данных 130 (без установки флажка трассировки 4199). Если вы устанавливаете флажок 4199 или включаете QUERY_OPTIMIZER_HOTFIXES, вы также получите все заплатки оптимизатора запросов, которые были выпущены после SQL Server 2016 RTM.
SQL Server 2016 SP1 также ввел хинты запроса USE HINT, которые легче использовать и понять, чем устаревшие хинты запроса QUERYTRACEON, которые могли использоваться в SQL Server 2014 и ранее. Это дает вам даже более тонкое управление поведением оптимизатора, чем связанное с используемыми уровнем совместимости и версией оценщика кардинального числа. Вы можете выполнить запрос к sys.dm_exec_valid_use_hints, чтобы получить список доступных названий в USE HINT.
Если у вас установлен SQL Server 2017 или новее, использование уровня совместимости базы данных 140 будет применять СЕ140 по умолчанию. Вы также получаете все другие связанные с производительностью изменения от 130 плюс новые. SQL Server 2017 ввел новые возможности адаптивной обработки запросов, и они доступны по умолчанию, когда уровень совместимости базы данных установлен в 140. Они включают обратную связь по выделению памяти в пакетном режиме (batch mode memory grant feedback), адаптивные соединения в пакетном режиме (batch mode adaptive joins) и чередующееся выполнение (interleaved execution).
Если вы имеете SQL Server 2019 или новее, использование уровня совместимости базы данных 150 будет применять СЕ150 по умолчанию. Вы также получаете все остальные изменения, связанные с производительностью, от 130 и 140 плюс новые, введенные в этой версии. SQL Server 2019 добавляет даже больше улучшений производительности и изменений поведения, чем доступно по умолчанию, когда база данных использует уровень совместимости 150. Главным примером является встраивание скалярных функций пользователя (scalar UDF inlining), который автоматически встраивает много скалярных UDF в ваших пользовательских базах данных. Это может быть одним из наиболее важных улучшений производительности при некоторых рабочих нагрузках.
Другим примером является интеллектуальная обработка запросов (intelligent query processing), которая является подмножеством адаптивной обработки запросов в SQL Server 2017. Новые возможности включают отложенную компиляцию табличных переменных (table variable deferred compilation), неточную обработку запросов (approximate query processing) и пакетный режим для построчного хранения (batch mode on rowstore).
Имеется также 16 новых конфигурационных параметров уровня базы данных (в CTP 2.2), которые дают вам больше возможностей управления базами данных, чем это предоставлялось флажками трассировки или уровнем совместимости базы данных. Это позволяет осуществлять более тонкий контроль высокоуровневых изменений, чем поведение по умолчанию при уровне совместимости базы данных 150.
Переход на современную версию SQL Server (т.е. SQL Server 2016 или старше) существенно более сложный процесс, чем это было со старыми версиями SQL Server. Поскольку изменения связаны с различными уровнями совместимости базы данных и различными версиями оценщика кардинального числа, действительно очень важно обдумать, спланировать и протестировать тот уровень совместимости, который вы хотите использовать на новой версии SQL Server, на которую вы переносите ваши базы данных.
Майкрософт рекомендует процесс апгрейда на новейшую версию SQL Server с сохранением уровня совместимости переносимых баз данных. Затем включить Query Store на каждой базе данных и собрать показательные данные при рабочей нагрузке. Потом установить уровень совместимости последней версии и использовать Query Store, чтобы зафиксировать падение производительности, принудительно используя последний заведомо хороший план.
Если вы хотите избежать бессистемной "слепой" миграции, когда вы находитесь в блаженном неведении того, как ваша рабочая нагрузка будет реагировать на эти изменения. Изменение уровня совместимости базы данных на подходящую версию и использование подходящих конфигурационных параметров уровня базы данных, наряду с абсолютно необходимыми соответствующими хинтами запросов, исключительно важно для современных версий SQL Server.
Другим вопросом, заслуживающим внимания, является то, что Майкрософт начинает продвигать идею, что вы должны думать о тестировании ваших баз данных и приложений на конкретном уровне совместимости базы данных, а не на конкретной версии SQL Server. Майкрософт обеспечивает защиту формы плана запроса, когда новая версия SQL Server запускается на железе, которое совместимо с железом, на котором была запущена предыдущая версия (исходная) SQL Server, и один и тот же поддерживаемый уровень совместимости базы данных используется на обеих версиях.
Основная идея заключается в том, что если вы протестировали ваши приложения на конкретном уровне совместимости, например, 130, вы получите то же самое поведение и производительность, если переместите базу данных на новую версию SQL Server (например, SQL Server 2017 или SQL Server 2019), пока вы используете тот же уровень совместимости базы данных и эквивалентное железо.
Для SQL Server 2012 и ниже уровень совместимости базы данных, главным образом, использовался для контроля над тем, доступны или нет новые возможности, введенные в конкретной версии SQL Server, и доступны или нет не поддерживаемые уже старые возможности. Уровень совместимости базы данных также использовался как метод поддержания обратной совместимости приложений со старыми версиями SQL Server. Если у вас не было времени, чтобы выполнить полное тестирование регрессии на новейшем уровне совместимости, вы могли просто использовать прежний уровень совместимости до тех пор, пока выполните тестирование и, при необходимости, модификацию ваших приложений.
В таблице 1 показаны основные версии SQL Server и их уровни совместимости, поддерживаемые и принимаемые по умолчанию.
| Версия SQL Server | Версия движка БД | Уровень совместимости по умолчанию | Поддерживаемые уровни |
|---|---|---|---|
| SQL Server 2019 | 15 | 150 | 150, 140, 130, 120, 110, 100 |
| SQL Server 2017 | 14 | 140 | 140, 130, 120, 110, 100 |
| SQL Server 2016 | 13 | 130 | 130, 120, 110, 100 |
| SQL Server 2014 | 12 | 120 | 120, 110, 100 |
| SQL Server 2012 | 11 | 110 | 110, 100, 90 |
| SQL Server 2008 R2 | 10.5 | 100 | 100, 90, 80 |
| SQL Server 2008 | 10 | 100 | 100, 90, 80 |
| SQL Server 2005 | 9 | 90 | 90, 80 |
| SQL Server 2000 | 8 | 80 | 80 |
Таблица 1: Версии SQL Server и поддерживаемые ими уровни совместимости
Создание новой базы данных
Когда вы создаете новую пользовательскую базу данных в SQL Server, уровень совместимости базы данных будет установлен в уровень совместимости по умолчанию для этой версии SQL Server. Так, например, новая пользовательская база данных, которая создается в SQL Server 2017 получит уровень совместимости базы данных 140. Исключение возникает, если вы измените уровень совместимости системной базы данных model на другой поддерживаемый уровень, тогда новая пользовательская база данных будет наследовать этот уровень совместимости от базы данных model.
Восстановление или присоединение базы данных
Если вы восстанавливаете полный бэкап базы данных, который был сделан на более старой версии SQL Server, на экземпляре, который запущен на более новой версии SQL Server, то уровень совместимости базы данных останется прежним, каким он был на старой версии SQL Server, если уровень совместимости старой базы данных не ниже, чем минимальный поддерживаемый уровень совместимости новой версии SQL Server.
Например, если вы восстанавливаете бэкап базы данных с SQL Server 2005 на экземпляре SQL Server 2017, уровень совместимости для восстановленной базы данных будет установлен в 100. Вы получите то же самое поведение, если отсоедините базу данных с более старой версии SQL Server, а затем присоедините её к более новой версии SQL Server.
Такое поведение не ново, но кое-что новое и важное может еще произойти, когда вы переводите пользовательскую базу данных на уровень совместимости 120 или новее. Эти дополнительные изменения, которые могут оказать существенное влияние на производительность, видимо, недостаточно известны и поняты широкому сообществу пользователей SQL Server. Я по-прежнему встречаю многих профессионалов в области баз данных и организации, выполняющих то, что я называю "апгрейд вслепую". Это когда они переходят с SQL Server 2012 или более ранних версий на SQL Server 2014 или новее (особенно SQL Server 2016 и SQL Server 2017), не выполняя сколь-нибудь серьезного тестирования падения производительности, чтобы выяснить, как их рабочая нагрузка будет вести себя на новом естественном уровне совместимости, и доступны ли дополнительные конфигурационные параметры, способные оказать положительный эффект.
Уровень совместимости 120
Здесь был введен новый оценщик кардинального числа (CE), т.е. изменилась оценка числа строк. Во многих случаях большинство ваших запросов стало выполняться быстрей при использовании нового оценщика, но зачастую имелось небольшое число запросов, которые демонстрировали существенное падение производительности с новым оценщиком кардинального числа. Использование уровня совместимости базы данных 120 означает, что вы будете использовать "новый" CE до тех пор, пока не установите флажок трассировки уровня экземпляра или не будете использовать хинт в запросе, чтобы отменить его действие.
Joe Sack еще в апреле 2014 года написал классическую работу “Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator” (Оптимизация ваших планов запросов с оценщиком кардинального числа SQL Server 2014), которая объясняет основание и поведение сделанных изменений. Если вы наблюдаете падение производительности некоторых запросов с новым СЕ, SQL Server имел немного вариантов, чтобы устранить проблемы с производительностью, вызванные новым СЕ. Joe подробно описал эти возможности, которые, по существу, сводились к флажкам трассировки или хинтам в запросах для контроля над тем, какой оценщик кардинального числа применяется оптимизатором запросов. Или же вы решали вернуться обратно к уровню совместимости базы данных 110 и ниже.
Я использую "новый" СЕ в кавычках потому, что теперь нет единственного "нового" СЕ. Каждая следующая версия SQL Server, начиная с SQL Server 2014, имеет свой СЕ, и изменения оптимизатора запросов привязаны к уровню совместимости базы данных. Новая более точная терминология, соответствующая SQL Server 2016 и далее, использует СЕ120 для уровня совместимости 120, СЕ130 - для уровня совместимости 130, СЕ140 - для уровня совместимости 140, и СЕ150 - для уровня совместимости 150.
Уровень совместимости базы данных 130
Если вы имеете SQL Server 2016 или новее, использование уровня совместимости базы данных 130 будет применять СЕ130 по умолчанию, и будут доступны и все другие изменения, касающиеся производительности. Эффекты глобальных флажков трассировки 1117, 1118 и 2371 будут действовать при уровне совместимости базы данных 130. Вы также получите эффект глобального флажка трассировки 4199 для всех заплаток (hotfix), которые были поставлены до SQL Server 2016 RTM.
SQL Server 2016 также ввел конфигурационные опции уровня базы данных, которые дают вам возможность контролировать некоторое поведение и которые ранее конфигурировались на уровне экземпляра сервера, с помощью команды ALTER DATABASE SCOPED CONFIGURATION. Двумя наиболее уместными для темы настоящей статьи конфигурационными опциями уровня базы данных являются ESTIMATION и QUERY_OPTIMIZER_HOTFIXES.
LEGACY_CARDINALITY ESTIMATION включает унаследованный СЕ (СЕ70) вне зависимости от установки уровня совместимости базы данных. Это эквивалентно флажку трассировки 9481, но влияет только на соответствующую базу данных, а не на весь экземпляр. Это позволяет вам установить уровень совместимости базы данных в 130 для того, чтобы получить другие функциональные возможности и выгоды с точки зрения производительности, и при этом использовать унаследованный СЕ уровня базы данных (если он не переписывается хинтом в запросе).
Опция QUERY_OPTIMIZER_HOTFIXES эквивалентна флажку трассировки 4199 на уровне базы данных. SQL Server 2016 делает доступными все заплатки оптимизатора запросов, поставленные до SQL Server 2016 RTM, когда вы используете уровень совместимости базы данных 130 (без установки флажка трассировки 4199). Если вы устанавливаете флажок 4199 или включаете QUERY_OPTIMIZER_HOTFIXES, вы также получите все заплатки оптимизатора запросов, которые были выпущены после SQL Server 2016 RTM.
SQL Server 2016 SP1 также ввел хинты запроса USE HINT, которые легче использовать и понять, чем устаревшие хинты запроса QUERYTRACEON, которые могли использоваться в SQL Server 2014 и ранее. Это дает вам даже более тонкое управление поведением оптимизатора, чем связанное с используемыми уровнем совместимости и версией оценщика кардинального числа. Вы можете выполнить запрос к sys.dm_exec_valid_use_hints, чтобы получить список доступных названий в USE HINT.
Уровень совместимости базы данных 140
Если у вас установлен SQL Server 2017 или новее, использование уровня совместимости базы данных 140 будет применять СЕ140 по умолчанию. Вы также получаете все другие связанные с производительностью изменения от 130 плюс новые. SQL Server 2017 ввел новые возможности адаптивной обработки запросов, и они доступны по умолчанию, когда уровень совместимости базы данных установлен в 140. Они включают обратную связь по выделению памяти в пакетном режиме (batch mode memory grant feedback), адаптивные соединения в пакетном режиме (batch mode adaptive joins) и чередующееся выполнение (interleaved execution).
Уровень совместимости базы данных 150
Если вы имеете SQL Server 2019 или новее, использование уровня совместимости базы данных 150 будет применять СЕ150 по умолчанию. Вы также получаете все остальные изменения, связанные с производительностью, от 130 и 140 плюс новые, введенные в этой версии. SQL Server 2019 добавляет даже больше улучшений производительности и изменений поведения, чем доступно по умолчанию, когда база данных использует уровень совместимости 150. Главным примером является встраивание скалярных функций пользователя (scalar UDF inlining), который автоматически встраивает много скалярных UDF в ваших пользовательских базах данных. Это может быть одним из наиболее важных улучшений производительности при некоторых рабочих нагрузках.
Другим примером является интеллектуальная обработка запросов (intelligent query processing), которая является подмножеством адаптивной обработки запросов в SQL Server 2017. Новые возможности включают отложенную компиляцию табличных переменных (table variable deferred compilation), неточную обработку запросов (approximate query processing) и пакетный режим для построчного хранения (batch mode on rowstore).
Имеется также 16 новых конфигурационных параметров уровня базы данных (в CTP 2.2), которые дают вам больше возможностей управления базами данных, чем это предоставлялось флажками трассировки или уровнем совместимости базы данных. Это позволяет осуществлять более тонкий контроль высокоуровневых изменений, чем поведение по умолчанию при уровне совместимости базы данных 150.
Заключение
Переход на современную версию SQL Server (т.е. SQL Server 2016 или старше) существенно более сложный процесс, чем это было со старыми версиями SQL Server. Поскольку изменения связаны с различными уровнями совместимости базы данных и различными версиями оценщика кардинального числа, действительно очень важно обдумать, спланировать и протестировать тот уровень совместимости, который вы хотите использовать на новой версии SQL Server, на которую вы переносите ваши базы данных.
Майкрософт рекомендует процесс апгрейда на новейшую версию SQL Server с сохранением уровня совместимости переносимых баз данных. Затем включить Query Store на каждой базе данных и собрать показательные данные при рабочей нагрузке. Потом установить уровень совместимости последней версии и использовать Query Store, чтобы зафиксировать падение производительности, принудительно используя последний заведомо хороший план.
Если вы хотите избежать бессистемной "слепой" миграции, когда вы находитесь в блаженном неведении того, как ваша рабочая нагрузка будет реагировать на эти изменения. Изменение уровня совместимости базы данных на подходящую версию и использование подходящих конфигурационных параметров уровня базы данных, наряду с абсолютно необходимыми соответствующими хинтами запросов, исключительно важно для современных версий SQL Server.
Другим вопросом, заслуживающим внимания, является то, что Майкрософт начинает продвигать идею, что вы должны думать о тестировании ваших баз данных и приложений на конкретном уровне совместимости базы данных, а не на конкретной версии SQL Server. Майкрософт обеспечивает защиту формы плана запроса, когда новая версия SQL Server запускается на железе, которое совместимо с железом, на котором была запущена предыдущая версия (исходная) SQL Server, и один и тот же поддерживаемый уровень совместимости базы данных используется на обеих версиях.
Основная идея заключается в том, что если вы протестировали ваши приложения на конкретном уровне совместимости, например, 130, вы получите то же самое поведение и производительность, если переместите базу данных на новую версию SQL Server (например, SQL Server 2017 или SQL Server 2019), пока вы используете тот же уровень совместимости базы данных и эквивалентное железо.
Обратные ссылки
Нет обратных ссылок
Автор не разрешил комментировать эту запись
Комментарии
Показывать комментарии Как список | Древовидной структурой