Кэш временных таблиц в SQL Server (SQL Server temporary table caching)

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

В данной статье мы попытаемся разобраться как разрешать кэширование временных таблиц и сравним производительность для cached vs. non-cached temporary tables.

Опция кэширование временных таблиц доступна начиная с версии MS SQL 2005. При соблюдении определенных условий метаданные временной таблицы будут оставаться в tempdb даже когда запрос пользователь выполнил свою задачу. С точки зрения пользователя временная таблица больше недоступна, т.к. она удалена. Но, когда пользовательская сессия повторно выполняет подобный запрос, который создает временную таблицу, SQL сервер может использовать временную таблицу, которая была создана ранее.

Ниже перечислениы условия для включения опции использования кэша временных таблиц:
— явно не объявлены ограничения (constraints)
— DDL инструкция, которая затрагивает временную таблицу, не выполняется сразу после ее создания (прим. CREATE INDEX/CREATE STATISTICS)
— временная таблица создана не с помощью динамического SQL
— временная таблица создана внутри объекта БД (процедура или триггер)

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

Сравнение DDL производительности кэшированных и некэшированных таблиц

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

Для тестов использовался SQL Server 2016.

USE tempdb
GO

CREATE PROCEDURE dbo.sp_NewTmpTable
AS
BEGIN
 SET NOCOUNT ON;

 CREATE TABLE #Tmp (A INT IDENTITY(1,1), B varchar(20))

 CREATE UNIQUE CLUSTERED INDEX AA ON #Tmp (A)

END
GO

CREATE PROCEDURE dbo.sp_CachedTmpTable
AS
BEGIN
 SET NOCOUNT ON;

 CREATE TABLE #Tmp (A INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, B varchar(20))

END
GO

Сделаем запрос в sys.dm_os_performance_counters для получения Temp Tables Creation Rate. Служба MS SQL была предварительно перезапущена, поэтому результат рейта будет равен 0.

SELECT * FROM sys.dm_os_performance_counters 
WHERE counter_name = 'Temp Tables Creation Rate' 
AND OBJECT_NAME = 'SQLServer:General Statistics' 
GO

Результат:sql-server-temporary-table-caching-p1

 

 

Откроем новую сессию и вызовем процедуру dbo.sp_NewTmpTable 50 раз и проверим опять Temp Tables Creation Rate. Как видим результат равен 50, как следствие повторного вызова процедуры. В данном случае временная таблица не кэшируется (хоть и создана внутри процедуры), т.к. внутри процедуры сразу за инструкцией создания таблица следует вызов DDL инструкции.

EXEC tempdb.dbo.pr_NewTmpTable
GO 50

SELECT * FROM sys.dm_os_performance_counters
WHERE counter_name = 'Temp Tables Creation Rate'
AND OBJECT_NAME = 'SQLServer:General Statistics'      
GO

Результат:
sql-server-temporary-table-caching-p2

 

А теперь давайте повторим то же, но уже с процедурой dbo.sp_CachedTmpTable

EXEC tempdb.dbo.sp_CachedTmpTable
GO 50

SELECT * FROM sys.dm_os_performance_counters
WHERE counter_name = 'Temp Tables Creation Rate'
AND OBJECT_NAME = 'SQLServer:General Statistics'      
GO

Результат:
sql-server-temporary-table-caching-p3

 

Как видим, Temp Tables Creation Rate увеличился лишь на 1, т.к. временная таблица была кэширована и далее использована.

Предположим процедура sp_CachedTmpTable выполнена одновременно в 3-х разных сессиях. В таком случае Temp Tables Creation Rate должен быть увеличен на 3 единицы. В любой момент времени, когда параллельное выполнение sp_CachedTmpTable равно или менее 3-х, то Вы не увидите увеличение показателя Temp Tables Creation Rate Таким образом,  SQL сервер автоматически создаст кэшированные временные таблицы для удовлетворения одновременных запросов.

Чтобы показать DDL конкуренцию в tempdb давайте откроем 3 новых окна (сессии) в SSMS и начнем выполнение dbo.sp_NewTmpTable 50 000 раз в рамках каждой сессии примерно в то же время. Подобный тест проведем и для процедуры dbo.sp_CachedTmpTable. Более того проведем тест для следующей ad-hoc инструкции:

SET NOCOUNT ON;
DECLARE @counter int = 1

WHILE @counter <= 50000
BEGIN
 CREATE TABLE #Tmp (A INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, B varchar(20))
 DROP TABLE #Tmp

 SET @counter += 1
END

Ниже показаны сравнительные результаты выполнения 3х вариантов в 3х параллельных сессиях:

Сессия 1 Сессия 2 Сессия 3
tempdb.dbo.sp_NewTmpTable 1 мин. 8 сек. 1 мин. 7 сек. 1 мин. 8 сек
tempdb.dbo.sp_CachedTmpTable 28 сек. 28 сек. 28 сек.
ad-hoc запрос 1 мин. 26 сек. 1 мин. 37 сек. 1 мин. 36 сек.

Заключение

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

В наших тестах мы видим, что в процедуре dbo.sp_NewTmpTable не работает опция кэширования по причине того, что внутри процедуры присутствует явное именование индекса АА в конструкции. Если же мы изменим конструкцию создания PK и напишем «CREATE UNIQUE CLUSTERED INDEX», то SQL Server применит опцию кэширования при последующих вызовах этой процедуры в параллельных сессиях.

Стоит заметить, что не все последующие  DLL операции предотвращают опцию кэширования. Исключением является «DROP TABLE» DDL инструкция. Это означает, что в процедуре dbo.sp_CachedTmpTable последующее использование «DROP TABLE»,  после создания временной таблицы, не запретит серверу кэширование и последующее использование таблицы #Tmp

Оригинал статьи

Поделиться в соц. сетях

Опубликовать в Google Buzz
Опубликовать в Google Plus
Опубликовать в LiveJournal

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *