Бөлісу құралы:


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

См. также