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.

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

• Avvio rapido 1: Tecnologie OLTP in memoria per migliorare le prestazioni di Transact-SQL
• Migrazione a OLTP in memoria
• Tabella temporanea più rapida e variabile di tabella tramite l'ottimizzazione per la memoria
• Monitorare e risolvere i problemi relativi all'utilizzo della memoria
• OLTP in memoria (ottimizzazione in memoria)

Contenuto correlato

• Strumenti per il monitoraggio e l'ottimizzazione delle prestazioni
• Procedure consigliate per le prestazioni e linee guida per la configurazione per SQL Server in Linux
• Avvio rapido: Installare SQL Server e creare un database in Red Hat