Руководство по обработке запросов для таблиц, оптимизированных для памяти

Статья
06/13/2017

В SQL ServerIn-Memory OLTP вводятся оптимизированные для памяти таблицы и скомпилированные в собственном коде хранимые процедуры. В данной статье приводится обзор обработки запросов для таблиц, оптимизированных для памяти, и хранимых процедур, скомпилированных в собственном коде.

Документ поясняет процесс компиляции и выполнения запросов к таблицам, оптимизированным для памяти, включая:

канал обработки запросов в SQL Server для дисковых таблиц;
оптимизацию запросов, роль статистических данных в таблицах, оптимизированных для памяти, а также рекомендации по решению проблем, связанных с неоптимальными планами запросов;
Использование интерпретированного Transact-SQL для доступа к таблицам, оптимизированным для памяти.
аспекты оптимизации запросов для доступа к таблицам, оптимизированным для памяти;
компиляция и обработка хранимых процедур, скомпилированных в собственном коде;
статистические данные, которые используются для оценки затрат оптимизатора;
способы исправления неоптимальных планов запросов;

Пример запроса

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

Мы рассмотрим две таблицы, Customer и Order. Следующий скрипт Transact-SQL содержит определения для этих двух таблиц и связанных индексов в их (традиционной) дисковой форме:

CREATE TABLE dbo.[Customer] (  
  CustomerID nchar (5) NOT NULL PRIMARY KEY,  
  ContactName nvarchar (30) NOT NULL   
)  
GO  
  
CREATE TABLE dbo.[Order] (  
  OrderID int NOT NULL PRIMARY KEY,  
  CustomerID nchar (5) NOT NULL,  
  OrderDate date NOT NULL  
)  
GO  
CREATE INDEX IX_CustomerID ON dbo.[Order](CustomerID)  
GO  
CREATE INDEX IX_OrderDate ON dbo.[Order](OrderDate)  
GO

Для конструирования планов запросов, показанных в данной статье, две таблицы были заполнены примерами данных из учебной базы данных Northwind, которую можно загрузить по следующий ссылке: Образцы баз данных Northwind и pubs для SQL Server 2000.

Рассмотрим следующий запрос, который выполняет соединение таблиц Customer и Order и возвращает идентификатор заказа и связанную с ним информацию о клиенте:

SELECT o.OrderID, c.* FROM dbo.[Customer] c INNER JOIN dbo.[Order] o ON c.CustomerID = o.CustomerID

Предполагаемый план выполнения в соответствии с отображением в SQL Server Management Studio следующий:

Запрос плана для соединения дисковых таблиц.
План запроса для соединения дисковых таблиц.

О данном плане запроса:

строки из таблицы Customer получены из кластеризованного индекса, который представляет собой основную структуру данных и содержит все данные таблицы;
данные из таблицы Order получены с помощью некластеризованного индекса в столбце CustomerID. Этот индекс содержит столбец CustomerID, который используется для соединения, и столбец первичного ключа OrderID, который возвращается пользователю. Для возвращения дополнительных столбцов из таблицы Order потребуется поиск по кластеризованному индексу для таблицы Order.
Логический оператор Inner Join реализован в форме физического оператора Merge Join. Остальные физические типы соединений — это Nested Loops и Hash Join. В операторе Merge Join используется то обстоятельство, что оба индекса отсортированы по столбцу соединения CustomerID.

Рассмотрим немного другую версию этого запроса, которая возвращает все строки из таблицы Order, а не только OrderID.

SELECT o.*, c.* FROM dbo.[Customer] c INNER JOIN dbo.[Order] o ON c.CustomerID = o.CustomerID

Предполагаемый план выполнения для этого запроса:

план запроса для хэш-соединений дисковых таблиц.

В этом запросе строки из таблицы заказов получаются с помощью кластеризованного индекса. Физический оператор Hash Match теперь используется для Inner Join. Кластеризованный индекс в таблице Order не отсортирован по столбцу CustomerID, поэтому для Merge Join потребуется оператор сортировки, который повлияет на производительность запроса. Обратите внимание на относительную стоимость оператора Hash Match (75%) по сравнению с затратами оператора Merge Join в предыдущем примере (46%). Оптимизатором также рассматривался оператор Hash Match из предыдущего примера, но оказалось, что оператор Merge Join обеспечивает лучшую производительность.

SQL Server Обработка запросов для дисковых таблиц

На следующей диаграмме показан поток обработки запросов в SQL Server для нерегламентированных запросов:

SQL Server конвейера обработки запросов.
Канал обработки запросов в SQL Server.

В этом сценарии:

Пользователь выполняет запрос.
Средство синтаксического анализа и алгебризатор создают дерево запросов с логическими операторами на основе текста Transact-SQL, отправленного пользователем.
Оптимизатор создает оптимизированный план запроса, содержащий физические операторы (например, соединения вложенных циклов). После оптимизации план может храниться в кэше планов. Это действие пропускается, если кэш планов уже содержит план для этого запроса.
Подсистема выполнения запросов обрабатывает интерпретацию плана запроса.
Для каждого оператора поиска в индексе, просмотра индекса и просмотра таблицы подсистема выполнения запрашивает строки из соответствующего индекса и табличных структур у методов доступа.
Методы доступа получают строки из индекса и страниц данных в буферном пуле, и по мере необходимости загружают страницы из диска в буферный пул.

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

Интерпретируемый доступ Transact-SQL к таблицам Memory-Optimized

Нерегламентированные пакеты и хранимые процедуры Transact-SQL также называют интерпретируемыми Transact-SQL. Термин «интерпретируемый» означает, что план запроса интерпретируется подсистемой выполнения запросов для каждого оператора в плане запроса. Подсистема выполнения считывает оператор и его параметры и выполняет операцию.

Интерпретируемый Transact-SQL можно использовать для доступа как к оптимизированным для памяти таблицам, так и к таблицам на основе дисков. На следующем рисунке показана обработка запросов для интерпретированного доступа Transact-SQL к оптимизированным для памяти таблицам.

Конвейер обработки запросов для доступа к оптимизированным для памяти таблицам с помощью интерпретируемого кода Transact-SQL.

Как показано на рисунке, конвейер обработки запросов в основном остается неизменным:

средство синтаксического анализа и алгебризатор строят дерево запроса;
оптимизатор запросов создает план выполнения;
подсистема выполнения запроса интерпретирует план выполнения;

Основное отличие от традиционного конвейера обработки запросов (рис. 2) заключается в том, что строки для оптимизированной для памяти таблицы получаются из буферного пула при помощи методов доступа. Вместо этого строки извлекаются из структур данных в памяти с помощью подсистемы In-Memory OLTP. Из-за различий в структурах данных оптимизатор в некоторых случаях выбирает разные планы, как показано в следующем примере.

Следующий скрипт Transact-SQL содержит оптимизированные для памяти версии таблиц Order и Customer, использующие хэш-индексы:

CREATE TABLE dbo.[Customer] (  
  CustomerID nchar (5) NOT NULL PRIMARY KEY NONCLUSTERED,  
  ContactName nvarchar (30) NOT NULL   
) WITH (MEMORY_OPTIMIZED=ON)  
GO  
  
CREATE TABLE dbo.[Order] (  
  OrderID int NOT NULL PRIMARY KEY NONCLUSTERED,  
  CustomerID nchar (5) NOT NULL INDEX IX_CustomerID HASH(CustomerID) WITH (BUCKET_COUNT=100000),  
  OrderDate date NOT NULL INDEX IX_OrderDate HASH(OrderDate) WITH (BUCKET_COUNT=100000)  
) WITH (MEMORY_OPTIMIZED=ON)  
GO

Тот же запрос, выполненный к таблицам, оптимизированным для памяти:

SELECT o.OrderID, c.* FROM dbo.[Customer] c INNER JOIN dbo.[Order] o ON c.CustomerID = o.CustomerID

Предполагаемый план:

План запроса для объединения таблиц, оптимизированных для памяти.
План запроса для соединения таблиц, оптимизированных для памяти.

Изучите следующие отличия от плана для того же запроса к дисковым таблицам (рисунок 1):

Этот план для таблицы Customer содержит операцию просмотра таблицы, а не просмотра кластеризованного индекса.
- Определение таблицы не содержит кластеризованный индекс.
- Кластеризованные индексы для таблиц, оптимизированных для памяти, не поддерживаются. Вместо этого каждая оптимизированная для памяти таблица должна содержать по крайней мере один некластеризованный индекс, а все индексы для оптимизированных в памяти таблиц могут эффективно получать все столбцы таблицы без сохранения их в индексе или ссылки на кластеризованный индекс.
Этот план содержит оператор Hash Match, а не Merge Join. Индексы в таблицах Order и Customer представляют собой хэш-индексы и, следовательно, не упорядочены. Оператор Merge Join потребовал бы добавления операторов сортировки, которые вызвали бы снижение производительности запроса.

скомпилированные в собственном коде хранимые процедуры

Скомпилированные в собственном коде хранимые процедуры Transact-SQL — это хранимые процедуры, скомпилированные в машинный код, а не интерпретируемые подсистемой выполнения запросов. Следующий скрипт создает скомпилированную в собственном коде хранимую процедуру, которая выполняет пример запроса (из раздела «Пример запроса»).

CREATE PROCEDURE usp_SampleJoin  
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER  
AS BEGIN ATOMIC WITH   
(  TRANSACTION ISOLATION LEVEL = SNAPSHOT,  
  LANGUAGE = 'english')  
  
  SELECT o.OrderID, c.CustomerID, c.ContactName   
FROM dbo.[Order] o INNER JOIN dbo.[Customer] c   
  ON c.CustomerID = o.CustomerID  
  
END

Скомпилированные в собственном коде хранимые процедуры компилируются при создании, а интерпретируемые хранимые процедуры компилируются в ходе первого выполнения. (Часть компиляции, в частности синтаксический анализ и алгебризация, выполняется при создании. Однако для интерпретируемых хранимых процедур оптимизация планов запросов выполняется при первом выполнении.) Логика перекомпиляции аналогична. При первом выполнении процедуры после перезапуска сервера выполняется перекомпиляция скомпилированных в собственном коде хранимых процедур. Интерпретируемые хранимые процедуры повторно компилируются, если в кэше планов отсутствует план запроса. В следующей таблице объединены случаи компиляции и повторной компиляции для скомпилированных в собственном коде и интерпретируемых хранимых процедур.

	Скомпилированные в собственном коде	Доступ к оптимизированным для памяти таблицам с помощью интерпретируемого кода
Первичная компиляция	При создании.	При первом выполнении.
Автоматическая повторная компиляция	При первом выполнении процедуры после перезапуска базы данных или сервера.	При перезапуске сервера. Либо при вытеснении из кэша планов, обычно вследствие изменений схемы или статистических данных или нагрузки на память.
Повторная компиляция вручную	Не поддерживается. Решение, позволяющее обойти эти ограничения, заключается в удалении и повторном создании хранимой процедуры.	Используйте ключевое слово `sp_recompile`. Можно вручную удалить план из кэша при помощи инструкции DBCC FREEPROCCACHE. Можно также создать хранимую процедуру с параметром WITH RECOMPILE; такая хранимая процедура будет повторно компилироваться при каждом выполнении.

Компиляция и обработка запросов

На следующей диаграмме показан процесс компиляции для скомпилированных в собственном коде хранимых процедур:

Собственная компиляция хранимых процедур.
Компиляция хранимых процедур в собственном коде.

Описание процесса

Пользователь выдает инструкцию CREATE PROCEDURE для SQL Server.
Средство синтаксического анализа и алгебризатор создают поток обработки для процедуры, а также деревья запросов для запросов Transact-SQL в хранимой процедуре.
Оптимизатор запросов создает оптимизированные планы выполнения запросов для всех запросов в данной хранимой процедуре.
Компилятор In-Memory OLTP принимает поток обработки с внедренными оптимизированными планами запросов и создает библиотеку DLL, которая содержит машинный код для выполнения хранимой процедуры.
Созданная библиотека DDL загружается в память.

Вызов хранимой процедуры, скомпилированной в собственном коде, транслируется в вызов функции из библиотеки DLL.

Выполнение скомпилированных в собственном коде хранимых процедур.
Выполнение хранимых процедур, скомпилированных в собственном коде.

Описание вызова хранимой процедуры, скомпилированной в собственном коде:

Пользователь выдает инструкциюEXEC usp_myproc.
Средство синтаксического анализа извлекает имя и параметры хранимой процедуры.

Если инструкция подготовлена, например, с помощью sp_prep_exec, анализатору не придется извлекать имя процедуры и параметры во время выполнения.
Среда выполнения In-Memory OLTP находит точки входа библиотеки DLL для хранимой процедуры.
Машинный код в DLL выполняется, и результаты возвращаются клиенту.

Пробное сохранение параметров

Интерпретируемые хранимые процедуры Transact-SQL компилируются при первом выполнении в отличие от скомпилированных в собственном коде хранимых процедур, которые компилируются во время создания. Если интерпретируемые хранимые процедуры компилируются при вызове, значения параметров, указанные для этого вызова, используются оптимизатором для создания плана выполнения. Такое использование параметров в процессе компиляции называется пробным сохранением параметров.

Пробное сохранение параметров не используется для компиляции хранимых процедур, скомпилированных в собственном коде. Предполагается, что у всех параметров хранимой процедуры значения UNKNOWN (неизвестны). Как и интерпретируемые хранимые процедуры, скомпилированные в собственном коде хранимые процедуры также поддерживают указание OPTIMIZE FOR. Дополнительные сведения см. в разделе Указания запросов (Transact-SQL).

Получение плана выполнения запроса для скомпилированных в собственном коде хранимых процедур

План выполнения запроса для скомпилированной в собственном коде хранимой процедуры можно получить с помощью предполагаемого плана выполнения в Среде Management Studio или с помощью параметра SHOWPLAN_XML в Transact-SQL. Пример:

SET SHOWPLAN_XML ON  
GO  
EXEC dbo.usp_myproc  
GO  
SET SHOWPLAN_XML OFF  
GO

План выполнения, созданный с помощью оптимизатора запросов, состоит из дерева с операторами запроса на узлах и конечных узлах дерева. Структура дерева определяет механизм взаимодействия (поток строк от одного оператора к другому) между операторами. В графическом представлении SQL Server Management Studioизображен поток справа налево. Например, план запроса в диаграмме 1 содержит два оператора просмотра индекса, которые передают строки оператору соединения слиянием. Оператор соединения слиянием merge join передает строки оператору выбора select. Наконец, оператор Select возвращает строки клиенту.

Операторы запросов в хранимых процедурах, скомпилированных в собственном коде

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

Оператор	Пример запроса
SELECT	`SELECT OrderID FROM dbo.[Order]`
INSERT	`INSERT dbo.Customer VALUES ('abc', 'def')`
UPDATE	`UPDATE dbo.Customer SET ContactName='ghi' WHERE CustomerID='abc'`
DELETE	`DELETE dbo.Customer WHERE CustomerID='abc'`
Compute Scalar	Этот оператор используется как для встроенных функций, так и для преобразований типов. Не все функции и преобразования типов поддерживаются в хранимых процедурах, скомпилированных в собственном коде. `SELECT OrderID+1 FROM dbo.[Order]`
Соединение вложенными циклами	Nested Loops — единственный оператор соединения, который поддерживается в хранимых процедурах, скомпилированных в собственном коде. Все планы, содержащие соединения, будут использовать оператор Вложенные циклы, даже если план для того же запроса, выполняемого как интерпретируемый Transact-SQL, содержит хэш-соединение или соединение слиянием. `SELECT o.OrderID, c.CustomerID` `FROM dbo.[Order] o INNER JOIN dbo.[Customer] c`
Сортировка	`SELECT ContactName FROM dbo.Customer` `ORDER BY ContactName`
TOP	`SELECT TOP 10 ContactName FROM dbo.Customer`
Оператор Top-sort	Выражение `TOP` (количество возвращаемых строк) не может превышать 8000 строк. Если в запросе есть операторы объединения и агрегирования, то строк должно быть еще меньше. Соединения и агрегатные выражения обычно уменьшают количество строк для сортировки в сравнении с количеством строк в базовых таблицах. `SELECT TOP 10 ContactName FROM dbo.Customer` `ORDER BY ContactName`
Статистическое выражение потока	Обратите внимание, что оператор Hash Match для статической обработки не поддерживается. Таким образом, для всех агрегатов в скомпилированных в собственном коде хранимых процедурах используется оператор Stream Aggregate, даже если в плане для одного и того же запроса в интерпретируемом Transact-SQL используется оператор Hash Match. `SELECT count(CustomerID) FROM dbo.Customer`

Статистика столбцов и соединения

SQL Server ведет статистику значений в ключевых столбцах индекса, что позволяет оценить стоимость отдельных операций, таких как просмотр индекса и поиск в индексе. (SQL Server также создает статистику по ключевым столбцам вне индекса, если они создаются явно или если оптимизатор запросов создает их в ответ на запрос с предикатом.) Основной показатель в оценке стоимости — количество строк, обрабатываемых одним оператором. Обратите внимание, что для дисковых таблиц количество страниц, к которым обращается конкретный оператор, является существенным для оценки стоимости. Тем не менее, поскольку количество страниц не имеет значения для таблиц, оптимизированных для памяти (оно всегда равно нулю), мы сосредоточимся на количестве строк. Оценка начинается с операторов поиска в индексе и просмотра индекса в плане, а затем дополняется другими операторами, например, операторами соединения. Предполагаемое количество строк, которые будет обрабатывать оператор соединения, основано на оценке базовых операторов поиска и просмотра индекса. Для интерпретированного доступа Transact-SQL к таблицам, оптимизированным для памяти, можно просмотреть фактический план выполнения, чтобы увидеть разницу между предполагаемым и фактическим числом строк для операторов в плане.

Например, на рисунке 1

Оператор просмотра кластеризованного индекса в таблице Customer: предполагаемое количество — 91; реальное — 91;
Просмотр некластеризованного индекса в CustomerID: предполагаемое 830, реальное 830.
Оператор соединения слиянием: предполагаемое 815; реальное 830.

Оценки для оператора просмотра индекса являются точными. SQL Server поддерживает количество строк для дисковых таблиц. Оценки для всей таблицы и просмотра индекса всегда являются точными. Оценки для соединений также достаточно точные).

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

Ниже приводится план запроса:

SELECT o.OrderID, c.* FROM dbo.[Customer] c INNER JOIN dbo.[Order] o ON c.CustomerID = o.CustomerID

После удаления всех строк, кроме одной, в таблице Customer:

Статистика и соединения столбцов.

Относительно этого плана запроса:

Оператор Hash Match был заменен на физический оператор соединения Nested Loops.
Полный просмотр индекса в IX_CustomerID заменен поиском по индексу. В результате для полного просмотра индекса было просмотрено 5 строк вместо 830.

Статистика и количество элементов для таблиц, оптимизированных для памяти

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

См. также:

Таблицы, оптимизированные для памяти

Поделиться через