SQL: domande e &risposteIndividuazione di blocchi, query di grandi dimensioni, statistiche di I/O e altro ancora

A cura di Nancy Michell

Scarica il codice per questo articolo: SQLQandA2007_08.exe (151KB)

Domanda: ho bisogno di vedere i nomi degli oggetti in tutti i database che contengono dei blocchi. Come è possibile eseguire questa operazione?

Risposta: in SQL Server™ 2000, è possibile eseguire una query sulla tabella di sistema syslocks in Master oppure eseguire il comando sp_lock per ottenere le informazioni di blocco correnti, quali:

SELECT * FROM master..syslocks
EXEC sp_lock

Tuttavia, si supponga di voler convertire l'oggetto effettivo NAMES da ObjID dell'elemento OUTPUT della stored procedure sp_lock (o dalla colonna ID della tabella master...syslocks).

In SQL Server 2005 SP1 e precedente, la funzione OBJECT_NAME consentiva di passare solo un parametro: object_id. Quindi, per ottenere il nome dell'oggetto in modo corretto, bisognava lavorare nel database CURRENT prima di eseguire OBJECT_NAME. Questo rendeva difficile il monitoraggio dei blocchi correnti, poiché bisognava scrivere codice specifico per ogni database per poter ottenere OBJECT_NAME.

USE DBNAME  
SELECT OBJECT_NAME(object_id)

SQL Server 2005 SP2 ha perfezionato questo metodo aggiungendo un secondo parametro, database_id. Questo nuovo parametro consente di richiedere i nomi degli oggetti indipendentemente dal database CURRENT a cui si è connessi.

OBJECT_NAME ( object_id [, database_id ] )

Ora è possibile eseguire query in dm_tran_locks e recuperare il nome dell'oggetto per ogni database, come questo:

SELECT
DB_NAME(resource_database_id),
OBJECT_NAME(resource_associated_entity_id, resource_database_id)
FROM sys.dm_tran_locks
WHERE resource_type='OBJECT'

Notare, tuttavia, che questo metodo funziona solo in SQL Server 2005 SP2 e versioni successive. Se questo metodo viene eseguito con le versioni precedenti di SQL Server 2005, verrà restituito questo errore:

Msg 174, Level 15, State 1, Line 1
The object_name function requires 1 argument(s).

Un altro esempio, illustrato nella figura 1, utilizza sys.dm_exec_sessions per ottenere informazioni sugli ID di processo del servizio (SPIDS) interessati dai blocchi. Per ulteriori informazioni, vedere la documentazione in linea di SQL Server all'indirizzo technet.microsoft.com/library/ms130214(sql.90).aspx.

Figure 1 Individuazione degli ID del processo server

SELECT
DB_NAME(resource_database_id) as DBName,
OBJECT_NAME(resource_associated_entity_id, resource_database_id) AS ObjectName,
request_mode,
request_type,
request_session_id,
es.host_name,
es.login_name,
es.login_time
FROM
sys.dm_tran_locks tl
INNER JOIN sys.dm_exec_sessions es
ON tl.request_session_id=es.session_id
WHERE resource_type='OBJECT'

Domanda: ho una query SQL dinamica di grandi dimensioni, che sembra a volte superare la lunghezza di NVARCHAR(max). Esiste un modo che mi permetta comunque di eseguire una stringa di grandi dimensioni?

Risposta: se si supera la lunghezza di NVARCHAR(max), significa che la query ha raggiunto una dimensione di 2 GB! Probabilmente sarà necessario convertire tutte le stringhe concatenate in NVARCHAR(max). Tuttavia, una soluzione migliore, che tra l'altro ha il vantaggio di essere supportata nelle versioni di SQL Server precedenti a SQL Server 2005, è concatenare insieme una serie di stringhe più piccole. Ecco un esempio:

DECLARE @q1 NVARCHAR(4000), @q2 NVARCHAR(4000), @q3 NVARCHAR(4000)
SET @q1 = 'SELECT...'
SET @q2 = 'FROM...'
SET @q3 = 'WHERE...'
EXEC (@q1 + @q2 + @q3)

SUGGERIMENTO: Uso della clausola OUTPUT

È ora possibile controllare le modifiche apportate utilizzando le istruzioni Data Manipulation Language (DML) senza neanche utilizzare i trigger. SQL Server 2005 ha introdotto una clausola OUTPUT come parte delle istruzioni DML che consente di tenere traccia delle modifiche apportate durante un'operazione DML. La clausola OUTPUT può salvare il set dei risultati in una tabella o in una variabile di tabella.

Questa funzionalità è simile a quello che eseguivano i trigger con le tabelle INSERTED e DELETED per l'accesso alle righe modificate durante l'operazione DML. Per vederne il funzionamento, modificare l'indirizzo dalla tabella degli indirizzi sul valore contrario a quello originale, come mostrato nella figura seguente. È inoltre possibile utilizzare la logica mostrata in questo codice per tenere traccia delle modifiche apportate ai dati e memorizzare i dati in una tabella.

L'output della query sarà simile a questo:

Original Value:'1234 One Microsoft Way, Redmond, Wa.' has been changed to: '.aW,dnomdeR 
,yaW tfosorciM enO 4321'

Modifica di un indirizzo

--Create the address table
Create Table Address (ProductID Int, SupplierID Int, Address Varchar(255))

--Insert data
Insert into Address Values (234,567,'1234 One Microsoft Way, Redmond, Wa.')


--Declare a table variable
Declare @Recordchanges table (change Varchar(255))

--Update the address
Update Supplier.Address
Set Address=reverse(address)

--Record the updates into the table variable
OUTPUT 'Original Value:' + DELETED.Address+' has been changed to: '+ INSERTED.Address+'' 
into @RecordChanges

--Query the changes from table variable
Select * from @RecordChanges

Domanda: ho un'installazione cluster cruciale di SQL Server 2005 in Windows Server® 2003. Microsoft® Distributed Transaction Coordinator (MS DTC) è stato reso di tipo cluster nello stesso gruppo quorum del cluster ma utilizza una risorsa disco dedicata (MS DTC condivide lo stesso nome di rete e indirizzo IP del gruppo quorum del cluster). Voglio allineare questa configurazione in base alle procedure consigliate di Microsoft. Quindi, ho bisogno di un consiglio per lo spostamento di MS DTC in un gruppo di cluster dedicato. È sufficiente rimuovere i servizi MS DTC utilizzando lo Strumento di amministrazione cluster e ricrearli in un gruppo dedicato?

Risposta: poiché è già in uso una risorsa disco dedicata per MS DTC, dovrebbe essere possibile rimuovere semplicemente la risorsa e crearla in un nuovo gruppo. È necessario creare un nuovo nome di rete e un indirizzo IP virtuale nel nuovo gruppo.

In alternativa, è possibile creare il nuovo nome di rete e l'indirizzo IP nel gruppo cluster e modificare le dipendenze per le nuove risorse. È quindi possibile trascinare MS DTC in un nuovo gruppo e di conseguenza verranno trasferiti anche il disco dedicato e le nuove risorse.

Domanda: devo poter vedere in un database le statistiche di I/O presenti nei file di database fisici. Cosa devo utilizzare?

Risposta: la funzione di sistema fn_virtualfilestats, disponibile in SQL Server 2000 e in SQL Server 2005, oppure sys.dm_io_virtual_file_stats (solo in SQL Server 2005) consente di ottenere questo. La funzione restituisce le informazioni statistiche raccolte a partire dall'ultimo avvio dell'istanza di SQL Server. I risultati di esempio sono illustrati nella figura 2.

Figure 2 Visualizzazione delle statistiche di I/O in un database

Comprendere l'impatto di I/O sui file di dati di base può aiutare a pianificare meglio le cose, ad esempio il posizionamento fisico dei file e dei gruppi di file nei volumi di dati, la rilevazione di possibili colli di bottiglia di I/O, l'esecuzione della gestione del database a livello di file e altre attività di questo tipo. Questa funzione è particolarmente utile per esaminare l'impatto di I/O nei database di grandi dimensioni, dove possono essere presenti più file e gruppi di file.

In SQL Server 2000, la query per la visualizzazione delle informazioni di I/O dei file è simile a questa:

SELECT *
FROM ::fn_virtualfilestats(default,default)
GO

Per vedere uno specifico ID di database, passare l'ID del database, in questo modo:

SELECT *
FROM ::fn_virtualfilestats(7,default)
GO

Questo è il codice di SQL Server 2005 che mostra le statistiche dei file di tutti i database del server:

SELECT *
FROM ::fn_virtualfilestats(NULL,NULL)
GO

La query seguente restituisce le statistiche dei file solo per il database corrente:

SELECT *
FROM ::fn_virtualfilestats(NULL,NULL)
WHERE DBID=db_id()
GO

Per SQL Server 2005, è disponibile anche una nuova funzione di sistema denominata sys.dm_io_virtual_file_stats che va a sostituire la precedente funzione fn_virtualfilestats:

sys.dm_io_virtual_file_stats( 
{ database_id | NULL },
{ file_id | NULL }
)

Ecco come utilizzarla:

SELECT * FROM sys.dm_io_virtual_file_stats(NULL,NULL)

Per generare un report più leggibile che mostri i nomi effettivi dei database e dei file dall'output, è possibile utilizzare il codice incluso nel download di SQL Server 2000 o di SQL Server 2005. Il codice è disponibile sul sito Web di TechNet Magazine.

Domanda: ho bisogno di un modo facile per vedere se una transazione di eliminazione ha attivato un trigger. In che modo posso ottenere queste informazioni?

Risposta: quando ci si occupa dei trigger che gestiscono operazioni di eliminazione, inserimento e aggiornamento, solitamente viene utilizzata una serie di tecniche differenti per determinare se un trigger è stato attivato da una transazione di eliminazione. Un metodo comune è confrontare il numero delle tabelle virtuali inserite ed eliminate per vedere se corrispondono. Tuttavia, esiste un metodo più semplice: è possibile utilizzare la funzione Columns_Updated.

Questo perché quando un trigger viene attivato da una transazione di eliminazione, la funzione Columns_Updated restituisce sempre il valore varbinary 0x. Il controllo seguente verificherà che il trigger sia stato attivato da un'operazione di eliminazione:

IF Columns_Updated() = 0x

SUGGERIMENTO: Inizializzazione dei dati e dei file di registro

Sapevate che i dati e i file di registro vengono inizializzati per sovrascrivere i dati esistenti lasciati nel disco dopo l'eliminazione di file precedenti? I dati e i file di registro vengono inizializzati inserendo dapprima degli zeri quando si crea un database, quando si aggiungono file, file di registro o dati a un database esistente, quando si aumentano le dimensioni di un file esistente (incluse le operazioni di aumento automatico delle dimensioni), o quando si ripristina un database o un gruppo di file. L'inizializzazione dei file prolunga la durata di queste operazioni. Tuttavia, quando i dati vengono inserirti per la prima volta nei file, il sistema operativo non deve inserire gli zeri nei file.

In SQL Server 2005, i file di dati possono essere inizializzati istantaneamente. Questa funzionalità consente l'esecuzione rapida delle operazioni citate in precedenza. L'inizializzazione file immediata recupera lo spazio su disco utilizzato senza riempire tale spazio con gli zeri. Il contenuto del disco viene invece sovrascritto non appena i dati vengono inseriti nei file. I file di registro non possono essere inizializzati istantaneamente. L'inizializzazione immediata dei file è disponibile solo in Windows XP Professional e in Windows Server 2003 o versioni successive.

Dato che il contenuto del disco eliminato viene sovrascritto solo quando i nuovi dati vengono inseriti nei file, potrebbe accadere che un'entità non autorizzata acceda al contenuto eliminato. Sebbene il file di database sia associato all'istanza di SQL Server, il pericolo di intercettazione delle informazioni è ridotto dall'elenco di controllo di accesso discrezionale (DACL) nel file. L'elenco DACL consente l'accesso al file soltanto all'account di servizio di SQL Server e all'amministratore locale. Tuttavia, quando il file viene scollegato dall'istanza, è possibile che avvenga un accesso al file da parte di un utente o di un servizio senza SE_MANAGE_VOLUME_NOME. Un pericolo simile esiste quando viene eseguito il backup del database. Il contenuto eliminato può diventare disponibile a un utente oppure a un servizio non autorizzato se il file di backup non è protetto con un apposito DACL.

Se la divulgazione del contenuto eliminato rappresenta un problema, è necessario verificare sempre che i file di backup e i file di dati dispongano di elenchi DACL restrittivi. Inoltre, disabilitare l'inizializzazione immediata dei file per l'istanza di SQL Server revocando SE_MANAGE_VOLUME_NAME dall'account di servizio di SQL Server.

Thanks to the following Microsoft IT pros for their technical expertise: Christian Bolton, Dan Carollo, Robert Davis, Jorge Guzman, Saleem Hakani, Ward Pond, Kalyan Yella, and Paolo Zavatarelli.

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