SQL Server

Ottimizzazione delle prestazioni della CPU in SQL Server

Zach Nichter

Panoramica:

• Risoluzione dei problemi di prestazioni del database
• Analisi delle cause hardware
• Utilizzo di PerfMon per tenere traccia dei colli di bottiglia a livello di database
• Valutazione delle prestazioni di esecuzione delle query

La risoluzione di problemi di prestazioni su un sistema di database può rivelarsi un'attività piuttosto complessa. Individuare il punto di origine di un problema è importante, ma ancora più cruciale è riuscire a capire le ragioni del comportamento del sistema a fronte di una specifica richiesta. Numerosi fattori possono influire sull'utilizzo della CPU

su un server di database: compilazione e ricompilazione di istruzioni SQL, indici mancanti, operazioni multithreading, colli di bottiglia a livello di disco e memoria, gestione di routine e, tra le altre cose, attività di estrazione, trasformazione e caricamento (ETL). L'utilizzo della CPU non costituisce di per sé un problema: eseguire operazioni rappresenta, d'altro canto, la funzione a cui la CPU è preposta. La chiave per un "sano" utilizzo della CPU consiste nel creare tutte le condizioni necessarie perché la CPU possa elaborare esclusivamente i processi specificati anziché sprecare cicli su codice scritto in modo approssimativo o hardware lento.

Due percorsi che si muovono verso uno stesso obiettivo

Da un punto di vista generale, esistono due percorsi che possono condurre all'identificazione dei problemi di prestazioni della CPU. Il primo prevede l'analisi delle prestazioni dell'hardware del sistema, un'operazione che consente di determinare dove cercare il problema quando si intraprende il secondo percorso, che consiste nell'analisi dell'efficienza delle query del server. Questo secondo percorso è in genere più efficace nell'identificazione dei problemi di prestazioni di SQL Server™. A meno che non si conosca con esattezza il punto di origine dei problemi di prestazioni delle query, tuttavia, è consigliabile iniziare sempre con una valutazione delle prestazioni del sistema. Alla fine, si finisce in genere con l'intraprendere entrambi i percorsi. A questo punto, è opportuno introdurre alcuni elementi di base necessari per esaminare entrambi i percorsi.

Concetti di base

Hyperthreading

La tecnologia hyperthreading è un argomento che merita un maggiore approfondimento data la notevole influenza che esercita su SQL Server. L'hyperthreading è una tecnologia grazie alla quale ciascun processore fisico viene visto dal sistema operativo come due processori logici. Questa tecnologia in sostanza sfrutta il tempo di elaborazione dei processori fisici in modo da consentire un pieno utilizzo di ciascun processore. Il sito Web Intel (intel.com/technology/platform-technology/hyper-threading/index.htm) fornisce una descrizione di gran lunga più dettagliata sulla modalità di funzionamento della tecnologia hyperthreading.

Sui sistemi SQL Server, il sistema di gestione di database (DBMS) mette a disposizione del sistema operativo le relative tecniche di accodamento e threading, estremamente efficienti e, pertanto, la tecnologia hyperthreading viene utilizzata solo per eseguire l'overload delle CPU fisiche sui sistemi in cui si registra già un utilizzo elevato della CPU. Quando SQL Server inserisce in coda più richieste di esecuzione delle operazioni su più utilità di pianificazione, il sistema operativo dovrà eseguire la commutazione del contesto dei thread sui processori fisici per soddisfare le richieste effettuate anche se i due processori logici si basano sullo stesso processore fisico. Se il valore del contatore Commutazioni di contesto/sec risulta superiore a 5000 per ciascun processore fisico, è consigliabile disattivare la tecnologia hyperthreading nel sistema e rieseguire il test delle prestazioni.

In rari casi, le applicazioni in SQL Server che presentano un utilizzo elevato della CPU possono utilizzare senza problemi la tecnologia hyperthreading. Prima di implementare le modifiche introdotte nei sistemi di produzione, testare sempre le applicazioni su SQL Server con la tecnologia hyperthreading sia attivata che disattivata.

Le prestazioni di un processore dual-core di fascia alta saranno di gran lunga superiori a quelle della RAM in un computer che, a sua volta, risulterà più veloce di un dispositivo di archiviazione collegato. Una CPU dalle prestazioni elevate è in grado di offrire una velocità effettiva di circa sei volte superiore a quella della memoria DDR2 di fascia alta corrente e di circa due volte superiore a quella della memoria DDR3 di fascia alta. La velocità effettiva della memoria tipica è di 10 volte superiore a quella delle più veloci unità Fiber Channel. Le unità disco, a loro volta, possono solo eseguire un numero finito di IOPS (operazioni di input/output al secondo), un valore completamente limitato dal numero di ricerche al secondo che un'unità è in grado di svolgere. In realtà, non accade in genere che venga utilizzata solo una singola unità di archiviazione per gestire tutte le esigenze di archiviazione sui sistemi di database aziendali. La maggior parte delle installazioni oggi prevede l'utilizzo delle reti SAN (Storage Area Network) sui server di database aziendali o gruppi RAID di maggiori dimensioni che sono in grado di impedire o ridurre al minimo i problemi relativi al processore di I/O del disco. L'aspetto più importante da tenere presente è che, indipendentemente dal tipo di installazione effettuato, i colli di bottiglia a livello del disco e della memoria possono influire sulle prestazioni dei processori.

A causa delle differenze di velocità delle operazioni di I/O, il recupero di dati dal disco risulta molto più dispendioso del recupero di dati dalla memoria. Una pagina di dati in SQL Server è 8 KB. Un extent in SQL Server è costituito da otto pagine di 8 KB, per un totale di 64 KB. È importante comprendere questo in quanto, quando SQL Server richiede il recupero di una specifica pagina di dati dal disco, in realtà non viene semplicemente recuperata la pagina di dati ma l'intero extent in cui risiede la pagina di dati. Esistono diversi motivi che spiegano i vantaggi derivanti dall'utilizzo di questo approccio per SQL Server; si tratta tuttavia di un argomento che esula dall'ambito del presente articolo. Il recupero di una pagina di dati già memorizzata nella cache dal pool di buffer, operando al massimo della velocità, dovrebbe richiedere meno di un millisecondo; il recupero di un singolo extent dal disco dovrebbe richiedere un intervallo di tempo compreso tra 2 e 4 millisecondi in un ambiente ottimale. Si prevede in genere che un'operazione di lettura di un sottosistema del disco integro e dalle prestazioni elevate richieda un periodo di tempo compreso tra 4 e 10 ms. La velocità di recupero di una pagina di dati dalla memoria è in genere 4-20 volte superiore a quella del recupero di una pagina di dati dal disco.

Quando SQL Server richiede una pagina di dati, controlla la cache del buffer in memoria prima di cercare la pagina di dati nel sottosistema del disco. Se la pagina di dati si trova nel pool di buffer, il processore recupererà i dati ed eseguirà le operazioni richieste. Questo comportamento è denominato errore di pagina software. Gli errori di pagina software sono ideali per SQL Server in quanto i dati recuperati come parte di una richiesta devono risiedere nella cache del buffer prima di poter essere utilizzati. Una pagina di dati che non risiede nella cache del buffer deve essere recuperata dal sottosistema del disco del server. Quando il sistema operativo deve recuperare la pagina di dati dal disco, questo comportamento è noto come errore di pagina hardware.

Quando si mettono in correlazione le prestazioni della memoria, le prestazioni del disco e le prestazioni della CPU, un denominatore comune consente di collocare il tutto nella giusta prospettiva: la velocità effettiva. In termini non propriamente scientifici, la velocità effettiva rappresenta la misurazione della quantità di dati che è possibile trasmettere attraverso una pipe con capacità limitata.

Percorso 1: prestazioni del sistema

Sono disponibili solo pochi metodi per determinare se su un server è presente un collo di bottiglia a livello di CPU e non esistono molte cause potenziali dell'utilizzo elevato della CPU. Alcuni di questi problemi possono essere rilevati tramite PerfMon o uno strumento di monitoraggio del sistema simile mentre altri vengono rilevati mediante SQL Profiler o strumenti simili. Un altro metodo consiste nell'utilizzare i comandi SQL tramite Analizzatore query o SQL Server Management Studio (SSMS).

La filosofia che utilizzo durante la valutazione delle prestazioni di un sistema prevede un procedimento che va dal generale al particolare. Ovviamente, è possibile focalizzare l'attenzione su determinate aree problematiche solo dopo averle identificate. Dopo aver valutato l'utilizzo globale della CPU con uno strumento come PerfMon, è possibile utilizzare tale strumento per esaminare un paio di contatori delle prestazioni molto semplici da utilizzare e di immediata comprensione.

Uno dei contatori delle prestazioni più noti è % Tempo processore; quando si utilizza PerfMon, questo contatore viene evidenziato non appena si apre la finestra Aggiungi contatori. % Tempo processore rappresenta la quantità di tempo impiegata dai processori nell'esecuzione delle operazioni. L'utilizzo delle risorse dei processori viene in genere considerato elevato quando questo valore è pari a 80% o superiore per la maggior parte del tempo in cui il sistema opera alla massima velocità. Accade che a volte si registrino picchi di utilizzo fino a 100% anche quando il server non opera a un tasso di utilizzo dell'80%.

Un altro contatore che è consigliabile esaminare è Lunghezza coda processore, disponibile nell'oggetto prestazione Sistema in PerfMon. Lunghezza coda processore indica il numero di thread in attesa di eseguire un'operazione sulla CPU. SQL Server gestisce le relative operazioni tramite le utilità di pianificazione nel motore di database, in cui vengono accodate ed elaborate le rispettive richieste. Poiché gestisce il relativo lavoro, SQL Server utilizza solo un singolo thread della CPU per ogni processore logico. Questo implica che ci dovrebbe essere un numero minimo di thread nella coda del processore in attesa di eseguire il lavoro su un sistema dedicato a SQL Server. In genere questo numero non dovrebbe essere di cinque volte superiore al numero di processori fisici su un sistema SQL Server dedicato, ma già un numero di due volte superiore può rivelarsi problematico. Sui server in cui DBMS condivide un sistema con altre applicazioni, è opportuno esaminare questo contatore insieme ai contatori delle prestazioni % Tempo processore e Commutazioni di contesto/sec (le commutazioni di contesto verranno trattate più avanti) per determinare se le altre applicazioni o il sistema DBMS deve essere spostato in un server differente.

In caso di accodamento del processore e di utilizzo elevato della CPU, esaminare i contatori Compilazioni SQL/sec e Ricompilazioni SQL/sec nell'oggetto prestazione SQLServer: Statistiche SQL (vedere la Figura 1). La compilazione e la ricompilazione dei piani di query contribuiscono all'utilizzo elevato della CPU di un sistema. Per le ricompilazioni si dovrebbero registrare valori vicino a zero; tuttavia è opportuno osservare le tendenze all'interno dei sistemi in uso per determinare il tipico comportamento del server e il numero di operazioni di compilazione che è normale eseguire. Le ricompilazioni non sempre possono essere evitate, ma è possibile ottimizzare le query e le stored procedure per ridurre al minimo le ricompilazioni e riutilizzare i piani di query. Confrontare questi valori con le istruzioni SQL effettive inserite nel sistema tramite il contatore Richieste batch/sec, disponibile nell'oggetto prestazione SQLServer: Statistiche SQL. Se le compilazioni e le ricompilazioni al secondo prevedono un'elevata percentuale delle richieste batch inoltrate al sistema, questa è un'area che è opportuno esaminare. In alcune situazioni gli sviluppatori SQL potrebbero non comprendere come o perché il relativo codice possa contribuire a generare questi tipi di problemi legati all'utilizzo eccessivo delle risorse del sistema. Più avanti in questo articolo verranno forniti alcuni riferimenti che potranno essere utili per ridurre al minimo questo tipo di attività.

Figura 1** Selezione dei contatori da monitorare **(Fare clic sull'immagine per ingrandirla)

Quando si utilizza PerfMon, esaminare il contatore delle prestazioni Commutazioni di contesto/sec (vedere la Figura 2). Questo contatore indica la frequenza con cui è necessario recuperare i thread dalle utilità di pianificazione del sistema operativo (non le utilità di pianificazione SQL) per eseguire le operazioni per altri thread in attesa. Le commutazioni di contesto sono in genere molto più frequenti sui sistemi di database condivisi con altre applicazioni come IIS o componenti di server applicazioni di altri fornitori. La soglia utilizzata per Commutazioni di contesto/sec è pari a circa 5000 volte il numero dei processori nel server. Questo valore può essere elevato anche sui sistemi con la tecnologia hyperthreading attivata e su cui si registra un utilizzo della CPU da moderato a elevato. Quando l'utilizzo della CPU e le commutazioni di contesto superano periodicamente le relative soglie, questo indica la presenza di un collo di bottiglia a livello della CPU. Se questo avviene a intervalli regolari, è consigliabile valutare l'opportunità di acquistare un numero maggiore di CPU o CPU più veloci se il sistema in uso è obsoleto. Per ulteriori informazioni, vedere l'intestazione laterale "Hyperthreading".

Figure 2 Contatori delle prestazioni da esaminare

Il Lazywriter SQL Server (come definito in SQL Server 2000) o il monitor risorse (come definito in SQL Server 2005) è un'altra area da monitorare quando l'utilizzo della CPU è elevato. Lo svuotamento del buffer e delle cache delle procedure possono incremento il tempo di CPU tramite il thread delle risorse definito monitor risorse. Il monitor risorse è un processo di SQL Server che determina quali pagine conservare e quali pagine scaricare dal pool di buffer sul disco. A ogni pagina nel buffer e nelle cache delle procedure viene originariamente assegnato un valore di spazio che rappresenta le risorse utilizzate quando tale pagina viene memorizzata nella cache. Questo valore di spazio viene decrementato ogni volta che viene analizzato dal monitor risorse. Quando si richiede spazio della cache, le pagine vengono rimosse dalla memoria in base al valore di spazio associato a ciascuna pagina; le pagine con i valori più bassi vengono rimosse per prime. È possibile tenere traccia dell'attività del monitor risorse tramite il contatore delle prestazioni Scritture Lazywriter/sec nell'oggetto SQLServer: Gestione buffer in PerfMon. È consigliabile tenere traccia della tendenza con cui questo valore determina quale sia la soglia tipica nel sistema in uso. Questo contatore viene in genere esaminato insieme ai contatori Permanenza presunta delle pagine e Pagine checkpoint/sec per determinare se si è in presenza di un numero eccessivo di richieste di memoria.

Il contatore Permanenza presunta delle pagine (PLE, Page Life Expectancy) consente di determinare il numero di richieste di memoria. Il contatore PLE indica la durata della permanenza di una pagina di dati nella cache del buffer. 300 secondi rappresenta la soglia generalmente accettata per questo contatore. Qualsiasi valore inferiore a una media di 300 secondi per un periodo di tempo prolungato indica che le pagine di dati vengono rimosse dalla memoria con frequenza eccessiva. Quando questo si verifica, si determina un aumento significativo del carico di lavoro del monitor risorse che, a sua volta, forza l'utilizzo di un maggior numero di risorse dei processori. È opportuno valutare il contatore PLE insieme al contatore Pagine checkpoint/sec. Quando si verifica un checkpoint nel sistema, le pagine di dati danneggiate nella cache del buffer vengono scaricate sul disco, causando una diminuzione del valore PLE. Il processo monitor risorse rappresenta il meccanismo con cui queste pagine vengono scaricate su disco; pertanto durante i checkpoint è possibile che il valore del contatore Scritture Lazywriter/sec aumenti. Se il valore PLE aumenta immediatamente dopo il completamento di un checkpoint, è possibile ignorare questo sintomo temporaneo. D'altra parte, se si rileva che il valore è regolarmente inferiore alla soglia PLE, esiste un'alta probabilità che una quantità di memoria aggiuntiva possa risolvere i problemi e, allo stesso tempo, rilasciare alcune risorse a vantaggio della CPU. Tutti questi contatori sono disponibili nell'oggetto prestazione SQLServer: Gestione buffer.

Percorso 2: prestazioni delle query

Esecuzione della traccia con stored procedure

Quando si esegue la traccia dell'applicazione SQL Server, è opportuno acquisire familiarità con le stored procedure utilizzate per la traccia. L'utilizzo dell'interfaccia GUI (SQL Server Profiler) per la traccia può determinare un aumento del carico del sistema che va dal 15 al 25%. L'utilizzo di stored procedure durante la traccia potrebbe ridurre di circa la metà questo carico.

Quando si rileva la presenza di un collo di bottiglia nel sistema e si desidera determinare quali istruzioni SQL correnti siano all'origine dei problemi verificatisi sul server in uso, eseguire la query riportata di seguito. Questa query consente di visualizzare le singole istruzioni e le risorse da esse utilizzate nonché le istruzioni che devono essere esaminate per ottenere un miglioramento delle prestazioni. Per ulteriori informazioni sulle tracce SQL, visitare il sito Web all'indirizzo msdn2.microsoft.com/ms191006.aspx.

SELECT 
    substring(text,qs.statement_start_offset/2
        ,(CASE    
            WHEN qs.statement_end_offset = -1 THEN len(convert(nvarchar(max), text)) * 2 
            ELSE qs.statement_end_offset 
        END - qs.statement_start_offset)/2) 
    ,qs.plan_generation_num as recompiles
    ,qs.execution_count as execution_count
    ,qs.total_elapsed_time - qs.total_worker_time as total_wait_time
    ,qs.total_worker_time as cpu_time
    ,qs.total_logical_reads as reads
    ,qs.total_logical_writes as writes
FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
    LEFT JOIN sys.dm_exec_requests r 
        ON qs.sql_handle = r.sql_handle
ORDER BY 3 DESC

Quando una nuova query viene inviata a SQL Server, i piani di query vengono valutati, ottimizzati, compilati e inseriti nella cache delle procedure. Ogni volta che una query viene inviata al server, la cache delle procedure viene esaminata per tentare di ottenere la corrispondenza tra un piano di query e una richiesta. Se non viene trovata alcuna corrispondenza, in SQL Server verrà creato un nuovo piano, un'operazione potenzialmente dispendiosa.

Di seguito sono riportate alcune considerazioni in relazione all'ottimizzazione della CPU T-SQL:

• Riutilizzo del piano di query
• Riduzione di compilazioni e ricompilazioni
• Operazioni di ordinamento
• Join non corretti
• Indici mancanti
• Scansione di tabelle/indici
• Utilizzo di funzioni nelle clausole SELECT e WHERE
• Operazioni multithreading

A questo punto è opportuno ricollocare il tutto nella giusta prospettiva. SQL Server in genere prevede l'estrazione di dati sia dalla memoria che dal disco ed è raro che si utilizzi solo una singola pagina di dati. Accade di frequente invece che più parti di un'applicazione utilizzino un record, eseguano più query di minori dimensioni o uniscano le tabelle in modo da fornire una visualizzazione completa dei dati rilevanti. Negli ambienti OLAP, le applicazioni potrebbero estrarre milioni di righe da una o due tabelle in modo che sia possibile consolidare, aggregare e riepilogare i dati per un report relativo alle vendite regionali. In situazioni di questo tipo, la restituzione dei dati può essere misurata in millisecondi se i dati sono in memoria, ma questi millisecondi possono trasformarsi in minuti quando gli stessi dati vengono recuperati dal disco anziché dalla RAM.

Il primo esempio è relativo a una situazione con un elevato volume di transazioni e in cui il riutilizzo dei piani dipende dall'applicazione. Un basso riutilizzo dei piani determina un numero elevato di compilazioni di istruzioni SQL che, a sua volta, mette a dura prova la capacità di elaborazione della CPU. Nel secondo esempio, l'utilizzo intensivo delle risorse di sistema può determinare un'eccessiva attività della CPU di un sistema, in quanto i dati esistenti devono essere costantemente eliminati dalla cache del buffer per risparmiare spazio per l'elevato volume delle nuove pagine di dati.

Si consideri un sistema che prevede un numero elevato di transazioni, in cui un'istruzione SQL come quella riportata di seguito viene eseguita 2000 volte in un periodo di 15 minuti per recuperare informazioni sull'imballo di spedizione. Senza il riutilizzo del piano di query si potrebbe registrare un tempo di esecuzione di circa 450 ms per ogni istruzione. Se lo stesso piano di query viene utilizzato dopo l'esecuzione iniziale, ogni query successiva potrebbe essere eseguita in circa 2 ms, determinando una riduzione del tempo totale di esecuzione di circa 5 secondi.

USE SHIPPING_DIST01;
SELECT 
    Container_ID
    ,Carton_ID
    ,Product_ID
    ,ProductCount
    ,ModifiedDate
FROM Container.Carton
WHERE Carton_ID = 982350144;

Il riutilizzo del piano di query è fondamentale per ottenere prestazioni ottimali su sistemi con un numero elevato di transazioni e in genere v iene ottenuto parametrizzando le query o le stored procedure. Di seguito sono riportate alcune risorse preziose contenenti informazioni sul riutilizzo del piano di query:

• Problemi di compilazione batch, ricompilazione e memorizzazione nella cache dei piani in SQL Server 2005 (microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx)
• Ottimizzazione delle stored procedure di SQL Server per evitare le ricompilazioni (sql-server-performance.com/rd_optimizing_sp_recompiles.asp)
• Ricompilazione di query in SQL Server 2000 (msdn2.microsoft.com/aa902682.aspx)

Una risorsa preziosa in cui è disponibile una quantità enorme di informazioni è rappresentata dalle viste a gestione dinamica (DMV) di SQL Server 2005. In caso di utilizzo elevato della CPU, è possibile utilizzare un paio di viste a gestione dinamica che consentono di determinare se la CPU viene utilizzata in modo appropriato.

Una di queste DMV è sys.dm_os_wait_stats, che viene utilizzata per fornire agli amministratori del database (DBA) un metodo per determinare ciascun tipo di risorsa o funzione utilizzata da SQL Server e di misurare la quantità di tempo in cui il sistema resta in attesa a causa di tale risorsa. I contatori in questa DMV sono cumulativi. Questo significa che per ottenere una vista chiara delle risorse che potrebbero influire su diverse aree del sistema, è necessario eseguire innanzitutto il comando DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR) per reimpostare tutti i contatori dopo aver esaminato i dati relativi a tutti i problemi di particolare rilievo. La DMV sys.dm_os_wait_stats è l'equivalente del comando per il controllo della coerenza del database DBCC SQLPERF(WAITSTATS) in SQL Server 2000. Ulteriori informazioni sui diversi tipi di attesa sono disponibili nella documentazione in linea di SQL Server all'indirizzo msdn2.microsoft.com/ms179984.aspx.

È importante sapere che le attese sono normali in un sistema anche quando il sistema funziona in modo ottimale. È necessario semplicemente determinare se le attese sono interessate da un eventuale collo di bottiglia a livello della CPU. I tempi di attesa del segnale dovrebbero essere il più basso possibile in relazione al tempo di attesa totale. Il periodo di tempo in cui una specifica risorsa resta in attesa di una risorsa del processore può essere determinato semplicemente sottraendo il tempo di attesa del segnale dal tempo di attesa totale; questo valore non dovrebbe essere superiore di circa il 20% a quello del tempo di attesa totale.

La DMV sys.dm_exec_sessions indica tutte le sessioni aperte in SQL Server. Questa DMV fornisce una visualizzazione dettagliata delle prestazioni di ciascuna sessione e di tutte le operazioni eseguite da ciascuna sessione sin da quando è stata avviata. Le informazioni fornite in tale vista includono il tempo totale in cui la sessione è rimasta in attesa, l'utilizzo totale della CPU, l'utilizzo della memoria e un conteggio di letture e scritture. Nella DMV vengono forniti inoltre l'account di accesso, il tempo di accesso, il computer host e l'ultima volta in cui la sessione ha effettuato una richiesta in SQL Server.

L'utilizzo della DMV sys.dm_exec_sessions consente di determinare solo le sessioni attive; pertanto, nel caso in cui si rilevi un utilizzo elevato della CPU, questa DMV rappresenta una delle prime aree che è opportuno esaminare. Analizzare prima le sessioni con un conteggio elevato dei cicli di CPU. Determinare l'applicazione e l'utente che ha eseguito l'attività, quindi avviare un'analisi più particolareggiata. La combinazione delle DMV sys.dm_exec_sessions e sys.dm_exec_requests può fornire molte delle informazioni disponibili tramite le stored procedure sp_who e sp_who2. Se si utilizzano questi dati in combinazione con la funzione a gestione dinamica (DMF) sys.exec_sql_text attraverso la colonna sql_handle è possibile ottenere la query correntemente in esecuzione della sessione. Nel frammento nella Figura 3 viene illustrato come unire questi dati per determinare gli eventi correntemente eseguiti su un server.

Figure 3 Determinazione dell'attività del server

SELECT es.session_id
    ,es.program_name
    ,es.login_name
    ,es.nt_user_name
    ,es.login_time
    ,es.host_name
    ,es.cpu_time
    ,es.total_scheduled_time
    ,es.total_elapsed_time
    ,es.memory_usage
    ,es.logical_reads
    ,es.reads
    ,es.writes
    ,st.text
FROM sys.dm_exec_sessions es
    LEFT JOIN sys.dm_exec_connections ec 
        ON es.session_id = ec.session_id
    LEFT JOIN sys.dm_exec_requests er
        ON es.session_id = er.session_id
    OUTER APPLY sys.dm_exec_sql_text (er.sql_handle) st
WHERE es.session_id > 50    -- < 50 system sessions
ORDER BY es.cpu_time DESC

Ritengo che questa istruzione consente di determinare le applicazioni su cui è necessario concentrare l'attenzione. Quando si mettono a confronto CPU, memoria, letture, scritture e letture logiche per tutte le sessioni all'interno di un'applicazione e si determina che la percentuale di utilizzo delle risorse della CPU è molto più elevata rispetto a quella delle altre risorse utilizzate, è opportuno incentrare l'attenzione su queste istruzioni SQL.

Per tenere traccia delle istruzioni SQL per un'applicazione cronologicamente, utilizzare le tracce SQL Server. A tal fine, è possibile utilizzare lo strumento SQL Server Profiler o le stored procedure di sistema di traccia per determinare cosa accade nel sistema. Per ulteriori informazioni su questo argomento, vedere l'intestazione laterale "Esecuzione della traccia con stored procedure". È opportuno controllare SQL Profiler per verificare se siano presenti istruzioni con un elevato utilizzo della CPU oltre ad avvisi relativi a operazioni di ordinamento e hash, mancati riscontri nella cache e altri "red flag". Questo può consentire di focalizzare l'attenzione su specifiche istruzioni SQL o su uno specifico periodo di tempo che ha causato un utilizzo elevato delle risorse. SQL Profiler è in grado di tenere traccia di elementi come testo di istruzioni SQL, piani di esecuzione, utilizzo della CPU, utilizzo della memoria, letture logiche, scritture, memorizzazione nella cache di piani di query, ricompilazioni, rimozione di piani di query dalla cache, mancati riscontri nella cache, scansioni di tabelle e indici, statistiche mancanti e molti altri eventi.

Dopo aver raccolto i dati dalle stored procedure sp_trace o da SQL Server Profiler, è opportuno utilizzare un database, che viene compilato con i dati di traccia "a posteriori" o impostando la traccia per la scrittura nel database. La compilazione del database "a posteriori" può essere eseguita utilizzando la funzione di sistema di SQL Server fn_trace_getinfo. Il vantaggio di questo approccio è rappresentato dalla possibilità di eseguire una query e ordinare i dati in più modi per verificare quali istruzioni SQL hanno utilizzato il maggior numero di risorse della CPU o hanno eseguito il maggior numero di letture, calcolare il numero di ricompilazioni eseguite e molte altre attività. Di seguito viene riportato un esempio della modalità di utilizzo di questa funzione per caricare una tabella con un file di traccia di SQL Profiler. Per impostazione predefinita, tutti i file di traccia per tale traccia verranno caricati nell'ordine in cui sono stati creati:

SELECT * INTO trc_20070401
FROM fn_trace_gettable('S:\Mountpoints\TRC_20070401_1.trc', default);
GO

Conclusioni

Come si è osservato, un utilizzo elevato della CPU non indica necessariamente la presenza di un collo di bottiglia a livello della CPU. Un utilizzo elevato della CPU può mascherare anche numerosi altri colli di bottiglia a livello di applicazione o hardware. Una volta rilevato che si è in presenza di un utilizzo elevato della CPU nonostante gli altri contatori sembrino funzionare correttamente, è possibile iniziare a cercare la causa all'interno del sistema e individuare una soluzione (che potrebbe prevedere l'acquisto di CPU aggiuntive o l'ottimizzazione del codice SQL). Qualunque sia la scelta effettuata, è importante non rinunciare in partenza. I suggerimenti forniti in questo articolo, unitamente a un minimo di esercitazione e di ricerca, rendono l'ottimizzazione dell'utilizzo della CPU in SQL Server un obiettivo raggiungibile.

Zach Nichter è un professionista SQL Server con oltre 10 anni di esperienza. Ha ricoperto ruoli di supporto SQL Server diversi, tra cui amministratore del database (DBA), responsabile del team, manager e consulente. Attualmente Zach è architetto DBA presso Levi Strauss & Co. e si dedica in particolare a prestazioni, monitoraggio, architettura e altre attività strategiche di SQL Server. Zach è inoltre l'autore di un blog video disponibile all'indirizzo www.sqlcatch.com.

© 2008 Microsoft Corporation e CMP Media, LLC. Tutti i diritti riservati. È vietata la riproduzione completa o parziale senza autorizzazione.