Ottimizzazione delle prestazioni del database SQL di AzureTuning performance in Azure SQL Database

Il servizio Database SQL di Azure fornisce suggerimenti per migliorare le prestazioni del database. In alternativa, è possibile lasciare che il database di SQL Azure si adatti automaticamente all'applicazione e applichi le modifiche che miglioreranno le prestazioni del carico di lavoro.Azure SQL Database provides recommendations that you can use to improve performance of your database, or you can let Azure SQL Database automatically adapt to your application and apply changes that will improve performance of your workload.

Se non sono disponibili suggerimenti applicabili e continuano a verificarsi problemi di prestazioni, è possibile usare i metodi seguenti per migliorarle:In you don't have any applicable recommendations, and you still have performance issues, you might use the following methods to improve performances:

  1. Aumentare i livelli di servizio e fornire altre risorse al database.Increase service tiers and provide more resources to your database.
  2. Ottimizzare l'applicazione e applicare alcune procedure consigliate che consentono di migliorare le prestazioni.Tune your application and apply some best practices that can improve performance.
  3. Ottimizzare il database modificando gli indici e le query per usare i dati in modo più efficiente.Tune the database by changing indexes and queries to more efficiently work with data.

Questi sono metodi manuali perché è necessario decidere quali livelli di servizio scegliere o è necessario riscrivere il codice dell'applicazione o del database e distribuire le modifiche.These are manual methods because you need to decide what service tiers you would choose or you would need to rewrite application or database code and deploy the changes.

Aumento del livello di prestazioni del databaseIncreasing performance tier of your database

Il servizio Database SQL di Azure offre quattro livelli di servizio tra cui scegliere: Basic, Standard, Premium e Premium RS (le prestazioni sono misurate in unità di trasmissione dati del database o DTU).Azure SQL Database offers four service tiers that you can choose from: Basic, Standard, Premium, and Premium RS (performance is measured in database throughput units, or DTUs. Ogni livello di servizio isola rigorosamente le risorse che possono essere usate dal database SQL e assicura prestazioni prevedibili per tale livello di servizio.Each service tier strictly isolates the resources that your SQL database can use, and guarantees predictable performance for that service level. In questo articolo sono disponibili indicazioni utili per scegliere il livello di servizio per l'applicazione.In this article, we offer guidance that can help you choose the service tier for your application. Viene illustrato anche come ottimizzare l'applicazione per ottenere il massimo dal database SQL di Azure.We also discuss ways that you can tune your application to get the most from Azure SQL Database.

Nota

Questo articolo è incentrato sulle indicazioni relative alle prestazioni per singoli database nel database SQL di Azure.This article focuses on performance guidance for single databases in Azure SQL Database. Per indicazioni sulle prestazioni relative ai pool elastici, vedere le considerazioni su prezzo e prestazioni per i pool elastici.For performance guidance related to elastic pools, see Price and performance considerations for elastic pools. Si noti, tuttavia, che molte raccomandazioni sull'ottimizzazione contenute in questo articolo possono essere applicate ai database in un pool elastico ottenendo vantaggi simili a livello di prestazioni.Note, though, that you can apply many of the tuning recommendations in this article to databases in an elastic pool, and get similar performance benefits.

  • Basic: il livello di servizio Basic offre una prevedibilità di prestazioni soddisfacente per ogni database calcolata a intervalli di un'ora ciascuno.Basic: The Basic service tier offers good performance predictability for each database, hour over hour. In un database Basic una quantità di risorse sufficiente supporta prestazioni ottimali in un database di piccole dimensioni senza richieste simultanee multiple.In a Basic database, sufficient resources support good performance in a small database that doesn't have multiple concurrent requests. I casi di utilizzo tipici in cui si usa il livello di servizio Basic sono:Typical use cases when you would use Basic service tier are:
    • Si sta iniziando a usare il database SQL di Azure.You're just getting started with Azure SQL Database. Le applicazioni sottoposte spesso a sviluppo non necessitano di livelli di prestazioni elevati.Applications that are in development often don't need high-performance levels. I database Basic costituiscono un ambiente ideale per lo sviluppo o il test di database a una fascia di prezzo conveniente.Basic databases are an ideal environment for database development or testing, at a low price point.
    • È presente un database con un singolo utente.You have a database with a single user. Le applicazioni in cui un singolo utente è associato a un database non sono in genere caratterizzate da requisiti elevati per concorrenza e prestazioni.Applications that associate a single user with a database typically don’t have high concurrency and performance requirements. Queste applicazioni sono candidati ottimali per il livello di servizio Basic.These applications are candidates for the Basic service tier.
  • Standard: il livello di servizio Standard offre una migliore prevedibilità delle prestazioni e fornisce prestazioni adeguate per i database con più richieste simultanee, quali le applicazioni di gruppi di lavoro e le applicazioni Web.Standard: The Standard service tier offers improved performance predictability and provides good performance for databases that have multiple concurrent requests, like workgroup and web applications. Quando si sceglie un database con livello di servizio Standard, è possibile ridimensionare le applicazioni di database in base alle prestazioni prevedibili minuto per minuto.When you choose a Standard service tier database, you can size your database application based on predictable performance, minute over minute.
    • Il database presenta più richieste simultanee.Your database has multiple concurrent requests. Le applicazioni che gestiscono più utenti contemporaneamente necessitano in genere di livelli di prestazioni più elevate.Applications that service more than one user at a time usually need higher performance levels. Le applicazioni di gruppo di lavoro o Web con requisiti di traffico I/O tra basso e medio che supportano più query contemporaneamente sono buoni candidati per il livello di servizio Standard.For example, workgroup or web applications that have low to medium IO traffic requirements supporting multiple concurrent queries are good candidates for the Standard service tier.
  • Premium: il livello di servizio Premium offre una prevedibilità delle prestazioni calcolata secondo per secondo per ogni database Premium.Premium: The Premium service tier provides predictable performance, second over second, for each Premium database. Quando si sceglie il livello di servizio Premium, è possibile ridimensionare l'applicazione di database in base al picco di carico per il database specifico.When you choose the Premium service tier, you can size your database application based on the peak load for that database. Il piano rimuove i casi in cui la varianza di prestazione può provocare un'esecuzione più lunga del previsto per query di piccole dimensioni in operazioni sensibili alla latenza.The plan removes cases in which performance variance can cause small queries to take longer than expected in latency-sensitive operations. Questo modello può semplificare notevolmente i cicli di sviluppo e di convalida del prodotto per applicazioni per cui occorre fare dichiarazioni forti sulle esigenze di risorse relative ai picchi, sulla varianza di prestazioni o sulla latenza di query.This model can greatly simplify the development and product validation cycles for applications that need to make strong statements about peak resource needs, performance variance, or query latency. La maggior parte dei casi d'uso del livello di servizio Premium presenta una o più di queste caratteristiche:Most Premium service tier use cases have one or more of these characteristics:
    • Picchi di carico elevati.High peak load. Un'applicazione che richiede una quantità elevata di CPU, memoria o input/output (I/O) per il completamento delle operazioni necessita di un livello a prestazioni elevate dedicato.An application that requires substantial CPU, memory, or input/output (I/O) to complete its operations requires a dedicated, high-performance level. Il livello di servizio Premium è ad esempio ideale nel caso in cui un'operazione di database utilizzi più core CPU per un lungo periodo di tempo.For example, a database operation known to consume several CPU cores for an extended time is a candidate for the Premium service tier.
    • Molte richieste simultanee.Many concurrent requests. Alcune applicazioni di database gestiscono molte richieste simultanee, ad esempio durante l'utilizzo di un sito Web con un elevato volume di traffico.Some database applications service many concurrent requests, for example, when serving a website that has a high traffic volume. Per i livelli di servizio Basic e Standard sono previsti limiti al numero di richieste simultanee per ogni database.Basic and Standard service tiers limit the number of concurrent requests per database. Per le applicazioni per cui sono richieste più connessioni devono essere selezionate dimensioni di prenotazione appropriate per gestire il numero massimo di richieste necessarie.Applications that require more connections would need to choose an appropriate reservation size to handle the maximum number of needed requests.
    • Bassa latenza.Low latency. Alcune applicazioni devono garantire una risposta dal database in tempi minimi.Some applications need to guarantee a response from the database in minimal time. Se una stored procedure specifica viene chiamata durante un'operazione più ampia del cliente, potrebbe essere necessario che la risposta alla chiamata in questione venga assicurata in non più di 20 millisecondi nel 99% dei casi.If a specific stored procedure is called as part of a broader customer operation, you might have a requirement to have a return from that call in no more than 20 milliseconds, 99 percent of the time. Questo tipo di applicazione trarrà vantaggio dal livello di servizio Premium per assicurare la disponibilità della potenza di calcolo necessaria.This type of application benefits from the Premium service tier, to make sure that the required computing power is available.
  • Premium RS: questo livello è progettato per carichi di lavoro con un elevato numero di operazioni di I/O che non richiedono una garanzia di disponibilità massima.Premium RS: The Premium RS tier is designed for IO-intensive workloads that do not require the highest availability guarantees. Gli esempi includono test di carichi di lavoro ad alte prestazioni e carichi di lavoro di analisi in cui il database non è il sistema di registrazione.Examples include testing high-performance workloads, or an analytical workload where the database is not the system of record.

Il livello di servizio esatto necessario per il database SQL dipende dai requisiti del carico massimo per ogni dimensione di risorsa.The service level that you need for your SQL database depends on the peak load requirements for each resource dimension. È possibile che alcune applicazioni usino quantità irrilevanti di una singola risorsa, ma abbiano requisiti significativi per altre.Some applications use a trivial amount of a single resource, but have significant requirements for other resources.

Limiti e funzionalità dei livelli di servizioService tier capabilities and limits

È possibile configurare il livello di prestazioni per ogni livello di servizio. Questa flessibilità consente di pagare solo la capacità necessaria.At each service tier, you set the performance level, so you have the flexibility to pay only for the capacity you need. È possibile regolare la capacità, in base alle modifiche del carico di lavoro.You can adjust capacity, up or down, as workload changes. Ad esempio, se il carico di lavoro del database è intenso durante il periodo di acquisti per il ritorno a scuola, è possibile aumentare il livello di prestazioni per il database per un periodo specifico, da luglio a settembre.For example, if your database workload is high during the back-to-school shopping season, you might increase the performance level for the database for a set time, July through September. È quindi possibile ridurlo al termine del picco stagionale.You can reduce it when your peak season ends. È possibile ridurre al minimo i costi del servizio ottimizzando l'ambiente cloud in base alla stagionalità della propria attività.You can minimize what you pay by optimizing your cloud environment to the seasonality of your business. Questo modello è adatto anche per i cicli di rilascio di prodotti software.This model also works well for software product release cycles. Un team di test può allocare la capacità durante l'esecuzione di test e quindi rilasciare tale capacità al termine dei test.A test team might allocate capacity while it does test runs, and then release that capacity when they finish testing. In un modello basato sulla richiesta di capacità, si paga la capacità necessaria, evitando i costi per risorse dedicate usate raramente.In a capacity request model, you pay for capacity as you need it, and avoid spending on dedicated resources that you might rarely use.

Vantaggi dei livelli di servizioWhy service tiers?

Anche se ogni carico di lavoro può presentare caratteristiche diverse, lo scopo dei livelli di servizio è offrire la prevedibilità delle prestazioni a diversi livelli.Although each database workload can differ, the purpose of service tiers is to provide performance predictability at various performance levels. I clienti con requisiti di risorse di database su larga scala possono operare in un ambiente di calcolo più dedicato.Customers with large-scale database resource requirements can work in a more dedicated computing environment.

Ottimizzare l'applicazioneTune your application

Nel sistema SQL Server tradizionale in locale il processo di pianificazione della capacità iniziale è spesso separato dal processo di esecuzione di un'applicazione in produzione.In traditional on-premises SQL Server, the process of initial capacity planning often is separated from the process of running an application in production. Vengono acquistate prima di tutto le licenze per hardware e prodotti e l'ottimizzazione delle prestazioni viene eseguita in un secondo momento.Hardware and product licenses are purchased first, and performance tuning is done afterward. Quando si usa il database SQL di Azure, è consigliabile eseguire e ottimizzare al tempo stesso un'applicazione.When you use Azure SQL Database, it's a good idea to interweave the process of running an application and tuning it. Il modello di pagamento della capacità su richiesta consente di ottimizzare l'applicazione in modo da usare la quantità minima di risorse necessaria al momento specifico, invece di effettuare l'overprovisioning dell'hardware in base a ipotesi di piani di crescita futura per un'applicazione, che spesso si rivelano errati.With the model of paying for capacity on demand, you can tune your application to use the minimum resources needed now, instead of overprovisioning on hardware based on guesses of future growth plans for an application, which often are incorrect. Alcuni clienti potrebbero decidere di non ottimizzare un'applicazione e scegliere in alternativa di effettuare l'overprovisioning delle risorse hardware.Some customers might choose not to tune an application, and instead choose to overprovision hardware resources. Questo approccio potrebbe risultare valido se non si vuole modificare un'applicazione chiave in un periodo di attività elevata.This approach might be a good idea if you don't want to change a key application during a busy period. L'ottimizzazione di un'applicazione può consentire tuttavia di ridurre i requisiti delle risorse e l'importo delle fatture mensili quando si usano i livelli di servizio nel database SQL di Azure.But, tuning an application can minimize resource requirements and lower monthly bills when you use the service tiers in Azure SQL Database.

Caratteristiche dell'applicazioneApplication characteristics

Anche se i livelli di servizio del database SQL di Azure sono progettati per migliorare la stabilità e la prevedibilità delle prestazioni di un'applicazione, alcune procedure consigliate consentono di ottimizzare l'applicazione in modo da sfruttare al meglio le risorse in un livello di prestazioni.Although Azure SQL Database service tiers are designed to improve performance stability and predictability for an application, some best practices can help you tune your application to better take advantage of the resources at a performance level. Anche se in molte applicazioni è possibile ottenere miglioramenti significativi delle prestazioni semplicemente passando a un livello di prestazioni o di servizio superiore, questo vantaggio non è assicurato per tutte le applicazioni senza un'ottimizzazione aggiuntiva.Although many applications have significant performance gains simply by switching to a higher performance level or service tier, some applications need additional tuning to benefit from a higher level of service. Per ottenere un aumento delle prestazioni, prendere in considerazione operazioni di ottimizzazione aggiuntive per le applicazioni con queste caratteristiche:For increased performance, consider additional application tuning for applications that have these characteristics:

  • Applicazioni con prestazioni ridotte a causa di un comportamento "eccessivamente comunicativo".Applications that have slow performance because of "chatty" behavior. Le applicazioni con un livello di comunicazioni elevato eseguono un numero eccessivo di operazioni di accesso ai dati, sensibili alla latenza di rete.Chatty applications make excessive data access operations that are sensitive to network latency. Potrebbe essere necessario modificare questi tipi di applicazioni, in modo da ridurre il numero di operazioni di accesso ai dati nel database SQL.You might need to modify these kinds of applications to reduce the number of data access operations to the SQL database. È ad esempio possibile migliorare le prestazioni dell'applicazione usando tecniche come l'invio in batch di query ad hoc o lo spostamento delle query in stored procedure.For example, you might improve application performance by using techniques like batching ad-hoc queries or moving the queries to stored procedures. Per altre informazioni, vedere Invio di query in batch.For more information, see Batch queries.
  • Database con un carico di lavoro elevato che non possono essere supportati da una singola macchina virtuale intera.Databases with an intensive workload that can't be supported by an entire single machine. I database che superano le risorse del livello di prestazioni Premium più elevato potrebbero trarre vantaggio dall'aumento del numero di istanze del carico di lavoro.Databases that exceed the resources of the highest Premium performance level might benefit from scaling out the workload. Per altre informazioni, vedere Partizionamento orizzontale tra database e Partizionamento funzionale.For more information, see Cross-database sharding and Functional partitioning.
  • Applicazioni con query non ottimali.Applications that have suboptimal queries. Le applicazioni, in particolare quelle incluse nel livello di accesso ai dati, con query non ottimizzate correttamente potrebbero non ottenere vantaggi da un livello di prestazioni superiore.Applications, especially those in the data access layer, that have poorly tuned queries might not benefit from a higher performance level. Tra queste sono incluse query prive della clausola WHERE, con indici mancanti o con statistiche obsolete.This includes queries that lack a WHERE clause, have missing indexes, or have outdated statistics. Queste applicazioni possono trarre vantaggio dalle tecniche standard di ottimizzazione delle prestazioni delle query.These applications benefit from standard query performance-tuning techniques. Per altre informazioni, vedere Indici mancanti e Hint/Ottimizzazione di query.For more information, see Missing indexes and Query tuning and hinting.
  • Applicazioni con struttura di accesso ai dati non ottimale.Applications that have suboptimal data access design. Le applicazioni con problemi intrinseci di concorrenza per l'accesso ai dati, ad esempio il deadlock, potrebbero non trarre vantaggio da un livello di prestazioni superiore.Applications that have inherent data access concurrency issues, for example deadlocking, might not benefit from a higher performance level. Provare a ridurre i round trip nel database SQL di Azure memorizzando nella cache i dati sul lato client con il servizio Caching di Azure o un'altra tecnologia di memorizzazione nella cache.Consider reducing round trips against the Azure SQL Database by caching data on the client side with the Azure Caching service or another caching technology. Vedere Memorizzazione nella cache a livello di applicazione.See Application tier caching.

Ottimizzare il databaseTune your database

Questa sezione illustra alcune tecniche che è possibile usare per ottimizzare il database SQL di Azure, in modo da ottenere le prestazioni migliori per l'applicazione ed eseguirla al livello di prestazioni più basso possibile.In this section, we look at some techniques that you can use to tune Azure SQL Database to gain the best performance for your application and run it at the lowest possible performance level. Alcune di queste tecniche corrispondono alle tradizionali procedure consigliate di ottimizzazione di SQL Server, ma altre sono specifiche del database SQL di Azure.Some of these techniques match traditional SQL Server tuning best practices, but others are specific to Azure SQL Database. In alcuni casi è possibile esaminare le risorse utilizzate per un database, in modo da individuare aree che richiedono ottimizzazione aggiuntiva e da estendere le tecniche tradizionali di SQL Server per l'uso nel database SQL di in Azure.In some cases, you can examine the consumed resources for a database to find areas to further tune and extend traditional SQL Server techniques to work in Azure SQL Database.

Identificare i problemi di prestazioni usando il portale di AzureIdentify performance issues using Azure portal

Nel portale di Azure sono disponibili i seguenti strumenti, utili per l'analisi e la risoluzione dei problemi di prestazioni del database SQL:The following tools in the Azure portal can help you analyze and fix performance issues with your SQL database:

Il portale di Azure include altre informazioni su entrambi gli strumenti e sul relativo uso.The Azure portal has more information about both of these tools and how to use them. Per diagnosticare e correggere in modo efficiente i problemi, è consigliabile provare prima di tutto a usare gli strumenti nel portale di Azure.To efficiently diagnose and correct problems, we recommend that you first try the tools in the Azure portal. È consigliabile usare gli approcci di ottimizzazione manuale illustrati di seguito, per gli indici mancanti e l'ottimizzazione delle query, solo in casi speciali.We recommend that you use the manual tuning approaches that we discuss next, for missing indexes and query tuning, in special cases.

Altre informazioni sull'identificazione dei problemi in Database SQL di Azure sono disponibili nell'articolo sul monitoraggio delle prestazioni.Find more information about identifying issues in Azure SQL Database on performance monitoring article.

Identificazione e aggiunta di indici mancantiIdentifying and adding missing indexes

Un problema comune nelle prestazioni del database OLTP è correlato alla progettazione fisica del database.A common problem in OLTP database performance relates to the physical database design. Spesso gli schemi di database vengono progettati e forniti senza verificare la scala (nel caricamento o nel volume di dati).Often, database schemas are designed and shipped without testing at scale (either in load or in data volume). Le prestazioni di un piano di query possono essere accettabili su scala ridotta, ma potrebbero purtroppo peggiorare notevolmente in caso di volumi elevati di dati a livello di produzione.Unfortunately, the performance of a query plan might be acceptable on a small scale but degrade substantially under production-level data volumes. L'origine più comune di questo problema è legata alla mancanza di indici adatti per soddisfare i filtri o altre restrizioni in una query.The most common source of this issue is the lack of appropriate indexes to satisfy filters or other restrictions in a query. La mancanza di indici si manifesta spesso con una scansione di tabella quando potrebbe essere sufficiente una ricerca dell'indice.Often, missing indexes manifests as a table scan when an index seek could suffice.

In questo esempio il piano di query selezionato usa un'analisi quando invece sarebbe sufficiente una ricerca:In this example, the selected query plan uses a scan when a seek would suffice:

DROP TABLE dbo.missingindex;
CREATE TABLE dbo.missingindex (col1 INT IDENTITY PRIMARY KEY, col2 INT);
DECLARE @a int = 0;
SET NOCOUNT ON;
BEGIN TRANSACTION
WHILE @a < 20000
BEGIN
    INSERT INTO dbo.missingindex(col2) VALUES (@a);
    SET @a += 1;
END
COMMIT TRANSACTION;
GO
SELECT m1.col1
FROM dbo.missingindex m1 INNER JOIN dbo.missingindex m2 ON(m1.col1=m2.col1)
WHERE m1.col2 = 4;

Piano di query con indici mancanti

Il database SQL di Azure può consentire di trovare e risolvere condizioni comuni di indici mancanti.Azure SQL Database can help you find and fix common missing index conditions. Le viste a gestione dinamica integrate nel database SQL di Azure esaminano le compilazioni di query in cui un indice ridurrebbe in modo significativo il costo stimato per l'esecuzione di una query.DMVs that are built into Azure SQL Database look at query compilations in which an index would significantly reduce the estimated cost to run a query. Durante l'esecuzione di query, il database SQL tiene traccia della frequenza con cui viene eseguito ogni piano di query e del divario stimato tra il piano di query eseguito e quello previsto in presenza dell'indice.During query execution, SQL Database tracks how often each query plan is executed, and tracks the estimated gap between the executing query plan and the imagined one where that index existed. È possibile usare queste viste a gestione dinamica per ipotizzare rapidamente quali modifiche alla progettazione fisica del database potrebbero migliorare il costo complessivo del carico di lavoro per un database e il rispettivo carico di lavoro reale.You can use these DMVs to quickly guess which changes to your physical database design might improve overall workload cost for a database and its real workload.

È possibile usare questa query per valutare gli indici potenzialmente mancanti:You can use this query to evaluate potential missing indexes:

SELECT CONVERT (varchar, getdate(), 126) AS runtime,
    mig.index_group_handle, mid.index_handle,
    CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact *
            (migs.user_seeks + migs.user_scans)) AS improvement_measure,
    'CREATE INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' +
              CONVERT (varchar, mid.index_handle) + ' ON ' + mid.statement + '
              (' + ISNULL (mid.equality_columns,'')
              + CASE WHEN mid.equality_columns IS NOT NULL
                          AND mid.inequality_columns IS NOT NULL
                     THEN ',' ELSE '' END + ISNULL (mid.inequality_columns, '')
              + ')'
              + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
    migs.*,
    mid.database_id,
    mid.[object_id]
FROM sys.dm_db_missing_index_groups AS mig
INNER JOIN sys.dm_db_missing_index_group_stats AS migs
    ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid
    ON mig.index_handle = mid.index_handle
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

In questo esempio la query ha restituito questo suggerimento:In this example, the query resulted in this suggestion:

CREATE INDEX missing_index_5006_5005 ON [dbo].[missingindex] ([col2])  

Dopo la creazione, la stessa istruzione SELECT seleziona un piano diverso, che usa una ricerca invece di un'analisi, e quindi esegue il piano in modo più efficiente:After it's created, that same SELECT statement picks a different plan, which uses a seek instead of a scan, and then executes the plan more efficiently:

Piano di query con indici corretti

L'aspetto chiave è che la capacità di I/O di un sistema apposito condiviso è più limitata di un server dedicato.The key insight is that the I/O capacity of a shared, commodity system is more limited than that of a dedicated server machine. Esiste un vantaggio nella riduzione dell'I/O non necessario, che consente di sfruttare al massimo il sistema nel valore di DTU di ogni livello di prestazioni incluso nei livelli di servizio per il database SQL di Azure.There's a premium on minimizing unnecessary I/O to take maximum advantage of the system in the DTU of each performance level of the Azure SQL Database service tiers. Le opzioni appropriate di progettazione fisica del database possono migliorare notevolmente la latenza delle singole query, la velocità effettiva di richieste simultanee gestibili per unità di scala e ridurre i costi necessari per soddisfare la query.Appropriate physical database design choices can significantly improve the latency for individual queries, improve the throughput of concurrent requests handled per scale unit, and minimize the costs required to satisfy the query. Per altre informazioni sulle DMV di indici mancanti, vedere sys.dm_db_missing_index_details.For more information about the missing index DMVs, see sys.dm_db_missing_index_details.

Hint/Ottimizzazione di queryQuery tuning and hinting

Query Optimizer nel database SQL di Azure è simile a Query Optimizer tradizionale di SQL Server.The query optimizer in Azure SQL Database is similar to the traditional SQL Server query optimizer. La maggior parte delle procedure consigliate per ottimizzare le query e per comprendere i motivi delle limitazioni del modello per Query Optimizer si applica anche al database SQL di Azure.Most of the best practices for tuning queries and understanding the reasoning model limitations for the query optimizer also apply to Azure SQL Database. Se si ottimizzano query nel database SQL di Azure, si potrebbe ottenere il vantaggio aggiuntivo della riduzione delle richieste di risorsa aggregate.If you tune queries in Azure SQL Database, you might get the additional benefit of reducing aggregate resource demands. L'applicazione potrebbe essere eseguita a un costo inferiore rispetto a un'applicazione equivalente non ottimizzata, perché potrebbe usare un livello di prestazioni inferiore.Your application might be able to run at a lower cost than an untuned equivalent because it can run at a lower performance level.

Un esempio comune in SQL Server e applicabile anche al database SQL di Azure è costituito dal modo in cui Query Optimizer "analizza" i parametri.An example that is common in SQL Server and which also applies to Azure SQL Database is how the query optimizer "sniffs" parameters. Durante la compilazione, Query Optimizer valuta il valore corrente di un parametro per determinare se può generare un piano di query più idoneo.During compilation, the query optimizer evaluates the current value of a parameter to determine whether it can generate a more optimal query plan. Anche se questa strategia può comportare spesso la creazione di un piano di query significativamente più veloce rispetto a un piano compilato senza valori di parametri noti, non funziona attualmente in modo perfetto in SQL Server e nel database SQL di Azure.Although this strategy often can lead to a query plan that is significantly faster than a plan compiled without known parameter values, currently it works imperfectly both in SQL Server and in Azure SQL Database. In alcuni casi il parametro non viene analizzato e in altri casi viene analizzato ma il piano generato non è ottimale per l'intero set di valori di parametri in un carico di lavoro.Sometimes the parameter is not sniffed, and sometimes the parameter is sniffed but the generated plan is suboptimal for the full set of parameter values in a workload. Microsoft include gli hint per la query (direttive) per consentire di specificare la finalità più deliberatamente ed eseguire l'override del comportamento predefinito per l'analisi dei parametri.Microsoft includes query hints (directives) so that you can specify intent more deliberately and override the default behavior of parameter sniffing. Se si usano gli hint, è spesso possibile risolvere i casi in cui il comportamento predefinito di SQL Server o del database SQL di Azure non è perfetto per il carico di lavoro di un cliente specifico.Often, if you use hints, you can fix cases in which the default SQL Server or Azure SQL Database behavior is imperfect for a specific customer workload.

L'esempio successivo illustra il modo in cui Query Processor può generare un piano non ottimale per i requisiti di prestazioni e risorse.The next example demonstrates how the query processor can generate a plan that is suboptimal both for performance and resource requirements. L'esempio mostra anche che l'uso di un hint di query può consentire di ridurre il tempo di esecuzione delle query e i requisiti delle risorse per il database SQL:This example also shows that if you use a query hint, you can reduce query run time and resource requirements for your SQL database:

DROP TABLE psptest1;
CREATE TABLE psptest1(col1 int primary key identity, col2 int, col3 binary(200));

DECLARE @a int = 0;
SET NOCOUNT ON;
BEGIN TRANSACTION
WHILE @a < 20000
BEGIN
    INSERT INTO psptest1(col2) values (1);
    INSERT INTO psptest1(col2) values (@a);
    SET @a += 1;
END
COMMIT TRANSACTION
CREATE INDEX i1 on psptest1(col2);
GO

CREATE PROCEDURE psp1 (@param1 int)
AS
BEGIN
    INSERT INTO t1 SELECT * FROM psptest1
    WHERE col2 = @param1
    ORDER BY col2;
END
GO

CREATE PROCEDURE psp2 (@param2 int)
AS
BEGIN
    INSERT INTO t1 SELECT * FROM psptest1 WHERE col2 = @param2
    ORDER BY col2
    OPTION (OPTIMIZE FOR (@param2 UNKNOWN))
END
GO

CREATE TABLE t1 (col1 int primary key, col2 int, col3 binary(200));
GO

Tramite il codice di installazione viene creata una tabella indicante la differenza nella distribuzione dei dati.The setup code creates a table that has skewed data distribution. Il piano di query ottimale varia in base al parametro selezionato.The optimal query plan differs based on which parameter is selected. Il comportamento di memorizzazione nella cache del piano non esegue sempre la ricompilazione della query in base al valore di parametro più comune, purtroppo.Unfortunately, the plan caching behavior doesn't always recompile the query based on the most common parameter value. È quindi possibile che un piano non ottimale venga memorizzato nella cache e usato per molti valori, anche se un piano diverso potrebbe costituire mediamente una scelta migliore.So, it's possible for a suboptimal plan to be cached and used for many values, even when a different plan might be a better plan choice on average. Il piano di query crea quindi due stored procedure identiche, ad eccezione del fatto che una include un hint di query speciale.Then the query plan creates two stored procedures that are identical, except that one has a special query hint.

Esempio (parte 1)Example, part 1

-- Prime Procedure Cache with scan plan
EXEC psp1 @param1=1;
TRUNCATE TABLE t1;

-- Iterate multiple times to show the performance difference
DECLARE @i int = 0;
WHILE @i < 1000
BEGIN
    EXEC psp1 @param1=2;
    TRUNCATE TABLE t1;
    SET @i += 1;
END

Esempio (parte 2)Example, part 2

È consigliabile attendere almeno 10 minuti prima di iniziare la parte 2 dell'esempio, in modo che i risultati siano distinti nei dati di telemetria risultanti.(We recommend that you wait at least 10 minutes before you begin part 2 of the example, so that the results are distinct in the resulting telemetry data.)

EXEC psp2 @param2=1;
TRUNCATE TABLE t1;

DECLARE @i int = 0;
WHILE @i < 1000
BEGIN
    EXEC psp2 @param2=2;
    TRUNCATE TABLE t1;
    SET @i += 1;
END

Ogni parte di questo esempio prova a eseguire 1.000 volte un'istruzione INSERT con parametri, per generare un carico sufficiente utilizzabile in un set di dati di test.Each part of this example attempts to run a parameterized insert statement 1,000 times (to generate a sufficient load to use as a test data set). Durante l'esecuzione di stored procedure, Query Processor esamina il valore del parametro passato alla procedura durante la prima compilazione ("analisi" dei parametri).When it executes stored procedures, the query processor examines the parameter value that is passed to the procedure during its first compilation (parameter "sniffing"). Query Processor memorizza nella cache il piano risultante e lo usa per le chiamate successive, anche se il valore del parametro è diverso.The processor caches the resulting plan and uses it for later invocations, even if the parameter value is different. È possibile che non venga usato il piano ottimale in tutti i casi.The optimal plan might not be used in all cases. È a volte necessario consentire a Query Optimizer la selezione di un piano che sia migliore per la metà dei casi anziché per il caso specifico quando la query viene compilata per la prima volta.Sometimes you need to guide the optimizer to pick a plan that is better for the average case rather than the specific case from when the query was first compiled. In questo esempio, il piano iniziale genera un piano di "analisi" che legge tutte le righe per cercare tutti i valori corrispondenti al parametro:In this example, the initial plan generates a "scan" plan that reads all rows to find each value that matches the parameter:

Ottimizzazione delle query mediante un piano di analisi

Poiché la procedura è stata eseguita con il valore 1, il piano risultante è ottimale per il valore 1, ma non per tutti gli altri valori nella tabella.Because we executed the procedure by using the value 1, the resulting plan was optimal for the value 1 but was suboptimal for all other values in the table. È probabile che il risultato non sia quello che si sceglierebbe se si potesse selezionare casualmente ogni piano, perché presenta prestazioni inferiori e usa una quantità maggiore di risorse.The result likely isn't what you would want if you were to pick each plan randomly, because the plan performs more slowly and uses more resources.

Se si esegue il test con SET STATISTICS IO impostato su ON, le operazioni di analisi logica in questo esempio vengono eseguite in background.If you run the test with SET STATISTICS IO set to ON, the logical scan work in this example is done behind the scenes. Come si può notare, 1.148 operazioni di lettura vengono eseguite dal piano e ciò è poco efficiente, se il caso medio consiste nel restituire solo una riga:You can see that there are 1,148 reads done by the plan (which is inefficient, if the average case is to return just one row):

Ottimizzazione delle query mediante un'analisi logica

La seconda parte dell'esempio usa un hint per la query per indicare a Query Optimizer di usare un valore specifico durante il processo di compilazione.The second part of the example uses a query hint to tell the optimizer to use a specific value during the compilation process. In questo caso impone a Query Processor di ignorare il valore passato come parametro e di presupporre invece UNKNOWN.In this case, it forces the query processor to ignore the value that is passed as the parameter, and instead to assume UNKNOWN. Ciò fa riferimento a un valore con frequenza media nella tabella, ignorando eventuali asimmetrie.This refers to a value that has the average frequency in the table (ignoring skew). Il piano risultante è un piano basato su ricerca, più veloce e con un minore impiego medio di risorse rispetto al piano della parte 1 dell'esempio:The resulting plan is a seek-based plan that is faster and uses fewer resources, on average, than the plan in part 1 of this example:

Ottimizzazione delle query mediante un hint di query

È possibile verificarne l'effetto nella tabella sys.resource_stats. Si verifica un ritardo dal momento in cui il test viene eseguito a quando i dati popolano la tabella.You can see the effect in the sys.resource_stats table (there is a delay from the time that you execute the test and when the data populates the table). Per questo esempio, la parte 1 è stata eseguita durante l'intervallo di tempo 22:25:00 e la parte 2 nell'intervallo di tempo 22:35:00.For this example, part 1 executed during the 22:25:00 time window, and part 2 executed at 22:35:00. Nell'intervallo di tempo precedente sono state usate più risorse rispetto a quello successivo, in seguito ai miglioramenti di efficienza di piano.The earlier time window used more resources in that time window than the later one (because of plan efficiency improvements).

SELECT TOP 1000 *
FROM sys.resource_stats
WHERE database_name = 'resource1'
ORDER BY start_time DESC

Risultati di esempio di ottimizzazione delle query

Nota

Anche se il volume in questo esempio è intenzionalmente ridotto, l'effetto dei parametri non ottimali può essere significativo, in particolare nei database di dimensioni più grandi.Although the volume in this example is intentionally small, the effect of suboptimal parameters can be substantial, especially on larger databases. La differenza, nei casi estremi, può essere di alcuni secondi per i casi veloci e di alcune ore per i casi lenti.The difference, in extreme cases, can be between seconds for fast cases and hours for slow cases.

Esaminando sys.resource_stats è possibile determinare se la risorsa usata per un test usa una quantità di risorse superiore o inferiore rispetto a un altro test.You can examine sys.resource_stats to determine whether the resource for a test uses more or fewer resources than another test. Quando si confrontano i dati, separare gli intervalli di test in modo che non rientrino nella stessa finestra di 5 minuti nella vista sys.resource_stats.When you compare data, separate the timing of tests so that they are not in the same 5-minute window in the sys.resource_stats view. L'obiettivo dell'esercizio consiste nel ridurre la quantità totale di risorse usate e non di ridurre le risorse di picco.The goal of the exercise is to minimize the total amount of resources used, and not to minimize the peak resources. In genere, anche con l'ottimizzazione di una parte di codice per la latenza viene ridotto il consumo di risorse.Generally, optimizing a piece of code for latency also reduces resource consumption. Assicurarsi che le modifiche apportate a un'applicazione siano necessarie e che non influiscano negativamente sull'esperienza dei clienti che potrebbero usare hint di query nell'applicazione.Make sure that the changes you make to an application are necessary, and that the changes don't negatively affect the customer experience for someone who might be using query hints in the application.

Se un carico di lavoro include un set di query ripetute, è spesso consigliabile acquisire e confermare la validità delle scelte del piano perché determinerà l'unità di dimensioni minima delle risorse per ospitare il database.If a workload has a set of repeating queries, often it makes sense to capture and validate the optimality of your plan choices because it drives the minimum resource size unit required to host the database. Dopo la convalida, esaminare di nuovo occasionalmente i piani per assicurarsi che siano ancora ottimali.After you validate it, occasionally reexamine the plans to help you make sure that they have not degraded. Per altre informazioni, vedere Hint per la query (Transact-SQL).You can learn more about query hints (Transact-SQL).

Partizionamento orizzontale tra databaseCross-database sharding

Poiché il database SQL di Azure viene eseguito in hardware apposito, i limiti della capacità per un database singolo sono inferiori a quelli per un'installazione locale tradizionale di SQL Server.Because Azure SQL Database runs on commodity hardware, the capacity limits for a single database are lower than for a traditional on-premises SQL Server installation. Alcuni clienti usano tecniche di partizionamento orizzontale per estendere le operazioni in più database quando non rientrano nei limiti relativi a un database singolo nel database SQL di Azure.Some customers use sharding techniques to spread database operations over multiple databases when the operations don't fit inside the limits of a single database in Azure SQL Database. La maggior parte dei clienti che usa le tecniche di partizionamento orizzontale nel database SQL di Azure suddivide i dati di una singola dimensione in più database.Most customers who use sharding techniques in Azure SQL Database split their data on a single dimension across multiple databases. Per questo approccio è necessario comprendere che le applicazioni OLTP eseguono spesso transazioni applicabili a una riga o a un piccolo gruppo di righe nello schema.For this approach, you need to understand that OLTP applications often perform transactions that apply to only one row or to a small group of rows in the schema.

Nota

Il database SQL offre ora una libreria per semplificare il partizionamento orizzontale.SQL Database now provides a library to assist with sharding. Per altre informazioni, vedere Panoramica della libreria client dei database elastici.For more information, see Elastic Database client library overview.

Se, ad esempio, un database include il nome del cliente, l'ordine e i dettagli dell'ordine (come illustrato nel database di esempio tradizionale Northwind incluso in SQL Server), è possibile suddividere questi dati in più database raggruppando un cliente con l'ordine correlato e con le informazioni dettagliate sull'ordine.For example, if a database has customer name, order, and order details (like the traditional example Northwind database that ships with SQL Server), you could split this data into multiple databases by grouping a customer with the related order and order detail information. È possibile assicurare che i dati del cliente rimangano in un singolo database.You can guarantee that the customer's data stays in a single database. L'applicazione suddividerebbe i diversi clienti tra database, estendendo di fatto il carico tra più database.The application would split different customers across databases, effectively spreading the load across multiple databases. Con il partizionamento orizzontale, i clienti possono evitare di raggiungere il limite massimo delle dimensioni del database, ma anche il database SQL di Azure può elaborare carichi di lavoro notevolmente maggiori rispetto ai limiti corrispondenti ai diversi livelli di prestazioni, a condizione che ogni singolo database rientri nel relativo valore di DTU.With sharding, customers not only can avoid the maximum database size limit, but Azure SQL Database also can process workloads that are significantly larger than the limits of the different performance levels, as long as each individual database fits into its DTU.

Anche se il partizionamento orizzontale del database non riduce la capacità aggregata delle risorse per una soluzione, è notevolmente efficace nel supportare soluzioni di dimensioni molto elevate distribuite in più database.Although database sharding doesn't reduce the aggregate resource capacity for a solution, it's highly effective at supporting very large solutions that are spread over multiple databases. Ogni database può essere eseguito a livelli di prestazioni diversi per supportare database "efficaci" di dimensioni molto grandi con requisiti molto elevati a livello di risorse.Each database can run at a different performance level to support very large, "effective" databases with high resource requirements.

Partizionamento funzionaleFunctional partitioning

Gli utenti di SQL Server combinano spesso molte funzioni all'interno di un singolo database.SQL Server users often combine many functions in a single database. Se un'applicazione include ad esempio la logica per gestire l'inventario di un negozio, è possibile che quel database includa la logica associata all'inventario, il rilevamento degli ordini di acquisto, le stored procedure e le viste indicizzate o materializzate mediante le quali sono stati gestiti i report di fine mese.For example, if an application has logic to manage inventory for a store, that database might have logic associated with inventory, tracking purchase orders, stored procedures, and indexed or materialized views that manage end-of-month reporting. Questa tecnica semplifica l'amministrazione del database per operazioni quali il backup, ma richiede anche il ridimensionamento dell'hardware per gestire il carico massimo in tutte le funzioni di un'applicazione.This technique makes it easier to administer the database for operations like backup, but it also requires you to size the hardware to handle the peak load across all functions of an application.

Se si usa un'architettura con aumento del numero di istanze nel database SQL di Azure, è consigliabile suddividere le diverse funzioni di un'applicazione in diversi database.If you use a scale-out architecture in Azure SQL Database, it's a good idea to split different functions of an application into different databases. Se si usa questa tecnica, ogni applicazione viene ridimensionata in modo indipendente.By using this technique, each application scales independently. Quando un'applicazione viene usata con maggiore frequenza e il carico nel relativo database aumenta, l'amministratore può scegliere un livello di prestazioni indipendente per ogni funzione in un'applicazione.As an application becomes busier (and the load on the database increases), the administrator can choose independent performance levels for each function in the application. Nei limiti, questa architettura fa sì che le dimensioni di un'applicazione diventino più grandi di quelle gestibili da una singola macchina apposita, perché il carico viene distribuito in più macchine.At the limit, with this architecture, an application can be larger than a single commodity machine can handle because the load is spread across multiple machines.

Invio di query in batchBatch queries

Per le applicazioni che accedono ai dati con un uso frequente ed elevato di query ad hoc, gran parte del tempo di risposta viene speso nelle comunicazioni di rete tra il livello applicazione e il livello di database SQL di Azure.For applications that access data by using high-volume, frequent, ad hoc querying, a substantial amount of response time is spent on network communication between the application tier and the Azure SQL Database tier. Anche quando l'applicazione e il database SQL di Azure risiedono nello stesso data center, la latenza di rete tra questi due elementi potrebbe essere aumentata per un numero elevato di operazioni di accesso ai dati.Even when both the application and Azure SQL Database are in the same data center, the network latency between the two might be magnified by a large number of data access operations. Per ridurre i round trip di rete per le operazioni di accesso ai dati, prendere in considerazione l'uso dell'opzione per l'invio in batch delle query ad hoc o per la compilazione delle query come stored procedure.To reduce the network round trips for the data access operations, consider using the option to either batch the ad hoc queries, or to compile them as stored procedures. L'invio in batch delle query ad hoc consente di inviare più query come un unico grande batch in una singola operazione al database SQL di Azure.If you batch the ad hoc queries, you can send multiple queries as one large batch in a single trip to Azure SQL Database. La compilazione di query ad hoc in stored procedure può produrre lo stesso risultato dell'invio in batch.If you compile ad hoc queries in a stored procedure, you could achieve the same result as if you batch them. L'uso di una stored procedure offre inoltre il vantaggio di aumentare le opportunità di memorizzare nella cache i piani di query nel database SQL di Azure per esecuzioni successive della stessa stored procedure.Using a stored procedure also gives you the benefit of increasing the chances of caching the query plans in Azure SQL Database so you can use the stored procedure again.

Alcune applicazioni comportano un utilizzo elevato di scrittura.Some applications are write-intensive. È a volte possibile ridurre il carico totale di I/O in un database considerando la modalità di invio in batch delle scritture.Sometimes you can reduce the total I/O load on a database by considering how to batch writes together. Questa operazione è spesso semplice come l'uso di transazioni esplicite anziché di transazioni autocommit in stored procedure e batch ad hoc.Often, this is as simple as using explicit transactions instead of auto-commit transactions in stored procedures and ad hoc batches. Per una valutazione delle differenti tecniche che si possono usare, vedere Tecniche di esecuzione in batch per applicazioni del database SQL in Azure.For an evaluation of different techniques you can use, see Batching techniques for SQL Database applications in Azure. Provare a usare il proprio carico di lavoro per individuare il modello ottimale per l'invio in batch.Experiment with your own workload to find the right model for batching. Assicurarsi di comprendere che un modello potrebbe offrire garanzie di coerenza transazionale leggermente diverse.Be sure to understand that a model might have slightly different transactional consistency guarantees. Per trovare il carico di lavoro ottimale che consenta un uso delle risorse minimo è necessario individuare la corretta combinazione di compromessi tra prestazioni e coerenza.Finding the right workload that minimizes resource use requires finding the right combination of consistency and performance trade-offs.

Memorizzazione nella cache a livello di applicazioneApplication-tier caching

Alcune applicazioni di database contengono carichi di lavoro con intensa attività di lettura.Some database applications have read-heavy workloads. I livelli di memorizzazione nella cache possono consentire di ridurre il carico nel database e di ridurre il livello di prestazioni necessario per supportare un database usando il database SQL di Azure.Caching layers might reduce the load on the database and might potentially reduce the performance level required to support a database by using Azure SQL Database. La Cache Redis di Azure, in caso di carico di lavoro con intensa attività di lettura, può consentire di leggere i dati una volta o forse una volta per macchina di livello applicazione, a seconda della relativa configurazione, e di archiviare i dati al di fuori del database SQL.With Azure Redis Cache, if you have a read-heavy workload, you can read the data once (or perhaps once per application-tier machine, depending on how it is configured), and then store that data outside your SQL database. In questo modo è possibile ridurre il carico del database (CPU e I/O letti), ma vi sarà un impatto sulla coerenza transazionale poiché i dati letti dalla cache potrebbero non essere sincronizzati rispetto ai dati nel database.This is a way to reduce database load (CPU and read I/O), but there is an effect on transactional consistency because the data being read from the cache might be out of sync with the data in the database. Anche se in molte applicazioni è accettabile un livello di incoerenza, ciò non rappresenta una soluzione valida per tutti i carichi di lavoro.Although in many applications some level of inconsistency is acceptable, that's not true for all workloads. È necessario conoscere bene tutti i requisiti delle applicazioni prima di implementare una strategia di caching a livello di applicazione.You should fully understand any application requirements before you implement an application-tier caching strategy.

Passaggi successiviNext steps