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

Применимо к:SQL Server в Linux

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

Примечание.

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

Создайте колоночный индекс

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

  1. Добавьте индекс columnstore в SalesOrderDetail таблицу, выполнив следующие команды Transact-SQL (T-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. Убедитесь, что индексы столбчатого хранилища использовались, найдя object_id для индекса столбчатого хранилища и подтвердив, что он отображается в статистике использования таблицы 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 предоставляет In-Memory OLTP-функции, которые могут значительно повысить производительность прикладных систем. Этот раздел проводит вас по этапам создания оптимизированной для памяти таблицы, хранящейся в памяти, и нативно скомпилированной сохранённой процедуры, которая может получить доступ к таблице.

Настройка базы данных для In-Memory 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;

Запрос динамических управляющих представлений

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

Чтобы выполнить запрос к динамическому административному представлению sys.dm_os_wait_stats:

SELECT wait_type,
       wait_time_ms
FROM sys.dm_os_wait_stats;

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

  • Последнее обновление: