Come ottimizzare Microsoft SQL Server per le prestazioni

Per mantenere i propri utenti, qualsiasi applicazione o sito Web deve funzionare velocemente. Per gli ambienti mission-critical, un paio di millisecondi di ritardo nell'acquisizione delle informazioni potrebbero creare grossi problemi. Poiché le dimensioni del database crescono di giorno in giorno, è necessario recuperare i dati il ​​più velocemente possibile e riscriverli nel database il più velocemente possibile. Per assicurarci che tutte le operazioni vengano eseguite senza intoppi, dobbiamo ottimizzare il nostro server di database per le prestazioni.

In questo articolo descriverò una procedura passo passo per l'ottimizzazione delle prestazioni di base su uno dei migliori server di database sul mercato: Microsoft SQL Server (SQL Server, in breve).

# 1 Trovare i colpevoli

Come con qualsiasi altro software, dobbiamo capire che SQL Server è un programma per computer complesso. Se abbiamo un problema con esso, dobbiamo scoprire perché non funziona come ci aspettiamo.

Da SQL Server è necessario eseguire il pull e il push dei dati nel modo più rapido e accurato possibile. Se ci sono problemi, un paio di ragioni di base e le prime due cose da controllare sono:

• Le impostazioni hardware e di installazione, che potrebbero dover essere corrette poiché le esigenze di SQL Server sono specifiche
• Se abbiamo fornito il codice T-SQL corretto per l'implementazione di SQL Server

Anche se SQL Server è un software proprietario, Microsoft ha fornito molti modi per comprenderlo e utilizzarlo in modo efficiente.

Se l'hardware è OK e l'installazione è stata eseguita correttamente, ma SQL Server continua a funzionare lentamente, prima dobbiamo scoprire se ci sono errori relativi al software. Per verificare cosa sta succedendo, dobbiamo osservare come funzionano i diversi thread. Ciò si ottiene calcolando le statistiche di attesa di thread diversi. Il server SQL utilizza i thread per ogni richiesta dell'utente e il thread non è altro che un altro programma all'interno del nostro complesso programma chiamato SQL Server. È importante notare che questo thread non è un thread del sistema operativo su cui è installato SQL Server; è correlato al thread SQLOS, che è uno pseudo sistema operativo per SQL Server.

Le statistiche di attesa possono essere calcolate utilizzando sys.dm_os_wait_stats Dynamic Management View (DMV), che fornisce informazioni aggiuntive sul suo stato corrente. Ci sono molti script online per interrogare questa vista, ma il mio preferito è lo script di Paul Randal perché è facile da capire e ha tutti i parametri importanti per osservare le statistiche di attesa:

 WITH [Waits] AS (SELECT [wait_type], [wait_time_ms] / 1000.0 AS [WaitS], ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS], [signal_wait_time_ms] / 1000.0 AS [SignalS], [waiting_tasks_count] AS [WaitCount], 100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage], ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum] FROM sys.dm_os_wait_stats WHERE [wait_type] NOT IN ( N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR', N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH', N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE', N'CHKPT', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE', N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE', N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD', N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE', N'EXECSYNC', N'FSAGENT', N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX', N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE', N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE', N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP', N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE', N'PWAIT_ALL_COMPONENTS_INITIALIZED', N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE', N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH', N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP', N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY', N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP', N'SLEEP_SYSTEMTASK', N'SLEEP_TASK', N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT', N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS', N'WAITFOR', N'WAITFOR_TASKSHUTDOWN', N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN', N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT') AND [waiting_tasks_count] > 0 ) SELECT MAX ([W1].[wait_type]) AS [WaitType], CAST (MAX ([W1].[WaitS]) AS DECIMAL (16,2)) AS [Wait_S], CAST (MAX ([W1].[ResourceS]) AS DECIMAL (16,2)) AS [Resource_S], CAST (MAX ([W1].[SignalS]) AS DECIMAL (16,2)) AS [Signal_S], MAX ([W1].[WaitCount]) AS [WaitCount], CAST (MAX ([W1].[Percentage]) AS DECIMAL (5,2)) AS [Percentage], CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgWait_S], CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S], CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgSig_S] FROM [Waits] AS [W1] INNER JOIN [Waits] AS [W2] ON [W2].[RowNum] <= [W1].[RowNum] GROUP BY [W1].[RowNum] HAVING SUM ([W2].[Percentage]) - MAX ([W1].[Percentage]) < 95; -- percentage threshold GO

Quando eseguiamo questo script, dobbiamo concentrarci sulle righe superiori del risultato perché sono impostate per prime e rappresentano il tipo di attesa massimo.

Dobbiamo comprendere i tipi di attesa in modo da poter prendere le decisioni corrette. Per conoscere i diversi tipi di attesa, possiamo andare all'eccellente documentazione Microsoft.

Facciamo un esempio in cui abbiamo troppo PAGEIOLATCH_XX . Ciò significa che un thread è in attesa di letture di pagine di dati dal disco nel buffer, che non è altro che un blocco di memoria. Dobbiamo essere sicuri di capire cosa sta succedendo. Ciò non significa necessariamente un sottosistema di I/O scadente o memoria insufficiente e l'aumento del sottosistema di I/O e della memoria risolverà il problema, ma solo temporaneamente. Per trovare una soluzione permanente, dobbiamo vedere perché vengono letti così tanti dati dal disco: quali tipi di comandi SQL stanno causando questo? Stiamo leggendo troppi dati invece di leggere meno dati utilizzando filtri, ad esempio where clausole? Si verificano troppe letture di dati a causa di scansioni di tabelle o scansioni di indici? Possiamo convertirli in ricerche di indici implementando o modificando gli indici esistenti? Stiamo scrivendo query SQL fraintese da SQL Optimizer (un altro programma all'interno del nostro programma server SQL)?

Dobbiamo pensare da diverse angolazioni e utilizzare diversi casi di test per trovare soluzioni. Ciascuno dei tipi di attesa precedenti richiede una soluzione diversa. Un amministratore di database deve ricercarli a fondo prima di intraprendere qualsiasi azione. Ma la maggior parte delle volte, trovare query T-SQL problematiche e ottimizzarle risolverà dal 60 al 70 percento dei problemi.

# 2 Trovare domande problematiche

Come accennato in precedenza, la prima cosa che possiamo fare è cercare query problematiche. Il seguente codice T-SQL troverà le 20 query con le prestazioni peggiori:

 SELECT TOP 20 total_worker_time/execution_count AS Avg_CPU_Time ,Execution_count ,total_elapsed_time/execution_count as AVG_Run_Time ,total_elapsed_time ,(SELECT SUBSTRING(text,statement_start_offset/2+1,statement_end_offset ) FROM sys.dm_exec_sql_text(sql_handle) ) AS Query_Text FROM sys.dm_exec_query_stats ORDER BY Avg_CPU_Time DESC

Dobbiamo stare attenti ai risultati; anche se una query può avere un tempo di esecuzione medio massimo, se viene eseguita una sola volta, l'effetto totale sul server è basso rispetto a una query che ha un tempo di esecuzione medio medio ed è eseguita molte volte in un giorno.

# 3 Query di messa a punto

La messa a punto di una query T-SQL è un concetto importante. La cosa fondamentale da capire è quanto bene possiamo scrivere query T-SQL e implementare indici, in modo che l'ottimizzatore SQL possa trovare un piano ottimizzato per fare ciò che volevamo. Con ogni nuova versione di SQL Server, otteniamo un ottimizzatore più sofisticato che coprirà i nostri errori nella scrittura di query SQL non ottimizzate e correggerà anche eventuali bug relativi all'ottimizzatore precedente. Ma, non importa quanto intelligente possa essere l'ottimizzatore, se non possiamo dirgli quello che vogliamo (scrivendo una query T-SQL adeguata), l'ottimizzatore SQL non sarà in grado di fare il suo lavoro.

SQL Server utilizza algoritmi avanzati di ricerca e ordinamento. Se siamo bravi nella ricerca e nell'ordinamento degli algoritmi, la maggior parte delle volte possiamo indovinare perché SQL Server sta intraprendendo un'azione particolare. Il miglior libro per saperne di più e comprendere tali algoritmi è The Art of Computer Programming di Donald Knuth.

Quando esaminiamo le query che devono essere perfezionate, è necessario utilizzare il piano di esecuzione di tali query in modo da poter scoprire in che modo SQL Server le sta interpretando.

Non posso coprire tutti gli aspetti del piano di esecuzione qui, ma a livello di base posso spiegare le cose che dobbiamo considerare.

• Per prima cosa dobbiamo scoprire quali operatori prendono la maggior parte del costo della query.
• Se l'operatore sta assumendo molti costi, dobbiamo imparare il motivo. La maggior parte delle volte, le scansioni richiedono più costi di quelli che cercano. È necessario esaminare il motivo per cui si verifica una scansione particolare (scansione della tabella o scansione dell'indice) anziché una ricerca dell'indice. Possiamo risolvere questo problema implementando indici adeguati sulle colonne della tabella, ma come con qualsiasi programma complesso, non esiste una soluzione fissa. Ad esempio, se la tabella è piccola, le scansioni sono più veloci delle ricerche.
• Sono disponibili circa 78 operatori, che rappresentano le varie azioni e decisioni del piano di esecuzione di SQL Server. Dobbiamo studiarli a fondo consultando la documentazione Microsoft, in modo da poterli comprendere meglio e agire nel modo giusto.

# 4 Riutilizzo del piano di esecuzione

Anche se implementiamo indici appropriati sulle tabelle e scriviamo un buon codice T-SQL, se il piano di esecuzione non viene riutilizzato, avremo problemi di prestazioni. Dopo aver perfezionato le query, dobbiamo assicurarci che il piano di esecuzione possa essere riutilizzato quando necessario. La maggior parte del tempo della CPU sarà dedicato al calcolo del piano di esecuzione che può essere eliminato se riutilizziamo il piano.

Possiamo utilizzare la query seguente per scoprire quante volte il piano di esecuzione viene riutilizzato, dove usecounts rappresenta quante volte il piano viene riutilizzato:

 SELECT [ecp].[refcounts] , [ecp].[usecounts] , [ecp].[objtype] , DB_NAME([est].[dbid]) AS [db_name] , [est].[objectid] , [est].[text] as [query_ext] , [eqp].[query_plan] FROM sys.dm_exec_cached_plans ecp CROSS APPLY sys.dm_exec_sql_text ( ecp.plan_handle ) est CROSS APPLY sys.dm_exec_query_plan ( ecp.plan_handle ) eqp

Il modo migliore per riutilizzare il piano di esecuzione consiste nell'implementazione di stored procedure con parametri. Quando non siamo in grado di implementare le procedure memorizzate, possiamo usare sp_executesql , che può essere utilizzato invece per eseguire istruzioni T-SQL quando l'unica modifica alle istruzioni SQL sono i valori dei parametri. Molto probabilmente SQL Server riutilizzerà il piano di esecuzione che ha generato nella prima esecuzione.

Ancora una volta, come con qualsiasi programma per computer complesso, non esiste una soluzione fissa. A volte è meglio compilare di nuovo il piano.

Esaminiamo le seguenti due query di esempio:

• select name from table where name = 'sri';
• select name from table where name = 'pal';

Supponiamo di avere un indice non cluster sulla colonna del name e metà della tabella ha valore sri e poche righe hanno pal nella colonna del name . Per la prima query, SQL Server utilizzerà l'analisi della tabella perché metà della tabella ha gli stessi valori. Ma per la seconda query, è meglio usare la scansione dell'indice perché solo poche righe hanno il valore pal .

Anche se le query sono simili, lo stesso piano di esecuzione potrebbe non essere una buona soluzione. Il più delle volte sarà un caso diverso, quindi dobbiamo analizzare attentamente tutto prima di decidere. Se non vogliamo riutilizzare il piano di esecuzione, possiamo sempre utilizzare l'opzione "ricompila" nelle stored procedure.

Tenere presente che anche dopo l'utilizzo di stored procedure o sp_executesql , a volte il piano di esecuzione non verrà riutilizzato. Loro sono:

• Quando gli indici utilizzati dalla query cambiano o vengono eliminati
• Quando le statistiche, la struttura o lo schema di una tabella utilizzata dalla query cambiano
• Quando utilizziamo l'opzione "ricompila".
• Quando è presente un numero elevato di inserimenti, aggiornamenti o eliminazioni
• Quando mescoliamo DDL e DML all'interno di una singola query

# 5 Rimozione di indici non necessari

Dopo aver messo a punto le query, è necessario verificare come vengono utilizzati gli indici. La manutenzione dell'indice richiede molta CPU e I/O. Ogni volta che inseriamo dati in un database, SQL Server ha anche bisogno di aggiornare gli indici, quindi è meglio rimuoverli se non vengono utilizzati.

Il server SQL ci fornisce dm_db_index_usage_stats DMV per trovare le statistiche dell'indice. Quando eseguiamo il codice T-SQL riportato di seguito, otteniamo statistiche sull'utilizzo per diversi indici. Se troviamo indici che non vengono utilizzati affatto o utilizzati raramente, possiamo eliminarli per aumentare le prestazioni.

 SELECT OBJECT_NAME(IUS.[OBJECT_ID]) AS [OBJECT NAME], DB_NAME(IUS.database_id) AS [DATABASE NAME], I.[NAME] AS [INDEX NAME], USER_SEEKS, USER_SCANS, USER_LOOKUPS, USER_UPDATES FROM SYS.DM_DB_INDEX_USAGE_STATS AS IUS INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = IUS.[OBJECT_ID] AND I.INDEX_ID = IUS.INDEX_ID

#6 Installazione di SQL Server e configurazione del database

Quando si configura un database, è necessario mantenere separati i dati e i file di registro. Il motivo principale è che la scrittura e l'accesso ai file di dati non sono sequenziali, mentre la scrittura e l'accesso ai file di registro sono sequenziali. Se li mettiamo sullo stesso disco non possiamo usarli in modo ottimizzato.

Quando acquistiamo Storage Area Network (SAN), un fornitore potrebbe darci alcuni consigli su come configurarlo, ma queste informazioni non sono sempre utili. Abbiamo bisogno di una discussione dettagliata con i nostri tecnici dell'hardware e del networking su come mantenere i dati e i file di registro separatamente e in modo ottimizzato.

# 7 Non sovraccaricare SQL Server

Il compito principale di qualsiasi amministratore di database è assicurarsi che il server di produzione funzioni senza problemi e serva i clienti nel miglior modo possibile. Affinché ciò avvenga, è necessario mantenere database separati (se possibile, su macchine separate) per i seguenti ambienti:

• Produzione
• Sviluppo
• Test
• Analitico

Per un database di produzione è necessario un database con modalità di ripristino completo e per altri database è sufficiente una semplice modalità di ripristino.

Il test su un database di produzione aumenterà molto il carico sul registro delle transazioni, sugli indici, sulla CPU e sull'I/O. Ecco perché abbiamo bisogno di utilizzare database separati per la produzione, lo sviluppo, il test e l'analisi. Se possibile, utilizzare macchine separate per ogni database, perché diminuirà il carico sulla CPU e sull'I/O.

# 8 Registro delle transazioni, tempdb e memoria

Il file di registro deve disporre di spazio libero sufficiente per le normali operazioni poiché un'operazione di aumento automatico su un file di registro richiede tempo e potrebbe costringere altre operazioni ad attendere fino al completamento. Per scoprire la dimensione del file di registro per ciascun database e quanto viene utilizzato, possiamo utilizzare DBCC SQLPERF(logspace) .

Il modo migliore per configurare tempdb è inserirlo su un disco separato. Dobbiamo mantenere la dimensione iniziale più grande che possiamo permetterci perché quando raggiunge una situazione di crescita automatica, le prestazioni diminuiranno.

Come accennato in precedenza, dobbiamo assicurarci che il server SQL venga eseguito su una macchina separata, preferibilmente una senza altre applicazioni su di essa. Abbiamo bisogno di conservare un po' di memoria per il sistema operativo, più un po' di più se fa parte di un cluster, quindi nella maggior parte dei casi dovrebbero bastare circa 2 GB.

Conclusione:

Le procedure e i suggerimenti discussi qui sono solo per l'ottimizzazione delle prestazioni di base. Se seguiamo questi passaggi, potremmo, in media, ottenere un miglioramento delle prestazioni dal 40 al 50 percento circa. Per eseguire l'ottimizzazione avanzata delle prestazioni di SQL Server, sarebbe necessario approfondire ciascuno dei passaggi descritti qui.

Ulteriori letture sul blog di Toptal Engineering:

• Risolvere i colli di bottiglia con indici SQL e partizioni
• Guida alla migrazione da Oracle a SQL Server e da SQL Server a Oracle