E’ dal 1999 quando ho cominciato a programmare in Engineering che utilizzo Microsoft Sql Server come motore di database.
Una scelta che mi è stata imposta dall’azienda in cui lavoravo, ma che poi ho continuato a seguire negli anni per la sua grande affidabilità.
Nulla in contrario a database open source come MySql di cui mi parlano tutti bene, ma ormai ho acquisito una certa esperienza e volevo condividerla con voi.
Una delle domande più frequenti che si fanno i programmatori, ma anche i clienti che dicono che un programma è lento è come velocizzare una query sql, come aumentare le performance di sql server, come monitorare ed ottimizzare le prestazioni delle query.
Adesso vi do alcuni suggerimenti che ho adottato negli anni e mi hanno portato ottimi risultati
Passa a Microsoft sql server per Linux
Molti sviluppatori Microsoft storceranno il naso per questa affermazione, anche io ero scettico. Ho preso un server Linux Ubuntu e gli ho montato la mia licenza sql server 2019 standard. Senza alcuna ottimizzazione, per fare un esempio, un planner piuttosto pieno di un albergo col mio gestionale alberghiero Hotelpedia è passato da un caricamento di circa tredici secondi a sette secondi e mezzo.
Linux non ha interfaccia grafica, gestisce diversamente la memoria e soprattutto i server linux costano molto meno: migrando la mia infrastruttura a linux ho migliorato le performance e risparmio circa 1500 euro l’anno di costi server cloud.
Non utilizzare i campi nvarchar se non è necessario
I campi nvarchar sono campi di tipo stringa a lunghezza variabile che occupano più memoria e sono più lenti da caricare, Bisogna usarli solo per i campi che richiedono caratteri speciali e stranieri. Con il tipo di campo varchar le query sono molto più veloci.
Utilizza lo schema quando fai le query
Molti quando fanno le query le semplificano scrivendo select cognome from clienti mentre invece se scrivessero anche lo schema, ad esempio select cognome from dbo.clienti sarebbero molto più veloci.
Non usare il carattere jolly * nelle query quando non serve
Quando fai una query se ti servono pochi campi mettili ad uno ad uno non usare la sintassi select * from clienti, la query diventa più lenta
Usa il SET NO COUNT ON nelle query
Se non hai bisogno di sapere quanti record stai selezionando usa la clausola SET NO COUNT ON prima di fare la query ad esempio in una stored procedure diventa tutto più veloce
Usa la clausola WITH (NOLOCK) nelle query
Se stai facendo query in sola lettura e non ti interessa che i dati che stai leggendo possano essere modificati in tempo reale da un altro utente usa nella query la sintassi WITH (NOLOCK) le performances della query sql server miglioreranno notevolmente, ad esempio select * from accessi (NOLOCK). In questo modo il software legge i dati senza alcun tipo di blocco.
Sostituisci nella query la IN con la EXISTS
Esiste in TSQL la clausola in che ci consente di prendere in una tabella tutti i dati di una query con un campo contenuto in una sottoquery. Ad esempio se volessi prendere tutte le prenotazioni di un hotel dove i clienti sono stranieri potrei scrivere (la cosa migliore è sempre fare un join) select dbo.cliente from prenotazioni where cliente in (select cliente from dbo.clienti where estero=1).
Per migliorare notevolmente il piano di esecuzione della query è sufficiente sostituire la clausola IN con la clausola EXISTS in questo modo
select dbo.cliente from prenotazioni where cliente EXISTS (select cliente from dbo.clienti where estero=1)
Il risultato è lo stesso ma la query è molto più veloce.
Utilizza sempre il livello di compatibilità più alto del database
Un errore che molti fanno è aggiornare il motore del database di sql server senza aggiornare anche il livello di compatibilità. In questo modo si perdono tante nuove caratteristiche ed ottimizzazioni ed il motore non sfrutta tutte le sue potenzialità sul nuovo database. La prima cosa da fare quando fate il restore di un database sql server su un nuovo server è portare il livello di compatibilità a quello più alto. Ovviamente dopo non potrete più tornare indietro, ossia ripristinare questo database sul vecchio server, ma sicuramente ne gioveranno le prestazioni.
Aggiorna le statistiche del database la sera
Un piano di manutenzione serale che consiglio per un server sql server è un aggiornamento light delle statistiche.
Basta che pianificate questa query e verrà eseguita in automatico su tutti i vostri database.
sp_MSforeachdb ‘use [?]; exec sp_updatestats’
Conclusioni
Ci sarebbero tante altre cose da fare e da dire sull’argomento come ad esempio non utilizzare la funzione di compressione dei database, cancellare gli indici non necessari.
Questi sono solo alcuni consigli semplici, con un minimo impatto e che vi porteranno dei risultati immediati.
Se volete continuare a seguirmi o per farmi domande potete mettere un mi piace alla mia pagina Facebook oppure alla mia pagina Instagram,
Buona ottimizzazione di sql server.
