Panoramica e scenari di utilizzoOverview and Usage Scenarios

In questo argomento si applica a: SìSQL ServerSìDatabase SQL di AzurenonAzure SQL Data Warehouse non Parallel Data WarehouseTHIS TOPIC APPLIES TO: yesSQL ServeryesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

OLTP in memoria è la principale tecnologia disponibile in SQL Server e nel database SQL di Azure per ottimizzare le prestazioni di elaborazione delle transazioni, l'inserimento di dati, il caricamento di dati e gli scenari di dati temporanei.In-Memory OLTP is the premier technology available in SQL Server and Azure SQL Database for optimizing performance of transaction processing, data ingestion, data load, and transient data scenarios. Questo argomento include una panoramica della tecnologia e descrive gli scenari di utilizzo per OLTP in memoria.This topic includes an overview of the technology and outlines usage scenarios for In-Memory OLTP. Usare queste informazioni per determinare se OLTP in memoria è appropriato all'applicazione usata.Use this information to determine whether In-Memory OLTP is right for your application. L'argomento si conclude con un esempio che mostra gli oggetti OLTP in memoria, un riferimento a una dimostrazione sulle prestazioni e riferimenti a risorse che è possibile usare per i passaggi successivi.The topic concludes with an example that shows In-Memory OLTP objects, reference to a perf demo, and references to resources you can use for next steps.

Questo articolo descrive la tecnologia di OLTP in memoria nel database SQL Server e nel database SQL di Azure.This article covers the In-Memory OLTP technology in both SQL Server and Azure SQL Database. Il post di blog seguente include un'analisi approfondita dei vantaggi a livello di prestazioni e uso delle risorse nel database SQL di Azure:The following blog post contains a deep dive into the performance and resource utilization benefits in Azure SQL Database:

Panoramica di OLTP in memoriaIn-Memory OLTP Overview

OLTP in memoria può fornire elevati miglioramenti delle prestazioni per i carichi di lavoro appropriati.In-Memory OLTP can provide great performance gains, for the right workloads. Un cliente, bwin, è riuscito a ottenere 1,2 milioni di richieste al secondo con un singolo computer che esegue SQL Server 2016, sfruttando OLTP in memoria.One customer, bwin, managed to achieve 1.2 Million requests per second with a single machine running SQL Server 2016, leveraging In-Memory OLTP. Un altro cliente, Quorum, è riuscito a raddoppiare il proprio carico di lavoro riducendo del 70% l'utilizzo delle proprie risorsesfruttando OLTP in memoria nel database SQL di Azure.Another customer, Quorum, managed to double their workload while reducing their resource utilization by 70%, by leveraging In-Memory OLTP in Azure SQL Database. Anche se i clienti in alcuni casi hanno potuto migliorare di 30 volte le prestazioni, il miglioramento che si ottiene dipende in realtà dal proprio carico di lavoro.While customers have seen up to 30X performance gain in some cases, how much gain you will see depends on the workload.

Da cosa dipende questo miglioramento delle prestazioni?Now, where does this performance gain come from? In sostanza, OLTP in memoria migliora le prestazioni di elaborazione delle transazioni rendendo più efficiente l'accesso ai dati e l'esecuzione delle transazioni e rimuovendo la contesa latch/blocco tra le transazioni attualmente in esecuzione: non è veloce perché è in memoria; è veloce perché è ottimizzato per i dati in memoria.In essence, In-Memory OLTP improves performance of transaction processing by making data access and transaction execution more efficient, and by removing lock and latch contention between concurrently executing transactions: it is not fast because it is in-memory; it is fast because it is optimized around the data being in-memory. L'archiviazione dei dati, l'accesso e l'elaborazione degli algoritmi sono stati riprogettati interamente per sfruttare i miglioramenti più recenti di elaborazione in memoria e concorrenza elevata.Data storage, access, and processing algorithms were redesigned from the ground up to take advantage of the latest enhancements in in-memory and high concurrency computing.

Ma solo perché i dati si trovano in memoria non significa che si perdono quando si verifica un errore.Now, just because data lives in-memory does not mean you lose it when there is a failure. Per impostazione predefinita, tutte le transazioni sono completamente durevoli, ovvero si hanno le stesse garanzie di durabilità che si hanno per qualsiasi altra tabella in SQL Server: come parte del commit della transazione, tutte le modifiche vengono scritte nel log delle transazioni su disco.By default, all transactions are fully durable, meaning that you have the same durability guarantees you get for any other table in SQL Server: as part of transaction commit, all changes are written to the transaction log on disk. Se si verifica un errore in qualsiasi momento dopo il commit della transazione, i dati rimangono presenti quando il database torna online.If there is a failure at any time after the transaction commits, your data is there when the database comes back online. OLTP in memoria funziona inoltre con tutte le funzionalità di disponibilità elevata e ripristino di emergenza di SQL Server, come AlwaysOn, backup e ripristino e così via.In addition, In-Memory OLTP works with all high availability and disaster recovery capabilities of SQL Server, like AlwaysOn, backup/restore, etc.

Per sfruttare OLTP in memoria nel database, è possibile usare uno o più dei seguenti tipi di oggetti:To leverage In-Memory OLTP in your database, you use one or more of the following types of objects:

  • Letabelle con ottimizzazione per la memoria vengono usate per archiviare i dati utente.Memory-optimized tables are used for storing user data. È possibile dichiarare una tabella ottimizzata per la memoria al momento della creazione.You declare a table to be memory-optimized at create time.
  • Letabelle non durevoli vengono usate per i dati temporanei, per la memorizzazione nella cache o per un set di risultati intermedio (sostituendo le tabelle temporanee tradizionali).Non-durable tables are used for transient data, either for caching or for intermediate result set (replacing traditional temp tables). Una tabella non durevole è una tabella ottimizzata per la memoria che viene dichiarata con DURABILITY=SCHEMA_ONLY, vale a dire che le modifiche apportate a queste tabelle non comportano operazioni di I/O.A non-durable table is a memory-optimized table that is declared with DURABILITY=SCHEMA_ONLY, meaning that changes to these tables do not incur any IO. Ciò evita l'utilizzo di risorse per le operazioni di I/O sui log per i casi in cui la durabilità non è un problema.This avoids consuming log IO resources for cases where durability is not a concern.
  • Itipi di tabella con ottimizzazione per la memoria vengono usati per i parametri con valori di tabella, ovvero come set di risultati intermedi nelle stored procedure.Memory-optimized table types are used for table-valued parameters (TVPs), as well as intermediate result sets in stored procedures. Questi tipi possono essere usati al posto dei tipi di tabella tradizionali.These can be used instead of traditional table types. Le variabili di tabella e i parametri con valori di tabella che vengono dichiarati usando un tipo di tabella ottimizzata per la memoria ereditano i vantaggi delle tabelle non durevoli ottimizzate per la memoria: accesso efficiente ai dati e nessuna operazione I/O.Table variables and TVPs that are declared using a memory-optimized table type inherit the benefits of non-durable memory-optimized tables: efficient data access, and no IO.
  • Imoduli T-SQL compilati in modo nativo vengono usati per ridurre ulteriormente il tempo impiegato per una singola transazione riducendo i cicli di CPU necessari per elaborare le operazioni.Natively compiled T-SQL modules are used to further reduce the time taken for an individual transaction by reducing CPU cycles required to process the operations. È possibile dichiarare un modulo Transact-SQL in modo da essere compilato in modo nativo al momento della creazione.You declare a Transact-SQL module to be natively compiled at create time. Attualmente, i moduli T-SQL che possono essere compilati in modo nativo sono i seguenti: stored procedure, trigger e funzioni scalari definite dall'utente.At this time, the following T-SQL modules can be natively compiled: stored procedures, triggers and scalar user-defined functions.

OLTP in memoria è incorporato in SQL Server e nel database SQL di Azure.In-Memory OLTP is built into SQL Server and Azure SQL Database. E poiché il comportamento di questi oggetti è molto simile a quello delle relative controparti tradizionali, spesso è possibile ottenere un miglioramento delle prestazioni apportandolo solo modifiche minime al database e all'applicazione.And because these objects behave very similar to their traditional counterparts, you can often gain performance benefits while making only minimal changes to the database and the application. Inoltre, nello stesso database è possibile avere sia le tabelle ottimizzate per la memoria che le tabelle tradizionali basati su disco ed eseguire le query in entrambi i tipi di tabella.Plus, you can have both memory-optimized and traditional disk-based tables in the same database, and run queries across the two. Alla fine di questo argomento è disponibile uno script Transact-SQL che mostra un esempio per ognuno di questi tipi di oggetti.You will find a Transact-SQL script showing an example for each of these types of objects towards the bottom of this topic.

Scenari di utilizzo per OLTP in memoriaUsage Scenarios for In-Memory OLTP

OLTP in memoria non è un pulsante magico per ottenere maggiore velocità e non è appropriato per tutti i carichi di lavoro.In-Memory OLTP is not a magic go-fast button, and is not suitable for all workloads. Ad esempio, le tabelle ottimizzate per la memoria non ridurranno effettivamente l'utilizzo della CPU se la maggior parte delle query comportano aggregazioni per grandi intervalli di dati. Per questo scenario saranno utili gli indici columnstore.For example, memory-optimized tables will not really bring down your CPU utilization if most of the queries are performing aggregation over large ranges of data – Columnstore indexes help with that scenario.

Di seguito è riportato un elenco di scenari e modelli di applicazione in cui i clienti hanno ottenuto risultati positivi con OLTP in memoria.Here is a list of scenarios and application patterns where we have seen customers be successful with In-Memory OLTP.

Elaborazione di transazioni con velocità effettiva elevata e bassa latenzaHigh-throughput and low-latency transaction processing

Questo rappresenta effettivamente lo scenario principale per cui è stato creato OLTP in memoria: supporto di volumi elevati di transazioni con latenza bassa costante per le singole transazioni.This is really the core scenario for which we built In-Memory OLTP: support large volumes of transactions, with consistent low latency for individual transactions.

Scenari di carico di lavoro comuni sono: intermediazione di strumenti finanziari, scommesse sportive, giochi per dispositivi mobili e servizi pubblicitari.Common workload scenarios are: trading of financial instruments, sports betting, mobile gaming, and ad delivery. Un altro modello comune è quello di un "catalogo" letto e/o aggiornato di frequente.Another common pattern we’ve seen is a “catalog” that is frequently read and/or updated. Un esempio è rappresentato dal caso in cui si hanno file di grandi dimensioni, ognuno distribuito in diversi nodi di un cluster ed è possibile catalogare la posizione di ogni partizione di ogni file in una tabella ottimizzata per la memoria.One example is where you have large files, each distributed over a number of nodes in a cluster, and you catalog the location of each shard of each file in a memory-optimized table.

Considerazioni sull'implementazioneImplementation considerations

Usare le tabelle ottimizzate per la memoria per le tabelle delle transazioni principali, ad esempio le tabelle con transazioni più critiche per le prestazioni.Use memory-optimized tables for your core transaction tables, i.e., the tables with the most performance-critical transactions. Usare le stored procedure compilate in modo nativo per ottimizzare l'esecuzione della logica associata alla transazione aziendale.Use natively compiled stored procedures to optimize execution of the logic associated with the business transaction. Quanto più è possibile distribuire la logica nelle stored procedure del database, tanto più sarà il vantaggio che si ottiene dall'utilizzo di OLTP in memoria.The more of the logic you can push down into stored procedures in the database, the more benefit you will see from In-Memory OLTP.

Per iniziare a usare questo approccio in un'applicazione esistente:To get started in an existing application:

  1. Usare il report di analisi delle prestazioni delle transazioni per identificare gli oggetti di cui eseguire la migrazioneuse the transaction performance analysis report to identify the objects you want to migrate,
  2. Usare gli advisor ottimizzazione per la memoria e compilazione nativa per facilitare la migrazione.and use the memory-optimization and native compilation advisors to help with migration.

Case study dei clientiCustomer Case Studies

Inserimento di dati, tra cui IoT (Internet delle cose)Data ingestion, including IoT (Internet-of-Things)

OLTP in memoria è consigliato per inserire contemporaneamente grandi volumi di dati da molte origini diverse.In-Memory OLTP is really good at ingesting large volumes of data from many different sources at the same time. Ed è spesso utile inserire dati in un database di SQL Server rispetto ad altre destinazioni, perché SQL velocizza l'esecuzione di query sui dati consentendo di ottenere informazioni in tempo reale.And it is often beneficial to ingest data into a SQL Server database compared with other destinations, because SQL makes running queries against the data really fast, and allows you to get real-time insights.

Modelli di applicazione comuni sono: l'inserimento di letture dei sensori e di eventi per consentire le notifiche nonché l'analisi cronologica.Common application patterns are: Ingesting sensor readings and events, to allow notification, as well as historical analysis. La gestione degli aggiornamenti batch, anche da più origini, riducendo al minimo l'impatto sul carico di lavoro di lettura simultaneo.Managing batch updates, even from multiple sources, while minimizing the impact on the concurrent read workload.

Considerazioni sull'implementazioneImplementation considerations

Usare una tabella ottimizzata per la memoria per l'inserimento dei dati.Use a memory-optimized table for the data ingestion. Se l'operazione di inserimento è costituita principalmente da inserimenti (anziché aggiornamenti) e il footprint di memoria di OLTP in memoria dei dati è un problema, procedere come segue:If the ingestion consists mostly of inserts (rather than updates) and In-Memory OLTP storage footprint of the data is a concern, either

  • Usare un processo per ripartire regolarmente il carico di lavoro dei dati in batch in una tabella basata su disco con un indice columnstore clustermediante un processo che esegue l'istruzione INSERT INTO <disk-based table> SELECT FROM <memory-optimized table>Use a job to regularly batch-offload data to a disk-based table with a Clustered Columnstore index, using a job that does INSERT INTO <disk-based table> SELECT FROM <memory-optimized table>; or
  • Usare una tabella temporale ottimizzata per la memoria per gestire i dati cronologici: in questo modo, i dati cronologici risiedono su disco e lo spostamento dei dati viene gestito dal sistema.Use a temporal memory-optimized table to manage historical data – in this mode, historical data lives on disk, and data movement is managed by the system.

Il repository di esempi di SQL Server contiene un'applicazione intelligente della griglia che usa una tabella temporale ottimizzata per la memoria, un tipo di tabella ottimizzata per la memoria e una stored procedure compilata in modo nativo per velocizzare l'inserimento dei dati, gestendo il footprint di memoria di OLTP in memoria dei dati del sensore:The SQL Server samples repository contains a smart grid application that uses a temporal memory-optimized table, a memory-optimized table type, and a natively compiled stored procedure, to speed up data ingestion, while managing the In-Memory OLTP storage footprint of the sensor data:

Case study dei clientiCustomer Case Studies

Memorizzazione nella cache e stato della sessioneCaching and session state

La tecnologia di OLTP in memoria rende SQL molto interessante per mantenere lo stato della sessione (ad esempio, per un'applicazione ASP.NET) e per la memorizzazione nella cache.The In-Memory OLTP technology makes SQL really attractive for maintaining session state (e.g., for an ASP.NET application) and for caching.

Lo stato della sessione ASP.NET è un caso di utilizzo di grande successo per OLTP in memoria.ASP.NET session state is a very successful use case for In-Memory OLTP. Con SQL Server, un cliente ha quasi raggiunto 1,2 milioni di richieste al secondo.With SQL Server, one customer was about to achieve 1.2 Million requests per second. Nel frattempo, questo cliente ha iniziato a usare OLTP in memoria per le esigenze di memorizzazione nella cache di tutte le applicazioni di livello intermedio nell'organizzazione.In the meantime, they have started using In-Memory OLTP for the caching needs of all mid-tier applications in the enterprise. Dettagli: Modo in cui bwin usa OLTP in memoria di SQL Server 2016 per ottenere prestazioni e scalabilità senza precedentiDetails: How bwin is using SQL Server 2016 In-Memory OLTP to achieve unprecedented performance and scale

Considerazioni sull'implementazioneImplementation considerations

È possibile usare tabelle non durevoli ottimizzate per la memoria come un semplice archivio chiave-valore mediante l'archiviazione di un BLOB in una colonna varbinary(max).You can use non-durable memory-optimized tables as a simple key-value store by storing a BLOB in a varbinary(max) columns. In alternativa, è possibile implementare una cache semistrutturata con supporto JSON in SQL Server e nel database SQL di Azure.Alternatively, you can implement a semi-structured cache with JSON support in SQL Server and Azure SQL Database. Infine, è possibile creare una cache relazionale completa tramite tabelle non durevoli con uno schema relazionale completo, compresi vari tipi di dati e vincoli.Finally, you can create a full relational cache through non-durable tables with a full relational schema, including various data types and constraints.

Iniziare a usare lo stato della sessione ASP.NET con ottimizzazione per la memoria sfruttando gli script pubblicati su GitHub per sostituire gli oggetti creati dal provider di stato della sessione SQL Server predefinito:Get started with memory-optimizing ASP.NET session state by leveraging the scripts published on GitHub to replace the objects created by the built-in SQL Server session state provider:

Case study dei clientiCustomer case studies

Sostituzione dell'oggetto tempdbTempdb object replacement

Sfruttare le tabelle non durevoli e i tipi di tabella ottimizzata per la memoria per sostituire le tabelle #temp tradizionali basate su tempdb, le variabili di tabella e i parametri con valori di tabella.Leverage non-durable tables and memory-optimized table types to replace your traditional tempdb-based #temp tables, table variables, and table-valued parameters (TVPs).

Le variabili di tabella con ottimizzazione per la memoria e le tabelle non durevoli riducono in genere l'utilizzo di CPU ed eliminano completamente le operazioni di I/O sui log rispetto alle variabili di tabella tradizionali e alla tabella #temp.Memory-optimized table variables and non-durable tables typically reduce CPU and completely remove log IO, when compared with traditional table variables and #temp table.

Considerazioni sull'implementazioneImplementation considerations

Per iniziare, vedere: Miglioramento delle prestazioni della tabella temporanea e della variabile di tabella con l'ottimizzazione della memoria.To get started see: Improving temp table and table variable performance using memory optimization.

Case study dei clientiCustomer Case Studies

ETL (Extract, Transform, Load, ovvero estrazione, trasformazione e caricamento)ETL (Extract Transform Load)

I flussi di lavoro ETL includono spesso il caricamento dei dati in una tabella di staging, le trasformazioni dei dati e il caricamento nelle tabelle finali.ETL workflows often include load of data into a staging table, transformations of the data, and load into the final tables.

Considerazioni sull'implementazioneImplementation considerations

Usare tabelle non durevoli ottimizzate per la memoria per lo staging dei dati.Use non-durable memory-optimized tables for the data staging. Eliminano completamente tutte le operazioni di I/O e rendono più efficiente l'accesso ai dati.They completely remove all IO, and make data access more efficient.

Se si eseguono le trasformazioni nella tabella di staging come parte del flusso di lavoro, è possibile usare le stored procedure compilate in modo nativo per velocizzare tali trasformazioni.If you perform transformations on the staging table as part of the workflow, you can use natively compiled stored procedures to speed up these transformations. Se è possibile eseguire queste trasformazioni in parallelo, si possono ottenere ulteriori vantaggi di scalabilità dall'ottimizzazione per la memoria.If you can do these transformations in parallel you get additional scaling benefits from the memory-optimization.

Script di esempioSample Script

Prima di iniziare a usare OLTP in memoria, è necessario creare il filegroup MEMORY_OPTIMIZED_DATA.Before you can start using In-Memory OLTP, you need to create a MEMORY_OPTIMIZED_DATA filegroup. Inoltre, è consigliabile usare il livello di compatibilità del database 130 (o versione successiva) e impostare l'opzione di database MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT su ON.In addition, we recommend to use database compatibility level 130 (or higher), and set the database option MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT to ON.

È possibile usare lo script del collegamento seguente per creare il filegroup nella cartella dati predefinita e per configurare le impostazioni consigliate:You can use the script at the following location to create the filegroup in the default data folder, and configure the recommended settings:

Lo script seguente illustra gli oggetti di OLTP in memoria che è possibile creare nel database:The following script illustrates In-Memory OLTP objects you can create in your database:

 -- configure recommended DB option
 ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON
 GO
 -- memory-optimized table
 CREATE TABLE dbo.table1
 ( c1 INT IDENTITY PRIMARY KEY NONCLUSTERED,
   c2 NVARCHAR(MAX))
 WITH (MEMORY_OPTIMIZED=ON)
 GO
 -- non-durable table
 CREATE TABLE dbo.temp_table1
 ( c1 INT IDENTITY PRIMARY KEY NONCLUSTERED,
   c2 NVARCHAR(MAX))
 WITH (MEMORY_OPTIMIZED=ON,
       DURABILITY=SCHEMA_ONLY)
 GO
 -- memory-optimized table type
 CREATE TYPE dbo.tt_table1 AS TABLE
 ( c1 INT IDENTITY,
   c2 NVARCHAR(MAX),
   is_transient BIT NOT NULL DEFAULT (0),
   INDEX ix_c1 HASH (c1) WITH (BUCKET_COUNT=1024))
 WITH (MEMORY_OPTIMIZED=ON)
 GO
 -- natively compiled stored procedure
 CREATE PROCEDURE dbo.usp_ingest_table1
   @table1 dbo.tt_table1 READONLY
 WITH NATIVE_COMPILATION, SCHEMABINDING
 AS
 BEGIN ATOMIC
     WITH (TRANSACTION ISOLATION LEVEL=SNAPSHOT,
           LANGUAGE=N'us_english')

   DECLARE @i INT = 1

   WHILE @i > 0
   BEGIN
     INSERT dbo.table1
     SELECT c2
     FROM @table1
     WHERE c1 = @i AND is_transient=0

     IF @@ROWCOUNT > 0
       SET @i += 1
     ELSE
     BEGIN
       INSERT dbo.temp_table1
       SELECT c2
       FROM @table1
       WHERE c1 = @i AND is_transient=1

       IF @@ROWCOUNT > 0
         SET @i += 1
       ELSE
         SET @i = 0
     END
   END

 END
 GO
 -- sample execution of the proc
 DECLARE @table1 dbo.tt_table1
 INSERT @table1 (c2, is_transient) VALUES (N'sample durable', 0)
 INSERT @table1 (c2, is_transient) VALUES (N'sample non-durable', 1)
 EXECUTE dbo.usp_ingest_table1 @table1=@table1
 SELECT c1, c2 from dbo.table1
 SELECT c1, c2 from dbo.temp_table1
 GO

Risorse per altre informazioni:Resources to learn more: