Condividi tramite

Risolvere i problemi relativi all'intera applicazione di database o SQL Server che sembra essere lenta

Si applica a: SQL Server

Quando si eseguono query su un'istanza di SQL Server o su un'applicazione specifica, tutte le query sono lente. Per risolvere il problema, seguire questa procedura:

Passaggio 1: Risolvere i problemi dell'applicazione

Controllare il livello applicazione. Eseguire una query dall'applicazione, eseguirla manualmente in un'istanza di SQL Server e vedere come viene eseguita. Testare diverse query in questo modo. Se le query sono più veloci nell'istanza di SQL Server, il problema potrebbe trovarsi a livello di applicazione o server applicazioni.

Note

Tenere presente le differenze di prestazioni delle query tra l'applicazione di database e SSMS.

Se l'applicazione è in esecuzione in un server diverso, controllare le prestazioni del server applicazioni (vedere Passaggio 2: Risolvere i problemi del sistema operativo per la risoluzione dei problemi). Potrebbe essere necessario coinvolgere il team di sviluppo di applicazioni per verificare la presenza di eventuali problemi con l'applicazione.

Passaggio 2: Risolvere i problemi del sistema operativo

Controllare se il sistema operativo in cui è in esecuzione SQL Server risponde lentamente. Ad esempio, il mouse si sposta lentamente, le finestre non rispondono per lunghi periodi, l'accesso desktop remoto al server è lento o la connessione a una condivisione nel server è lenta.

Questo problema può essere causato da un altro servizio o applicazione. Usare Perfmon per risolvere i problemi.

Per altri problemi di prestazioni del sistema operativo, vedere la documentazione sulla risoluzione dei problemi relativi alle prestazioni di Windows Server.

I problemi comuni includono:

Questo problema può essere causato da altre applicazioni, dal sistema operativo o dai driver in esecuzione nel sistema.

Per risolvere questo problema, usare Gestione attività, Monitor prestazioni o Monitoraggio risorse per identificare questo problema. Per altre informazioni, vedere Linee guida per la risoluzione dei problemi di utilizzo elevato della CPU.

Passaggio 3: Risolvere i problemi di rete

Il problema potrebbe trovarsi nel livello di rete, causando una comunicazione lenta tra l'applicazione e SQL Server. Usare i metodi seguenti per risolvere questo problema:

  • Un sintomo di questo potrebbe essere ASYNC_NETWORK_IO l'attesa sul lato SQL Server. Per altre informazioni, vedere Risolvere i problemi relativi alle query lente risultanti da ASYNC_NETWORK_IO tipo di attesa.

  • Collaborare con l'amministratore di rete per verificare la presenza di problemi di rete (firewall, routing e così via).

  • Raccogliere una traccia di rete e verificare la presenza di eventi di reimpostazione e ritrasmissione della rete. Per informazioni sulla risoluzione dei problemi, vedere Problemi di rete intermittenti o periodici.

  • Abilitare i contatori Perfmon per controllare le prestazioni di rete a livello di interfaccia di rete .Enable Perfmon counters to check network performance at the network interface level (NIC). Dovrebbero essere presenti zero pacchetti scartati e pacchetti di errore. Controllare la larghezza di banda dell'interfaccia di rete:

    • Interfaccia di rete\Pacchetti ricevuti rimossi
    • Interfaccia di rete\Pacchetti ricevuti
    • Interfaccia di rete\Pacchetti Eliminati in uscita
    • Interfaccia di rete\Pacchetti Errori in uscita
    • Interfaccia di rete\Totale byte/sec
    • Interfaccia di rete\Larghezza di banda corrente

Passaggio 4: Risolvere i problemi relativi all'utilizzo elevato della CPU in SQL Server

Se le query a elevato utilizzo di CPU vengono eseguite nel sistema, possono causare l'esaurimento della capacità della CPU da parte di altre query. Più frequentemente, tuttavia, un utilizzo elevato della CPU proveniente da query può essere un'indicazione che le query devono essere ottimizzate. Per risolvere il problema, seguire questa procedura:

  1. Prima di tutto, verificare se SQL Server causa un utilizzo elevato della CPU (usando i contatori Perfmon).
  2. Identificare le query che contribuiscono all'utilizzo della CPU.
  3. Aggiornare le statistiche.
  4. Aggiungere indici mancanti.
  5. Analizzare e risolvere i problemi sensibili ai parametri.
  6. Analizzare e risolvere i problemi di sargability.
  7. Disabilitare la traccia pesante.
  8. Correzione della SOS_CACHESTORE contesa di spinlock.
  9. Configurare la macchina virtuale.
  10. Aumentare le prestazioni del sistema aggiungendo altre CPU.

Per informazioni dettagliate sulla risoluzione dei problemi, vedere Risolvere i problemi di utilizzo elevato della CPU in SQL Server.

Passaggio 5: Risolvere i problemi di I/O eccessivi che causano lentezza in SQL Server

Un altro motivo comune per la lentezza complessiva percepita dei carichi di lavoro di SQL Server è costituito da problemi di I/O. La lentezza di I/O può influire sulla maggior parte o su tutte le query nel sistema. Usare i metodi seguenti per risolvere il problema:

  • Verificare la presenza di problemi hardware:

    • Configurazione errata san (commutatore, cavi, HBA, archiviazione).
    • È stata superata la capacità di I/O (sbilanciata in tutta la rete SAN, non solo l'archiviazione back-end, controllare la velocità effettiva di I/O di tutti i server che condividono la rete SAN).
    • Problemi o aggiornamenti dei driver o del firmware.

  • Verificare la presenza di query di SQL Server non ottimali che causano un numero elevato di volumi di I/O e saturazione dei volumi di dischi con richieste di I/O.

    • Trovare le query che causano un numero elevato di letture logiche (o scritture) e ottimizzare tali query per ridurre al minimo l'I/O del disco usando gli indici appropriati è il primo passaggio.
    • Mantenere aggiornate le statistiche man mano che forniscono a Query Optimizer informazioni sufficienti per scegliere il piano migliore.
    • La riprogettazione delle query e talvolta delle tabelle può risultare utile per migliorare l'I/O.

  • Driver di filtro: la risposta di I/O di SQL Server può essere gravemente interessata se i driver di filtro del file system elaborano traffico di I/O elevato.

    • Escludere le cartelle dati dall'analisi antivirus e avere problemi di driver di filtro corretti dai fornitori di software per evitare un impatto sulle prestazioni di I/O.

  • Altre applicazioni: un'altra applicazione nello stesso computer con SQL Server può saturare il percorso di I/O con richieste di lettura o scrittura eccessive. Questa situazione può spingere il sottosistema di I/O oltre i limiti di capacità e causare lentezza di I/O per SQL Server. Identificare l'applicazione e ottimizzarla o spostarla altrove per eliminare l'effetto sullo stack di I/O. Questo problema può essere causato anche dalle applicazioni in esecuzione in altri computer, ma che condividono la stessa SAN con questo computer SQL Server. Collaborare con l'amministratore SAN per bilanciare il traffico di I/O (vedere Verificare la presenza di problemi hardware).

Per informazioni dettagliate sulla risoluzione dei problemi relativi all'I/O con SQL Server, vedere Risolvere i problemi di prestazioni lente di SQL Server causati da problemi di I/O.

Passaggio 6: Risolvere i problemi di memoria

Memoria insufficiente nel sistema in generale o all'interno di SQL Server può causare lentezza quando le query sono in attesa di concessioni di memoria (RESOURCE_SEMAPHORE) o memoria di compilazione (RESOURCE_SEMAPHORE_QUERY_COMPILE). Usare i metodi seguenti per risolvere il problema:

  • Verificare la memoria esterna a livello di sistema operativo usando i contatori Perfmon:

    • Memoria\Byte disponibili
    • Processo(*)\Working Set (tutte le istanze)
    • Processo(*)\Byte privati (tutte le istanze)

  • Per una pressione di memoria interna, usare query di SQL Server per eseguire query sys.dm_os_memory_clerks o usare DBCC MEMORYSTATUS.

  • Controllare il log degli errori di SQL Server per gli errori 701 .

Per informazioni dettagliate sulla risoluzione dei problemi, vedere Risolvere i problemi di memoria insufficiente o memoria insufficiente in SQL Server.

Passaggio 7: Risolvere i problemi di blocco

L'acquisizione di blocchi viene usata per proteggere le risorse in un sistema di database. Se i blocchi vengono acquisiti per molto tempo e altre sessioni finiscono in attesa di tali blocchi, si riscontra uno scenario di blocco.

Il blocco breve si verifica nei sistemi di database come SQL Server per tutto il tempo. Ma il blocco prolungato, soprattutto quando la maggior parte o tutte le query sono in attesa di un blocco, potrebbe comportare che l'intero server venga percepito come non risponde.

Per risolvere il problema, seguire questa procedura:

  1. Identificare la sessione di blocco head esaminando la colonna blocking_session_id in sys.dm_exec_requests output DMV o la colonna BlkBy nell'output sp_who2 della stored procedure.

  2. Trovare le query eseguite dalla catena di blocco head (che contiene blocchi per un periodo prolungato).

    Se nessuna query viene eseguita attivamente nella sessione di blocco head, potrebbe essere stata rilevata una transazione orfana a causa di problemi dell'applicazione.

  3. Riprogettare o ottimizzare la query di blocco head per l'esecuzione più veloce o ridurre il numero di query all'interno di una transazione.

  4. Esaminare l'isolamento delle transazioni usato nella query e regolare.

Per informazioni dettagliate sulla risoluzione dei problemi relativi agli scenari di blocco, vedere Informazioni e risoluzione dei problemi di blocco di SQL Server.

Passaggio 8: Risolvere i problemi dell'utilità di pianificazione (utilità di pianificazione senza rendimento, utilità di pianificazione senza deadlock, listener IOCP senza rendimento, monitoraggio delle risorse)

SQL Server usa un meccanismo di pianificazione cooperativo (Utilità di pianificazione) per esporre i thread al sistema operativo per la pianificazione della CPU. Se si verificano problemi relativi alle utilità di pianificazione SQL, i thread di SQL Server potrebbero interrompere l'elaborazione di query, account di accesso, disconnessione e così via. Di conseguenza, SQL Server potrebbe sembrare non rispondente, parzialmente o completamente, a seconda del numero di utilità di pianificazione interessate. I problemi dell'utilità di pianificazione possono derivare da un'ampia gamma di problemi, tra cui bug del prodotto, driver esterni e di filtro e problemi hardware.

Per risolvere questi problemi, seguire questa procedura:

  1. Controllare la presenza di errori nel log degli errori di SQL Server, ad esempio quelli seguenti al momento della mancata risposta segnalata da SQL Server:

    • ***********************************************
*
* BEGIN STACK DUMP:
* 03/10/22 21:16:35 spid 22548
*
* Non-yielding Scheduler
*
***********************************************

    • **********************************************
*
* BEGIN STACK DUMP:
* 03/25/22 08:50:29 spid 355
*
* Deadlocked Schedulers
*
* ********************************************


    • * *******************************************************************************                                
*                                                                                                                
* BEGIN STACK DUMP:                                                                                              
* 09/07/22 23:01:04 spid 0                                                                                     
*                                                                                                                
* Non-yielding IOCP Listener                                                                                     
*                                                                                                                
* *******************************************************************************   

    • * ********************************************
*
* BEGIN STACK DUMP:
* 07/25/22 11:44:21 spid 2013
*
* Non-yielding Resource Monitor
*
* ********************************************

  2. Se si individua uno di questi errori, identificare la versione dell'aggiornamento cumulativo (CU) di SQL Server in uso. Verificare se sono presenti problemi risolti nelle UNITÀ di configurazione fornite dopo il cu corrente. Per le correzioni di SQL Server, vedere Aggiornamenti più recenti disponibili per le versioni attualmente supportate di SQL Server. Per un elenco di correzioni dettagliato, è possibile scaricare questo file di Excel.

  3. Per altre idee, usare la risoluzione dei problemi relativi alla pianificazione e alla resa di SQL Server.

  4. Verificare la presenza di scenari di blocco elevati o di query di parallelismo massicce che possono causare utilità di pianificazione dei deadlock. Per informazioni dettagliate, vedere The Tao of a Deadlocked Scheduler.For detailed information, see The Tao of a Deadlocked Scheduler.

  5. Per un listener IOCP senza rendimento, controllare se il sistema è insufficiente nella memoria e SQL Server viene sottoposto a paging. Un altro motivo potrebbe essere l'intercettazione delle chiamate API di I/O da virus o intrusioni e rallentare l'attività del thread. Per altre informazioni, vedere Il listener IOCP è effettivamente in ascolto? e problemi di prestazioni e coerenza quando vengono caricati determinati moduli o driver di filtro.

  6. Per i problemi di Monitoraggio risorse, in alcuni casi potrebbe non essere necessario preoccuparsi di questo problema. Per altre informazioni, vedere Monitoraggio risorse immette una condizione che non produce in un server che esegue SQL Server.

  7. Se queste risorse non sono utili, individuare il dump della memoria creato nella sottodirectory \LOG e aprire un ticket di supporto con Microsoft CSS caricando il dump della memoria per l'analisi.

Passaggio 9: Cercare tracce XEvent o Profiler a elevato utilizzo di risorse

Cercare gli eventi estesi attivi o le tracce di SQL Server Profiler, in particolare quelle con filtro sulle colonne di testo (nome del database, nome dell'account di accesso, testo di query e così via). Se possibile, disabilitare le tracce e verificare se le prestazioni delle query migliorano. A seconda dell'evento selezionato, ogni thread potrebbe utilizzare cpu aggiuntive causando lentezza complessiva. Per identificare le tracce attive per gli eventi estesi, vedere sys.dm_xe_sessions e per le tracce di Profiler, vedere sys.traces.

SELECT * FROM sys.dm_xe_sessions
GO
SELECT * FROM sys.traces