Opzioni di configurazione e aggiornamenti consigliati per SQL Server con carichi di lavoro a prestazioni elevate
Questo articolo include un elenco dei miglioramenti delle prestazioni e delle opzioni di configurazione disponibili per SQL Server 2012 e versioni successive.
Versione originale del prodotto: SQL Server 2014, SQL Server 2012
Numero KB originale: 2964518
Applicare gli aggiornamenti consigliati e migliorare le prestazioni di SQL Server 2014 e SQL Server 2012
Questo articolo descrive i miglioramenti delle prestazioni e le modifiche disponibili per le versioni di SQL Server 2014 e SQL Server 2012 tramite varie opzioni di configurazione e aggiornamenti del prodotto. È possibile valutare la possibilità di applicare questi aggiornamenti per migliorare le prestazioni dell'istanza di SQL Server. Il grado di miglioramento visualizzato dipenderà da vari fattori che includono il modello di carico di lavoro, i punti di contesa, il layout del processore (numero di gruppi di processori, socket, nodi NUMA, core in un nodo NUMA) e la quantità di memoria presente nel sistema. Il team di supporto di SQL Server ha usato questi aggiornamenti e modifiche alla configurazione per ottenere miglioramenti ragionevoli delle prestazioni per i carichi di lavoro dei clienti che usavano sistemi hardware con diversi nodi NUMA e molti processori. Il team di supporto continuerà ad aggiornare questo articolo con altri aggiornamenti in futuro.
Sistemi di fascia alta Un sistema di fascia alta in genere dispone di più socket, otto core o più per socket e una metà terabyte o più di memoria.
Note
In SQL Server 2016 e versioni successive, molti dei flag di traccia indicati in questo articolo sono il comportamento predefinito e non è necessario abilitarli in tali versioni.
Le raccomandazioni sono raggruppate in tre tabelle come indicato di seguito:
- La tabella 1 contiene gli aggiornamenti e i flag di traccia più frequentemente consigliati per la scalabilità nei sistemi di fascia alta.
- La tabella 2 contiene raccomandazioni e indicazioni per l'ottimizzazione delle prestazioni aggiuntiva.
- La tabella 3 contiene correzioni di scalabilità aggiuntive incluse insieme a un aggiornamento cumulativo.
Tabella 1. Aggiornamenti importanti e flag di traccia per sistemi di fascia alta
Esaminare la tabella seguente e abilitare i flag di traccia nella colonna Flag di traccia dopo aver verificato che l'istanza di SQL Server soddisfi i requisiti nella colonna Versione applicabile e intervalli di compilazione .
Note
Versione applicabile e build indica l'aggiornamento specifico in cui è stato introdotto il flag di modifica o traccia. Se non viene specificato alcun CU, vengono inclusi tutti i CU nel sp.
Versione non applicabile e build indica l'aggiornamento specifico in cui il flag di modifica o traccia è diventato il comportamento predefinito. Pertanto, solo applicando tale aggiornamento sarà sufficiente per ottenere i vantaggi.
Importante
Quando si abilitano le correzioni con i flag di traccia negli ambienti Always On, tenere presente che è necessario abilitare i flag di correzione e traccia in tutte le repliche che fanno parte del gruppo di disponibilità.
| Scenario e sintomo da considerare | Flag di traccia | Versione applicabile e intervalli di compilazione | Versione non applicabile e intervalli di compilazione | Articolo della Knowledge Base/Collegamento al blog che fornisce altri dettagli |
|---|---|---|---|---|
|
T8048 |
|
|
|
|
T8079 | DA SQL Server 2014 SP2 a SP/CU corrente |
|
|
|
T9024 | Pacchetto di aggiornamento cumulativo 3 per SQL Server 2012 Service Pack 1 a SP2 SQL Server 2014 RTM |
|
FIX: valore del contatore "Attese di scrittura log" elevato in un'istanza di SQL Server 2012 o SQL Server 2014 |
| L'istanza di SQL Server gestisce migliaia di reimpostazioni della connessione a causa del pool di connessioni. | T1236 | Pacchetto di aggiornamento cumulativo da 9 per SQL Server 2012 Service Pack 1 a SP2 aggiornamento cumulativo 1 per SQL Server 2014 |
|
|
|
T1118 |
|
|
Miglioramenti della concorrenza per il database tempdb NOTA Abilitare il flag di traccia e aggiungere più file di dati per il database tempdb. |
|
T1117 |
|
|
Suggerimenti per ridurre la contesa dell'allocazione nel database tempdb di SQL Server |
Conflitti di spinlock pesanti SOS_CACHESTORE o i piani vengono rimossi frequentemente nei carichi di lavoro di query ad hoc. |
T174 |
|
None |
|
|
T8032 |
|
None |
|
| Le statistiche esistenti non vengono aggiornate di frequente a causa del numero elevato di righe nella tabella. | T2371 |
|
None | |
|
T7471 | DA SQL Server 2014 SP1 CU6 a SP/CU corrente | None | Miglioramento delle prestazioni delle statistiche di aggiornamento con SQL 2014 e SQL 2016 |
| Il comando CHECKDB richiede molto tempo per i database di grandi dimensioni. |
|
|
None | |
| Il comando CHECKDB richiede molto tempo per i database di grandi dimensioni. | T2566 |
|
None |
|
L'esecuzione di query simultanee del data warehouse che richiedono tempi di compilazione lunghi comporta RESOURCE_SEMAPHORE_QUERY_COMPILE attese. |
T6498 | Pacchetto di aggiornamento cumulativo da 6 per SQL Server 2014 a SP1 |
|
|
| Le correzioni di Optimizer per problemi di prestazioni delle query specifiche vengono disabilitate per impostazione predefinita. | T4199 |
|
None | |
| Si verificano prestazioni lente usando operazioni di query con tipi di dati spaziali. |
|
|
|
|
|
T8075 |
|
|
CORREZIONE: errore di memoria insufficiente quando lo spazio degli indirizzi virtuali del processo di SQL Server è insufficiente in SQL Server |
|
T3449 |
|
|
CORREZIONE: la creazione di database di SQL Server in un sistema con un volume elevato di memoria richiede più tempo del previsto |
Tabella 2. Considerazioni generali e procedure consigliate per migliorare le prestazioni dell'istanza di SQL Server
Esaminare il contenuto nella colonna Delle risorse online dell'articolo della Knowledge Base e prendere in considerazione l'implementazione delle linee guida nella colonna Azioni consigliate.
| Articolo della Knowledge Base/Risorsa online della Documentazione | Azioni consigliate |
|---|---|
| Configurare l'opzione di configurazione del server max degree of parallelism | Usare la stored procedure sp_configure per apportare modifiche alla configurazione per Configurare l'opzione di configurazione del server max degree of parallelism per l'istanza di SQL Server in base all'articolo della Knowledge Base. |
| Limiti della capacità di calcolo per edizione di SQL Server | edizione Enterprise con licenza CAL (Server + Client Access License) è limitato a 20 core per ogni istanza di SQL Server. Non sono previsti limiti nel modello di licenza server basato su core. Prendere in considerazione l'aggiornamento dell'edizione di SQL Server allo SKU appropriato per sfruttare tutte le risorse hardware. |
| Rallentamento delle prestazioni in Windows Server quando si usa il piano di risparmio energia "Bilanciato" | Esaminare l'articolo e collaborare con l'amministratore di Windows per implementare una delle soluzioni indicate nella sezione "Risoluzione" dell'articolo. |
| Assegnare manualmente i nodi NUMA ai gruppi K. | |
| Ottimizzare i carichi di lavoro ad hoc FORCED PARAMETERIZATION | Le voci nella cache dei piani vengono rimosse a causa della crescita in altre cache o impiegati di memoria. È anche possibile che si verifichi la rimozione della cache dei piani quando la cache raggiunge il numero massimo di voci. Oltre al flag di traccia 8032 descritto in precedenza, prendere in considerazione l'opzione del server optimize for ad hoc workloads e anche l'opzione di database FORCED PARAMETERIZATION . |
| Come ridurre il paging della memoria del pool di buffer nella configurazione della memoria di SQL Server e considerazioni sul ridimensionamento in SQL Server 2012 e versioni successive | Assegnare il diritto utente Abilita le pagine di blocco in memoria (Windows) all'account di avvio del servizio SQL. Vedere Come abilitare la funzionalità "pagine bloccate" in SQL Server 2012. Impostare la memoria massima del server su circa il 90% della memoria fisica totale. Assicurarsi che le opzioni di configurazione della memoria del server siano account per la memoria solo dai nodi configurati per l'uso delle impostazioni affinity mask. |
| Informazioni su SQL Server e pagine di grandi dimensioni... Opzioni di ottimizzazione per SQL Server durante l'esecuzione in carichi di lavoro a prestazioni elevate | Valutare la possibilità di abilitare TF 834 se si dispone di un server con una grande quantità di memoria, in particolare con un carico di lavoro di data warehousing analitico o dati. Tenere presente che TF 834 non è consigliato se si usano indici columnstore. |
| Descrizione delle opzioni "access check cache bucket count" e "access check cache quota" disponibili nella stored procedure sp_configure | Usare le opzioni di configurazione del server di controllo di accesso per configurare questi valori in base alle raccomandazioni nell'articolo della Knowledge Base. I valori consigliati per i sistemi di fascia alta sono i seguenti: "access check cache bucket count": 256 "access check cache quota": 1024 |
| Hint per la concessione di memoria ALTER WORKLOAD GROUP | Se sono presenti molte query che esauriscono concessioni di memoria di grandi dimensioni, ridurre request_max_memory_grant_percent il gruppo di carico di lavoro predefinito nella configurazione di Resource Governor dal 25% predefinito a un valore inferiore. Sono disponibili nuove opzioni di concessione di memoria query (min_grant_percent e max_grant_percent) in SQL Server |
| Inizializzazione immediata dei file | Collaborare con l'amministratore di Windows per concedere all'account del servizio SQL Server il diritto utente "Esegui attività di manutenzione volume" in base alle informazioni contenute nella documentazione online. |
| Considerazioni sulle impostazioni "aumento automatico" e "autoshrink" in SQL Server | Controllare le impostazioni correnti del database e assicurarsi che siano configurate in base alle raccomandazioni riportate nell'articolo della Knowledge Base. |
| Checkpoint di database (SQL Server) | Valutare la possibilità di abilitare checkpoint indiretti nei database utente per ottimizzare il comportamento di I/O in SQL Server 2012 e 2014. |
| CORREZIONE: sincronizzazione lenta quando i dischi hanno dimensioni del settore diverse per i file di log della replica primaria e secondaria negli ambienti di disponibilità e logshipping di SQL Server | Se si dispone di un gruppo di disponibilità in cui il log delle transazioni nella replica primaria si trova su un disco con dimensioni del settore a 512 byte e il log delle transazioni della replica secondaria si trova in un'unità con dimensioni di settore 4K, è possibile che si verifichi un problema a causa del quale la sincronizzazione è lenta. In questi casi, l'abilitazione di TF 1800 deve risolvere il problema. Per altre informazioni, vedere Flag di traccia 1800. |
| Se SQL Server non è già associato alla CPU e un sovraccarico da 1,5% a 2% è trascurabile per i carichi di lavoro, è consigliabile abilitare TF 7412 come flag di traccia di avvio. Questo flag abilita la profilatura leggera in SQL Server 2014 SP2 o versione successiva, che consente di eseguire la risoluzione dei problemi delle query in tempo reale negli ambienti di produzione. |
Tabella 3. Correzioni delle prestazioni incluse in un aggiornamento cumulativo
Esaminare la descrizione nella colonna Sintomi e applicare gli aggiornamenti necessari nella colonna Aggiornamento richiesto negli ambienti applicabili. Per altre informazioni sui rispettivi problemi, vedere l'articolo della Knowledge Base. Questi consigli non richiedono l'abilitazione di flag di traccia aggiuntivi come parametri di avvio. È sufficiente applicare l'aggiornamento cumulativo o il Service Pack più recente che include queste correzioni per ottenere il vantaggio.
Note
Il nome cu nella colonna Aggiornamento richiesto fornisce il primo aggiornamento cumulativo di SQL Server che risolve questo problema. Un aggiornamento cumulativo contiene tutti gli hotfix e tutti gli aggiornamenti inclusi nella versione precedente dell'aggiornamento di SQL Server. È pertanto consigliabile installare l'aggiornamento cumulativo più recente per risolvere i problemi.
Note importanti
Se si applicano tutte le condizioni nella tabella 1:
- Indicazioni per SQL Server 2014: applicare almeno l'aggiornamento cumulativo 1 per SQL Server 2014 per RTM e aggiungere "-T8048 -T9024 -T1236 -T1117 -T1118" all'elenco dei parametri di avvio di SQL Server.
- Indicazioni per SQL Server 2012: applicare SP2 e aggiungere "-T8048 -T9024 -T1236 -T1117 -T1118" all'elenco dei parametri di avvio di SQL Server.
Per informazioni generali su come usare i flag di traccia, vedere l'argomento DBCC TRACEON - Flag di traccia (Transact-SQL) nella documentazione online di SQL Server.
Per altre informazioni sul numero di processori, la configurazione NUMA e così via, vedere Visualizzare il log degli errori di SQL Server in SQL Server Management Studio (SSMS).
Per trovare la versione di SQL Server, verificare quanto segue:
Come determinare la versione e l'edizione di SQL Server e dei relativi componenti
Riferimenti
Come ottenere il Service Pack più recente per SQL Server 2012
Dove trovare informazioni sulle build SQL Server più recenti
Risorse della community di SQL Server su aggiornamenti importanti per SQL Server
Si applica a
- SQL Server 2014 Enterprise
- SQL Server 2014 Enterprise Core
- SQL Server 2014 Business Intelligence
- SQL Server 2014 Developer
- SQL Server 2014 R2
- SQL Server 2014 Web
- SQL Server 2014 Express
- SQL Server 2012 Business Intelligence
- SQL Server 2012 Developer
- SQL Server 2012 Enterprise
- SQL Server 2012 Standard
- SQL Server 2012 Web
- SQL Server 2012 Enterprise Core