В SQL Server 2014 появилось несколько новшеств, способных увеличить быстродействие ваших решений: поддержка определений встроенных индексов, типы таблиц, оптимизированных для размещения в оперативной памяти, возвращающие табличное значение параметры (TVP), параллельные инструкции SELECT INTO, нестрогая обязательная запись и увеличенная мощность отношений для табличных переменных. Последние два усовершенствования были «обратно перенесены» в SQL Server 2012. Некоторые новшества предназначены специально для временных объектов, тогда как другие носят более общий характер, хотя неизбежно затрагивают и временные объекты.
В примерах статьи, демонстрирующих новые возможности, используется тестовая база данных с именем PerformanceV3. Исходный текст для создания этой базы данных можно найти по адресу: http://tsql.solidq.com/books/source_code/PerformanceV3.zip.
После выполнения этого сценария убедитесь, что задан контекст базы данных для PerformanceV3 (см. листинг 1).
В некоторых примерах используется вспомогательная табличная функция GetNums, которая принимает целочисленные входные данные @low и @high и возвращает последовательность целых чисел в заданном диапазоне. Используйте программный код листинга 2 для создания функций GetNums в тестовой базе данных.
Определение встроенных индексов
Если рассматривать индексацию, то до появления SQL Server 2014 табличные переменные находились в невыгодном положении по сравнению с регулярными и временными таблицами. После того как вы объявите табличную переменную, вы не можете изменить ее определение. Это означает, что если требовались индексы в табличных переменных, то можно было только задать их косвенно через встроенные определения ограничений PRIMARY KEY и UNIQUE. Например, нельзя было определить неуникальные индексы.
Как известно, одно из самых важных новшеств SQL Server 2014 — оптимизация в памяти In-Memory OLTP (http://msdn.microsoft.com/en-us/library/dn133186.aspx) с поддержкой оптимизированных для размещения в оперативной памяти таблиц, хеша и индексов BW-Tree, а также хранимых процедур, скомпилированных в собственном коде. В первоначальном варианте не было возможности изменить определение оптимизированной для размещения в оперативной памяти таблицы после ее создания. Поэтому компании Microsoft пришлось обеспечить поддержку встроенных определений индексов как составной части синтаксиса создания таблиц. Соответствующая работа уже была проведена в средстве синтаксического анализа, и разработчики решили распространить такой синтаксис и на дисковые таблицы вместе с табличными переменными. В результате в SQL Server 2014 можно определить встроенные неуникальные индексы как часть определения табличной переменной (см. листинг 3).
Точно так же, как в случае с ограничениями уровня столбцов и уровня таблиц, вы можете определить индексы на уровне столбцов и таблиц. Предшествующий синтаксис обычно используется, когда в индексе имеется единственный ключевой столбец, как в приведенном примере с индексом по col1. Второй индекс следует использовать, когда индекс составной, как индекс по col2 и col3. Вы можете указать, является индекс кластерным или некластерным. Сейчас встроенные индексы не поддерживают параметры UNIQUE, INCLUDE и WHERE. Отсутствие параметра UNIQUE — не проблема, поскольку всегда можно определить ограничение UNIQUE или PRIMARY KEY, в результате чего скрыто создается уникальный индекс. Надеюсь, в дальнейшем будет реализована поддержка параметров INCLUDE и WHERE.
Типы таблиц, оптимизированных для размещения в оперативной памяти, и параметры TVP
В SQL Server 2008 появилась поддержка типов таблиц (http://msdn.microsoft.com/en-us/library/ms175007.aspx) и возвращающих табличное значение параметров (TVP) (http://msdn.microsoft.com/en-us/library/bb510489.aspx). До выхода версии SQL Server 2014 табличная переменная на основе табличного типа всегда была представлена как набор страниц в tempdb. К таблицам, оптимизированным для размещения в оперативной памяти, и хранимым процедурам, скомпилированным в собственном коде, реализованным в SQL Server как часть In-Memory OLTP, специалисты Microsoft добавили поддержку типов таблиц, оптимизированных для размещения в оперативной памяти, и параметров TVP. Первоначально предполагалось обеспечить возможность объявлять табличную переменную типа таблиц, оптимизированных для размещения в оперативной памяти, заполнять ее строками и передавать как параметр TVP хранимой процедуре, скомпилированной в собственном коде. Но ничто не мешает создать табличные переменные на основе типов таблиц, оптимизированных для размещения в оперативной памяти, и использовать их для других целей, в том числе передавать в качестве параметров TVP регулярным процедурам. Таким образом можно реализовать преимущества производительности, свойственные структурам, оптимизированным для размещения в оперативной памяти, и избежать дискового представления в tempdb. Просто помните, что начальная реализация компонента In-Memory OLTP в SQL Server 2014 с использованием в запросе таблиц, оптимизированных для размещения в оперативной памяти, мешает параллелизму. Поэтому обязательно проведите тестирование, чтобы сравнить типы дисковых таблиц и параметры TVP с соответствующими элементами, оптимизированными для размещения в оперативной памяти, и выяснить, какие из них больше подходят для вашего случая.
Для использования этих возможностей необходимо добавить в базу данных файловую группу для данных, оптимизированных для размещения в оперативной памяти (отмеченную как CONTAINS MEMORY_OPTIMIZED_DATA), и контейнер, указывающий на папку в файловой системе, подобно тому как это делается в FILESTREAM. Родительская папка уже должна существовать, а дочерняя папка не должна существовать во время добавления контейнера. Поэтому, прежде чем выполнить программный код, убедитесь, что папка C:\IMOLTP\ существует, а папка C:\IMOLTP\PerformanceV3_dir\ не существует (или измените имена папок по своему выбору) (см. листинг 4).
Чтобы создать тип таблицы, оптимизированной для размещения в оперативной памяти, добавьте параметр MEMORY_OPTIMIZED = ON к определению типа. В качестве примера код в листинге 5 создает тип таблицы OrderIDs, который представляет собой набор идентификаторов заказов.
Таблица, оптимизированная для размещения в оперативной памяти, должна располагать хотя бы одним индексом, чтобы получить доступ к строкам в памяти. Это может быть индекс BW-Tree (разновидность индекса B-tree без блокировок, без защелок), как приведенный в моем примере, или хеш-индекс. Первый эффективен для действий в диапазоне и по порядку. Второй оптимален для точечных запросов. Чтобы сделать индекс в нашем примере хеш-индексом, добавьте HASH WITH (BUCKET_COUNT =
Определив тип таблицы, можно использовать его в качестве типа табличных переменных, например, как в листинге 6.
Как отмечалось выше, еще можно использовать тип таблицы, оптимизированной для размещения в оперативной памяти, как тип для параметров TVP. Например, в программном коде в листинге 7 создается процедура, принимающая параметр @OrderIDs типа OrderIDs с входным набором идентификаторов заказов. Возвращается информация о запрошенных заказах.
Используйте программный код листинга 8 для выполнения процедуры с набором идентификаторов заказов {1759, 1760, 1761} в качестве входных данных. В приведенной таблице показаны выходные данные.
| Таблица. Выходные данные процедуры GetOrders |
.jpg) |
После завершения выполните программный код листинга 9 для очистки.
В SQL Server 2014 нельзя удалить файловую группу, оптимизированную для размещения в оперативной памяти. Чтобы это сделать, нужно удалить и повторно создать тестовую базу данных.
Параллельная инструкция SELECT INTO
До появления SQL Server 2014 инструкцию SELECT INTO нельзя было обрабатывать параллельно. В частности собственно элемент вставки (оператор Table Insert) всегда обрабатывался в последовательной зоне. В SQL Server 2014 появилась возможность параллельной обработки SELECT INTO. Красота этого решения в том, что оно не требует никаких изменений со стороны пользователя, помимо использования SQL Server 2014. Благодаря параллельной обработке достигается существенное повышение производительности. Возможности не ограничиваются использованием временных таблиц в качестве цели, но SELECT INTO очень часто используется для создания и заполнения целевой временной таблицы.
В качестве примера я выполнил приведенный в листинге 10 программный код как в SQL Server 2012, так и в SQL Server 2014.
На рисунке 1 показан план для SQL Server 2012 (с последовательным оператором Table Insert), а на рисунке 2 — план, полученный для SQL Server 2014 (с параллельным оператором Table Insert).
.jpg) |
| Рисунок 1. План для SQL Server 2012 |
.jpg) |
| Рисунок 2. План для SQL Server 2014 |
Обязательные операции записи
В SQL Server используется функция, известная как «обязательные операции записи» (eager writes), благодаря которой групповые операции не заполняют память новыми страницами за счет существующих кэшированных страниц. Идея заключается в отслеживании циклического списка из 32 «грязных» страниц. Когда список полон, eager writes записывают их на диск, чтобы освободить список для нового набора страниц. Недостаток этой функции в том, что при кратковременных групповых операциях в tempdb (например, SELECT INTO #TempTable, CREATE INDEX WITH SORT_IN_TEMPDB и др.) в конечном итоге возникает интенсивная физическая активность диска, которая негативно отражается на производительности операции. В SQL Server 2014 реализовано дополнение, облегчающее обязательные операции записи для любой страницы, на которую ведется запись групповой операции. Например, если хранимая процедура создает и заполняет временную таблицу с помощью инструкции SELECT INTO и быстро завершается, то, скорее всего, физическая активность диска будет отсутствовать.
Чтобы получить информацию об обязательных операциях записи, можно установить в сеансе флаг трассировки 3917. Также необходимо установить флаг трассировки 3604, чтобы получить выходные данные, направляемые клиенту, или флаг 3605, чтобы получить данные, направляемые в журнал ошибок.
Достоинство этой функции в том, что, как и в случае с параллельной инструкцией SELECT INTO, пользователю не требуется изменять свой программный код. Производительность просто повышается, если использовать версию продукта с номером сборки, которая поддерживает это улучшение. Это новшество появилось в SQL Server 2014, но позднее было перенесено в предыдущие версии — SQL Server 2012 SP1 CU10 и SQL Server 2012 SP2 CU1 (http://support2.microsoft.com/kb/2958012/en-us? sd=rss&spid=1044).
Дополнительные сведения об улучшениях в обязательных операциях записи можно найти в блоге CSS SQL Server Engineers (http://blogs.msdn.com/b/psssql/archive/2014/04/09/sql-server-2014-tempdb-hidden-performance-gem.aspx).
Кардинальность с флагом трассировки 2453
Последнее новшество, рассматриваемое в этой статье, относится к табличным переменным и доступно в версиях SQL Server 2014 RTM CU3 и SQL Server 2012 SP2. Справочную информацию можно найти по адресу: (http://support.microsoft.com/kb/2952444/en-us).
Как известно, в SQL Server нет средств статистики, таких как гистограммы и векторы плотности для табличных переменных. Однако SQL Server ведет подсчет строк в таблице, что в некоторых случаях может быть очень полезно для оптимизатора. Хороший пример — ситуация, когда нужно сохранить набор ключей, скажем, идентификаторов заказа, а затем объединить табличную переменную с пользовательской таблицей, чтобы получить данные из связанных строк. При малом числе значений в табличной переменной оптимальная стратегия — использовать последовательный план с алгоритмом соединения вложенными циклами. При большом числе значений предпочтительно использовать параллельный план с алгоритмом хеш-соединения. Хотя SQL Server учитывает число строк в табличной переменной, эти сведения обычно недоступны оптимизатору. Причина в том, что первоначальная единица оптимизации представляет собой целый пакет, а не единственную инструкцию (в отличие от единицы повторной компиляции). Оптимизатор не выполняет программный код, который заполняет табличную переменную перед оптимизацией запроса; поэтому оптимизация запроса производится без сведений о числе строк. По умолчанию предполагается, что таблица очень мала (обычно состоит из одной строки).
В качестве примера рассмотрим программный код, приведенный в листинге 11.
План для запроса показан на рисунке 3. Обратите внимание, что прогнозируемое число строк равно 1, хотя в действительности их 100 000. В результате оптимизатор выбирает последовательный план с алгоритмом соединения с вложенными циклами.
.jpg) |
| Рисунок 3. План для соединения с вложенными циклами |
Типовое решение этой задачи — заставить SQL Server перекомпилировать запрос при каждом выполнении программного кода, указывая параметр RECOMPILE. В нашем случае просто уберите символ комментария в коде. Если данный параметр применяется, код оптимизируется на уровне инструкций после заполнения табличной переменной, поэтому число строк известно оптимизатору. Выполните программный код после удаления символа комментария параметра. План для запроса показан на рисунке 4.
.jpg) |
| Рисунок 4. План для соединения с хешем |
Обратите внимание, что на этот раз мощность связи точна и потому оптимизатор выбирает параллельный план с алгоритмом хеш-соединения.
Таким образом, благодаря параметру RECOMPILE вы получаете эффективный план на основе известного числа строк, но за это приходится платить перекомпиляцией при каждом выполнении.
Другой способ предоставить оптимизатору информацию о числе строк в табличной переменной — передать его хранимой процедуре в качестве входного параметра TVP. Оптимизатор может определить число строк, так как табличная переменная заполняется до того, как она будет передана в хранимую процедуру как TVP, то есть до начала оптимизации. Чтобы продемонстрировать это решение, сначала создайте тип таблицы с именем OrderIDs, выполнив программный код листинга 12.
Затем создайте хранимую процедуру с именем GetOrders, которая принимает параметр TVP типа таблицы OrderIDs в качестве входных данных и присоединяет таблицу Orders с входным TVP, чтобы возвратить информацию о запрошенных заказах (см. листинг 13).
Используйте программный код листинга 14, чтобы объявить табличную переменную типа таблицы OrderIDs, заполнить ее 100 000 идентификаторами заказов, а затем вызвать процедуру GetOrders с табличной переменной в качестве входного параметра TVP.
Вы получите такой же план, как показан на рисунке 4. Оптимизатор верно определяет мощность связи и выбирает эффективный план для размера входной таблицы.
Но что делать, если вы хотите задействовать хранимую процедуру с параметром TVP (например, нужно изменить табличную переменную), но не хотите выполнять принудительную перекомпиляцию при каждом выполнении кода? Компания Microsoft предложила решение в виде флага трассировки 2453, который упоминается как доступный в SQL Server 2014 RTM CU3 и SQL Server 2012 SP2. Если флаг трассировки установлен, изменения в табличных переменных приводят к перекомпиляции для нетривиальных планов на основе тех же пороговых значений, что и для других таблиц. Естественно, это ведет к уменьшению числа перекомпиляций по сравнению с принудительными перекомпиляциями при каждом выполнении кода. А когда перекомпиляция происходит, число строк становится известно оптимизатору. Любопытно, что, как объясняется в справочном материале для этого флага трассировки (http://support.microsoft.com/kb/2952444), в отличие от OPTION (RECOMPILE), он не приводит к встраиванию параметра в процессе перекомпиляции (parameter peeking). Для демонстрации данного решения (предполагается, что вы работаете с версией и сборкой, поддерживающими эту функцию), выполните программный код листинга 15.
Программный код устанавливает флаг трассировки 2453 на уровне сеанса (укажите -1 в качестве второго параметра, чтобы он действовал глобально), объявляет табличную переменную, заполняет ее 100 000 строками, а затем объединяет таблицу Orders с табличной переменной, чтобы возвратить запрошенные заказы. К табличной переменной добавляется достаточное число строк, чтобы запустить перекомпиляцию на уровне инструкций, что в свою очередь позволяет оптимизатору правильно определить число строк в таблице. В результате для данного случая вы получаете тот же эффективный план, который показан на рисунке 4.
Чтобы снять флаг трассировки, используйте следующий программный код:
DBCC TRACEOFF (2453);
используйте -1 в качестве второго параметра, чтобы обеспечить глобальный эффект
Итак, мы рассмотрели пять усовершенствований в SQL Server 2014, два из которых были перенесены в SQL Server 2012. Некоторые улучшения, такие как определения встроенных индексов, типы таблиц, оптимизированных для размещения в оперативной памяти, и параметры TVP, представляют собой новые инструменты, которые вы будете использовать в новом программном коде. Другие новшества, такие как параллельные инструкции SELECT INTO и облегченные обязательные операции записи, — внутренние и не требуют никаких изменений в программном коде. Необходимо просто использовать нужные версию и сборку, и работа автоматически начинает выполняться быстрее. Оба типа улучшений можно только приветствовать.
--------------------------------------------------------------------- -- T-SQL Black-Belt 2015-04, Itzik Ben-Gan -- Improvements in Table Variables and Temporary Tables in SQL Server 2014 --------------------------------------------------------------------- -- Убедитесь, что база данных PerformanceV3 установлена -- Сценарий установки: http://tsql.solidq.com/books/source_code/PerformanceV3.zip USE PerformanceV3; GO
IF OBJECT_ID(N'dbo.GetNums', N'IF') IS NOT NULL DROP FUNCTION dbo.GetNums; GO CREATE FUNCTION dbo.GetNums(@low AS BIGINT, @high AS BIGINT) RETURNS TABLE AS RETURN WITH L0 AS (SELECT c FROM (SELECT 1 UNION ALL SELECT 1) AS D(c)), L1 AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B), L2 AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B), L3 AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B), L4 AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B), L5 AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B), Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum FROM L5) SELECT TOP(@high - @low + 1) @low + rownum - 1 AS n FROM Nums ORDER BY rownum; GO
DECLARE @T1 AS TABLE ( keycol INT NOT NULL PRIMARY KEY NONCLUSTERED, col1 INT NOT NULL INDEX idx_cl_col1 CLUSTERED, -- column index col2 INT NOT NULL, col3 INT NOT NULL, INDEX idx_nc_col2_col3 NONCLUSTERED (col2, col3) -- table index );
ALTER DATABASE PerformanceV3 ADD FILEGROUP PerformanceV3_MO CONTAINS MEMORY_OPTIMIZED_DATA; GO ALTER DATABASE PerformanceV3 ADD FILE ( NAME = PerformanceV3_dir, FILENAME = 'C:\IMOLTP\PerformanceV3_dir' ) TO FILEGROUP PerformanceV3_MO; GO
F TYPE_ID(N'dbo.OrderIDs') IS NOT NULL DROP TYPE dbo.OrderIDs; CREATE TYPE dbo.OrderIDs AS TABLE ( orderid INT NOT NULL PRIMARY KEY NONCLUSTERED ) WITH (MEMORY_OPTIMIZED = ON);
DECLARE @MyOrderIDs AS dbo.OrderIDs; INSERT INTO @MyOrderIDs (orderid) VALUES (1759), (1760), (1761); SELECT * FROM @MyOrderIDs;
IF OBJECT_ID(N'dbo.GetOrders', N'P') IS NOT NULL DROP PROC dbo.GetOrders; GO CREATE PROC dbo.GetOrders ( @OrderIDs AS dbo.OrderIDs READONLY ) AS SELECT O.orderid , O.orderdate , O.custid , O.empid , O.filler FROM dbo.Orders AS O INNER JOIN @OrderIDs AS I ON O.orderid = I.orderid ORDER BY I.orderid; GO
DECLARE @MyOrderIDs AS dbo.OrderIDs; INSERT INTO @MyOrderIDs (orderid) VALUES (1759), (1760), (1761); EXEC dbo.GetOrders @OrderIDs = @MyOrderIDs;
IF OBJECT_ID(N’dbo.GetOrders’, N’P’) IS NOT NULL DROP PROC dbo.GetOrders; IF TYPE_ID(N’dbo.OrderIDs’) IS NOT NULL DROP TYPE dbo.OrderIDs;
IF OBJECT_ID(N'tempdb..#MyOrders', N'U') IS NOT NULL DROP TABLE #MyOrders; GO SELECT * INTO #MyOrders FROM PerformanceV3.dbo.Orders; GO
DECLARE @OrderIDs AS TABLE ( orderid INT NOT NULL PRIMARY KEY ); INSERT INTO @OrderIDs (orderid ) SELECT N.n AS orderid FROM dbo.GetNums(900001, 1000000) AS N; -- http://tsql.solidq.com/GetNums.txt SELECT O.orderid , O.orderdate , O.custid , O.empid , O.filler FROM @OrderIDs AS K INNER JOIN dbo.Orders AS O ON O.orderid = K.orderid --OPTION(RECOMPILE);
IF TYPE_ID(N'dbo.OrderIDs') IS NOT NULL DROP TYPE dbo.OrderIDs; CREATE TYPE dbo.OrderIDs AS TABLE ( orderid INT NOT NULL PRIMARY KEY NONCLUSTERED );
IF TYPE_ID(N'dbo.OrderIDs') IS NOT NULL DROP TYPE dbo.OrderIDs; CREATE TYPE dbo.OrderIDs AS TABLE ( orderid INT NOT NULL PRIMARY KEY NONCLUSTERED ); IF OBJECT_ID(N'dbo.GetOrders', N'P') IS NOT NULL DROP PROC dbo.GetOrders; GO CREATE PROC dbo.GetOrders ( @OrderIDs AS dbo.OrderIDs READONLY ) AS SELECT O.orderid , O.orderdate , O.custid , O.empid , O.filler FROM dbo.Orders AS O INNER JOIN @OrderIDs AS I ON O.orderid = I.orderid; GO
DECLARE @MyOrderIDs AS dbo.OrderIDs; INSERT INTO @MyOrderIDs(orderid) SELECT N.n AS orderid FROM dbo.GetNums(900001, 1000000) AS N; EXEC dbo.GetOrders @OrderIDs = @MyOrderIDs;
DBCC TRACEON(2453); -- используйте -1 в качестве второго параметра, чтобы обеспечить глобальный эффект DECLARE @OrderIDs AS TABLE ( orderid INT NOT NULL PRIMARY KEY ); INSERT INTO @OrderIDs(orderid) SELECT N.n AS orderid FROM dbo.GetNums(900001, 1000000) AS N; SELECT O.orderid , O.orderdate , O.custid , O.empid , O.filler FROM @OrderIDs AS K INNER JOIN dbo.Orders AS O ON O.orderid = K.orderid;