Domande e risposte su SQLColli di bottiglia della CPU, ripristino e spostamento di database e altro ancora

A cura di Nancy Michell

D All'improvviso, l'utilizzo della CPU di SQL Server™ ha iniziato a bloccarsi eccessivamente, ma non è cambiato nulla. Non è stato aggiunto alcun nuovo utente, non è stato disattivato alcun hardware e non è stata creata alcuna nuova tabella. Dunque cosa succede?

Suggerimento: accesso durante la creazione dell'indice

Capita a volte che durante il processo di creazione di indici in tabelle di grandi dimensioni (operazione che può richiedere parecchio tempo), si desideri poter comunque disporre dei dati. Come è possibile raggiungere entrambi gli obiettivi?

Ogni volta che viene creato, rilasciato o ricreato un indice cluster, SQL Server inserisce nella tabella un blocco modifica schema (SCH-M), il che impedisce a tutti gli utenti di accedere ai dati sottostanti per tutta la durata dell'operazione. Ciò è quanto avviene durante la creazione di un indice cluster in una tabella. Per contrasto, quando si crea un indice non cluster in una colonna, SQL Server inserisce un blocco condiviso (S), impedendo al tempo stesso l'aggiornamento dei dati nella tabella sottostante; al massimo consente di eseguire le istruzioni SELECT in modo da poter leggere i dati.

Se è importante che la tabella possa essere letta durante la creazione dell'indice cluster, è possibile creare un indice in una tabella e renderla un'operazione in linea. Ecco il comando:

CREATE UNIQUE CLUSTERED INDEX CLUST_IDX_SQLTIPS 
ON SQLTips (tip) with (ONLINE=ON) Go;

Quando si utilizzano operazioni di indice in linea nelle tabelle, SQL Server inserirà nuovamente un blocco SCH-M per un indice cluster o il blocco condiviso (S) per un indice non cluster nella tabella sottostante, ma soltanto per un breve periodo di tempo: durante la fase di avvio e fine dell'operazione di indice. Pertanto, questa opzione consente un accesso migliore per richiedere e aggiornare la tabella sottostante durante il processo di creazione dell'indice. Si noti che le operazioni di creazione dell'indice in linea sono disponibili solo con l'edizione SQL Server 2005 Enterprise.

R Le ragioni alla base di un collo di bottiglia CPU che si verifica improvvisamente e inaspettatamente senza alcuna modifica o carico aggiuntivo sul server possono essere svariate, ma alcune delle più comuni prevedono un piano di query non ottimale, una progettazione errata del database e dell'applicazione, nonché risorse hardware insufficienti.

La prima cosa da fare in una situazione di questo tipo è stabilire se esiste un limite alla CPU del server e, in tal caso, identificare le istruzioni che consumano la quantità maggiore di CPU nel sistema SQL Server locale. È possibile utilizzare Performance Monitor per stabilire se la CPU del server ha dei limiti osservando il contatore PROCESSOR:% PROCESSOR TIME. Se si scopre che il valore di riferimento per il tempo della CPU è uguale o maggiore al 75 percento, si verifica un collo di bottiglia della CPU.

È necessario, inoltre, monitorare le utilità di pianificazione di SQL Server eseguendo una query della vista a gestione dinamica (DMV) del sistema definita SYS.DM_OS_SCHEDULERS per visualizzare il valore delle attività eseguibili. Un valore diverso da zero indica che le attività devono aspettare un determinato periodo di tempo per l'esecuzione; anche valori alti per questo contatore sono un sintomo di un collo di bottiglia della CPU.

È possibile utilizzare la seguente query per elencare tutte le utilità di pianificazione e osservare le attività eseguibili:

SELECT
 Scheduler_ID,
 Current_Tasks_Count,
 Runnable_Tasks_Count
FROM
 SYS.DM_OS_SCHEDULERS
WHERE
 Scheduler_ID < 255

Per ottenere le prime 50 istruzioni SQL con un elevato utilizzo di CPU, utilizzare la query nella Figura 1.

Figure 1 I primi 50 problemi di CPU

SELECT TOP 50 (a.total_worker_time/a.execution_count) AS [Avg_CPU_Time],
 Convert(Varchar,Last_Execution_Time) AS 'Last_execution_Time',
 Total_Physical_Reads,
 SUBSTRING(b.text,a.statement_start_offset/2,
 (case when a.statement_end_offset = -1 then len(convert(nvarchar(max), b.text)) * 2
 else
 a.statement_end_offset end - a.statement_start_offset)/2) AS Query_Text,
 dbname=Upper(db_name(b.dbid)),
 b.objectid AS 'Object_ID'
 FROM sys.dm_exec_query_stats a
 CROSS APPLY
 sys.dm_exec_sql_text(a.sql_handle) AS b
 ORDER BY
 [Avg_CPU_Time] DESC

D Devo recuperare un database SQL Server 2005 dal nastro. Dopo il ripristino, la maggior parte delle autorizzazioni dell'utente sono state perse. Quali sono state le operazioni errate durante il ripristino? I dati era corretti ma le autorizzazioni erano in disordine.

R Molto probabilmente il database principale non è stato ripristinato nello stesso momento e gli ID di accesso nel database dell'utente non corrispondono al database principale corrente. È necessario sincronizzare tutti i dati. L'intestazione laterale "Dove richiedere supporto durante lo spostamento di un database" elenca le risorse più utili per ottenere l'origine dei problemi riscontrati durante il ripristino o lo spostamento di un database.

D Ho sviluppato un'applicazione in cui ho utilizzato una stored procedure che distrugge i dati XML nelle tabelle relazionali utilizzando OpenXML in SQL Server 2005 SP1. Il documento XML è al massimo di 5KB (la media è di 2,5KB). La stored procedure viene chiamata molte volte in parallelo (fino a 50 volte).

Mi trovo ad affrontare seri problemi di conflitti del blocco e penso che possano essere dovuti a OpenXML. Cosa ne pensi?

R Nonostante OpenXML potrebbe essere più veloce per la distruzione o la conversione dei dati in un singolo thread rispetto al metodo nodes, tale metodo in genere è scalabile, soprattutto se utilizzato in parallelo. Tuttavia, se si utilizza OpenXML, è necessario utilizzare le seguenti linee guida per migliorare le prestazioni globali di OpenXML.

Invece di chiamare OpenXML cinque volte con lo stesso modello di riga (così come avveniva nella soluzione), è necessario estrarre tutti i dati con lo stesso modello di riga in una tabella temporanea ed eseguire le selezioni dalla stessa tabella. Tentare di rilasciare la memoria con sp_xml_removedocument il prima possibile. Inoltre, è un'ottima idea evitare l'uso di caratteri jolly come * e //, se possibile; fornendo il percorso esplicito si otterrà una query dalle prestazioni più elevate.

D DBCC SHRINKFILE funziona molto lentamente sul mio server. Riuscirei a ottenere delle prestazioni migliori su un computer a processi multipli? Cosa posso fare per migliorare la situazione?

R DBCC SHRINKFILE è un'operazione a thread singolo, il che significa non sfrutta le CPU multiple. Sposta le pagine dalla parte finale del file a quella iniziale, una pagina alla volta. Inoltre, la compattazione, come è spesso denominata, non esegue la deframmentazione; in realtà, in molte situazioni, la compattazione aumenta la deframmentazione logica.

Alcuni metodi che migliorano le prestazioni della compattazione includono il movimento delle pagine negli indici cluster. Se si dispone di heap e tali heap hanno molti indici non cluster, la velocità sarebbe notevolmente lenta (paragonata a una situazione di indice cluster).

Si noti, inoltre, che lo spostamento delle pagine per i dati LOB (large object blob) è lento, perché è necessario leggere i dati nella riga per trovare il percorso principale dei dati LOB.

Se la maggior parte del contenuto di un indice/tabella si trova alla fine del file, è possibile ricreare gli indici per spostarli nella parte anteriore del file. La ricostruzione degli indici sfrutta CPU multiple e potrebbe utilizzare meno spazio del log in modalità bulk_logged. Quando si esegue la compattazione, verrà eseguita più rapidamente.

Per ulteriori informazioni sulle operazioni di compattazione, iniziare con le voci agli indirizzi blogs.msdn.com/sqlserverstorageengine/archive/2007/03/29/how-does-your-schema-affect-your-shrink-run-time.aspx e blogs.msdn.com/sql-serverstorageengine/archive/2007/04/15/how-to-avoid-using-shrink-in-sql-server-2005.aspx.

Dove richiedere supporto durante lo spostamento di un database

• Come spostare i database tra i computer che eseguono SQL Server
• Come spostare i database di SQL Server in una nuova posizione utilizzando le funzioni di collegamento e scollegamento di SQL Server
• INF: Utilizzo dell'opzione WITH MOVE con l'istruzione RESTORE
• Come risolvere i problemi relativi alle autorizzazioni quando un database viene spostato tra i server che eseguono SQL Server
• INF: Come trasferire accessi e password tra le istanze di SQL Server
• INF: Comprendere e risolvere i problemi relativi alla Copia guidata database in SQL Server 2000
• Dopo il ripristino del database gli accessi e le autorizzazioni dell'utente potrebbero non essere corretti
• Non è possibile ripristinare i backup del database di sistema in una build differente di SQL Server
• Come ripristinare una tabella o righe specifiche dai backup del database o dai backup del log delle transazioni in SQL Server

Suggerimento: modifica delle porte

Chiunque abbia una certa dimestichezza con SQL Server sa bene che il numero di porta predefinito su cui SQL Server è in ascolto è 1433. Nonostante le istanze denominate di SQL Server siano configurate per l'uso di porte dinamiche (il che significa che è possibile selezionare qualsiasi porta disponibile ogni volta che viene avviata un'istanza di SQL Server), l'istanza di SQL Server predefinita è sempre in ascolto sulla porta 1433. Pertanto, se il server è in ascolto sulla porta predefinita e non è molto protetto, è a rischio. Tuttavia, è possibile contrastare gli attacchi modificando la porta predefinita. Ecco cosa fare.

Aprire la funzionalità di gestione della configurazione di SQL Server ed espandere Configurazione di rete di SQL Server 2005, quindi i protocolli. Successivamente, fare doppio clic su TCP/IP. L'elenco delle proprietà TCP/IP e delle relative funzioni viene illustrato nel grafico riportato di seguito, di conseguenza eseguire l'impostazione.

Tenere presente che il motore di database di SQL Server può essere in ascolto su più porte nello stesso indirizzo IP, dunque elencare le porte che si intende utilizzare separandole da una virgola nel formato 1433,1500,1501. Se si desidera configurare un solo indirizzo IP per l'ascolto su più porte, è necessario impostare su no il parametro Listen All nella scheda relativa ai protocolli della finestra di dialogo Proprietà TCP/IP.

A questo punto, fare doppio clic su ciascun indirizzo e selezionare Proprietà per identificare l'indirizzo IP che si intende configurare. Se la finestra di dialogo Porte dinamiche TCP contiene 0, il che indica che il motore di database è in ascolto su porte dinamiche, eliminare lo 0. Nell'area delle proprietà IP, nella casella Porta TCP, immettere il numero di porta su cui si desidera che l'indirizzo IP sia in ascolto e fare clic su OK. Nel riquadro della console, selezionare Servizi di SQL Server 2005, mentre nel riquadro dei dettagli fare clic con il pulsante destro del mouse su SQL Server (<(nome istanza>), quindi scegliere l'opzione di riavvio per arrestare e riavviare SQL Server.

Dopo aver configurato SQL Server affinché sia in ascolto su una porta specifica, il client può collegarsi alla porta in tre modi differenti. È possibile eseguire il servizio SQL Server Browser sul server per effettuare il collegamento all'istanza del motore di database per nome; è possibile creare un alias sul client, specificando il numero di porta; oppure è possibile programmare il client in modo che effettui la connessione utilizzando una stringa di connessione personalizzata.

Grazie ai professionisti IT di Microsoft per aver risposto ai quesiti di questo mese: Chad Boyd, Cindy Gross, John Hadden, Saleem Hakani, Stephen Jiang, Mahesh Nayak, Paul Randal e Wayne Yu.

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