Esempio in memoria in database SQL di Azure
Si applica a:database SQL di Azure
Le tecnologie in memoria in database SQL di Azure consentono di migliorare le prestazioni dell'applicazione e di ridurre potenzialmente i costi del database. Usando le tecnologie in memoria in database SQL di Azure, è possibile ottenere miglioramenti delle prestazioni con vari carichi di lavoro.
In questo articolo verranno visualizzati due esempi che illustrano l'uso di OLTP in memoria, nonché indici columnstore in database SQL di Azure.
Per altre informazioni, vedi:
- Panoramica e scenari di utilizzo OLTP in memoria (include riferimenti ai case study dei clienti e informazioni per iniziare)
- Documentazione per OLTP in memoria
- Guida agli indici columnstore
- L'elaborazione analitica e transazionale ibrida (HTAP), anche nota come analisi operativa in tempo reale
Per una demo delle prestazioni più semplice, ma visivamente più accattivante per OLTP in memoria, vedere:
- Versione: in-memory-oltp-demo-v1.0
- Codice sorgente: in-memory-oltp-demo-source-code
1. Installare l'esempio OLTP in memoria
È possibile creare il AdventureWorksLT
database di esempio con alcuni passaggi nella portale di Azure. I passaggi descritti in questa sezione illustrano quindi come arricchire il AdventureWorksLT
database con oggetti OLTP in memoria e dimostrare i vantaggi delle prestazioni.
Passaggi di installazione
Nella portale di Azure creare un database Premium (DTU) o Business Critical (vCore) in un server. Impostare Source sul
AdventureWorksLT
database di esempio. Per istruzioni dettagliate, vedere Creare il primo database nel database SQL di Azure.Connessione al database con SQL Server Management Studio (SSMS).
Copiare lo script Transact-SQL OLTP in memoria negli Appunti. Lo script T-SQL crea gli oggetti in memoria necessari nel
AdventureWorksLT
database di esempio creato nel passaggio 1.Incollare lo script T-SQL in SSMS.exe, quindi eseguirlo. La
MEMORY_OPTIMIZED = ON
clausola nelleCREATE TABLE
istruzioni è 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, eseguire lo script T-SQL seguente per verificare se il database supporta oggetti in memoria:
SELECT DatabasePropertyEx(DB_Name(), 'IsXTPSupported');
Un risultato indica 0
che in memoria non è supportato e 1
significa che è supportato. Le tecnologie in memoria sono disponibili nei livelli database SQL di Azure 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 filtrare per visualizzare solo le tabelle ottimizzate per la memoria in Esplora oggetti in SSMS. Quando si fa clic con il pulsante destro del mouse su Tabelle, passare a >Filtro filtro>Impostazioni> Is Ottimizzazione 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
tramite una query di visualizzazione del catalogo:
SELECT uses_native_compilation, OBJECT_NAME(object_id), 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 versioni delle 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 in condizioni di sovraccarico. È possibile mettere a confronto i tempi necessari per il completamento dei due test di stress.
Installare le utilità RML e ostress
È consigliabile pianificare l'esecuzione di ostress.exe su una macchina virtuale di Azure. Si creerebbe una macchina virtuale di Azure nella stessa area di Azure del AdventureWorksLT
database. È tuttavia possibile eseguire ostress.exe nella workstation locale, purché sia possibile connettersi al database SQL di Azure.
Installare nella macchina virtuale o nell'host scelto le utilità RML (Replay Markup Language), che includono ostress.exe.
Per altre informazioni, vedi:
- Discussione ostress.exe in Database di esempio per OLTP in memoria.
- Database di esempio per OLTP in memoria.
- Blog sull'installazione di ostress.exe.
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 i valori dei parametri progettati per stressare il carico di lavoro usando entrambe le strategie seguenti:
- Eseguire un numero elevato di connessioni simultanee usando
-n100
. - Ogni connessione viene ripetuta centinaia di volte usando
-r500
.
Tuttavia, è possibile iniziare con valori molto più piccoli come -n10
e -r50
per assicurarsi che tutto funzioni.
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 per ostress.exe, occorre 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
Per eseguire la riga di comando ostress.exe è possibile usare una finestra del prompt dei comandi RML . 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:
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;
Copiare il testo della riga di comando ostress.exe precedente negli Appunti.
Sostituire per
<placeholders>
i parametri-S -U -P -d
con i valori reali corretti.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:
Reimpostare il database eseguendo questo comando in SSMS per eliminare tutti i dati inseriti dall'esecuzione precedente:
EXECUTE Demo.usp_DemoReset;
Modificare la riga di comando ostress.exe per sostituire tutte le occorrenze di _inmem con _ondisk.
Eseguire ostress.exe per la seconda volta e acquisire il risultato relativo alla durata.
Reimpostare nuovamente il database, per eliminare in modo responsabile una potenziale grande quantità di dati di test.
Risultati previsti per il confronto
I test in memoria hanno dimostrato che le prestazioni sono migliorate di nove volte per questo carico di lavoro semplicistico, con ostress
l'esecuzione in una macchina virtuale 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
Usare il portale di Azure per creare un nuovo
AdventureWorksLT
database dall'esempio.- Usare esattamente questo nome.
- Scegliere qualsiasi livello di servizio Premium.
Copiare sql_in-memory_analytics_sample negli Appunti.
- Lo script T-SQL crea gli oggetti in memoria necessari nel
AdventureWorksLT
database di esempio creato nel passaggio 1. - Lo script crea tabelle delle dimensioni e due tabelle dei fatti. Ogni tabella dei fatti viene popolata con 3,5 milioni di righe.
- Il completamento dello script potrebbe richiedere 15 minuti.
- Lo script T-SQL crea gli oggetti in memoria necessari nel
Incollare lo script T-SQL in SSMS.exe, quindi eseguirlo. La parola chiave COLUMNSTORE nell'istruzione
CREATE INDEX
è fondamentale:CREATE NONCLUSTERED COLUMNSTORE INDEX ...;
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 con un indice columnstore cluster, con compressione avanzata a livello di dati .dbo.FactResellerSalesXL_PageCompressed
è una tabella con un indice cluster normale equivalente, compresso solo a livello di pagina .
4. Eseguire query chiave per confrontare l'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
Nella tabella è presente un FactResellerSalesXL_CCI
indice columnstore cluster.
Lo script T-SQL seguente stampa le statistiche logiche di I/O e ora, usando edizione Standard T STATISTICS IO e edizione Standard T STATISTICS TIME per ogni query.
/*********************************************************************
Step 2 -- Overview
-- Page Compressed BTree table v/s Columnstore table performance differences
-- Enable actual Query Plan in order to see Plan differences when Executing
*/
-- Ensure Database is in 130 compatibility mode
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 Prior query on a table with a clustered columnstore index CCI
-- The comparison numbers are even more dramatic the larger the table is (this is an 11 million row table only)
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 con piano tariffario P2 è possibile raggiungere circa 9X il guadagno sulle prestazioni per la query tramite l'indice columnstore cluster rispetto a un indice tradizionale. Con P15, è possibile prevedere un miglioramento delle prestazioni pari a 57X usando l'indice columnstore.
Contenuto correlato
- Guida introduttiva 1: Tecnologie OLTP in memoria per prestazioni T-SQL più veloci
- Uso di OLTP in memoria per migliorare le prestazioni delle applicazioni
- Monitorare l'archiviazione OLTP in memoria
- Blog: OLTP in memoria in database SQL di Azure
- OLTP in memoria
- Indici columnstore
- Analisi operativa in tempo reale con indici columnstore
- Articolo tecnico: OLTP in memoria - Modelli di carico di lavoro comuni e considerazioni sulla migrazione in SQL Server 2014
Commenti e suggerimenti
https://aka.ms/ContentUserFeedback.
Presto disponibile: Nel corso del 2024 verranno gradualmente disattivati i problemi di GitHub come meccanismo di feedback per il contenuto e ciò verrà sostituito con un nuovo sistema di feedback. Per altre informazioni, vedereInvia e visualizza il feedback per