Come configurare Sql Server

Che cos’è sql server?

Quando sviluppiamo una applicazione desktop mobile o una web application, abbiamo bisogno di un database sul quale appoggiarci, ossia un contenitore di tutti i dati della applicazione.

Sql server è un motore di database professionale sviluppato da Microsoft giunto adesso alla versione 2019, che noi stessi utilizziamo come database server per le nostre applicazioni.

Installazione di sql server

Quando abbiamo deciso che sql server debba essere il nostro motore di database, il primo consiglio che vi diamo è prendere un server dedicato, preferibilmente una macchina reale e non virtuale, con almeno 16 giga di ram, che sia ridondata e con dischi RAID 1.

Altra cosa utile sarebbe avere dischi SSD con il sistema operativo su un disco ed sql server su un disco rigido separato con formattazione dei cluster a 64k, mentre per il disco rigido si può usare la dimensione dei cluster di default di 4k.

Quando si installa sql server, le impostazioni di base spesso non consentono di ottenere le prestazioni migliori perchè la configurazione di default nasce per le macchine con prestazioni basse in maniera tale che l’installazione di sql server non pregiudichi il funzionamento del server sul quale viene installato.

In questo articolo vogliamo darvi alcuni semplici consigli per migliorare le prestazioni della vostra istanza di sql server per un miglior accesso delle applicazioni ai dati, rendendo le query più performanti ed i programmi più veloci.

Dopo aver installato sql server clicchiamo col tasto destro sul nome dell’istanza e clicchiamo sulla voce di menù proprietà col tasto sinistro. Compariranno una serie di opzioni, quella che ci interessa in particolare è la linguetta opzioni avanzate:

Vediamo adesso quali sono i parametri più importanti da modificare per aumentare la velocità di Sql server.

Massimo grado di parallelismo

Il massimo grado di parallelismo influenza il numero massimo di processori che vengono utilizzati per l’esecuzione delle query. Il valore di default è zero che significa che la macchina utilizza tutta la massima potenza disponibile per l’esecuzione delle query.

Questo è un parametro errato in quanto ricordiamoci che innanzitutto la macchina potrebbe essere dedicata anche ad altri scopi, ma poi anche che un server windows ha bisogno di una certa potenza operativa di base per funzionare correttamente sia in termini di processore che di memoria disponibile.

Come fare a capire quale è il valore ideale del MAXDOP ( acronimo diMax Degree of Parallelism)?

Aprendo la finestra nuova query possiamo eseguire questa stored procedure che ci consiglia il valore da inserire nella finestra avanzate di sql server per il massimo grado di parallelismo:

DECLARE @CoreCount int;
DECLARE @NumaNodes int;
SET @CoreCount = (SELECT i.cpu_count from sys.dm_os_sys_info i);
SET @NumaNodes = (
    SELECT MAX(c.memory_node_id) + 1 
    FROM sys.dm_os_memory_clerks c 
    WHERE memory_node_id < 64
    );
IF @CoreCount > 4 /* If less than 5 cores, don't bother. */
BEGIN
    DECLARE @MaxDOP int;
    /* 3/4 of Total Cores in Machine */
    SET @MaxDOP = @CoreCount * 0.75; 
    /* if @MaxDOP is greater than the per NUMA node
       Core Count, set @MaxDOP = per NUMA node core count
    */
    IF @MaxDOP > (@CoreCount / @NumaNodes) 
        SET @MaxDOP = (@CoreCount / @NumaNodes) * 0.75;
    /*
        Reduce @MaxDOP to an even number 
    */
    SET @MaxDOP = @MaxDOP - (@MaxDOP % 2);
    /* Cap MAXDOP at 8, according to Microsoft */
    IF @MaxDOP > 8 SET @MaxDOP = 8;
    PRINT 'Suggested MAXDOP = ' + CAST(@MaxDOP as varchar(max));
END
ELSE
BEGIN
    PRINT 'Suggested MAXDOP = 0 since you have less than 4 cores total.';
    PRINT 'This is the default setting, you likely do not need to do';
    PRINT 'anything.';
END

Soglia costo per parallelismo

La soglia costo per parallelismo è il tempo di attesa in secondi superato il quale il query optimizer di sql server considera l’esecuzione di una query in parallelo.

Poichè alcune query sono molto più pesanti delle altre conviene alzare la soglia dei 5 secondi predefinita per non sprecare risorse.

Il valore da noi consigliato in secondi per la soglia del parallelismo è di 50 secondi diciamo un valore oscillante tra i 30 e i 70 secondi.

Questo parametro come il precedente può essere cambiato a caldo senza riavviare il servizio sql server. Per questo motivo potete testare varie possibili configurazioni tenendo aperta la finestra gestione attività e guardando come vengono sollecitati il processore e la memoria ram del server.

Risparmio energetico del server

Anche se il vostro server è una macchina virtuale accertatevi che le impostazioni risparmio energia della macchina siano sempre abilitate per le massime prestazioni del processore.

I flag di traccia

Per il debug di alcune situazioni interne, sql server utilizza i cosiddetti flag di traccia, alcuni dei quali sono dannosi per le prestazioni in quanto sono utili solo per fare un debug di problemi sul server, e molti non sono documentati.

Per approfondire l’argomento Microsoft ha messo a disposizione l’elenco ufficiale dei flag di traccia presenti in sql server.

Dimensioni pacchetto di rete

Le dimensioni del pacchetto di rete indicano la dimensione dei blocchi di dati che vengono trasderiti tra client e server ed hanno come opzione predefinita 4096 byte.

Molti erroneamente impostano questo valore al valore massimo che è 32767 byte pensando che in questo modo il server risponda più veloemente, ma questo causa invece problemi per la ricomposizione dei pacchetti provocando invece rallentamenti.

Per capire come impostare questo valore c’è un trucco molto semplice.

La dimensione massima del pacchetto di rete deve essere uguale alla MTU che è l’acronimo di Maximum transfer unit ossia la capacità massima di trasferimento dei pacchetti della rete.

Per trovare questo valore basta fare un semplice ping da un client al nostro server inserendo l’indirizzo ip del server stesso.

Dobbiamo utilizzare l’opzione -l che ci consente di controllare la perdita dei pacchetti partendo da un valore prestabilito esempio 1300 seguito da un -f a scendere finché notiamo che non si ha più perdita dei pacchetti: quello è il valore che dobbiamo inserire nella configurazione di sql server.

Supponiamo che il nostro server abbia indirizzo 80.211.147.75 il comando da utilizzare nel prompt dei comandi sul nostro client è

ping 80.211.147.75 -l 1300 -f
se nel risultato troviamo qualche pacchetto perso allora riproviamo con
ping 80.211.147.75 -l 1299 -f
questo finchè non abbiamo pacchetti persi zero.

Un altro sistema ancora più semplice per controllare l’ MTU è utilizzare un servizio on line per calcolare la dimensione massima dell’MTU ed impostarla velocemente su sql server.

La scelta dell’hosting

La scelta dell’hosting è anch’esso un parametro molto importante da valutare.

Affidarsi ad un servizio di hosting efficiente assicura le massime prestazioni e la business continuity del nostro server sql.

Abbiamo fatto molte ricerche sul mercato italiano ed internazionale alla ricerca del miglior hosting per sql server.

Noi personalmente ci siamo affidati alla azienda Flamenetworks con cui ci troviamo molto bene e con la quale abbiamo ottenuto grossi miglioramenti generali di prestazioni affidabilità e sicurezza dei nostri software gestionali.