Пошаговое руководство по функциям повышения производительности в 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;

См. также

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