Performance Tuning

Introducing SQL Server Performance Tuning

Siete frustrati dalla scarsa SQL server performance (prestazioni scadenti possono influire sull'esperienza dell'utente, sulle operazioni commerciali e sulla scalabilità del sistema)? Se sì, allora siete nel posto giusto. Qui e ora riceverete assistenza per il tuning delle prestazioni del server SQL e un supporto completo con un rapporto dettagliato sul lavoro svolto. La nostra strategia può influenzare in modo significativo la velocità e l'efficienza della vostra applicazione. Sbloccheremo il pieno potenziale del vostro database. Preparatevi a sovraccaricare il vostro server SQL!

Ottimizzazione delle query è una tecnica fondamentale per migliorare le prestazioni di SQL Server. L'ottimizzazione delle query SQL consiste nell'individuare i metodi più efficienti ed efficaci per migliorare le prestazioni delle query. Esistono diverse strategie di ottimizzazione delle query che possono essere utilizzate per raggiungere questo obiettivo. Un approccio comune consiste nel creare indici appropriati sulle colonne utilizzate nelle clausole WHERE, JOIN e ORDER BY per accelerare il recupero dei dati. Inoltre, selezionare solo i campi necessari invece di usare SELECT * ed evitare l'uso di DISTINCT nelle istruzioni SELECT può contribuire a ottimizzare le prestazioni delle query. Utilizzando queste tecniche, gli utenti di SQL Server possono migliorare significativamente la velocità e l'efficienza delle loro query, con conseguenti migliori prestazioni complessive..
Indicizzazione è un'altra tecnica importante per migliorare le prestazioni di SQL Server. Gli indici di SQL Server aiutano a velocizzare il recupero dei dati, fornendo un modo più rapido per individuare i dati all'interno di una tabella. Esistono diverse best practice per l'indicizzazione in SQL Server, come la creazione di indici pertinenti e l'evitare la sovraindicizzazione. Inoltre, gli strumenti di ottimizzazione delle prestazioni possono essere utilizzati per identificare e risolvere i problemi di indicizzazione, ad esempio consigliando la creazione di nuovi indici o la rimozione di quelli non necessari. Implementando correttamente le tecniche di indicizzazione, gli utenti di SQL Server possono assicurarsi che i loro database funzionino ai massimi livelli di prestazioni.
Configurazione del server è un altro fattore critico nella messa a punto delle prestazioni di SQL Server. Una corretta configurazione dell'istanza e dell'hardware di SQL Server può contribuire a garantire un funzionamento efficiente ed efficace del server. Ad esempio, la configurazione delle impostazioni di memoria del server e dell'affinità del processore può contribuire a ottimizzare le prestazioni. Inoltre, le attività di monitoraggio e messa a punto delle prestazioni possono aiutare a identificare e isolare i problemi di prestazioni, come i colli di bottiglia o i problemi di prestazioni del database. Seguendo le best practice per la configurazione e il monitoraggio del server, gli utenti di SQL Server possono ottimizzare le prestazioni del loro server e assicurarsi che funzioni al massimo dell'efficienza.

Tecniche per migliorare SQL Server performance

In conclusione, la messa a punto delle prestazioni di SQL Server è fondamentale per garantire che i sistemi di database funzionino in modo efficiente ed efficace. Implementando strategie di ottimizzazione delle query, tecniche di indicizzazione e best practice di configurazione del server, le organizzazioni possono migliorare le prestazioni di SQL Server e, in ultima analisi, migliorare le operazioni aziendali complessive. È importante valutare e mettere a punto regolarmente le prestazioni di SQL Server per garantire che soddisfino le esigenze dell'organizzazione e dei suoi utenti. Con le tecniche e le strategie giuste, le organizzazioni possono ottimizzare le prestazioni di SQL Server e ottenere un maggiore successo nelle loro attività commerciali.

Il sys.dm_exec_sessions DMV include le sessioni utente e le sessioni interne di SQL Server utilizzate per l'esecuzione di attività in background.
sys.dm_exec_connections contiene informazioni aggiuntive per le sessioni stabilite da client esterni, compresi i dettagli del protocollo.
sys.dm_exec_requests contiene informazioni sulle richieste attive di SQL Server (ad esempio, le query in esecuzione).

Vista della memoria allocata

Fornisce informazioni più dettagliate sulla top 20 dell'allocazione e dell'utilizzo della memoria di specifiche query di database, oltre a campi aggiuntivi
per la gestione dei dati.Tabelle di origine del database perf_querystore da dove prendere i dati:

perfsys.dm_exec_sessions – fornisce informazioni su tutte le connessioni utente attive e sui task interni.
Questo include informazioni sul client, come i nomi delle applicazioni e degli host, le impostazioni di sicurezza, le impostazioni di configurazione e altri dati specifici della sessione.
perfsys.dm_exec_requests - fornisce informazioni su ogni richiesta in esecuzione all'interno di SQL Server.
Ciò include le richieste provenienti da sessioni utente, sessioni di sistema e processi interni.
perfsys.dm_exec_query_memory_grants - fornisce informazioni su tutte le query che hanno richiesto e sono in attesa di una concessione di memoria o a cui è stata concessa
una memoria grant. perfsys.dm_exec_sql_text - informazioni sul testo della query SQL secondo query sql_handle.
dbo.tbl_query_store_plan_handle - rappresenta l'identificatore della posizione di memoria in cui è memorizzato il piano di esecuzione effettivo di una determinata query.

Script WhoTheFuckIsWrong.sql è un potente strumento per l'analisi delle prestazioni di SQL Server.

Con sceneggiatura WhoTheFuckIsWrong.sql è possibile verificare facilmente quali query problematiche sono in esecuzione su SQL Server in tempo reale.

Nei risultati dell'esecuzione di questo script verranno visualizzate tutte le metriche per identificare le query problematiche che occupano la maggior parte delle risorse di SQL Server.

Alcune delle metriche più importanti che si possono analizzare nei risultati di WhoTheFuckIsWrong.sql esecuzione:

Memoria concessa - memoria allocata sul server per la query
Memoria usata – memoria utilizzata durante l'esecuzione della query
Letture – numero di letture eseguite dalla query
Scrive – numero di scritture eseguite dalla query
Tempo di CPU – mostra in millisecondi per quanto tempo la CPU è stata occupata dalla query
Tempo totale trascorso - mostra in millisecondi la durata dell'esecuzione della query
Dichiarazione SQL – è il testo SQL della query
Piano SQL – mostra il piano di esecuzione della query

È inoltre possibile vedere da quale server e applicazione è stata avviata la query e quale utente l'ha eseguita.
Scrittura WhoTheFuckIsWrong.sql è un potente strumento per l'analisi delle prestazioni di SQL Server.

Acquista la sceneggiatura ➜

Database perf_neartime

Il database raccoglie dati in tempo reale catturando le attività del sistema e memorizzandole in varie tabelle. Questi dati vengono salvati ogni 5 minuti per garantire una registrazione aggiornata delle prestazioni del sistema. Il processo di salvataggio dei dati è avviato da un job denominato "Performance_Neartime" che esegue una stored procedure denominata "StorePerformanceData" Questo job e la stored procedure lavorano insieme per recuperare le informazioni necessarie dal sistema e memorizzarle nelle tabelle appropriate del database.

I dati raccolti forniscono preziose informazioni sulle prestazioni del sistema, consentendo agli utenti di analizzare e monitorare vari aspetti delle attività del sistema. Raccogliendo i dati a intervalli regolari, è possibile tracciare i cambiamenti e identificare le tendenze nel tempo. Queste informazioni sono fondamentali per gli amministratori e gli analisti di sistema per ottimizzare le prestazioni del sistema, risolvere i problemi e prendere decisioni informate sull'allocazione delle risorse e sulla configurazione del sistema.

I dati raccolti includono informazioni sul server, come il nome del server, la versione, l'ora di accesso e i dettagli della sessione. Vengono inoltre acquisite le richieste attive, i piani di esecuzione, le dimensioni dei database, le sessioni bloccanti, le statistiche dei gruppi di lavoro, gli utenti connessi, le attività di allocazione e deallocazione delle pagine, le concessioni di memoria, le query in esecuzione, lo stato del pool di risorse, le transazioni aperte, lo stato di attesa, i lavori pianificati e l'utilizzo corrente della memoria. Ciascuna di queste tabelle contiene colonne specifiche che contengono informazioni rilevanti relative alla rispettiva categoria di dati.

Performance tunung Panoramica Database completo perf_neartime

Database perf_querystore

La funzione Realtime Data Collection for the Query Store offre preziose informazioni sulle scelte del piano di query e sulle prestazioni di SQL Server. Semplifica notevolmente la risoluzione dei problemi di prestazioni, consentendo di identificare rapidamente le discrepanze di prestazioni causate da modifiche al piano di query. Query Store acquisisce e conserva automaticamente la cronologia delle query, dei piani e delle statistiche di runtime per consentirne l'analisi. Il processo di salvataggio dei dati viene avviato da un job denominato Performance_QueryStore. Questo job richiama una serie di stored procedure che salvano i dati dalle tabelle di sistema di Query Store e dalle Dynamical Management Views alle tabelle del database perf_querystore. I dati salvati vengono utilizzati per creare viste e tabelle di reporting con informazioni per analizzare e definire la maggior parte delle query di carico.

Performance tunung Panoramica Database completo perf_querystore

SQL Server Performance Tuning Migliori pratiche

Prima di addentrarci negli strumenti e nelle tecniche specifiche per il tuning delle prestazioni, esaminiamo alcune best practice generali che possono aiutarvi a migliorare le prestazioni delle istanze di SQL Server..

Utilizzate una convenzione di denominazione e uno stile di codifica coerente per gli oggetti, le variabili e le query. Questo renderà il codice più leggibile, manutenibile e facile da debuggare.
Evitare di utilizzare indici, vincoli, trigger, viste, funzioni e stored procedure inutili o ridondanti. Questi possono aggiungere overhead e complessità al sistema e influire sul piano di esecuzione delle query e sulle prestazioni.
Utilizzare tipi e dimensioni di dati appropriati per le colonne e le variabili. In questo modo si ridurrà lo spazio di archiviazione e l'uso della memoria e si migliorerà la qualità e l'integrità dei dati.
Utilizzare query e stored procedure parametrizzate invece di SQL dinamico o stringhe concatenate. In questo modo si prevengono gli attacchi di SQL injection, si riducono i tempi di parsing e di compilazione e si migliora il riutilizzo e la cache del piano di query.
Utilizzare le operazioni batch e i metodi di inserimento massivo invece delle operazioni riga per riga. In questo modo si ridurranno i viaggi di rete e l'attività del registro delle transazioni, migliorando il throughput e la concurrency.
Utilizzare strategie e tecniche di indicizzazione adeguate per le tabelle e le query. Questo migliorerà le prestazioni delle query riducendo il numero di operazioni di I/O su disco e di scansioni dei dati.
Utilizzare i suggerimenti e le opzioni per le query solo se necessario e con cautela. Questi possono annullare il comportamento predefinito dell'ottimizzatore di query e influenzare il piano di esecuzione della query e le prestazioni. Alcuni di questi suggerimenti e opzioni sono: NOLOCK, RECOMPILE, OPTIMIZE FOR, MAXDOP, ecc.
Monitorate e analizzate regolarmente le metriche e i contatori delle prestazioni delle istanze di SQL Server. Questo vi aiuterà a identificare le prestazioni di base, a rilevare eventuali anomalie o colli di bottiglia e a risolvere eventuali problemi.

SQL Server Performance Tuning Strumenti

SQL Server offre una serie di strumenti e funzioni che possono aiutare nella messa a punto delle prestazioni. Alcuni di questi strumenti sono:

SQL Server Management Studio (SSMS): È lo strumento principale per gestire e amministrare le istanze di SQL Server. Fornisce un'interfaccia grafica (GUI) per eseguire varie operazioni, come la creazione e la modifica di oggetti, l'esecuzione di query, la visualizzazione dei risultati, il monitoraggio delle prestazioni, ecc.
SQL Server Profiler: È uno strumento per catturare e analizzare gli eventi che si verificano sulle istanze di SQL Server. Consente di tracciare l'attività del server, ad esempio le query eseguite, gli errori verificatisi, le transazioni impegnate o annullate, ecc. È possibile utilizzare questo strumento per identificare le query che consumano più risorse o che causano problemi di prestazioni.
Database Engine Tuning Advisor (DTA): È uno strumento che analizza lo schema del database e il carico di lavoro e consiglia indici, statistiche, partizioni ecc. ottimali. Utilizza i dati di traccia raccolti da SQL Server Profiler o da altre fonti per generare raccomandazioni di tuning.
Query Store: È una funzione che raccoglie e archivia i piani di esecuzione delle query e le metriche delle prestazioni dei database. Permette di seguire la storia delle query, di confrontare i diversi piani e le metriche nel tempo, di identificare eventuali modifiche o regressioni del piano, ecc.
Extended Events: È una funzione che fornisce un quadro leggero e flessibile per la raccolta e l'analisi degli eventi che si verificano sulle istanze di SQL Server. Consente di creare sessioni personalizzate con vari eventi, target, azioni, filtri e così via. È possibile utilizzare questa funzione per monitorare vari aspetti delle prestazioni del server, come attese, blocchi, deadlock, utilizzo della memoria, ecc.
Dynamic Management Views (DMVs) and Functions (DMFs): Si tratta di viste e funzioni di sistema che espongono varie informazioni sulle istanze di SQL Server. Consentono di interrogare vari aspetti dello stato del server, della configurazione, delle prestazioni e così via. Alcune di queste viste e funzioni sono: sys.dm_exec_requests, sys.dm_exec_sessions, sys.dm_exec_query_stats, sys.dm_os_wait_stats, sys.dm_os_performance_counters, sys.dm_db_index_usage_stats, etc.

SQL Server Performance Tuning Tecniche

Oltre all'uso degli strumenti sopra menzionati, esistono alcune tecniche specifiche che possono essere utilizzate per sintonizzare le prestazioni delle istanze di SQL Server. Alcune di queste tecniche sono:

Query Ottimizzazione: Si tratta di scrivere query efficienti che restituiscano i risultati desiderati con un consumo minimo di risorse. È possibile utilizzare vari metodi per ottimizzare le query, come ad esempio:

Utilizzo di join invece di subquery o subquery correlate
Utilizzo di EXISTS al posto di IN o NOT IN
Utilizzo delle clausole WHERE per filtrare i dati non necessari
Utilizzare le clausole ORDER BY solo se necessario
Utilizzo delle clausole GROUP BY con le funzioni aggregate al posto di DISTINCT
Utilizzo di espressioni di tabella comuni (CTE) o di tabelle temporanee al posto delle tabelle derivate
Utilizzo di operazioni basate su set invece di cursori o loop
Utilizzo di UNION ALL invece di UNION
Utilizzando gli operatori e le funzioni appropriate, come LIKE, BETWEEN, ISNULL, COALESCE, ecc..

Index Ottimizzazione: Ciò comporta la creazione e il mantenimento di indici ottimali per le tabelle e le query. È possibile utilizzare vari metodi per ottimizzare gli indici, come ad esempio:

Scegliere il tipo di indice giusto, ad esempio clustered, non clustered, filtrato, columntore, ecc.
Scegliere le colonne indice giuste, come quelle utilizzate nelle clausole WHERE, JOIN, ORDER BY o GROUP BY.
Scegliere l'ordine di indicizzazione corretto, ad esempio ascendente o discendente.
Scelta delle giuste opzioni per gli indici, come il fattore di riempimento, la compressione, il partizionamento, ecc.
Aggiornare regolarmente le statistiche dell'indice per riflettere le modifiche dei dati..
Ricostruzione o riorganizzazione periodica della frammentazione dell'indice per migliorarne le prestazioni.
Eliminazione degli indici inutilizzati o duplicati per ridurre l'overhead di manutenzione degli indici.

Database Ottimizzazione: This involves designing and maintaining optimal database structures and settings for the system. Various methods can be used to optimise the database, such as:

Scelta dei tipi di dati e delle dimensioni giuste per le colonne e le variabili
Scegliere il giusto livello di fascicolazione e compatibilità per il proprio database
Scelta del modello di ripristino e della strategia di backup più adatti al vostro database
Scegliere il giusto gruppo di file e il giusto layout di file per il database
Scegliere lo schema di partizionamento e la strategia giusta per il database
Scegliere l'opzione e il livello di compressione giusti per il proprio database
Normalizzazione o denormalizzazione dello schema del database in base alle proprie esigenze.

Conclusione

SQL Server performance tuning è un processo complesso e continuo che richiede molte conoscenze ed esperienza. Tuttavia, seguendo alcune delle migliori pratiche e degli strumenti menzionati in questo blog post, è possibile migliorare notevolmente le prestazioni delle istanze di SQL Server. Ricordate di testare sempre le modifiche in un ambiente di sviluppo o di staging prima di applicarle alla produzione e di monitorare l'impatto delle modifiche sulle prestazioni del sistema. Buon tuning!

Documentazione

Database perf_neartime.docx (1,52 MB)

Database perf_neartime_red.docx (1,79 MB)

Query store results description.docx (359,29 KB)

Database perf_querystore.docx (3,95 MB)

SQL Server Performance Tuning è un processo complesso e minuzioso. Affidate questo lavoro a professionisti.

Sarò lieto di assistervi nell'ottimizzazione e nell'impostazione del database della vostra azienda..

Basta chiamare +41 (0)58 590 10 04 o contattateci in qualsiasi modo conveniente.

I nostri servizi per voi

Performance Tuning

Introducing SQL Server Performance Tuning

Accordo con l'utente