Microsoft SQL Server
В данном разделе рассматриваются вопросы по установке, настройке и администрированию MS SQL. Вопросы по выборкам, созданию процедур, индексов и прочему программированию — лучше обращаться сюда: Microsoft SQL Server в разделе Прикладное Программирование
Полезные ресурсы: Ссылки на официальные ресурсы: TechNet Rus по SQL TechNet Eng по SQL 2000 TechNet Eng по SQL 2005 MS SQL Server на форумах TechNet Rus
Где найти дистрибутив?
Текущий и наверное Последний Service Pack: SP4 (build 8. 2039) Последний кумулятивный пакет хотфиксов после SP4: build 8. 2187 Список всех хотфиксов после SP4 на текущий момент: Cumulative list of the hotfixes that are available for SQL Server 2000 SP4 SQL Server 2000 Books Online June 2007 Update Сравнение редакций, совместимость с ОС
Текущая версия: Последний Service Pack — 2 Их было два, оба с одинаковым билдом — 9. 3042 Здесь (ENG) описано, почему так произошло и как отличить «правильный» от «не правильного».
Здесь можно посмотреть, какой апдейт на сегодняшний день крайний для MS SQL SP2: The SQL Server 2005 builds that were released after SQL Server 2005 Service Pack 2 was released (ENG)
Вы не привязаны к какому-то конкретному поддерживаемому уровню совместимости и можете изменить его по вашему желанию на любой другой из поддерживаемых. Во многих случаях большинство пользовательских баз данных никогда не изменяют свой уровень совместимости после перехода к новой версии SQL Server. Это обычно не вызывает никаких проблем до тех пор, пока вам не понадобятся новые возможности, доступные на последнем доступном уровне совместимости базы данных.
Для SQL Server 2012 и ниже уровень совместимости базы данных, главным образом, использовался для контроля над тем, доступны или нет новые возможности, введенные в конкретной версии SQL Server, и доступны или нет не поддерживаемые уже старые возможности. Уровень совместимости базы данных также использовался как метод поддержания обратной совместимости приложений со старыми версиями SQL Server. Если у вас не было времени, чтобы выполнить полное тестирование регрессии на новейшем уровне совместимости, вы могли просто использовать прежний уровень совместимости до тех пор, пока выполните тестирование и, при необходимости, модификацию ваших приложений.
В таблице 1 показаны основные версии SQL Server и их уровни совместимости, поддерживаемые и принимаемые по умолчанию.
Версия SQL ServerВерсия движка БДУровень совместимости по умолчаниюПоддерживаемые уровни
SQL Server 201915150150, 140, 130, 120, 110, 100
SQL Server 201714140140, 130, 120, 110, 100
SQL Server 201613130130, 120, 110, 100
SQL Server 201412120120, 110, 100
SQL Server 201211110110, 100, 90
SQL Server 2008 R210. 5100100, 90, 80
SQL Server 200810100100, 90, 80
SQL Server 200599090, 80
SQL Server 200088080
Таблица 1: Версии SQL Server и поддерживаемые ими уровни совместимости
Есть некоторые древние приложения, которые в процессе установки делают обращение к экземпляру SQL Server, создают там базу данных и выполняют её первоначальное наполнение. В случае, если такие приложения хотят, чтобы их база данных имела определённый уровень совместимости (compatibility level), но сами при этом не выполняют настройку этого уровня в процессе создания и инициализации БД, может возникнуть необходимость изменения уровня совместимости, используемого по умолчанию в экземпляре SQL Server. Рассмотрим пример того, как это можно «провернуть».
Предположим, у нас есть экземпляр SQL Server 2012, на котором должна быть развернута БД некоторого старого приложения, которое хочет чтобы эта БД имела уровень совместимости с SQL Server 2005. Однако при создании для новых баз в нашем случае будет назначаться уровень совместимости SQL Server 2012 (110). Попробуем сделать так, чтобы по умолчанию уровень совместимости выставлялся в SQL Server 2005 (90).
Для начала на нашем экземпляре SQL Server выполним запрос, который покажет установленный уровень совместимости для всех баз:
Как видим, уровень совместимости по умолчанию для новых баз в нашем экземпляре установлен в 110, так как он определяется уровнем базы model. Информацию о возможных уровнях совместимости на разных версиях SQL Server можно найти в онлайн-документе:MSDN Library — Transact-SQL Reference (Database Engine) — ALTER DATABASE Compatibility Level
Соответственно, чтобы изменить уровень совместимости по умолчанию для вновь создаваемых баз до уровня SQL Server 2005, выполним запрос:
Обратите внимание на то, что такой запрос изменит на указанный уровень совместимости не только базу model, но и базу tempdb
Чтобы снова вернуть изначально установленный уровень совместимости выполним запрос:
Чем такие манипуляции могут обернуться на продуктивных экземплярах 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.
Совместимость MS SQL Server 2016 и 1с 8
Модератор: Дмитрий Юхтимовский
Просьба подсказать про совместимость MS SQL Server 2016 и 1с 8. Наши сисадмины желают перенести базы с MS SQL Server 2008r2 на MS SQL Server 2016 (на другой теоретически более мощный сервер).
в случае возникновения проблем обратный перенос баз с MS SQL 2016 на MS SQL 2008 затруднителен, то хотелось бы узнать имеющийся опыт, может кто-то уже использует MS SQL Server 2016, как рабочую СУБД. В этой теме меня интересует не возможный прирост производительности, а отсутствие новых (дополнительных) критичных глюков.
На этом сервере у нас около 8ми баз «УПП» с количеством активных пользователей от 20 до 120.
Мы уже, конечно, начали предварительное тестирование, но смоделировать реальную многопользовательскую нагрузку на тестовом сервере проблематично.
На данный момент версия платформы 8. 2530, режим совместимости «Версия 8. 16».
Сообщений: 6Зарегистрирован: 02 авг 2016, 05:13
Гилёв Вячеслав » 02 авг 2016, 12:35
для начала стоит перейти на 8. 8 и отказаться от режима совместимости в свойствах информационной базы 1срежим с 8. 2 означает что многие механизмы по факту будут работать на старом движке 8. 2 (ветки кода)
в свойствах базы данных на скуле от режима совместимости может зависеть работа оптимизатора, сильно зависеть.
Сообщений: 2496Зарегистрирован: 11 фев 2013, 15:40Откуда: Россия, Москва
IvanovMihail » 02 авг 2016, 13:26
Да, теоретически производительность должна увеличиться при отказе от режима совместимости с 8. Но сейчас больше интересует не прирост производительности, у нас проблема сейчас в другом. Есть новый более мощный сервер, на нем стоит MS SQL Server 2016. Сейчас удобный момент, чтобы определиться оставить на новом сервере MS SQL Server 2016 или снести и поставить проверенный MS SQL Server 2008r2. Когда сервер уже станет рабочим, этот будет сделать гораздо сложнее.
А уже потом при наличии времени заниматься отказом от совместимости. Тем более отказ от совместимости с «8. 2» сильно переписанной конфигурации упп 1. 2, тоже непонятно к чему приведет (не разбирался на сколько это легко сделать), на это тоже надо потратить достаточно много времени.
Гилёв Вячеслав » 02 авг 2016, 16:17
формально платформа не использует новых фич редакций скуля уже десятилетием и в теории все должно работатьна практике ни кто не знает как платформа реально строит сложные конструкции и как это все в итоге будет на выходеесли у кого то работает такая связка, то это не означает что проблемы нет чем выше «цена ошибки» (например в следствии большого количества пользователей), тем меньше поводов ставить малообкатанные версииесли у вас человек 50, то по идее должно быть все равно
IvanovMihail » 09 авг 2016, 12:57
Прикрепил результаты тестирования, может кому интересно будет. Замерял время выполнения «ключевой операции» (отчет, в котором выполняется сложный запрос) в зависимости от значений режима совместимости в 1с и режима совместимости в СУБД (MS SQL 2016). В нашем случае оказалось, что MS SQL 2016 можно использовать только в режиме совместимости «2008r2» или «2012», иначе значительно (в разы!) увеличивается время выполнения операции.
Гилёв Вячеслав » 09 авг 2016, 13:15
спасибо за информацию,хотя по поведению отчета нельзя судить О ВСЕХ ОПЕРАЦИЯХ, но все равно ситуация вполне понятнаявзамен тоже поделюсь с вами информацией по ссылке
выполните внимательно рекомендации, относящиеся к 2014й версии скуля — уверен, что добъетесь скорости младших версий субд флагамик сожалению до описания флагов 2016 версии не добрался, надо будет обновить
в любом случае, сообщите пожалуйста даже если не получится
IvanovMihail » 22 сен 2016, 12:12
Перенесли базы на MS SQL Server 2016 в режиме совместимости 2008. Сисадмин отверг идею с настройкой флагов.
Что касается производительности. Установлена подсистема Оценка производительности из БСП. Судя по ней, в среднем картина существенно не изменилась.
Дмитрий Юхтимовский » 22 сен 2016, 12:55
Картина не изменилась — то есть работает по-прежнему медленно или по-прежнему быстро?
Сообщений: 727Зарегистрирован: 11 фев 2013, 19:28Откуда: gilev
IvanovMihail » 22 сен 2016, 13:09
Быстро или медленно — это все относительно. Не то чтобы совсем медленно, но есть ощущение, что программа может и должна работать быстрее.
Но новый сервер под скуль приобретался сисадминами, не столько для увеличения производительности, сколько для расширения имевшихся ограничений и более стабильной работы в пиковые нагрузки и еще по другим причинам. У нас, у разработчиков, параллельно, конечно, была надежда, что в целом производительность улучшится хотя бы немного. Видимо, остается искать резервы для повышения производительности в оптимизации кода и структуры базы.
Дмитрий Юхтимовский » 22 сен 2016, 13:18
Ну теперь наши сервисы вам в помощь. Настраивайте QueryTJ, Status, SQLSize и анализируйте увиденное. Если нужна помощь в анализе и оптимизации — зовите нас.
Гилёв Вячеслав » 22 сен 2016, 16:48
IvanovMihail писал(а):Сисадмин отверг идею с настройкой флагов.
что это значит? он не умеет включать флаги? ссытся? не понимает что надо делать?
Дмитрий Юхтимовский » 26 сен 2016, 12:02
Это вполне реально, мы такое неоднократно делали. Нашими силами это будет стоить предварительно в районе 96-120 тысяч рублей (более детально стоимость может быть определена после анализа именно вашей конфигурации), в короткие сроки (несколько рабочих дней) с гарантией результата. Если интересно — обращайтесь.
Ахахаха Артём » 26 сен 2016, 12:19
Да не, спасибо) Я собственными силами потихоньку сделаю, раз это в принципе возможно просто думал мало ли, вдруг есть что-нибудь такое в УПП, что в принципе работать не будет после отказа от режима совместимости с 8. 2 может конечно плохо искал, но раньше нигде не находил информацию о подобных переходах, поэтому только мечтал об этом)
Сообщений: 2Зарегистрирован: 26 сен 2016, 09:46
Вернуться в MS SQL Server для целей 1С:Предприятие
Кто сейчас на форуме
Переход на современную версию 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), пока вы используете тот же уровень совместимости базы данных и эквивалентное железо.