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

Пошаговое руководство по функциям повышения производительности в 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) AS SumUnitPrice,
       AVG(UnitPrice) AS AvgUnitPrice,
       SUM(OrderQty) AS SumOrderQty,
       AVG(OrderQty) AS 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. Необходимо установить для базы данных уровень совместимости не менее 130, чтобы использовать OLTP в памяти. Используйте следующий запрос, чтобы проверить текущий уровень 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 AS INT = 0;
    WHILE @i < @InsertCount
        BEGIN
            INSERT INTO dbo.ShoppingCart
            VALUES (1, SYSDATETIME(), NULL);
            SET @i += 1;
        END
END;

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

    EXECUTE 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
     INNER JOIN sys.query_store_query AS Qry
         ON Pl.query_id = Qry.query_id
     INNER 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;

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