Condividi tramite

Procedura dettagliata per le funzionalità per le prestazioni di SQL Server in Linux

  • Articolo

Si applica a: SQL Server - Linux

Le attività seguenti illustrano alcune delle funzionalità per le prestazioni per gli utenti Linux che non hanno familiarità con SQL Server. Queste attività non sono univoche o specifiche di Linux, ma offrono un'idea delle aree da approfondire. In ogni esempio viene fornito un collegamento alla documentazione dettagliata dell'area.

Nota

Negli esempi seguenti viene usato il database di esempio AdventureWorks2022. Per istruzioni su come ottenere e installare questo database di esempio, vedere Eseguire la migrazione di un database di SQL Server da Windows a Linux usando il backup e il ripristino.

Creare un indice columnstore

L'indice columnstore è una tecnologia per l'archiviazione e l'esecuzione di query su grandi archivi di dati in un formato a colonne, detto columnstore.

  1. Aggiungere un indice columnstore alla tabella SalesOrderDetail eseguendo i comandi Transact-SQL seguenti:

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

  2. Eseguire la query seguente che usa l'indice columnstore per analizzare la tabella:

    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. Per verificare che sia stato usato l'indice columnstore, cercare object_id per l'indice columnstore e controllare che venga visualizzato nelle statistiche di utilizzo per la tabella 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');

Usare OLTP in memoria

SQL Server fornisce funzionalità OLTP in memoria che possono migliorare notevolmente le prestazioni dei sistemi di applicazioni. Questa sezione illustra i passaggi per creare una tabella ottimizzata per la memoria archiviata in memoria e una stored procedure compilata in modo nativo in grado di accedere alla tabella senza che sia necessario compilarla o interpretarla.

Configurare il database per OLTP in memoria

  1. È necessario impostare il database su un livello di compatibilità di almeno 130 per l'uso di OLTP in memoria. Usare la query seguente per controllare il livello di compatibilità corrente di AdventureWorks2022:

    USE AdventureWorks2022;
GO

SELECT d.compatibility_level
FROM sys.databases AS d
WHERE d.name = DB_NAME();
GO

    Se necessario, aggiornare il livello impostandolo su 130:

    ALTER DATABASE CURRENT
    SET COMPATIBILITY_LEVEL = 130;
GO

  2. Quando una transazione coinvolge sia una tabella basata su disco che una tabella ottimizzata per la memoria, è essenziale che la parte ottimizzata per la memoria operi al livello di isolamento della transazione denominato SNAPSHOT. Per applicare in modo affidabile questo livello per le tabelle ottimizzate per la memoria in una transazione tra contenitori, eseguire quanto segue:

    ALTER DATABASE CURRENT
    SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;
GO

  3. Prima di creare una tabella ottimizzata per la memoria, è necessario creare un filegroup ottimizzato per la memoria e un contenitore per i file di dati:

    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

Creare una tabella ottimizzata per la memoria

L'archivio primario per le tabelle ottimizzate per la memoria è la memoria principale, quindi, a differenza di quanto avviene con le tabelle basate su disco, i dati non devono essere letti dal disco nei buffer di memoria. Per creare una tabella ottimizzata per la memoria, usare la clausola MEMORY_OPTIMIZED = ON.

  1. Eseguire la query seguente per creare la tabella ottimizzata per la memoria dbo.ShoppingCart. Per impostazione predefinita, i dati vengono salvati in modo permanente su disco per motivi di durabilità. La DURABILITÀ può anche essere impostata per salvare in modo permanente solo lo schema.

    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. Inserire alcuni record nella tabella:

    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);

Stored procedure compilate in modo nativo

SQL Server supporta le stored procedure compilate in modo nativo che accedono alle tabelle ottimizzate per la memoria. Le istruzioni T-SQL vengono compilate nel codice del computer e archiviate come DLL native, consentendo un accesso ai dati più veloce e un'esecuzione delle query più efficiente rispetto a T-SQL tradizionale. Le stored procedure che sono contrassegnate con NATIVE_COMPILATION vengono compilate in modo nativo.

  1. Eseguire lo script seguente per creare una stored procedure compilata in modo nativo che inserisce un numero elevato di record nella tabella 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. Inserire 1 milione di righe:

    EXECUTE usp_InsertSampleCarts 1000000;

  3. Verificare che le righe siano state inserite:

    SELECT COUNT(*)
FROM dbo.ShoppingCart;

Usare Query Store

Query Store raccoglie informazioni dettagliate sulle prestazioni relative a query, piani di esecuzione e statistiche di runtime.

Prima di SQL Server 2022 (16.x), Query Store non è abilitato per impostazione predefinita e può essere abilitato con ALTER DATABASE:

ALTER DATABASE AdventureWorks2022
    SET QUERY_STORE = ON;

Eseguire la query seguente per restituire informazioni su query e piani in Query Store:

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;

Eseguire query su DMV

Le viste a gestione dinamica restituiscono informazioni sullo stato del server che possono essere usate per monitorare l'integrità di un'istanza del server, diagnosticare i problemi e ottimizzare le prestazioni.

Per eseguire una query sulla vista a gestione dinamica dm_os_wait stats:

SELECT wait_type,
       wait_time_ms
FROM sys.dm_os_wait_stats;

Contenuto correlato