Инструкция: ускоряем tempdb переносом в ram диск

Performance improvements in tempdb for SQL Server

Starting with SQL Server 2016 (13.x), performance is further optimized in the following ways:

Temporary tables and table variables are cached. Caching allows operations that drop and create the temporary objects to run very quickly. Caching also reduces page allocation and metadata contention.
The allocation page latching protocol is improved to reduce the number of (update) latches that are used.
Logging overhead for is reduced to reduce disk I/O bandwidth consumption on the log file.
Setup adds multiple data files during a new instance installation. You can accomplish this task by using the new UI input control in the Database Engine Configuration section and the command-line parameter . By default, setup adds as many data files as the logical processor count or eight, whichever is lower.
When there are multiple data files, all files autogrow at the same time and by the same amount, depending on growth settings. Trace flag 1117 is no longer required.
All allocations in use uniform extents. Trace flag 1118 is no longer required.
For the primary filegroup, the property is turned on and the property can’t be modified.

For more information on performance improvements in , see the blog article TEMPDB — Files and Trace Flags and Updates, Oh My!.

Экспертный кейс. Расследование фатального замедления времени расчета себестоимости в 1С:ERP 2

При выполнении нагрузочного тестирования информационной системы на базе 1С:ERP для одного из клиентов с целью оценки возможности миграции системы на PostgreSQL и Astra Linux мы столкнулись с неприемлемым увеличением времени выполнения расчета себестоимости.

Строго говоря, сценарий тестирования закрытия месяца не был выполнен вообще – он не укладывался в таймаут выполнения теста, 24 часа. По прошествии 18 часов всё ещё шло выполнение операции «Распределение затрат и расчет себестоимости». Более 16 часов выполнялся подэтап “Расчет партий и себестоимости. Этап. Расчет себестоимости: РассчитатьСтоимость”. Всё это время выполнялся запрос, который в текущей инфраструктуре клиента (СУБД MS SQL Server) выполняется чуть более 3 минут на аналогичных данных.

Memory-optimized tempdb metadata

Metadata contention in has historically been a bottleneck to scalability for many workloads running on SQL Server. SQL Server 2019 (15.x) introduces a new feature that’s part of the in-memory database feature family: memory-optimized tempdb metadata.

This feature effectively removes this bottleneck and unlocks a new level of scalability for tempdb-heavy workloads. In SQL Server 2019 (15.x), the system tables involved in managing temporary table metadata can be moved into latch-free, non-durable, memory-optimized tables.

Note

Currently the memory-optimized tempdb metadata feature is not available in Azure SQL Database or Azure SQL Managed Instance.

Watch this seven-minute video for an overview of how and when to use memory-optimized tempdb metadata:

Configuring and using memory-optimized tempdb metadata

To opt in to this new feature, use the following script:

This configuration change requires a restart of the service to take effect.

You can verify whether or not is memory-optimized by using the following T-SQL command:

If the server fails to start for any reason after you enable memory-optimized metadata, you can bypass the feature by starting the SQL Server instance with minimal configuration through the -f startup option. You can then disable the feature and restart SQL Server in normal mode.

To protect the server from potential out-of-memory conditions, you can bind to a resource pool. This is done through the command rather than the steps you would normally follow to bind a resource pool to a database.

This change also requires a restart to take effect, even if memory-optimized tempdb metadata is already enabled.

Memory-optimized tempdb limitations

Toggling the feature on and off is not dynamic. Because of the intrinsic changes that need to be made to the structure of , a restart is required to either enable or disable the feature.
A single transaction is not allowed to access memory-optimized tables in more than one database. Any transactions that involve a memory-optimized table in a user database won’t be able to access system views in the same transaction. If you try to access system views in the same transaction as a memory-optimized table in a user database, you’ll receive the following error:

Example:
Queries against memory-optimized tables don’t support locking and isolation hints, so queries against memory-optimized catalog views won’t honor locking and isolation hints. As with other system catalog views in SQL Server, all transactions against system views will be in (or in this case, ) isolation.
Columnstore indexes can’t be created on temporary tables when memory-optimized metadata is enabled.
Due to the limitation on columnstore indexes, use of the system stored procedure with the or data compression parameter is not supported when memory-optimized metadata is enabled.

Note

These limitations apply only when you’re referencing system views. You can create a temporary table in the same transaction as you access a memory-optimized table in a user database, if desired.

Monitor temporary tables space usage

Additionally, if a large amount of data is stored in temporary tables due to the higher SQL server activity, check the number of rows, and used/reserved space of each of temporary tables which had been created in a specific database:

1
2
3
4
5
6
7
8
9
10
11
12
13

USE<database_name>

SELECT tb.name ASTemporary table name,

stt.row_count ASNumber of rows,

stt.used_page_count *8ASUsed space(KB),

stt.reserved_page_count *8ASReserved space(KB)FROM tempdb.sys.partitions ASprt

INNER JOIN tempdb.sys.dm_db_partition_stats ASstt

ON prt.partition_id=stt.partition_id

ANDprt.partition_number=stt.partition_number

INNER JOIN tempdb.sys.tables AStb

ON stt.object_id=tb.object_id

ORDER BY tb.name

In this case, we used custom Test database. Temporary objects marked above in the screenshot are user-created tables.

Note that all temporary objects will be removed on SQL Server service or machine restart, and information on them can be retrieved only during the active user session.

Move file locations

As in any regular database, tempdb data and log files can be moved to another location if and when needed to. The following is an example for moving 2 data files and 1 transaction log file to a new location in disk T:

1
2
3
4
5
6
7
8
9
10

ALTER DATABASE tempdb

MODIFY FILE(NAME=tempdev,FILENAME=’T:\TempDB\Data\tempdb.mdf’);

ALTER DATABASE tempdb

MODIFY FILE(NAME=temp2,FILENAME=’T:\TempDB\Data\tempdb_mssql_2.ndf’);

ALTER DATABASE tempdb

MODIFY FILE(NAME=templog,FILENAME=’T:\TempDB\Log\templog.ldf’);

Since tempdb is recreated every time the SQL Server instance restarts, there is no need to copy the current files to the new location. The only thing that it needs to be done to have these changes implemented is to perform a SQL Server service restart and then the new files will be created in the new location (folder structure should exist). After that, old tempdb data and log files can be deleted from old location.

Процедура восстановления после сбоя

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

Важно!

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

Убедитесь, что учетная запись службы для ядра СУБД SQL Server имеет полные разрешения на новое расположение файлов. Дополнительные сведения см. в статье Настройка учетных записей службы Windows и разрешений. Если учетная запись службы ядра СУБД не может управлять файлами в новом расположении, экземпляр SQL Server не запустится.
Остановите работу экземпляра SQL Server , если он запущен.
Запустите экземпляр SQL Server в режиме восстановления «только master», запустив из командной строки одну из следующих команд. При использовании параметра запуска 3608 SQL Server прекращается автоматический запуск и восстановление любой базы данных, кроме . Дополнительные сведения см. в разделах и .

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

В случае с экземпляром по умолчанию (MSSQLSERVER) запустите следующую команду:

В случае с именованным экземпляром запустите следующую команду:

Дополнительные сведения см. в статье Iniciar, parar, pausar, retomar e reiniciar os serviços SQL Server.
Сразу после запуска службы с флагом трассировки 3608 и запустите подключение к серверу sqlcmd, чтобы утвердить доступное отдельное подключение. Например, при локальном выполнении программы sqlcmd на том же сервере, что и экземпляр по умолчанию (MSSQLSERVER), а также для подключения с помощью встроенной проверки подлинности Active Directory выполните следующую команду:

Чтобы подключиться к именованному экземпляру на локальном сервере с помощью встроенной проверки подлинности Active Directory выполните следующее:

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

Для каждого перемещаемого файла используйте команды sqlcmd или SQL Server Management Studio для выполнения следующей инструкции. Дополнительные сведения об использовании программы sqlcmd см. в статье Использование программы sqlcmd. После открытия сеанса sqlcmd выполните следующую инструкцию по одному разу для перемещения каждого файла:
Завершите работу программы sqlcmd или SQL Server Management Studio.
Остановите экземпляр SQL Server. Например, в командной строке выполните команду .
Скопируйте файл (файлы) в новое расположение.
Повторно запустите экземпляр SQL Server. Например, в командной строке выполните команду .
Проверьте изменения в файле с помощью следующего запроса.
Поскольку на шаге 7 вместо того чтобы переместить файлы базы данных, вы скопировали их, теперь можно безопасно удалить неиспользуемые файлы базы данных из их предыдущего расположения.

Disk space

tempdb is one particular database that is more difficult to estimate the needed disk space. As showed in the section, it stores many kinds of objects and they are not so easy to estimate. Unfortunately, during all these years working with SQL Server I couldn’t yet find the magic formula but I would recommend to provide as much disk space you can with the minimum disk size being the same size as the largest table in the SQL Server instance. It is also wise to add some safety factor to prevent the database growth.

Fortunately, for the tempdb transaction log file, it is easier to estimate the necessary size. The tempdb, being a database with the recovery model set to Simple, it will be minimally logged. You can use a rule of thumb to create the transaction log file based on 20%-30% of the data size and tweak it later if needed.

The space in tempdb is vital for the health of the SQL Server instance. When running out of space the SQL Server instance may become unresponsive so it is always better to reserve more space than give less for the tempdb data and log files.

Установка SQL Server 2012

Сервисные учетные записи

Sql_installation – учетная запись Active Directory (AD), используем для инсталляции SQL, которая на время инсталляции необходимо включить в группу локальных администраторов. После инсталляции учетную запись Sql_installation заблокировать. Она нужна будет только на время инсталляции обновлений.
Sql_engine – учетная запись Active Directory (AD), администратор отдельного экземпляра SQL Server 2012 (для каждого экземпляра свой), которая не входит в группу локальных администраторов. Под этой учетной записью будет работать сервисы SQL Server для устанавливаемого экземпляра (служба SQL Server Database Engine). Эта учетная запись не должна входить в группы локальных, доменных администраторов или администраторов предприятий.
SQL_agent – учетная запись службы агента SQL Server, позволяющей автоматизировать некоторые административные задачи. Агент SQL-сервера выполняет задания, контролирует SQL Server и обрабатывает предупреждения. Служба агента SQL Server должна быть запущена для автоматического запуска локальных или много серверных административных заданий. Рекомендуется выбирать учетную запись пользователя домена Active Directory, не входящего в группу локальных администраторов.
SQL_browser – учетная запись браузера SQL-сервера, которая прослушивает входящие запросы на ресурсы SQL-сервера и предоставляет сведения об экземплярах, установленных на этом компьютере. Рекомендуется выбирать учетную запись пользователя домена Active Directory, не входящего в группу локальных администраторов. В случае, если на сервере инсталлируется экземпляр, имя которого отличается от имени по умолчанию или количество экземпляров более одного, включение службы является обязательным.
SQL_admin – учетная запись администратора SQL-сервера.

Параметры сортировки

Англоязычная версия SharePoint 2013 использует Latin1_General_CI_AS_KS_WS;
Русскоязычная версия SharePoint 2013 использует Cyrillic_General_CI_AS.

CI, Case Insensitive (нечувствительность к регистру). «A» и «а» рассматриваются как один и тот же символ.
AS, Accent Sensitive (чувствительность диакритическим знакам, — знакам акцента). Символы «a» и «á» рассматриваются как разные символы.
KS, Kana Sensitive (чувствительность к символам японского алфавита). Символы Japanese Hirakana и Katakana, которые выглядят одинаково, рассматриваются как разные символы.
WS, Width Sensitive (чувствительность к объему данных). Символ, требующий для хранения в таблице SQL один байт и тот же символ, требующий для хранения два байта рассматриваются системой как два разных символа.

Расположение файлов баз данных по умолчанию

Файлов данных системной базы tempdb;
Журнала транзакций системной базы tempdb;
Файлов данных баз контента SharePoint;
Журналов транзакций баз контента SharePoint;
Резервный копий баз данных SharePoint.

Увеличение производительности базы данных tempdb в SQL Server

Начиная с версии SQL Server 2016 (13.x);, производительность дополнительно оптимизирована следующим образом:

Временные таблицы и табличные переменные кэшируются. Кэширование позволяет операциям удаления и создания временных объектов выполняться очень быстро. Кэширование также снижает вероятность возникновения состязаний, связанных с метаданными и выделением страниц.
Усовершенствован протокол кратковременных блокировок выделения страниц для снижения количества используемых кратковременных блокировок (обновление).
Снижены затраты ресурсов на ведение журнала — уменьшено потребление пропускной способности подсистемы ввода-вывода файлом журнала .
Программа установки добавляет множество файлов данных при установке нового экземпляра. Эту задачу можно выполнить с помощью нового элемента управления для ввода в пользовательском интерфейсе в разделе Настройка ядра СУБД и параметра командной строки . По умолчанию программа установки добавляет столько файлов данных , сколько имеется логических процессоров, но их может быть не больше восьми.
При наличии множества файлов данных автоматическое увеличение выполняется для всех файлов в одно время и в равном объеме согласно параметрам увеличения. Флаг трассировки 1117 больше не требуется.
Для всех операций распределения в используются единообразные экстенты. Флаг трассировки 1118 больше не требуется.
Для первичной файловой группы свойство включено и не может быть изменено.

Дополнительные сведения об улучшениях производительности в см. в статье блога TEMPDB — Files and Trace Flags and Updates, Oh My! (TEMPDB — файлы, флаги трассировки и обновления).

Lock pages in memory — реальность

Рекомендуют установить разрешение на Lock pages in memory (блокировку страниц в памяти) для пользователя, от которого запущена служба Microsoft SQL Server. Эта политика Windows определяет, какие учетные записи могут сохранять данные в физической памяти, чтобы система не отправляла страницы данных в виртуальную память на диске.

Запускаем Local Group Policy Editor: gpedit.msc
Слева выбираем Local Computer Policy, Computer Configuration, Windows Settings, Security Settings, Local Policies, User Rights Assigment.
Тыкаем в Lock pages in memory.

Видим, что политика не настроена. Можно добавить сюда пользователя, от имени которого работает SQL Server, я просто добавляю сюда группу Administrators.

Эту настройку применяем. Нужно учитывать, что потребление оперативки возрастёт.

Недостатки

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

Новые объекты создаются в пользовательской базе данных.
Каждый из новых объектов занимает менее 64 КБ хранилища.

Если эти условия выполняются, можно выделить 64 КБ (восемь страниц * 8 КБ = 64 КБ) для объекта, для которого требуется только 8 КБ пространства, что означает 56 КБ хранилища. Однако если новый объект использует более 64 КБ (восемь страниц) за время существования, нет недостатков флага трассировки. Таким образом, в худшем случае SQL Server выделить семь (7) дополнительных страниц во время первого выделения только для новых объектов, которые никогда не выходят за пределы одной (1) страницы.

Планирование ресурсов для tempdb в SQL Server

Определение требуемого размера в рабочей среде SQL Server зависит от многих факторов. Как описано выше, эти факторы включают текущую рабочую нагрузку и используемые функции SQL Server. Рекомендуется проанализировать текущую рабочую нагрузку, выполнив следующие задачи в среде тестирования SQL Server:

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

Физические свойства tempdb в SQL Server

В следующей таблице приводятся исходные значения конфигурации для файлов данных и журналов в SQL Server. Значения основаны на значениях по умолчанию для базы данных . Размеры этих файлов могут немного отличаться в зависимости от выпуска SQL Server.

Файл	Логическое имя	Физическое имя	Начальный размер	Увеличение размера файлов
Первичные данные	tempdev	tempdb.mdf	8 МБ	Автоматическое увеличение на 64 МБ до заполнения диска.
Вторичные файлы данных.	temp#	tempdb_mssql_ # .ndf	8 МБ	Автоматическое увеличение на 64 МБ до заполнения диска.
Журнал	templog	templog.ldf	8 МБ	Автоматическое увеличение на 64 МБ до максимального размера в 2 ТБ.

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

Примечание

Количество файлов данных по умолчанию основано на общих рекомендациях, приведенных в статье KB 2154845.

Примечание

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

Параметры базы данных для tempdb в SQL Server

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

Параметр базы данных	Значение по умолчанию	Можно ли изменить
ALLOW_SNAPSHOT_ISOLATION	OFF	Да
ANSI_NULL_DEFAULT	OFF	Да
ANSI_NULLS	OFF	Да
ANSI_PADDING	OFF	Да
ANSI_WARNINGS	OFF	Да
ARITHABORT	OFF	Да
AUTO_CLOSE	OFF	нет
AUTO_CREATE_STATISTICS	ON	Да
AUTO_SHRINK	OFF	нет
AUTO_UPDATE_STATISTICS	ON	Да
AUTO_UPDATE_STATISTICS_ASYNC	OFF	Да
CHANGE_TRACKING	OFF	нет
CONCAT_NULL_YIELDS_NULL	OFF	Да
CURSOR_CLOSE_ON_COMMIT	OFF	Да
CURSOR_DEFAULT	GLOBAL	Да
Параметры доступности базы данных	ONLINE MULTI_USER READ_WRITE	Нет Нет Нет
DATE_CORRELATION_OPTIMIZATION	OFF	Да
DB_CHAINING	ON	нет
ENCRYPTION	OFF	нет
MIXED_PAGE_ALLOCATION	OFF	нет
NUMERIC_ROUNDABORT	OFF	Да
PAGE_VERIFY	Значение CHECKSUM для новых установок SQL Server. Значение NONE для обновлений SQL Server.	Да
PARAMETERIZATION	ПРОСТОЙ	Да
QUOTED_IDENTIFIER	OFF	Да
READ_COMMITTED_SNAPSHOT	OFF	нет
RECOVERY	ПРОСТОЙ	нет
RECURSIVE_TRIGGERS	OFF	Да
Параметры компонента Service Broker	ENABLE_BROKER	Да
TRUSTWORTHY	OFF	нет

Описание этих баз данных см. в статье Параметры ALTER DATABASE SET (Transact-SQL).

tempdb in Azure SQL

The behavior of tempdb in Azure SQL Database differs from the behavior SQL Server, Azure SQL Managed Instance, and SQL Server on Azure VMs.

tempdb in SQL Database

Single and pooled databases in Azure SQL Database support global temporary tables and global temporary stored procedures scoped to the database level, and stored in . Global temporary tables and global temporary stored procedures are shared for all users’ sessions within the same database. User sessions from other databases can’t access global temporary tables. For more information, see .

For single and pooled databases in Azure SQL Database, out of all the system databases, only the master database and database are accessible. For more information, see What is a logical server in Azure?

To learn more about tempdb sizes in Azure SQL Database, review:

vCore purchasing model: single databases, pooled databases
DTU purchasing model: ,

tempdb in SQL Managed Instance

Azure SQL Managed Instance supports temporary objects in the same way as SQL Server, where all global temporary tables and global temporary stored procedures are accessible by all user sessions within the same managed instance. Likewise, all system databases are accessible.

To learn more about tempdb sizes in Azure SQL Managed Instance, review resource limits.

Увеличение числа файлов данных tempdb с одинаковым размером

Например, если размер одного файла данных tempdb равен 8 ГБ, а размер файла журнала — 2 ГБ, рекомендуем увеличить количество файлов данных до восьми (8) (каждый из 1 ГБ для поддержания равного размера) и оставить файл журнала как есть. Наличие разных файлов данных на отдельных дисках обеспечит дополнительную производительность. Однако это не обязательно. Файлы могут сосуществовать на томе одного диска.

Оптимальное количество файлов данных tempdb зависит от степени состязания в базе данных tempdb. В качестве отправной точки можно настроить базу данных tempdb как минимум равным числу логических процессоров, назначенных для SQL Server. Для систем более высокого уровня начальное число может быть восемь (8). Если состязание не уменьшается, может потребоваться увеличить количество файлов данных.

Рекомендуется использовать одинаковый размер файлов данных. SQL Server 2000 с пакетом обновления 4 (SP4) появился исправление, использующее алгоритм циклического перебора для распределения смешанных страниц. Из-за этого улучшения начальный файл отличается для каждого последовательного распределения смешанных страниц (если существует несколько файлов). Новый алгоритм распределения для SGAM является чисто циклическим перебором и не учитывает пропорциональное заполнение для поддержания скорости. Рекомендуется создать все файлы данных tempdb одного размера.

Оптимизированные для памяти метаданные tempdb

Состязание метаданных всегда было узким местом для масштабируемости многих рабочих нагрузок, выполняющихся в SQL Server. В SQL Server 2019 (15.x) появилась новая функция оптимизированных для памяти метаданных tempdb, входящая в семейство функций выполняющейся в памяти базы данных.

Она эффективно устраняет существующее узкое место и открывает новый уровень масштабируемости для рабочих нагрузок, активно использующих tempdb. В SQL Server 2019 (15.x) системные таблицы, связанные с управлением метаданными временных таблиц, можно переместить в неустойчивые таблицы без кратковременной блокировки, оптимизированные для памяти.

Примечание

Сейчас функция оптимизированных для памяти метаданных tempdb недоступна для Базы данных SQL Azure и Управляемых экземпляров SQL Azure.

Просмотрите это 7-минутное видео, чтобы узнать, как и когда следует использовать метаданные tempdb, оптимизированные для памяти:

Настройка и использование метаданных оптимизированной для памяти базы данных tempdb

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

Чтобы это изменение конфигурации вступило в силу, нужно перезапустить службу.

Вы можете проверить, является ли оптимизированной для памяти, используя следующую команду T-SQL:

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

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

Кроме того, даже если метаданные оптимизированной для памяти базы данных tempdb уже включены, чтобы это изменение вступило в силу, требуется перезагрузка.

Ограничения оптимизированной для памяти базы данных tempdb

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

Пример.
Запросы к таблицам, оптимизированным для памяти, не поддерживают указания блокировки и изоляции, поэтому запросы к представлениям каталога оптимизированной для памяти не будут учитывать указания блокировки и изоляции. Как и в случае с другими системными представлениями каталога в SQL Server, все транзакции для системных представлений будут находиться в изоляции (или в нашем случае).
Если оптимизированные для памяти метаданные включены, индексы columnstore нельзя создавать во временных таблицах.
В связи с ограничением на индексы columnstore использование системной хранимой процедуры с параметром сжатия данных или не поддерживается, если включены оптимизированные для памяти метаданные .

Примечание

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

Конфигурации в области базы данных

В SQL Server 2016 и базе данных SQL Azure существует ряд свойств конфигурации, которые можно задать на уровне базы данных. Дополнительные сведения обо всех этих параметрах см. в разделе ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL).

Традиционная оценка кратности
Укажите модель оценки кратности оптимизатора запросов для базы данных-источника независимо от уровня совместимости базы данных. Это эквивалентно флагу трассировки 9481.

Традиционная оценка кратности для баз данных-получателей
Укажите модель оценки кратности оптимизатора запросов для баз данных-получателей (при наличии) независимо от уровня совместимости базы данных. Это эквивалентно флагу трассировки 9481.

Максимальное значение DOP
Укажите используемый по умолчанию параметр MAXDOP для баз данных-источников, который должен использоваться в инструкциях.

Максимальное значение DOP для баз данных-получателей
Укажите используемый по умолчанию параметр MAXDOP для баз данных-получателей (при их наличии), который должен использоваться в инструкциях.

Сканирование параметров
Включает или отключает сканирование параметров базы данных-источника. Это эквивалентно флагу трассировки 4136.

Сканирование параметров баз данных-получателей
Включает или отключает сканирование параметров баз данных-получателей (при наличии). Это эквивалентно флагу трассировки 4136.

Исправления оптимизатора запросов
Включает или отключает исправления оптимизации запросов для базы данных-источника независимо от уровня совместимости базы данных. Это эквивалентно флагу трассировки 4199. Подробности см. в параметре .

Исправления оптимизатора запросов для баз данных-получателей
Включает или отключает исправления оптимизации запросов для баз данных-получателей (при наличии) независимо от уровня совместимости базы данных. Это эквивалентно флагу трассировки 4199. Подробности см. в параметре .

Симптомы

На сервере под управлением Microsoft SQL Server вы заметите серьезную блокировку, когда на сервере наблюдается высокая нагрузка. Динамические административные представления указывают, что эти запросы или задачи ожидают ресурсов tempdb . Кроме того, тип ожидания — это ресурс ожидания, указывающий на страницы в базе данных tempdb. Эти страницы могут иметь формат 2:1:1, 2:1:3 и т. д. (страницы PFS и SGAM в базе данных tempdb).

Примечание.

Если страница равномерно делится на 8088, это страница PFS. Например, страница 2:3:905856 является PFS в file_id=3 в базе данных tempdb.

В следующих операциях база данных tempdb широко используется:

Повторяющиеся операции создания и удаления временных таблиц (локальных или глобальных).
Табличные переменные, использующие базу данных tempdb для хранения.
Рабочие таблицы, связанные с CURSORS.
Рабочие таблицы, связанные с предложением ORDER BY.
Рабочие таблицы, связанные с предложением GROUP BY.
Рабочие файлы, связанные с HASH PLANS.

Эти действия могут вызвать проблемы с состязанием.

Optimizing tempdb performance in SQL Server

The size and physical placement of the database can affect the performance of a system. For example, if the size that’s defined for is too small, part of the system-processing load might be taken up with autogrowing to the size required to support the workload every time you restart the instance of SQL Server.

If possible, use instant file initialization to improve the performance of growth operations for data files.

Preallocate space for all files by setting the file size to a value large enough to accommodate the typical workload in the environment. Preallocation prevents from expanding too often, which affects performance. The database should be set to autogrow to increase disk space for unplanned exceptions.

Data files should be of equal size within each , because SQL Server uses a proportional-fill algorithm that favors allocations in files with more free space. Dividing into multiple data files of equal size provides a high degree of parallel efficiency in operations that use .

Set the file growth increment to a reasonable size to prevent the database files from growing by too small a value. If the file growth is too small compared to the amount of data that’s being written to , might have to constantly expand. That will affect performance.

To check current size and growth parameters for , use the following query:

Put the database on a fast I/O subsystem. Use disk striping if there are many directly attached disks. Individual or groups of data files don’t necessarily need to be on different disks or spindles unless you’re also encountering I/O bottlenecks.

Put the database on disks that differ from the disks that user databases use.

Сервер 1С:Предприятие на Ubuntu 16.04 и PostgreSQL 9.6, для тех, кто хочет узнать его вкус. Рецепт от Капитана

Если кратко описать мое отношение к Postgres: Использовал до того, как это стало мейнстримом.
Конкретнее: Собирал на нем сервера для компаний среднего размера (до 50 активных пользователей 1С).
На настоящий момент их набирается уже больше, чем пальцев рук пары человек (нормальных, а не фрезеровщиков).
Следуя этой статье вы сможете себе собрать такой же и начать спокойную легальную жизнь, максимально легко сделать первый шаг в мир Linux и Postgres.
А я побороться за 1. Лучший бизнес-кейс (лучший опыт автоматизации предприятия на базе PostgreSQL).
Если, конечно, статья придется вам по вкусу.

Database instant file initialization — частично миф, частично реальность

Рекомендуют включить возможность Database instant file initialization для пользователя, от которого запущена служба Microsoft SQL Server.

Что это за штука?

Во-первых, эта настройка влияет только на файл данных. Когда файл автоматически вырастает, то новый кусок заполняется нулями, в этот момент 1С может тормозить. Instant File Initialization (IFI) позволяет отключить это зануление.

Делается так.

Запускаем Local Group Policy Editor: gpedit.msc
Слева выбираем Local Computer Policy, Computer Configuration, Windows Settings, Security Settings, Local Policies, User Rights Assigment.
Тыкаем в Perform volume maintenance tasks.
Сюда добавляем юзера, от имени которого запускается SQL Server.

Однако, у меня там уже прописана группа Administrators, а пользователь, под которым работает служба SQL Server уже в этой группе.

Так что у меня ничего не пришлось настраивать. Но вы у себя проверьте.

Давайте проверим, что всё работает. Рекомендуют создать новую БД размером 5 Гб и логом 1 Мб. Вот тут тоже нужно быть внимательным, нужно создавать под тем же пользователем, от которого запускается сервис SQL. Я создам от имени другого пользователя, который тоже в группе Administrators.

База создалась мгновенно. На всякий случай попробую создать базу размером 50 Гб, место есть. Да, моментально на диске пропало 50 Гб и БД создалась быстро.

Вывод:

Эту настройку применяем только при необходимости.