Флаг трассировки 1117

Флаг трассировки 1117

При наличии типа ожидания pagelatch рекомендуется вспомнить про логическую конкуренцию ресурсов файлов.

select   session_id, wait_duration_ms,   resource_description      from    sys.dm_os_waiting_tasks where   wait_type like ‘PAGE%LATCH_%’ and              resource_description like ‘2:%’
В рамках одной файловой группы может быть создано несколько файлов. Например, для базы tempdb рекомендуется создавать несколько файлов, что может в некоторых сценариях увеличить производительность системы.

Идея такая:
Если логических ядер <=8 то файлов tempdb должно быть равно количеству ядер (при том что диск достаточный для увеличения нагрузки)

Если логических ядер >8 то добавить еще 4 файла и посмотреть по факту. Надо, еще добавить, но при этом важно не переборщить, можно вместо логических ожиданий наплодить очередей к диску например.

Теперь предположим ситуацию: все файлы, входящие в файловую группу, имеют одинаковый размер. Создается большая временная таблица. Места в файле #1 не достаточно и разумеется происходит AutoGrow. Через время такая же таблица пересоздается, но вставка происходит в файл #2, потому что #1 временно заблокирован. Что в таком случае будет?AutoGrow для #2… и повторная задержка при выполнении запросов. Для таких случаев, был предусмотрен TF 1117. Работал он глобально и при нехватке места в одном файле вызывал AutoGrow для всех файлов в рамках одной файловой группы.

В MS SQL Server 2016 данный трейс-флаг включен по умолчанию для tempdb и может избирательно настраиваться для пользовательских баз:
ALTER DATABASE test
MODIFY FILEGROUP [PRIMARY] AUTOGROW_ALL_FILES
GO
ALTER DATABASE test
MODIFY FILEGROUP [PRIMARY] AUTOGROW_SINGLE_FILE
GO

Посмотрим на размер файлов:
USE tempdb
GO

SELECT
name
, physical_name
, current_size_mb = ROUND(size * 8. / 1024, 0)
, auto_grow =
CASE WHEN is_percent_growth = 1
THEN CAST(growth AS VARCHAR(10)) + ‘%’
ELSE CAST(CAST(ROUND(growth * 8. / 1024, 0) AS INT) AS VARCHAR(10)) + ‘MB’
END
FROM sys.database_files
WHERE [type] = 0

name physical_name size_mb auto_grow
---------- --------------------------------------------------- -------- ------------
tempdev D:\DATABASES\SQL_2016RC0\TEMP\tempdb.mdf 8.000000 64MB
temp2 D:\DATABASES\SQL_2016RC0\TEMP\tempdb_mssql_2.ndf 8.000000 64MB
temp3 D:\DATABASES\SQL_2016RC0\TEMP\tempdb_mssql_3.ndf 8.000000 64MB
temp4 D:\DATABASES\SQL_2016RC0\TEMP\tempdb_mssql_4.ndf 8.000000 64MB

Создаем временную таблицу:
IF OBJECT_ID('#t') IS NOT NULL
DROP TABLE #t
GO

CREATE TABLE #t (
ID INT DEFAULT 1,
Value CHAR(8000) DEFAULT ‘X’
)
GO

INSERT INTO #t
SELECT TOP(10000) 1, ‘X’
FROM [master].dbo.spt_values c1
CROSS APPLY [master].dbo.spt_values c2

Места чтобы вставить данные не хватит и произойдет AutoGrow.

AUTOGROW_SINGLE_FILE:
name physical_name size_mb auto_grow
---------- --------------------------------------------------- ----------- ------------
tempdev D:\DATABASES\SQL_2016RC0\TEMP\tempdb.mdf 72.000000 64MB
temp2 D:\DATABASES\SQL_2016RC0\TEMP\tempdb_mssql_2.ndf 8.000000 64MB
temp3 D:\DATABASES\SQL_2016RC0\TEMP\tempdb_mssql_3.ndf 8.000000 64MB
temp4 D:\DATABASES\SQL_2016RC0\TEMP\tempdb_mssql_4.ndf 8.000000 64MB

AUTOGROW_ALL_FILES:
name physical_name size_mb auto_grow
---------- --------------------------------------------------- ----------- ------------
tempdev D:\DATABASES\SQL_2016RC0\TEMP\tempdb.mdf 72.000000 64MB
temp2 D:\DATABASES\SQL_2016RC0\TEMP\tempdb_mssql_2.ndf 72.000000 64MB
temp3 D:\DATABASES\SQL_2016RC0\TEMP\tempdb_mssql_3.ndf 72.000000 64MB
temp4 D:\DATABASES\SQL_2016RC0\TEMP\tempdb_mssql_4.ndf 72.000000 64MB