Campione in memoria nel database SQL di Azure

  • Articolo

Si applica a: Database SQL di Azure

Le tecnologie in memoria del database SQL di Azure consentono di migliorare le prestazioni dell'applicazione e ridurre potenzialmente i costi del database. Utilizzando le tecnologie in memoria nel database SQL di Azure, è possibile ottenere miglioramenti delle prestazioni con diversi carichi di lavoro.

In questo articolo verranno esaminati due esempi che illustrano l'uso di OLTP in memoria e degli indici columnstore nel database SQL di Azure.

Per altre informazioni, vedi:

Per una demo introduttiva di OLTP in memoria, vedere:

1. Installare l'esempio di OLTP in memoria

È possibile creare il database di esempio AdventureWorksLT con pochi passi nel portale di Azure. I passaggi descritti in questa sezione illustrano come aggiungere oggetti OLTP in memoria al proprio database AdventureWorksLT e dimostrare i vantaggi sulle prestazioni.

Passaggi di installazione

  1. Nel portale di Azure creare un database Premium (DTU) o Business Critical (vCore) in un server logico. Impostare Origine sul database AdventureWorksLT di esempio. Per istruzioni dettagliate, vedere Creare il primo database nel database SQL di Azure.

  2. Connettersi al database con SQL Server Management Studio (SSMS).

  3. Copiare lo script Transact-SQL OLTP in memoria negli Appunti. Lo script T-SQL crea gli oggetti in memoria necessari nel database AdventureWorksLT di esempio creato nel passaggio 1.

  4. Incollare lo script T-SQL in SSMS.exe, quindi eseguirlo. La clausola MEMORY_OPTIMIZED = ON nelle istruzioni CREATE TABLE è fondamentale. Ad esempio:

    CREATE TABLE [SalesLT].[SalesOrderHeader_inmem](
    [SalesOrderID] int IDENTITY NOT NULL PRIMARY KEY NONCLUSTERED ...,
    ...
) WITH (MEMORY_OPTIMIZED = ON);

Errore 40536

Se viene visualizzato l'errore 40536 quando si esegue lo script T-SQL, verificare se il database supporta le funzionalità in memoria eseguendo questo script T-SQL:

SELECT DATABASEPROPERTYEX(DB_NAME(), 'IsXTPSupported');

Se il risultato è 0, le funzionalità OLTP in memoria non sono supportate, mentre 1 indica che sono supportate. Le tecnologie OLTP in memoria sono disponibili nei livelli database SQL Premium (DTU) e Business Critical (vCore).

Informazioni sugli elementi ottimizzati per la memoria creati

Tabelle: l'esempio contiene le tabelle ottimizzate per la memoria seguenti:

  • SalesLT.Product_inmem
  • SalesLT.SalesOrderHeader_inmem
  • SalesLT.SalesOrderDetail_inmem
  • Demo.DemoSalesOrderHeaderSeed
  • Demo.DemoSalesOrderDetailSeed

È possibile esaminare le tabelle ottimizzate per la memoria tramite Esplora oggetti in SSMS. Quando si fa clic con il pulsante destro del mouse su Tabelle, passare a >Filtra>Impostazioni filtro>È ottimizzato per la memoria. Il valore è uguale a 1.

In alternativa, è possibile eseguire una query delle viste del catalogo, ad esempio:

SELECT is_memory_optimized, name, type_desc, durability_desc
    FROM sys.tables
    WHERE is_memory_optimized = 1;

Stored procedure compilata in modo nativo: è possibile esaminare SalesLT.usp_InsertSalesOrder_inmem usando una query delle viste del catalogo:

SELECT uses_native_compilation, OBJECT_NAME(object_id) AS module_name, definition
    FROM sys.sql_modules
    WHERE uses_native_compilation = 1;

2. Eseguire il carico di lavoro OLTP di esempio

L'unica differenza tra le due stored procedure seguenti è che la prima usa le tabelle ottimizzate per la memoria, mentre la seconda usa tabelle basate su disco tradizionali:

  • SalesLT.usp_InsertSalesOrder_inmem
  • SalesLT.usp_InsertSalesOrder_ondisk

Questa sezione illustra come usare l'utilità ostress.exe per eseguire le due stored procedure. È possibile mettere a confronto i tempi necessari per il completamento dei due test di stress.

Installare le utilità RML e ostress

Preferibilmente, è consigliabile eseguire ostress.exe in una macchina virtuale (VM) di Azure. Si creerebbe una macchina virtuale di Azure nella stessa area di Azure del database AdventureWorksLT. È anche possibile eseguire ostress.exe nel computer locale se è possibile connettersi al database SQL di Azure. Tuttavia, la latenza di rete tra il computer e il database in Azure potrebbe ridurre i vantaggi delle prestazioni di OLTP in memoria.

Nella VM o qualunque host si seleziona, installare le utilità RML (Replay Markup Language). Le utilità includono ostress.exe.

Per altre informazioni, vedi:

Script per ostress.exe

Questa sezione illustra lo script T-SQL incorporato nella riga di comando ostress.exe. Lo script usa gli elementi creati dallo script T-SQL installato in precedenza.

Quando si esegue ostress.exe, è consigliabile passare valori di parametri specifici per sollecitare il carico di lavoro utilizzando entrambe le seguenti strategie:

  • Eseguire un numero elevato di connessioni simultanee, usando -n100.
  • Ripetere ogni ciclo di connessione centinaia di volte, usando -r500.

È opportuno, tuttavia, iniziare con valori molto più bassi, ad esempio -n10 e -r50, per assicurarsi che tutto funzioni correttamente.

Lo script riportato di seguito inserisce un ordine di vendita di esempio con cinque voci nelle tabelle ottimizzate per la memoria seguenti:

  • SalesLT.SalesOrderHeader_inmem
  • SalesLT.SalesOrderDetail_inmem
DECLARE
    @i int = 0,
    @od SalesLT.SalesOrderDetailType_inmem,
    @SalesOrderID int,
    @DueDate datetime2 = sysdatetime(),
    @CustomerID int = rand() * 8000,
    @BillToAddressID int = rand() * 10000,
    @ShipToAddressID int = rand() * 10000;

INSERT INTO @od
    SELECT OrderQty, ProductID
    FROM Demo.DemoSalesOrderDetailSeed
    WHERE OrderID= cast((rand()*60) as int);

WHILE (@i < 20)
BEGIN;
    EXECUTE SalesLT.usp_InsertSalesOrder_inmem @SalesOrderID OUTPUT,
        @DueDate, @CustomerID, @BillToAddressID, @ShipToAddressID, @od;
    SET @i = @i + 1;
END

Per creare la versione _ondisk dello script T-SQL precedente, ostress.exeoccorre sostituire le due occorrenze della sottostringa _inmem con _ondisk. Queste sostituzioni interessano i nomi delle tabelle e delle stored procedure.

Eseguire prima di tutto il test di stress del carico di lavoro per _inmem

È possibile usare una finestra richiesta cmd RML per eseguire ostress.exe. I parametri della riga di comando indicano al comando ostress di:

  • Eseguire 100 connessioni simultaneamente (-n100).
  • Fare in modo che ogni connessione esegua lo script T-SQL 50 volte (-r50).
ostress.exe -n100 -r50 -S<servername>.database.windows.net -U<login> -P<password> -d<database> -q -Q"DECLARE @i int = 0, @od SalesLT.SalesOrderDetailType_inmem, @SalesOrderID int, @DueDate datetime2 = sysdatetime(), @CustomerID int = rand() * 8000, @BillToAddressID int = rand() * 10000, @ShipToAddressID int = rand()* 10000; INSERT INTO @od SELECT OrderQty, ProductID FROM Demo.DemoSalesOrderDetailSeed WHERE OrderID= cast((rand()*60) as int); WHILE (@i < 20) begin; EXECUTE SalesLT.usp_InsertSalesOrder_inmem @SalesOrderID OUTPUT, @DueDate, @CustomerID, @BillToAddressID, @ShipToAddressID, @od; set @i += 1; end"

Per eseguire la riga di comando ostress.exe precedente:

  1. Reimpostare il contenuto dei dati del database eseguendo questo comando in SSMS per eliminare tutti i dati inseriti da esecuzioni precedenti:

    EXECUTE Demo.usp_DemoReset;

  2. Copiare il testo della riga di comando ostress.exe precedente negli Appunti.

  3. Sostituire <placeholders> per i parametri -S -U -P -d con i valori corretti.

  4. Eseguire la riga di comando modificata in una finestra dei comandi RML.

Il risultato è un intervallo di tempo

Al termine, ostress.exe scrive la durata dell'esecuzione come ultima riga di output nella finestra dei comandi RML. Ad esempio, per un'esecuzione dei test più breve, durata circa 1,5 minuti:

11/12/15 00:35:00.873 [0x000030A8] OSTRESS exiting normally, elapsed time: 00:01:31.867

Reimpostare, modificare per l'esecuzione _ondisk ed eseguire di nuovo il test

Dopo aver ottenuto il risultato dell'esecuzione _inmem, seguire la procedura indicata di seguito per l'esecuzione _ondisk:

  1. Reimpostare il database eseguendo questo comando in SSMS per eliminare tutti i dati inseriti dall'esecuzione precedente:

    EXECUTE Demo.usp_DemoReset;

  2. Modificare la riga di comando ostress.exe per sostituire tutte le occorrenze di _inmem con _ondisk.

  3. Eseguire ostress.exe per la seconda volta e acquisire il risultato relativo alla durata.

  4. Anche in questo caso, reimpostare il database.

Risultati previsti per il confronto

I test delle funzionalità OLTP in memoria hanno mostrato un miglioramento delle prestazioni pari a 9 volte per questo semplice carico di lavoro, con l'utilità ostress.exe in esecuzione in una VM di Azure nella stessa area di Azure del database.

3. Installare l'esempio di analisi in memoria

In questa sezione vengono messi a confronto i risultati di statistiche e IO quando si usa un indice columnstore rispetto a un indice ad albero B tradizionale.

Per l'analisi in tempo reale in un carico di lavoro OLTP, è spesso preferibile usare un indice columnstore non cluster. Per informazioni dettagliate, vedere Descrizione degli indici columnstore.

Preparare il test di analisi columnstore

  1. Usare il portale di Azure per creare un nuovo database AdventureWorksLT dall'esempio. Usare qualsiasi obiettivo di servizio che supporti gli indici columnstore.

  2. Copiare sql_in-memory_analytics_sample negli Appunti.

    • Lo script T-SQL crea gli oggetti necessari nel database AdventureWorksLT di esempio creato nel passaggio 1.
    • Lo script crea le tabelle delle dimensioni e due tabelle dei fatti. Ogni tabella dei fatti viene popolata con 3,5 milioni di righe.
    • Per gli obiettivi di servizio più piccoli, il completamento dello script potrebbe richiedere 15 minuti o più.

  3. Incollare lo script T-SQL in SSMS.exe, quindi eseguirlo. La parola chiave COLUMNSTORE è fondamentale in una istruzione CREATE INDEX: CREATE NONCLUSTERED COLUMNSTORE INDEX ...;

  4. Impostare AdventureWorksLT sul livello di compatibilità più recente, SQL Server 2022 (160): ALTER DATABASE AdventureworksLT SET compatibility_level = 160;

Tabelle e indici columnstore fondamentali

  • dbo.FactResellerSalesXL_CCI è una tabella contenente un indice columnstore cluster, che presenta una compressione avanzata a livello di dati.

  • dbo.FactResellerSalesXL_PageCompressed è una tabella contenente un indice cluster equivalente tradizionale, che presenta una compressione solo a livello di pagina.

4. Query fondamentali per il confronto dell'indice columnstore

Sono disponibili diversi tipi di query T-SQ che è possibile eseguire per migliorare le prestazioni. Nel passaggio 2 nello script T-SQL, prestare attenzione a questa coppia di query. Le due query differiscono per una sola riga:

  • FROM FactResellerSalesXL_PageCompressed AS a
  • FROM FactResellerSalesXL_CCI AS a

Un indice columnstore cluster si trova nella tabella FactResellerSalesXL_CCI.

Lo script T-SQL seguente stampa le statistiche logiche di I/O e ora, usando SET STATISTICS IO e SET STATISTICS TIME per ogni query.

/*********************************************************************
Step 2 -- Overview
-- Page compressed BTree table vs Columnstore table performance differences
-- Enable actual query plan in order to see Plan differences when executing.
*/
-- Ensure the database uses the latest compatibility level
ALTER DATABASE AdventureworksLT SET compatibility_level = 160
GO

-- Execute a typical query that joins the fact table with dimension tables.
-- Note this query will run on the page compressed table. Note down the time.
SET STATISTICS IO ON
SET STATISTICS TIME ON
GO

SELECT c.Year
    ,e.ProductCategoryKey
    ,FirstName + ' ' + LastName AS FullName
    ,COUNT(SalesOrderNumber) AS NumSales
    ,SUM(SalesAmount) AS TotalSalesAmt
    ,AVG(SalesAmount) AS AvgSalesAmt
    ,COUNT(DISTINCT SalesOrderNumber) AS NumOrders
    ,COUNT(DISTINCT a.CustomerKey) AS CountCustomers
FROM FactResellerSalesXL_PageCompressed AS a
INNER JOIN DimProduct AS b ON b.ProductKey = a.ProductKey
INNER JOIN DimCustomer AS d ON d.CustomerKey = a.CustomerKey
INNER JOIN DimProductSubCategory AS e on e.ProductSubcategoryKey = b.ProductSubcategoryKey
INNER JOIN DimDate AS c ON c.DateKey = a.OrderDateKey
GROUP BY e.ProductCategoryKey,c.Year,d.CustomerKey,d.FirstName,d.LastName
GO

SET STATISTICS IO OFF
SET STATISTICS TIME OFF
GO


-- This is the same query on a table with a clustered columnstore index (CCI).
-- The comparison numbers are the more pronounced the larger the table is (this is an 11 million row table).
SET STATISTICS IO ON
SET STATISTICS TIME ON
GO
SELECT c.Year
    ,e.ProductCategoryKey
    ,FirstName + ' ' + LastName AS FullName
    ,COUNT(SalesOrderNumber) AS NumSales
    ,SUM(SalesAmount) AS TotalSalesAmt
    ,AVG(SalesAmount) AS AvgSalesAmt
    ,COUNT(DISTINCT SalesOrderNumber) AS NumOrders
    ,COUNT(DISTINCT a.CustomerKey) AS CountCustomers
FROM FactResellerSalesXL_CCI AS a
INNER JOIN DimProduct AS b ON b.ProductKey = a.ProductKey
INNER JOIN DimCustomer AS d ON d.CustomerKey = a.CustomerKey
INNER JOIN DimProductSubCategory AS e on e.ProductSubcategoryKey = b.ProductSubcategoryKey
INNER JOIN DimDate AS c ON c.DateKey = a.OrderDateKey
GROUP BY e.ProductCategoryKey,c.Year,d.CustomerKey,d.FirstName,d.LastName
GO

SET STATISTICS IO OFF
SET STATISTICS TIME OFF
GO

In un database che usa l’obiettivo di servizio P2 è possibile raggiungere circa nove volte il guadagno sulle prestazioni per la query tramite l'indice columnstore cluster rispetto a un indice rowstore. Con l’obiettivo di servizio P15, è possibile prevedere un miglioramento delle prestazioni pari a 57 volte usando l'indice columnstore.

Contenuto correlato