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

Статья
04/26/2023

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

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

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

Конвейер обработки запросов в 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.

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

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

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

Примечание.

В документации термин B-tree обычно используется в ссылке на индексы. В индексах rowstore ядро СУБД реализует дерево B+. Это не относится к индексам columnstore или индексам в таблицах, оптимизированных для памяти. Дополнительные сведения см. в руководстве по архитектуре и проектированию индексов SQL Sql Server и Azure.

Интерпретируемый доступ Transact-SQL к таблицам, оптимизированным для памяти

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

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

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

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

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.

См. также

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

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