Правка 2016: Этот флаг трассировки требуется во всех версиях SQL Server включая SQL Server 2014. На каждом экземпляре SQL Server в мире он должен быть включён. В SQL Server 2016 поведение, включаемое этим флагом, стало значением по умолчанию, поэтому сам TF1118 больше не требуется и не оказывает эффекта.
Вокруг флага трассировки TF1118 существует немало путаницы и заблуждений. Он переключает выделение страниц в tempdb: вместо одиночных распределений для первых 8 страниц они сразу выделяются как экстент (8 страниц). Он применяется, чтобы при высокой нагрузке ослабить конкуренцию за карты распределения (allocation bitmap contention) в tempdb, которая бывает связана, например, с массовым созданием и удалением маленьких временных таблиц.
Есть несколько мест, которые способны вызвать путаницу, на каждом из которых я остановлюсь по порядку. Затем я докажу, что TF1118 по‑прежнему работает во всех версиях SQL Server вплоть до 2014 включительно.
1) Почему TF1118 обычно был обязателен в 2000? В SQL Server 2000 при каждом создании временной таблицы в tempdb и вставке строки должны быть выделены страница IAM и одна страница данных. Обе эти страницы — «одиночные» распределения из смешанного экстента (mixed extent; см. «Анатомия экстента»). Это означает, что требуется доступ к странице‑карте SGAM, а также к странице PFS (см. «GAM, SGAM, PFS и другие карты распределения»).
Когда создаётся множество очень маленьких временных таблиц, это приводит к тому, что самая первая страница SGAM и самая первая страница PFS в файле данных постоянно читаются/меняются всеми потоками, вызывая конкуренцию за краткую блокировку (latch contention) на этих двух страницах. Когда временные таблицы снова удаляются, соответствующие страницы освобождаются, для чего опять требуется доступ к PFS и, возможно, к SGAM.
Есть два способа облегчить эту проблему. Во‑первых, создать несколько файлов данных в tempdb — это распределяет конкуренцию за краткие блокировки между несколькими картами распределения (так как они идут из разных файлов) и снижает вероятность возникновения проблем. Общее правило большого пальца было: один файл данных tempdb на каждое процессорное ядро. Во‑вторых, включить TF1118, который заставляет первые 8 страниц данных во временной таблице поступать из выделенного экстента. Это означает, что один экстент выделяется по карте GAM, вместо 8 одиночных страниц (и, потенциально, 8 обращений к SGAM). Страницы внутри экстента резервируются и выделяются по одной из этого экстента по мере необходимости. Это тоже уменьшает конкуренцию и документировано в KB328551 - Concurrency enhancements for the tempdb database.
2) Что означает «зарезервировано» vs. «выделено»? Когда экстент выделяется таблице, все 8 страниц в экстенте не становятся «выделенными» немедленно. Выделение экстента означает, что эти 8 страниц зарезервированы исключительно для последующего выделения этой таблице. Страницы выделяются по одной по мере надобности, но никакая другая таблица не может их занять. Поэтому такие экстенты называются «выделенными» (dedicated; подробности в моей публикации, указанной выше). В выводе sp_spaceused можно увидеть счётчики зарезервированных страниц и выделенных страниц.
4) Существует ли trace flag в 2005–2014? Да, существует — KB328551 явно гласит:
Примечание. TF1118 также доступен и поддерживается в Microsoft SQL Server 2005 и SQL Server 2008. Однако если вы используете SQL Server 2005 или SQL Server 2008, вам не нужно применять никакие хотфиксы.
На всякий случай (я всегда осторожничаю с категоричными заявлениями) я уточнил у моего хорошего друга Райана Стоунсайфера, руководителя команды разработки, которая отвечает за распределение (и ещё за массу вещей, включая DBCC). Он подтвердил, что код в 2008 такой же, как и в 2005. И ниже я это тоже докажу. В 2008R2, 2012 или 2014 не было изменений, которые отменили бы необходимость TF1118.
[Правка 2012:] 4a) Какова рекомендация Пола по использованию TF1118? Всем следует включить его на всех экземплярах SQL Server, начиная с SQL Server 2000. Никаких минусов от его включения нет.
5) И почему он всё ещё нужен в 2005–2014? Он делает в 2005–2014 то же, что делал в 2000. Если нагрузка на создание/удаление временных таблиц достаточно высока, вы всё ещё можете наблюдать конкуренцию за краткие блокировки, поскольку кэш временных таблиц не всегда способен полностью избавить от необходимости создавать реальные новые временные таблицы, вместо того чтобы брать их «с полки». В таком случае применение trace flag для перехода к выделению на уровне экстентов (в точности так же, как в 2000) может помочь, как и создание дополнительных файлов данных tempdb.
Что касается файлов данных, то число изменилось. Вместо соответствия 1‑в‑1 между ядрами и файлами данных tempdb (ЕСЛИ наблюдается конкуренция за краткие блокировки) теперь так много файлов не нужно. [Правка 2012:] Совет, которым я пользуюсь, тот, что Боб Уорд из CSS озвучил на PASS в 2011: если у вас меньше 8 ядер, используйте число файлов = числу ядер. Если больше 8 ядер, начните с 8 файлов, и если конкуренция сохранится, добавляйте ещё по 4 файла. Это официальная рекомендация Microsoft в статье Рекомендации по сокращению состязания выделения ресурсов в базе данных tempdb SQL Server.
6) Почему DBCC IND всё равно показывает две страницы, даже при включённом TF1118? Я слышал, что вывод DBCC IND порой вводит в заблуждение, когда TF1118 включён. Создание временной таблицы с одной строкой покажет в выводе DBCC всего две страницы — одну IAM и одну страницу данных. Да, это совершенно верно: выделены только две страницы, но страница данных поступает из выделенного экстента, а не из смешанного. (Страницы IAM всегда выделяются по одной из смешанных экстентов.)
А теперь доказательство для SQL Server 2014.
SELECT @@VERSION;
GO
Microsoft SQL Server 2014 – 12.0.4422.0 (X64)
Сначала создам временную таблицу при выключенном TF1118 и посмотрю, какие страницы таблица получила, заглянув в первую IAM. Я возьму временную таблицу с размером строки больше 8000 байт и вставлю две строки — чтобы для наглядности было две страницы данных.
DBCC TRACEOFF (1118, -1);
GO
USE tempdb;
GO
CREATE TABLE #temp (c1 INT IDENTITY, c2 CHAR (8000) DEFAULT 'a');
GO
INSERT INTO #temp DEFAULT VALUES;
GO 2
Теперь найду первую страницу IAM с помощью моего скрипта sp_AllocationMetadata и посмотрю дамп страницы через DBCC PAGE, чтобы увидеть, какие одиночные страницы она отслеживает и какие выделенные экстенты закреплены за таблицей:
EXEC sp_AllocationMetadata '#temp';
GO
Object Name Index ID Alloc Unit ID Alloc Unit Type First Page Root Page First IAM Page
-------------- --------- -------------------- ---------------- ----------- ---------- ---------------
#temp__ 0 1441151881544663040 IN_ROW_DATA (1:289) (0:0) (9:10)
DBCC TRACEON (3604);
GO
DBCC PAGE ('tempdb', 9, 10, 3);
GO
<snip>
IAM: Single Page Allocations @0x000000001447A08E
Slot 0 = (1:289) Slot 1 = (8:16) Slot 2 = (0:0)
Slot 3 = (0:0) Slot 4 = (0:0) Slot 5 = (0:0)
Slot 6 = (0:0) Slot 7 = (0:0)
IAM: Extent Alloc Status Slot 1 @0x000000001447A0C2
(1:0) - (1:3192) = NOT ALLOCATED
Как отчётливо видно из фрагмента дампа страницы IAM, имеются две одиночные страницы и никаких экстентов, выделенных этой временной таблице. Это и должно происходить, когда TF1118 не включён.
Теперь сделаю то же самое при включённом TF1118.
USE tempdb;
GO
DROP TABLE #temp;
GO
DBCC TRACEON (1118, -1);
GO
CREATE TABLE #temp (c1 INT IDENTITY, c2 CHAR (8000) DEFAULT 'a');
GO
INSERT INTO #temp DEFAULT VALUES;
GO 2
EXEC sp_AllocationMetadata '#temp';
GO
Object Name Index ID Alloc Unit ID Alloc Unit Type First Page Root Page First IAM Page
-------------- --------- -------------------- ---------------- ----------- ---------- ---------------
#temp__ 0 1513209475623550976 IN_ROW_DATA (8:96) (0:0) (7:11)
DBCC TRACEON (3604);
GO
DBCC PAGE ('tempdb', 7, 11, 3);
GO
<snip>
IAM: Single Page Allocations @0x000000001A78A08E
Slot 0 = (0:0) Slot 1 = (0:0) Slot 2 = (0:0)
Slot 3 = (0:0) Slot 4 = (0:0) Slot 5 = (0:0)
Slot 6 = (0:0) Slot 7 = (0:0)
IAM: Extent Alloc Status Slot 1 @0x000000001A78A0C2
(8:0) - (8:88) = NOT ALLOCATED
(8:96) - = ALLOCATED
(8:104) - (8:3192) = NOT ALLOCATED
Теперь, как вы ясно видите, одиночных страниц нет, а таблице выделён один экстент. Доказательство, что TF1118 по‑прежнему делает именно то, что должен, в SQL Server 2014.
Теперь запустим DBCC IND для таблицы:
DBCC IND ('tempdb', '#temp', -1);
GO
PageFID PagePID IAMFID IAMPID ObjectID IndexID
------- ----------- ------ ----------- ----------- -------
7 11 NULL NULL -1354579385 0
8 96 7 11 -1354579385 0
8 97 7 11 -1354579385 0
(Для наглядности я убрал часть хвостовых столбцов)
Видим, что по‑прежнему перечислены только две страницы данных (8:96, 8:97) и страница IAM (7:11) — хотя весь экстент был выделен временной таблице, фактически были выделены и использованы лишь две страницы; остальные зарезервированы под эту таблицу, но остаются не распределёнными.
Надеюсь, эта публикация прояснила многие вопросы вокруг этого trace flag и его действия.
Комментариев нет:
Отправить комментарий