Пошаговое руководство по функциям повышения производительности в SQL Server на Linux

Область применения: SQL Server — Linux

Если вы являетесь пользователем Linux, который является новым для SQL Server, то в следующих задачах описаны некоторые функции производительности. Это не уникальные или характерные для Linux, но это помогает дать вам представление о областях для дальнейшего изучения. В каждом примере приводится ссылка на подробную документацию по соответствующей теме.

Примечание.

В следующих примерах используется пример базы данных AdventureWorks2022. Инструкции по получению и установке этого образца базы данных см. в статье Восстановление базы данных SQL Server из Windows в Linux.

Создание индекса columnstore

Индекс columnstore — это технология хранения и запроса больших объемов данных с использованием формата хранения данных в столбцах, называемого columnstore.

1. Добавьте индекс columnstore в SalesOrderDetail таблицу, выполнив следующие команды Transact-SQL:

   CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_SalesOrderDetail_ColumnStore]
      ON Sales.SalesOrderDetail
      (UnitPrice, OrderQty, ProductID);
   GO
   

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

   SELECT ProductID, SUM(UnitPrice) SumUnitPrice, AVG(UnitPrice) AvgUnitPrice,
      SUM(OrderQty) SumOrderQty, AVG(OrderQty) AvgOrderQty
   FROM Sales.SalesOrderDetail
      GROUP BY ProductID
      ORDER BY ProductID;
   

3. Убедитесь, что индекс columnstore использовался путем поиска object_id индекса columnstore и подтверждения того, что он отображается в статистике использования для SalesOrderDetail таблицы:

   SELECT * FROM sys.indexes WHERE name = 'IX_SalesOrderDetail_ColumnStore'
   GO
   
   SELECT *
   FROM sys.dm_db_index_usage_stats
      WHERE database_id = DB_ID('AdventureWorks2022')
      AND object_id = OBJECT_ID('AdventureWorks2022.Sales.SalesOrderDetail');
   

Использование выполняющейся в памяти OLTP

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

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

1. Для использования выполняющейся в памяти OLTP рекомендуется задавать для базы данных уровень совместимости не ниже 130. Используйте следующий запрос, чтобы проверить текущий уровень AdventureWorks2022совместимости:

   USE AdventureWorks2022;
   GO
   SELECT d.compatibility_level
   FROM sys.databases as d
       WHERE d.name = DB_NAME();
   GO
   

   При необходимости установите уровень 130:

   ALTER DATABASE CURRENT
   SET COMPATIBILITY_LEVEL = 130;
   GO
   

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

   ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON;
   GO
   

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

   ALTER DATABASE AdventureWorks2022
      ADD FILEGROUP AdventureWorks_mod
         CONTAINS memory_optimized_data;
   GO
   ALTER DATABASE AdventureWorks2022
      ADD FILE (NAME='AdventureWorks_mod',
      FILENAME='/var/opt/mssql/data/AdventureWorks_mod')
         TO FILEGROUP AdventureWorks_mod;
   GO
   

Создание таблицы с оптимизацией для памяти

Основное хранилище для оптимизированных для памяти таблиц — это основная память, поэтому в отличие от таблиц на основе дисков данные не нужно считывать с диска в буферы памяти. Чтобы создать оптимизированную для памяти таблицу, используйте предложение MEMORY_OPTIMIZED = ON.

1. Выполните следующий запрос, чтобы создать оптимизированную для памяти таблицу dbo.ShoppingCart. По умолчанию данные сохраняются на диске для целей устойчивости (УСТОЙЧИВОСТЬ также можно задать только для сохранения схемы).

   CREATE TABLE dbo.ShoppingCart (
   ShoppingCartId INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
   UserId INT NOT NULL INDEX ix_UserId NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),
   CreatedDate DATETIME2 NOT NULL,
   TotalPrice MONEY
   ) WITH (MEMORY_OPTIMIZED=ON);
   GO
   

2. Вставьте в таблицу несколько записей:

   INSERT dbo.ShoppingCart VALUES (8798, SYSDATETIME(), NULL);
   INSERT dbo.ShoppingCart VALUES (23, SYSDATETIME(), 45.4);
   INSERT dbo.ShoppingCart VALUES (80, SYSDATETIME(), NULL);
   INSERT dbo.ShoppingCart VALUES (342, SYSDATETIME(), 65.4);
   

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

SQL Server поддерживает скомпилированные в собственном коде хранимые процедуры, которые обращаются к таблицам, оптимизированным для памяти. Инструкции T-SQL компилируются в машинный код и сохраняются в виде собственных библиотек DLL, благодаря чему обеспечивается более быстрый доступ к данным и повышение эффективности выполнения запросов по сравнению с традиционным T-SQL. Хранимые процедуры, которые отмечены как NATIVE_COMPILATION, компилируются в собственном коде.

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

   CREATE PROCEDURE dbo.usp_InsertSampleCarts @InsertCount INT
       WITH NATIVE_COMPILATION, SCHEMABINDING AS
   BEGIN ATOMIC
       WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
   
   DECLARE @i INT = 0
   
   WHILE @i < @InsertCount
      BEGIN
          INSERT INTO dbo.ShoppingCart VALUES (1, SYSDATETIME(), NULL)
   
          SET @i += 1
      END
   END
   

2. Вставка 1 000 000 строк:

   EXEC usp_InsertSampleCarts 1000000;
   

3. Проверка успешной вставки строк:

   SELECT COUNT(*) FROM dbo.ShoppingCart;
   

Использование хранилища запросов

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

Перед SQL Server 2022 (16.x) хранилище запросов не включен по умолчанию и может быть включен с помощью ALTER DATABASE:

ALTER DATABASE AdventureWorks2022 SET QUERY_STORE = ON;

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

SELECT Txt.query_text_id, Txt.query_sql_text, Pl.plan_id, Qry.*
FROM sys.query_store_plan AS Pl
   JOIN sys.query_store_query AS Qry
      ON Pl.query_id = Qry.query_id
   JOIN sys.query_store_query_text AS Txt
      ON Qry.query_text_id = Txt.query_text_id;

Запрос динамических административных представлений

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

Запрос динамического административного представления статистики dm_os_wait:

SELECT wait_type, wait_time_ms
FROM sys.dm_os_wait_stats;

См. также

• Краткое руководство 1. Технологии выполнения OLTP в памяти для повышения производительности службы Transact-SQL
• Миграция в In-Memory OLTP
• Улучшение производительности временной таблицы и табличной переменной с помощью оптимизации памяти
• Наблюдение и устранение неисправностей при использовании памяти
• In-Memory OLTP (оптимизация в памяти)

Связанный контент

• Средства контроля и настройки производительности
• Рекомендации по производительности и рекомендации по конфигурации для SQL Server в Linux
• Краткое руководство. Установка SQL Server и создание базы данных в Red Hat