Domande e risposte su SQLConfigurazione della memoria, profiling delle prestazioni, impostazione del fattore di riempimento e altro

A cura di Nancy Michell

Configurazione della memoria

Suggerimento: profiling più semplice

Ora è possibile mettere in correlazione PerfMon con SQL Server 2005 Profiler.

Durante l'esecuzione di Performance Monitor sul computer SQL Server è probabile che si siano notati picchi di utilizzo della CPU, un consumo eccessivo della memoria o una riduzione delle prestazioni globali e che non si sia riusciti a capire la causa di queste anomalie. Prima del rilascio di SQL Server 2005, una situazione di questo tipo prevedeva l'utilizzo di Profiler per acquisire una traccia, l'analisi dei sysprocesses in Enterprise Manager e infine acquisizione dei registri di Performance Monitor, che richiedeva ovviamente l'avvio di PerfMon. Dopo aver utilizzato tutti questi strumenti, era comunque necessario riconciliare manualmente gli eventi tra gli strumenti per individuare la causa della riduzione delle prestazioni. Il processo richiedeva l'analisi di ogni singolo registro. Non era un'operazione divertente, ma era necessaria per individuare l'origine dei problemi di prestazioni.

Con SQL Server 2005, è ancora necessario acquisire una traccia ed esaminare i registri PerfMon, ma Profiler ora consente di correlare i registri. È possibile scorrere le istruzioni T-SQL e in Profiler verrà automaticamente visualizzata una rappresentazione grafica degli eventi che si sono verificati. Se si fa clic sull'interfaccia utente di Performance Monitor in Profiler, sarà possibile accedere all'istruzione correlata al timestamp. Questo consente di ridurre notevolmente i tempi per la risoluzione dei problemi correlati all'ambiente SQL Server.

Di seguito è riportata la procedura per correlare i registri PerfMon a Profiler:

1. Avviare Performance Monitor e iniziare ad acquisire informazioni dal server di database.
2. Creare un nuovo registro contatori in Avvisi e registri di prestazioni, quindi immettere un nome per il nuovo registro.
3. Aggiungere i nuovi contatori, ad esempio % Tempo processore. È possibile impostare la registrazione in modo che venga avviata manualmente o automaticamente in base a una pianificazione utilizzando la scheda Pianificazione.
4. Fare clic su OK e, se si è scelto di impostare l'opzione manuale, avviare la registrazione.
5. Configurare una traccia in SQL Server tramite Profiler. A tal fine, scegliere Nuova traccia dal menu File. Includere StartTime ed EndTime nella traccia, quindi assegnare un nome alla traccia e salvarla in un file. Infine, è necessario avviare il server per simulare alcune attività di transazione, quindi interrompere l'acquisizione dei dati di Performance Monitor e Profiler.
6. In Profiler, scegliere Importa dati prestazioni dal menu File. Selezionare il percorso in cui si è memorizzato il registro PerfMon e selezionare File | Apri | Traccia. Infine, selezionare il percorso in cui si è memorizzata la traccia di Profiler.

Al termine, si scoprirà quanto sia semplice determinare l'impatto che istruzioni SQL specifiche hanno avuto sui tempi di elaborazione.

D Sto cercando di scoprire quale sia la migliore configurazione della memoria per i miei computer SQL Server™. L'amministratore che mi ha preceduto ha impostato il file Boot.ini per ciascun computer con 12 GB di RAM su un cluster a due nodi SQL Server 2000, come descritto di seguito: Yes /PAE NO /3GB (No AWE set for SQL Server). Con 12 GB di RAM disponibili, è necessario rimuovere l'opzione /3GB in Boot.ini, attivare AWE e dedicare a SQL Server circa 10 GB dei 12 GB? Nei computer che eseguono SQL Server non sono disponibili altre applicazioni e pertanto non è necessario aggiungere ulteriore memoria.

R Sì, è consigliabile attivare la funzionalità AWE (Address Windowing Extensions) e preallocare un limite superiore di RAM per SQL Server; 10 GB sembrano sufficienti su un computer SQL Server dedicato con 12 GB di RAM. La preallocazione è valida solo per SQL Server 2000. A partire da SQL Server 2005, l'utilizzo di AWE non è più statico e può essere modificato in tempo reale. È da sempre vivo un forte dibattito tra gli esperti del settore circa la necessità di utilizzare contemporaneamente le opzioni /3GB e /PAE o solo l'opzione /PAE obbligatoria. Sebbene in realtà sia necessario solo impostare l'opzione /PAE e attivare la funzionalità AWE, è consigliabile utilizzare entrambe le opzioni; in tal caso, tuttavia, è necessario prendere in considerazione alcuni fattori.

Se si prevede di utilizzare l'opzione /3GB, occorre determinare soprattutto se l'opzione è necessaria nella situazione specifica. La memoria nell'area MemToLeave compresa nei primi 2 o 3 GB dello spazio degli indirizzi virtuali è esaurita? Se si attiva l'opzione, la memoria destinata al sistema operativo non è sufficiente? Per ulteriori informazioni , visitare il sito support.microsoft.com/kb/316739. Se si utilizza un cluster, è possibile impostare /3GB su un solo nodo e non sull'altro. In tal modo, quando si esegue il processo di test con l'opzione /3GB e si verificano dei problemi, è possibile eseguire rapidamente il failover dell'istanza sull'altro nodo. È bene ricordare che, se si dispone di più di 16 GB di RAM, l'opzione /3GB non è supportata.

L'utilizzo dell'opzione /3GB determina un incremento del 50% dello spazio degli indirizzi virtuali (VAS, Virtual Address Space) e questo rappresenta un enorme vantaggio, non solo per la cache dati, ma anche per le applicazioni che fanno un uso estensivo della memoria disponibile nello spazio di indirizzi virtuali. Per fortuna, i server a 64 bit, IA64 e x64, eliminano questo fattore di confusione. Le considerazioni relative all'insufficienza della memoria destinata al sistema operativo non sono applicabili se il computer è dedicato a SQL Server. Anche una base di 2 GB per il sistema operativo deve essere considerata eccessiva; se questo server è dedicato a SQL Server ed esegue solo i servizi standard minimi del sistema operativo, sul server saranno disponibili circa 1,3 GB di memoria e si potrà pertanto destinare anche questa quantità aggiuntiva a SQL Server. Iniziando con 10 GB, utilizzare PerfMon per monitorare la memoria disponibile per un lungo periodo di tempo in modo da controllare per quanto tempo resta inattiva, quindi modificarla di conseguenza. Se si esegue l'overcommit della memoria in SQL Server 2000, si verificherà uno swapping, dal momento che la funzionalità AWE non è tanto dinamica quanto in SQL Server 2005. Pertanto la chiave per decidere se utilizzare o meno /3GB consiste nel testare l'opzione nello specifico ambiente in uso.

Nomi di istanza per la replica

D Ora è possibile utilizzare l'indirizzo IP del server in uso in Replica di SQL Server 2005 per indicare quale istanza replicare? In SQL Server 2000, in base all'articolo "Come eseguire la replica tra computer che eseguono SQL Server in domini non trusted o su Internet" (support.microsoft.com/kb/321822), l'esecuzione di questa operazione può causare la generazione di errori, ma non so se questo accade ancora.

R Quando si specificano istanze del server da includere nel processo di replica, è necessario fornire il nome dell'istanza registrato di SQL Server. Ad esempio, è necessario utilizzare il nome dell'istanza di SQL Server quando si specifica il parametro Publisher o Subscriber per le stored procedure di replica o le impostazioni di connessione per gli agenti di replica nella riga di comando. Se il nome di rete per l'istanza di SQL Server differisce dal nome dell'istanza registrato, le connessioni di replica in base agli agenti non riusciranno.

Se il nome di rete dell'istanza e il nome dell'istanza di SQL Server differiscono, valutare l'opportunità di aggiungere il nome dell'istanza di SQL Server come nome di rete valido. Un metodo per impostare un nome di rete alternativo consiste nell'aggiungerlo al file degli host locale. Il file degli host locale risiede per impostazione predefinita in WINDOWS\system32\drivers\etc o WINNT\system32\drivers\etc. Se, ad esempio, il nome del computer è comp1, l'indirizzo IP del computer è 10.193.17.129 e il nome dell'istanza è inst1/instname, aggiungere la seguente voce al file degli host:

10.193.17.129 inst1

SQL Server Integration Services

D Sto installando un cluster attivo/attivo per SQL Server 2005 (Enterprise Edition a 64 bit con due server) e dispongo di un numero totale di quattro istanze di SQL Server 2005. È necessario utilizzare SQL Server Integration Services (SSIS) per tutte le istanze. Come si esegue il clustering di SSIS e qual è l'effetto di questa operazione sui piani di manutenzione?

R Sebbene sia possibile eseguire il clustering del servizio SSIS, questa operazione non è necessaria e la relativa esecuzione può determinare l'insorgenza di una serie di problemi, tra cui la mancanza di supporto per la delega (visitare il sito msdn2.microsoft.com/aa337083) e per le istanze multiple, ovvero sarà possibile eseguire su un nodo una sola istanza per volta.

In precedenza, per eseguire la Creazione guidata piano di manutenzione non era necessario che il servizio SSIS fosse in esecuzione ma solo che venisse installato. Tuttavia, questo non avviene in SQL Server 2005 SP1. Se il servizio SSIS non è in esecuzione, i piani di manutenzione possono essere eseguiti da SQL Server Agent.

Anziché eseguire il clustering di SSIS, eseguire questa applicazione come servizio autonomo e modificare il file MsDtsSrvr.ini.xml in modo che faccia riferimento a tutte le istanze in esecuzione. In tal modo, sarà possibile gestire i pacchetti da qualsiasi nodo e fornire la disponibilità elevata richiesta dalla maggior parte dei clienti senza dover far fronte a tutti i problemi associati al clustering del servizio.

Per ulteriori informazioni sulla creazione non riuscita dei piani di manutenzione, consultare l'articolo della Knowledge Base all'indirizzo support.microsoft.com/kb/909036.

Tempi di esecuzione anomali

D Durante l'esecuzione del test del carico sul computer SQL Server 2005 SP1, SQL Server Profiler ha registrato molti valori negativi relativi al tempo di esecuzione delle stored procedure (SP, Stored Procedure) e in alcuni casi non esisteva alcuna corrispondenza tra il tempo di esecuzione SP e il risultato derivante dalla sottrazione dell'ora di inizio dall'ora di fine.

D Molti fattori possono influire sui report relativi al tempo di esecuzione SP e ad altri tempi di prestazioni in SQL Server Profiler. In SQL Server 2005 vengono utilizzati i millisecondi per conteggiare il tempo di esecuzione e, se si utilizzano tecnologie che modificano l'unità di misura, si otterranno report incoerenti e tempi di esecuzione piuttosto anomali.

Se, ad esempio, si utilizzano combinazioni per il risparmio energia, stepping di CPU o la tecnologia AMD Cool 'n Quiet si ottiene una modifica delle frequenze della CPU che, quindi, non corrisponderanno al risultato previsto in SQL Server Profiler durante il calcolo del tempo di esecuzione.

È disponibile un articolo della Knowledge Base all'indirizzo support.microsoft.com/kb/931279 in cui vengono illustrati i sintomi, una serie di cause e alcuni rimedi.

Suggerimento: controllare il fattore di riempimento

Si supponga di versare dell'acqua in un bicchiere completamento pieno. Cosa succede? L'acqua fuoriesce.

Lo stesso accade in SQL Server. Ogni volta che una nuova riga viene aggiunta a una pagina di indice completa, in SQL Server circa metà delle righe viene spostata in una nuova pagina per fare posto alle nuove. Questo processo è noto come divisione di pagina. Le divisioni di pagina consentono di creare spazio per nuovi record, ma sono notevolmente dispendiose in termini di tempo e risorse. Possono inoltre causare la frammentazione che influisce negativamente sulle operazioni di I/O. Come è possibile evitarle?

Per evitare situazioni di questo tipo, è necessario determinare in modo proattivo il valore del fattore di riempimento. Quando un indice viene creato o ricreato, il valore del fattore di riempimento determina la percentuale di spazio in ciascuna pagina al livello foglia che deve essere occupata dai dati, riservando lo spazio rimanente all'espansione futura. Se, ad esempio, si configura un valore del fattore di riempimento pari a 60, il 40% di ciascuna pagina al livello foglia verrà lasciato vuoto per consentire l'espansione dell'indice man mano che si aggiungono dati alla tabella sottostante.

Il fattore di riempimento predefinito è sempre impostato su 0, un valore appropriato per la maggior parte delle situazioni. In sostanza, un fattore di riempimento pari a 0 indica che la capacità del livello foglia è quasi esaurita, ma parte dello spazio viene riservato per l'inserimento di almeno una riga di indice aggiuntiva (i valori del fattore di riempimento 0 e 100 sono simili).

È possibile impostare il valore del fattore di riempimento per singoli indici durante un'istruzione CREATE INDEX o ALTER INDEX o è possibile configurare questo valore direttamente a livello di server in modo che tutti i nuovi indici creati utilizzino il valore predefinito.

Nell'esempio riportato di seguito il valore del fattore di riempimento viene impostato a livello di server su 70%, il che indica che all'espansione futura viene riservato il 30% dello spazio disponibile. Ovviamente, è necessario testare con attenzione questa opzione prima di implementarla in un ambiente di produzione.

USE Master;
GO
SP_Configure 'show advanced options',1;
GO
SP_Configure 'Fill Factor', 70;
GO
--You must restart SQL Server Engine for changes to take effect.

Se invece si desidera configurare il fattore di riempimento a livello di singolo indice? Si supponga di creare la tabella riportata di seguito e che si desideri creare un indice univoco nella colonna Col_A con un valore del fattore di riempimento pari a 70. In tal caso, si utilizzerà un comando simile al seguente:

--Create an Item table
USE Item_DB;
GO
CREATE TABLE ITEM (Col_A Varchar(100),Col_b Varchar(200));
GO;

--Create a unique index on colum Col_A of Item table with a Fill Factor value of 70
CREATE UNIQUE INDEX AK_Index ON Item (Col_A)
WITH (FillFactor = 70);
GO

Come è possibile identificare il fattore di riempimento per ciascun indice? È possibile eseguire una query in sys.Indexes per ottenere il valore del fattore di riempimento per tutti gli indici di un database, come illustrato di seguito:

USE Item_DB;
GO
SELECT Fill_Factor FROM Sys.Indexes WHERE Object_id=object_id('item') AND name IS NOT NULL;
GO

© 2008 Microsoft Corporation e CMP Media, LLC. Tutti i diritti riservati. È vietata la riproduzione completa o parziale senza autorizzazione.