SQL: domande & risposteNuova creazione di indici, lunghezza della coda del disco e altro

A cura di Nancy Michell

D Come vengono ricreati gli indici di SQL Server? Mi serve sapere perché DBCC DBREINDEX utilizza tutto lo spazio sul disco e perché lo spazio non viene liberato se il comando ha esito negativo. La dimensione del database è di 90 GB e la tabella più grande è di 70 GB.

Quando eseguo DBCC DBREINDEX, lasciando libero il 10 percento dello spazio, viene utilizzato tutto lo spazio disponibile sul disco e il comando ha esito negativo. Mentre inizialmente il file di database ha una dimensione di 90 GB, quando il comando DBCC ha esito negativo, la dimensione del file di database aumenta a 160 GB e i 70 GB aggiuntivi non vengono mai liberati. È quindi necessario ridurre manualmente le dimensioni del database in più riprese per liberare questo spazio.

Quando il comando ha esito positivo, anche la dimensione del file di database è di 160 GB, ma lo spazio viene automaticamente liberato poche ore dopo la compattazione automatica. È corretto eseguire una deframmentazione invece di DBREINDEX per utilizzare meno spazio? Non sono presenti indici di cluster e utilizzo la modalità di ripristino semplice.

R Al livello più semplice, gli indici vengono ricreati generando una nuova copia di un indice che viene poi eliminata. Ciò significa che per un breve periodo di tempo esistono essenzialmente due copie di uno stesso indice. La creazione del nuovo indice potrebbe richiedere lo stesso spazio del file di database quanto quello dell'indice originale e se la nuova creazione richiede un'operazione di ordinamento è necessario ulteriore spazio, pari al 20 percento della dimensione dell'indice.

Nel peggiore dei casi, quindi, la ricostruzione di un indice richiede circa 1,2 volte lo spazio del vecchio indice. Se il file di database non dispone di spazio sufficiente, il file aumenterà di dimensione man mano che l'operazione procede. Se la funzionalità di aumento automatico di dimensione non è abilitata, oppure se non c'è spazio sufficiente sul volume del disco, è possibile che non sia disponibile spazio libero sufficiente e che l'operazione di ricostruzione non riesca.

A prescindere dalla riuscita, al termine dell'operazione di ricostruzione lo spazio aggiuntivo allocato al file di database non viene liberato. Si prevede che lo spazio sarà utilizzato per le normali operazioni del database.

L'esecuzione di una compattazione (manuale o automatica) genererà quasi sicuramente una frammentazione dell'indice a causa della modalità con cui funziona l'algoritmo. Per ulteriori dettagli, vedere Motore di archiviazione di SQL Server. La compattazione automatica può essere particolarmente dannosa per le prestazioni se il database ha bisogno di spazio libero per le normali operazioni, perché si potrebbe entrare in un circolo vizioso che vede l'alternarsi delle operazioni di aumento automatico di dimensioni-compattazione-aumento automatico di dimensioni-compattazione potenzialmente devastante per la frammentazione e le prestazioni.

L'utilizzo di DBCC INDEXDEFRAG (o ALTER INDEX ... REORGANIZE in SQL Server™ 2005) consente di utilizzare pochissimo spazio aggiuntivo del file di database, ma può richiedere molto più tempo e generare molte più registrazioni di transazioni rispetto alla ricostruzione di un indice. DBCC INDEXDEFRAG viene sempre registrato completamente, indipendentemente dalla modalità di ripristino in uso, mentre nella modalità di ripristino semplice la ricostruzione di un indice sarà con registrazione minima delle transazioni di massa. Esistono diversi vantaggi e svantaggi per ognuna delle soluzioni e sono descritte con maggiori dettagli nel white paper Procedure consigliate per la deframmentazione dell'indice di SQL Server.

Prima di decidere come correggere la frammentazione occorre dapprima decidere se davvero è necessario farlo. A seconda del tipo di operazioni per cui generalmente si utilizza l'indice, la frammentazione può non avere effetti sulle prestazioni, pertanto correggerla sarebbe uno spreco di risorse. Il white paper è molto dettagliato.

In conclusione: assicurarsi di aver scelto il metodo più appropriato per la rimozione della frammentazione per il proprio ambiente e che tale operazione supporti le prestazioni delle query.

D Ho configurato correttamente il mirroring del database tra due istanze SQL Server 2005. L'applicazione si connette a SQL Server utilizzando un account di accesso di SQL Server ed è realizzata utilizzando ADO e SQL Native Client. La stringa e le impostazioni di connessione specificano le informazioni corrette, compreso il partner di failover appropriato. Gli stessi account di accesso esistenti sul server principale sono stati creati anche sul server mirror. Quando si verifica un errore del database, il server mirror assume il ruolo principale e tutto sembra funzionare correttamente sull'istanza di SQL Server. È anche possibile collegarsi al server mirror utilizzando un account di accesso di Windows®. Tuttavia, la riconnessione dell'applicazione non avviene correttamente e segnala il seguente errore:

Cannot open database "<db name>" requested by the login. The login failed. 

Sembra che l'account di accesso non sia associato a un utente nel nuovo database principale (in origine, il mirror). Dopo aver eseguito sp_change_users_login per sincronizzare gli utenti e gli account di accesso per il database, viene visualizzato un messaggio che indica che sono stati corretti più utenti isolati (orfani). In seguito, l'applicazione si ricollega correttamente al nuovo server principale. A ogni tentativo di failover viene rilevato lo stesso comportamento, ossia la perdita dell'associazione tra utente e account di accesso.

Esiste un modo per configurare le impostazioni di mirroring in modo da evitare questo problema?

R Sì. Il problema è dovuto al fatto che gli identificatori di protezione (SID) per gli account di accesso di SQL Server su ogni server non corrispondono, anche se i nomi per gli account di accesso sono gli stessi. Questo non è un problema per gli account di accesso Windows/utente/gruppo dominio perché i SID di questi account di accesso vengono creati in base al SID di dominio per il singolo utente/gruppo e saranno quindi gli stessi per lo stesso utente/gruppo, e non dipende dall'SQL Server al quale viene aggiunto quell'utente/gruppo.

Per eliminare la necessità della sincronizzazione lsp_change_users_login è necessario creare gli account di accesso di SQL Server sul server mirror non solo con lo stesso nome, ma anche con lo stesso SID del server principale. A tal fine, si utilizza la specifica SID nell'istruzione CREATE LOGIN durante la creazione di account di accesso sul server mirror, come indicato di seguito:

CREATE LOGIN <loginname> WITH PASSWORD = <password>, 
SID = <sid for 
same login on principal server>,...

È possibile ripristinare il SID per ogni account di accesso dal server principale tramite una query su una vista del catalogo sys.sql_logins. Un esempio di query che genererà un'istruzione CREATE LOGIN effettiva per ogni account di accesso SQL Server/Windows su un determinato server viene illustrato nella Figura 1.

Figure 1 Generare un'istruzione CREATE LOGIN

select 'create login [' + p.name + '] ' + 
case when p.type in('U','G') then 'from windows ' else '' end + 
'with ' +
case when p.type = 'S' then 'password = ' + master.sys.fn_varbintohexstr(l.password_hash) + 
' hashed, ' + 'sid = ' + master.sys.fn_varbintohexstr(l.sid) + ', check_expiration = ' +
case when l.is_policy_checked > 0 then 'ON, ' else 'OFF, ' end + 'check_policy = ' + 
case when l.is_expiration_checked > 0 then 'ON, ' else 'OFF, ' end +
case when l.credential_id > 0 then 'credential = ' + c.name + ', ' else '' end 
else '' end +
'default_database = ' + p.default_database_name +
case when len(p.default_language_name) > 0 then ', default_language = ' + p.default_language_name else '' end
from sys.server_principals p
left join sys.sql_logins l
on p.principal_id = l.principal_id
left join sys.credentials c
on l.credential_id = c.credential_id
where p.type in('S','U','G')
and p.name <> 'sa'

D Quale dovrebbe essere la lunghezza media della coda del disco? Ad esempio, con 20 assi fisici separati dell'architettura Storage Area Network (SAN) con configurazione RAID 01, come si calcola la lunghezza media della coda del disco? La media della lunghezza della coda del disco è 20 o 2?

R Prima di sprecare tempo per la lunghezza media della coda del disco in un ambiente SAN, è bene considerare la latenza del disco. La risposta, in realtà, dipende da ciò che si desidera scoprire. Tra breve vedremo il perché.

La spiegazione di questo contatore (da Perfmon) è la seguente: "La lunghezza media della coda del disco è il numero medio di richieste di lettura e scrittura che sono state accodate per il disco scelto durante l'intervallo di campionamento". Si tratta di un contatore disco fisico o logico, pertanto il numero che si ottiene dipende dalla modalità con cui l'archiviazione sottostante viene presentata al sistema operativo.

Si prenda in considerazione il caso descritto. La presenza di 20 assi in una configurazione RAID 01 significa che vengono effettuati lo striping e il mirroring (oppure il mirroring e lo striping, a seconda di come viene letto 01 o 10). L'aspetto più importante della matrice di archiviazione è che sono presenti 10 assi in un set di striping.

Mancano però delle informazioni fondamentali, ossia la dimensione di striping, la dimensione delle scritture e il tipo di I/O che si sta inviando (lettura, scrittura, sequenziale o casuale).

Trascurando per il momento le informazioni mancanti, se la lunghezza media della coda del disco indica 10, significa che il sistema operativo ha accodato 10 I/O alla matrice del disco. In teoria, potrebbe esistere un I/O per ciascuno dei 10 set di mirror nello striping o 10 I/O tutti sullo stesso disco. Non c'è modo di sapere qual è la situazione.

Qui entrano in gioco le informazioni mancanti. Si supponga che la dimensione di striping sia di 64 KB, la dimensione di scrittura di 8 K e che si desideri eseguire un unico blocco di scritture sequenziali. Questo è uno scenario tipico per l'attività di archiviazione di SQL Server. In questo caso, ci sono molte probabilità che otto dei 10 I/O vadano sul primo disco e i due I/O rimanenti sul disco successivo. Quindi se si tenta di individuare la lunghezza della coda del disco per ciascun disco in questo scenario, il risultato è 8 per il primo disco, 2 per il secondo disco e 0 per i restanti otto dischi della matrice.

Si modifichi ora lo scenario con una dimensione teorica di striping di 8 KB, con blocchi di scrittura di 64 KB e una lunghezza della coda del disco pari a 10. In questo caso ogni blocco da 64 KB viene suddiviso su 8 dischi, quindi un I/O è scritto su 8 dischi e i 10 I/O accodati vengono distribuiti su 80 scritture su disco su tutti i 10 dischi della matrice. Se si tenta di calcolare la lunghezza della coda del disco per ogni disco nella matrice, il risultato è 8 per ogni disco nella matrice.

Ma siamo realistici e aggiungiamo un altro livello di incertezza allo scenario. Nella maggior parte dei casi, l'unità di archiviazione SAN si connette al server utilizzando uno o più HBA nel server, diverse fibre ottiche per collegare HBA a SAN, diverse porte sul front-end della SAN e probabilmente qualche tipo di modulo switch fiber nell'infrastruttura tra il server e la SAN. Successivamente, passiamo all'architettura interna dei bus nella SAN e alla modalità con cui i dischi vengono connessi alle porte anteriori della SAN.

Qualsiasi accodamento segnalato in Perfmon può essere un sintomo di elevata latenza o accodamento in uno qualunque di questi punti tra la posizione in cui il sistema operativo misura la lunghezza della coda del disco e la superficie dei dischi. Ecco perché si dovrebbe prendere in considerazione la latenza e basare le decisioni su quel contatore piuttosto che sulla lunghezza media della coda del disco.

D Utilizzando la replica transazionale e sapendo che molte righe sono state modificate manualmente nella tabella del sottoscrittore è normale riscontrare degli errori quando il server di pubblicazione tenta di aggiornare una riga che non esiste più nel sottoscrittore.

Vorrei sapere se esiste un modo, attraverso la replica, per reinizializzare la tabella dal server di pubblicazione invece di applicare nuovamente uno snapshot completo. La funzione tablediff sembra adatta allo scopo ma è necessario capire come interagisce con la replica.

Ad esempio, tablediff realizza uno snapshot della tabella del server di pubblicazione e lo confronta con l'equivalente della sottoscrizione? È necessario interrompere la replica per utilizzare l'utilità tablediff e garantire l'uniformità dei dati? Che altro è necessario sapere?

R Per prima cosa, tablediff non individua uno snapshot della tabella del server di pubblicazione o del sottoscrittore. Se si considera lo scenario descritto, esistono alcune opzioni da prendere in considerazione.

La prima è quella che consente di arrestare temporaneamente la replica ed eseguire l'utilità. Se si vuole evitare che gli utenti possano modificare i dati è possibile utilizzare i parametri -sourcelocked e -destinationlocked, che creano un blocco esclusivo su entrambe le tabelle mentre l'utilità è in esecuzione. Se questa soluzione non è percorribile, un'altra opzione consiste nel prendere in considerazione i parametri -rc e -ri lasciando la replica in esecuzione. In questo caso, tablediff viene eseguita una volta, successivamente viene eseguita ogni volta che si rilevano errori, eliminando così gli errori dovuti ai ritardi di propagazione della replica. È necessario ricordare, tuttavia, che con questa opzione, in base al ritardo di replica, non è detto che vengano individuate necessariamente tutte le righe modificate nel sottoscrittore.

Si ringraziano i seguenti professionisti IT di Microsoft per la propria esperienza tecnica: Sunil Agarwal, Chad Boyd, David Browne, Gilles Comeau, Emmanuel Dreux, Amanda Foote, Matt Hollingsworth, Paul Mestemaker, Uttam Parui, Paul Randal, Dennis Tighe e Steven Wort.

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