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

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 Ripristinare un database di SQL Server da Windows a Linux.

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) SumUnitPrice, AVG(UnitPrice) AvgUnitPrice,
       SUM(OrderQty) SumOrderQty, AVG(OrderQty) 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. Per usare OLTP in memoria, è consigliabile impostare il database su un livello di compatibilità pari almeno a 130. 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 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:

    EXEC 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 le informazioni sulle query e sui piani inclusi nell'archivio query:

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;

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;

Vedi anche