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

Область применения: 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.

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

1. Пользователь выполняет запрос.

2. Парсер и algebrizer создают дерево запросов с логическими операторами на основе текста Transact-SQL, отправленного пользователем.

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

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

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

6. Методы доступа получают строки из индекса и страниц данных в буферном пуле, и по мере необходимости загружают страницы из диска в буферный пул.

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

Примечание.

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

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

Нерегламентированные пакеты и хранимые процедуры 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 — это хранимые процедуры 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; такая хранимая процедура будет повторно компилироваться при каждом выполнении.

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

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

Компиляция хранимых процедур в машинный код.

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

1. Пользователь выдает инструкцию CREATE PROCEDURE в SQL Server.

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

3. Оптимизатор запросов создает оптимизированные планы выполнения запросов для всех запросов в данной хранимой процедуре.

4. Компилятор In-Memory OLTP принимает поток обработки с внедренными оптимизированными планами запросов и создает библиотеку DLL, которая содержит машинный код для выполнения хранимой процедуры.

5. Созданная библиотека DLL загружается в память.

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

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

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

1. Пользователь выполняет инструкцию EXECusp_myproc.

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

   Если инструкция подготовлена, например, с помощью sp_prep_exec, анализатору не придется извлекать имя процедуры и параметры во время выполнения.

3. Среда выполнения In-Memory OLTP находит точку входа библиотеки DLL для данной хранимой процедуры.

4. Машинный код в 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 OrderID FROM dbo.[Order]
ВСТАВИТЬ	INSERT dbo.Customer VALUES ('abc', 'def')
ОБНОВЛЕНИЕ	UPDATE dbo.Customer SET ContactName='ghi' WHERE CustomerID='abc'
Удалить	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 — единственный оператор соединения, поддерживаемый в нативно скомпилированных хранимых процедурах. Все планы запросов, содержащие соединения, будут использовать оператор Nested Loops, даже если план для того же запроса в интерпретации Transact-SQL содержит хэш-соединение или соединение слиянием.
Сортировать	SELECT ContactName FROM dbo.Customer ORDER BY ContactName
Верх	SELECT TOP 10 ContactName FROM dbo.Customer
Топ-сорт	SELECT TOP 10 ContactName FROM dbo.Customer ORDER BY ContactName	Выражение TOP (количество возвращаемых строк) не может превышать 8000 строк. Если в запросе есть операторы объединения и агрегирования, то строк должно быть еще меньше. Соединения и агрегатные выражения обычно уменьшают количество строк для сортировки в сравнении с количеством строк в базовых таблицах.
Потоковая агрегация	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.

См. также

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