• Articles
• admin

Recentemente, abbiamo lavorato su problemi di prestazioni di SQL Server per alcuni dei nostri grandi clienti che eseguono SQL Server come database. In questo articolo, voglio riassumere quello che ho imparato dal processo descrivendo alcuni passi che possiamo prendere per problemi-sparare problema di prestazioni.

Controllare la configurazione di SQL Server

Assicurarsi che il server di database sia configurato con risorse sufficienti, ad esempio il numero di core della CPU e la quantità di memoria.

Per controllare la configurazione del server, è possibile aprire” Informazioni di sistema ” vista:

Per controllare l’impostazione della memoria di SQL Server,

1. Avviare SQL Server Management Studio.
2. Fare clic con il pulsante destro del mouse sull’istanza del database e selezionare “Proprietà”.
3. Fare clic sulla tabella ” Memoria “nella finestra a comparsa” Proprietà server”.
4. Controllare le impostazioni di memoria.

Assicurarsi che la modalità Snapshot sia attiva

Assicurarsi che la modalità snapshot sia attivata per il database. Per impedire il blocco di SQL Server, è necessario attivare il flag modalità istantanea. Eseguire la seguente query per verificare se il flag è attivo:

SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name= '<database_name>'

Se la query restituisce ‘1’, la modalità snapshot è già attivata. In caso contrario, eseguire la seguente query per attivarla.

ALTER DATABASE <database_name> SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

CORRELATI: Definizione e implementazione delle baseline dei requisiti

Controllare gli indici del database

Controllare gli indici del database per assicurarsi che non vi siano indici mancanti. Eseguire la seguente query per elencare tutti tutti gli indici nel database (credit: tsql-Elenco di tutte le colonne indice & in SQL Server DB-Stack Overflow):

SELECT t.name TableName, col.name ColumnName, ind.name IndexNameFROM sys.indexes indINNER JOIN sys.index_columns ic ON ind.object_id = ic.object_id and ind.index_id = ic.index_idINNER JOIN sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_idINNER JOIN sys.tables t ON ind.object_id = t.object_idWHERE (ind.is_primary_key = 0 AND t.is_ms_shipped = 0)ORDER BY t.name, col.name, ind.name

Salvare i risultati della query in un file di testo con colonne delimitate da tabulazioni in modo che possano essere importati in un foglio di calcolo in un secondo momento.

Evitare la frammentazione

Assicurarsi che gli indici del database non siano frammentati. Gli indici nel database aiutano ad accelerare la query del database. Ma quando vengono frammentati, le query potrebbero funzionare molto lentamente. Come parte della manutenzione del database, gli indici dovrebbero essere riorganizzati o ricostruiti regolarmente.

Eseguire la seguente query per verificare la percentuale di frammentazione per tutti gli indici nel database (credit: come controllare la frammentazione dell’indice sugli indici in un database):

SELECT dbschemas.Jama Software AS 'Schema', dbtables.Jama Software AS 'Table', dbindexes.Jama Software AS 'Index', indexstats.avg_fragmentation_in_percent, indexstats.page_count, dbindexes.fill_factorFROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstatsINNER JOIN sys.tables dbtables ON dbtables. = indexstats.INNER JOIN sys.schemas dbschemas ON dbtables. = dbschemas.INNER JOIN sys.indexes AS dbindexes ON dbindexes. = indexstats. AND indexstats.index_id = dbindexes.index_idWHERE indexstats.database_id = DB_ID()ORDER BY indexstats.avg_fragmentation_in_percent DESC

Ecco un risultato di query di esempio per un database con indici altamente frammentati:

Per gli indici con conteggi di pagina superiori a 1000, “avg_framentation_in_percent” deve essere mantenuto al di sotto del 10%. Non importa molto per i piccoli indici. Come regola generale, gli indici con percentuali di frammentazione tra il 5% e il 30% potrebbero essere riorganizzati e gli indici con percentuali di frammentazione superiori al 30% dovrebbero essere ricostruiti.

Per riorganizzare un singolo indice:

ALTER INDEX REORGANIZE

Per ricostruire un singolo indice:

ALTER INDEX REBUILD WITH (ONLINE = ON)

È anche possibile riorganizzare o ricostruire un indice utilizzando SQL Server Management Studio facendo clic destro sull’indice che si desidera ricostruire o riorganizzare.

Se ci sono molti indici deframmentati nel database, è possibile eseguire la seguente query per ricostruirli tutti (credit: SQL SERVER – 2008 -2005-Ricostruire ogni indice di tutte le tabelle del database – Ricostruire l’indice con FillFactor). Si noti che la query può essere eseguita per un po ‘ per un database di grandi dimensioni. Si consiglia di eseguire la query mentre il database è inattivo o offline.

DECLARE @TableName VARCHAR(255)DECLARE @sql NVARCHAR(500)DECLARE TableCursor CURSOR FORSELECT OBJECT_SCHEMA_NAME()+'.'+name AS TableNameFROM sys.tablesOPEN TableCursorFETCH NEXT FROM TableCursor INTO @TableNameWHILE @@FETCH_STATUS = 0BEGINSET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD'PRINT @sqlEXEC (@sql)FETCH NEXT FROM TableCursor INTO @TableNameENDCLOSE TableCursorDEALLOCATE TableCursorGO

Si consiglia di impostare il processo di manutenzione in SQL Server studio per eseguire regolarmente la riorganizzazione dell’indice del database. Sebbene la ricostruzione degli indici in SQL Server dovrebbe probabilmente essere eseguita offline o quando il sistema è inattivo, la riorganizzazione degli indici potrebbe essere eseguita online. Ecco un buon articolo su questo argomento: Ricostruire o riorganizzare: SQL Server Index Maintenance.

Per impostare un piano di riorganizzazione dell’indice in SQL Server, fare clic con il pulsante destro del mouse su “Gestione”, quindi su “Piani di manutenzione” e selezionare “Nuovo piano di manutenzione” o “Procedura guidata piano di manutenzione”. Seguire le istruzioni per creare un piano.

CORRELATI: Rilascio di Opzioni per la Gestione del Jama Connect

Esegui Mancanti Indice Relazione

SQL Server fornisce uno strumento che analizza le attività di database e consiglia ulteriori indici che possono aiutare con le prestazioni delle query. La relazione potrebbe darci alcune idee sul motivo per cui alcune query sono lente.

Per generare il report, eseguire la seguente query dopo che l’istanza del database è stata utilizzata per un po ‘ (credito: Non creare ciecamente quegli indici “mancanti”!):

SELECT d., s = OBJECT_SCHEMA_NAME(d.), o = OBJECT_NAME(d.), d.equality_columns, d.inequality_columns, d.included_columns, s.unique_compiles, s.user_seeks, s.last_user_seek, s.user_scans, s.last_user_scan, s.avg_total_user_cost, s.avg_user_impactFROM sys.dm_db_missing_index_details AS dINNER JOIN sys.dm_db_missing_index_groups AS gON d.index_handle = g.index_handleINNER JOIN sys.dm_db_missing_index_group_stats AS sON g.index_group_handle = s.group_handleWHERE d.database_id = DB_ID() AND OBJECTPROPERTY(d., 'IsMsShipped') = 0

Leggi l’articolo Trovare gli indici mancanti per comprendere l’output di questa query.

Monitorare le sessioni del database

Utilizzare Activity Monitor per monitorare le sessioni del database. Microsoft SQL Server Management Studio viene fornito con Activity Monitor, che può essere utilizzato per monitorare le sessioni del database.

Cerca le sessioni bloccate da altre sessioni per un lungo periodo. Fare clic destro sulla sessione e selezionare “Dettagli” per mostrare i dettagli della query associata al processo.

Usa Windows Resource Monitor

Windows Resource Monitor può essere utilizzato per monitorare la memoria e l’utilizzo della CPU del processo SQL Server per assicurarsi che ci sia abbastanza memoria nel sistema e che la CPU non sia satura. Si noti che esiste un problema noto per SQL Server 2008 in cui la figura di memoria mostrata per il processo SQL Server in Resource Monitor non è corretta.

Identificare Slow Query

Se abbiamo stabilito che le prestazioni non sono relativi alle risorse, il passo successivo è quello di identificare slow query che portano a cattive prestazioni.

La seguente query restituisce le prime 100 query lente che vengono eseguite per più di 300 ms (credito: query a lunga esecuzione):

SELECT st.text, qp.query_plan, qs.*FROM ( SELECT TOP 50 * FROM sys.dm_exec_query_stats ORDER BY total_worker_time DESC) AS qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS stCROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qpWHERE qs.max_worker_time > 300 OR qs.max_elapsed_time > 300

Ecco una schermata di alcuni risultati di esempio:

Una volta raccolto un elenco di query lente, potremmo eseguirle direttamente sul database e esaminare i piani di esecuzione per comprendere tali query.

Altri strumenti

Ecco un elenco di altri strumenti che possono essere utilizzati per risolvere i problemi di SQL Server e applicazioni Java:

• SQL Server Profiler: può essere utilizzato per identificare query lente. È possibile avviarlo da SQL Server Management Studio
• VisualVM: può essere utilizzato per monitorare l’utilizzo delle risorse Java e fare discariche di thread
• Java Mission Control / Flight recording: monitorare e registrare eventi di sistema
• JProfiler: identificare le chiamate di servizio o database lente (sviluppo, non supportato da 8.x deployment)
• New Relic: può monitorare le prestazioni del database e registrare query lente

Check List

Ecco un elenco di informazioni che possono essere raccolte per risolvere i problemi di SQL Server:

1. configurazione del server di Database (numero di core della CPU, memoria fisica, spazio su disco, Windows Server versione, la versione di SQL Server, SQL Server impostazioni di memoria)
2. configurazione del server di Applicazioni (numero di core della CPU, memoria fisica, spazio su disco, Java core impostazioni di memoria)
3. indice del Database delle statistiche
4. Database frammentazione delle statistiche
5. SQL server mancanti indice relazione
6. memoria del server del Database e le statistiche della CPU quando il server è lento
7. Operazioni che possono causare il sistema sia lento
8. SQL server query lenta report
9. Statistiche della tabella del database
10. Un paio di dump di thread da applicazioni Java prese durante le operazioni lente se applicabile

Conclusione

Il debug dei problemi di prestazioni di SQL Server non è sempre semplice. Ma con gli strumenti giusti e la pazienza, dovremmo essere in grado di capire la causa di questi problemi. Spero che le informazioni in questo articolo ti aiutino.