SQL Server

Ridurre al minimo i blocchi in SQL Server

Cherié Warren

Panoramica:

• Perché si verifica l'escalation dei blocchi
• Evitare i blocchi inutili
• Ottimizzazione delle query
• Controllo dell'impatto dei blocchi sulle prestazioni

I blocchi sono necessari per il supporto di attività di lettura e scrittura contemporanee su un database, ma possono influire negativamente sulle prestazioni del sistema, a volte in modi poco evidenti. Nell'articolo si illustrerà come sia possibile ottimizzare un database SQL Server 2005 o SQL Server 2008 per ridurre al minimo

i blocchi e come si possa monitorare il sistema in modo da comprendere meglio l'impatto che i blocchi hanno sulle prestazioni.

Blocchi ed escalation

SQL Server® sceglie il livello di blocco più appropriato, in base alla quantità di record interessati e alle attività simultanee esistenti sul sistema. Per impostazione predefinita, SQL Server sceglie il livello minimo possibile di blocco, riservando i livelli maggiori alle occasioni in cui questi consentono di utilizzare in modo più efficace la memoria di sistema. SQL Server esegue l'escalation di un blocco solo quando questa presenta dei vantaggi in termini di prestazioni complessive del sistema. Come illustrato dalla Figura 1, le escalation si verificano quando il numero di blocchi in una scansione particolare supera i 5.000 o quando la memoria utilizzata per i blocchi dal sistema supera la capacità disponibile:

Figura 1** Condizioni che provocano l'escalation dei blocchi **(Fare clic sull'immagine per ingrandirla)

• 24% della memoria non AWE (Address Windowing Extension) utilizzata dal motore di database se l'impostazione dei blocchi è 0
• 40% della memoria non AWE utilizzata dal motore di database se l'impostazione dei blocchi non è 0

Quando si verifica, l'escalation è sempre verso un blocco a livello di tabella.

Evitare i blocchi inutili

I blocchi si possono verificare a qualsiasi livello, ma l'esposizione ai blocchi aumenta quando si verificano escalation. L'escalation dei blocchi può essere un sintomo di un'applicazione progettata, codificata o configurata in modo poco efficiente.

L'adesione ai principi di base della progettazione dei database (come l'utilizzo di uno schema normalizzato con chiavi ristrette e la limitazione delle operazioni di massa sui dati in sistemi transazionali) è importante per evitare i blocchi. Se non si seguono questi principi (ad esempio, si separa il sistema di segnalazione dal sistema transazionale o l'elaborazione dei dati viene eseguita durante le ore di chiusura dell'ufficio), sarà difficile ottimizzare il sistema.

L'indicizzazione può rivelarsi un fattore chiave per la quantità di blocchi necessari per accedere ai dati. Un indice può ridurre il numero di record a cui accede una query, riducendo il numero di ricerche interne eseguite dal motore di database. Quando, ad esempio, si seleziona una singola riga da una tabella su una colonna non indicizzata, ciascuna riga della tabella deve essere temporaneamente bloccata fino a che il record desiderato non viene identificato. Al contrario, quando la colonna è indicizzata, è necessario un solo blocco.

SQL Server 2005 e SQL Server 2008 contengono entrambi viste a gestione dinamica (sys.dm_db_missing_index_group_stats, sys.dm_db_missing_index_groups, sys.dm_db_missing_index_details) che rivelano tabelle e colonne che si avvantaggerebbero dell'uso degli indici, in base alle statistiche d'uso accumulate.

Anche la frammentazione può causare problemi di prestazioni, poiché in tal caso il motore di database deve accedere a più pagine del necessario. Inoltre, delle statistiche non esatte possono indurre Query Optimizer a scegliere un piano meno efficiente.

Se da un lato accelerano l'accesso ai dati, gli indici possono dall'altro rallentare la modifica dei dati, perché oltre alla modifica dei dati sottostanti si rende necessario anche l'aggiornamento degli indici. La vista di gestione dinamica sys.dm_db_index_usage_stats evidenzia la frequenza nell'uso degli indici. Un esempio tipico di indicizzazione poco efficiente sono gli indici composti, in cui la stessa colonna è indicizzata sia singolarmente che in combinazione. Poiché SQL Server accede agli indici da sinistra a destra, l'indice viene utilizzato fintanto che le colonne più a sinistra sono utili.

Con un partizionamento delle tabelle è possibile sia ottimizzare il sistema (con una minore esposizione ai blocchi) che suddividere i dati in oggetti fisici separati a cui si può accedere separatamente. L'attivazione delle partizioni di riga è un metodo ovvio di separazione dei dati, ma un'altra opzione da considerare è la partizione orizzontale dei dati. È possibile scegliere di denormalizzare intenzionalmente dividendo una tabella in tabelle separate con lo stesso numero di righe e chiavi, ma con colonne diverse, per ridurre le probabilità che processi diversi tentino di accedere contemporaneamente ai dati in modo esclusivo.

Maggiori sono i modi in cui un'applicazione può accedere a una specifica riga di dati e maggiori sono le colonne che possono essere incluse in tale riga, più l'approccio di partizionamento delle colonne può risultare interessante. Questo tipo di approccio può talvolta essere utile per le tabelle di stato e di coda delle applicazioni. SQL Server 2008 aggiunge la possibilità di disattivare le escalation dei blocchi la partizione (o per tabella quando le partizioni non sono attivate per la tabella).

Ottimizzazione delle query

L'ottimizzazione delle query svolge un ruolo importante per il miglioramento delle prestazioni. Di seguito sono riportati tre possibili approcci da adottare:

Abbreviare le transazioni Uno dei modi più importanti per ridurre i blocchi, come pure per migliorare le prestazioni globali, è assicurarsi che le transazioni abbiano le minori dimensioni possibili. Qualsiasi elaborazione che non è fondamentale per l'integrità della transazione (come la ricerca di dati correlati, l'indicizzazione e lo scrubbing dei dati) dovrebbe essere eliminata per ridurne la dimensione.

SQL considera qualsiasi istruzione come una transazione implicita. Se l'istruzione influisce su molte righe, un'unica istruzione può costituire tuttavia una transazione di grandi dimensioni, soprattutto se coinvolge molte colonne o se le colonne contengono tipi di dati di grandi dimensioni. Una singola istruzione potrebbe causare anche divisioni di pagina se il fattore di riempimento è elevato o se un'istruzione UPDATE compila una colonna con un valore maggiore di quanto allocato. In tali circostanze, può essere utile dividere la transazione in gruppi di righe, elaborandone una per volta fino al completamento dell'operazione. La suddivisione in batch dovrebbe essere presa in considerazione solo quando la singola istruzione o i gruppi di istruzioni possono essere suddivisi in batch più piccoli che possono essere tuttavia considerati unità di lavoro complete se vengono elaborati più o meno correttamente.

Sequenza delle transazioni All'interno di una transazione, l'organizzazione in sequenza delle istruzioni può diminuire la probabilità di blocchi. È necessario considerare due principi. In primo luogo, accedere agli oggetti nello stesso ordine all'interno di tutto il codice SQL nel sistema. Senza un ordine coerente, potrebbero verificarsi deadlock quando due processi concorrenti accedono ai dati con un ordine diverso, causando un errore di sistema per uno dei processi. In secondo luogo, collocare gli oggetti cui si accede di frequente o gli oggetti l'accesso ai quali risulta più costoso alla fine della transazione. SQL aspetta prima di bloccare gli oggetti finché sono necessari nelle transazioni. Ritardare l'accesso alle aree sensibili consente l'applicazione di blocchi su questi oggetti per una minore percentuale di tempo.

Uso delle indicazioni di blocco È possibile utilizzare le indicazioni di blocco a livello di sessione o a livello di istruzione per una specifica tabella o vista. Uno scenario tipico per l'utilizzo di un'indicazione di blocco a livello di sessione è un'elaborazione batch in un data warehouse, in cui lo sviluppatore sa che quel processo sarà l'unico a essere eseguito in un determinato momento su quell'insieme di dati. Utilizzando un comando come SET ISOLATION LEVEL READ UNCOMMITTED all'inizio della stored procedure, SQL Server non riserverà alcun blocco in lettura, riducendo in tal modo l'overhead complessivo derivante dai blocchi e consentendo un miglioramento delle prestazioni.

Uno scenario tipico per l'uso di indicazioni di blocco sarebbe quando uno sviluppatore sa che una lettura dirty può essere eseguita senza problemi (ad esempio quando viene letta una singola riga da una tabella in cui nessun altro processo concorrente necessiterà mai della stessa riga) o quando tutti gli altri tentativi di ottimizzazione delle prestazioni non sono riusciti (progettazione di schemi, progettazione e manutenzione di indici e ottimizzazione delle query) e lo sviluppatore desidera forzare il compilatore a utilizzare un tipo specifico di indicazione.

Le indicazioni di blocco a livello di riga sono utili se il monitoraggio dimostra che blocchi con un maggiore livello di dettaglio si sono verificati laddove pochi record sono coinvolti nella query, poiché questo potrebbe ridurre i blocchi. Le indicazioni di blocco a livello di tabella sono utili se il monitoraggio evidenzia la presenza di blocchi con un maggior livello di dettaglio (e non soggetti a escalation) quando quasi tutti i record della tabella sono coinvolti nella query, poiché questo potrebbe ridurre le risorse di sistema necessarie per contenere i blocchi. Anche quando si specifica un'indicazione di blocco, non esistono garanzie sulla non esecuzione dell'escalation del blocco quando il numero di blocchi raggiunge la soglia per la memoria di sistema. Tuttavia, in tal modo si eviteranno tutte le altre escalation.

Regolazione della configurazione

Come illustrato dalla Figura 2, è necessario prendere in considerazione numerosi fattori quando si configura un sistema SQL Server.

Figura 2** Modalità di determinazione in SQL Server della quantità di memoria che può essere utilizzata per i blocchi **(Fare clic sull'immagine per ingrandirla)

Memoria I blocchi sono sempre nella memoria non AWE, quindi l'aumento delle dimensioni della memoria non AWE comporterà l'aumento della capacità del sistema di contenere blocchi.

Quando è necessario aumentare la capacità per i blocchi, un'architettura a 64 bit diventa la prima scelta, poiché nell'architettura a 32 bit la memoria non AWE è limitata a 4 GB mentre in quella a 64 bit non esiste alcun limite.

Nei sistemi a 32 bit è possibile ricavare un gigabyte di memoria aggiuntivo dal sistema operativo per SQL Server aggiungendo l'opzione /3GB al file Boot.ini.

Impostazioni di configurazione di SQL Server È possibile definire una serie di impostazioni tramite sp_configure per influire sui blocchi. Le impostazioni dei blocchi consentono di configurare la quantità di blocchi che un sistema può contenere prima di generare un errore. Per impostazione predefinita, l'impostazione è 0, vale a dire che il server regolerà dinamicamente i blocchi riservati con gli altri processi che competono per l'accesso alla memoria. SQL riserva inizialmente 2.500 blocchi e ciascun blocco occupa 96 byte di memoria. La memoria di paging non viene utilizzata.

Le impostazioni di memoria minima e massima riservano la quantità di memoria utilizzata da SQL Server, configurando così il server in modo da occupare in modo statico la memoria. Poiché l'escalation dei blocchi è correlata alla memoria disponibile, se si riserva la quantità di memoria dai processi in competizione, si influisce sull'effettivo verificarsi delle escalation.

Impostazioni di connessione Per impostazione predefinita, i blocchi non sono soggetti a timeout, ma è possibile utilizzare l'impostazione @@LOCK_TIMEOUT, in modo da provocare la generazione di un errore quando viene superata la soglia di attesa specificata per il rilascio di un blocco.

Flag di traccia Due flag di traccia in particolare sono correlati alle escalation dei blocchi. Uno è il flag di traccia 1211, che disattiva le escalation dei blocchi. Se il numero di blocchi utilizzati supera la memoria disponibile, viene generato un errore. L'altro flag di traccia è il 1224, che disattiva le escalation dei blocchi per le singole istruzioni.

Osservazione del sistema

Ulteriori letture

• Inside Microsoft SQL Server 2005: The Storage Engine di Kalen Delaney
• Come monitorare i blocchi in SQL Server 2005 e SQL Server 2000
• Script per ottenere statistiche sulle prestazioni di SQL Server 2005
• Come risolvere i problemi dei blocchi causati dall'escalation dei blocchi in SQL Server
• Escalation dei blocchi (Motore di database)

L'impatto che i blocchi hanno sulle prestazioni globali del sistema può essere monitorato per i blocchi eseguendo a intervalli regolari il polling dei dati sullo stato (magari ogni ora) e acquisendo le statistiche correnti sui blocchi gestiti. Le informazioni chiave da acquisire sono:

• Oggetto interessato, livello di dettaglio tipo di blocco
• Blocco e durata del blocco
• Comando SQL inviato (nome della stored procedure, istruzione SQL interna)
• Informazioni sulla catena di blocco, ove pertinente
• Modalità di utilizzo da parte del sistema della capacità di blocco disponibile

Per acquisire queste informazioni, è possibile eseguire uno script simile a quello nella Figura 3, scrivendolo in una tabella con il relativo timestamp. Per suddividere ulteriormente il ResourceId dei dati bloccati, è possibile eseguire uno script come quello riportato nella Figura 4.

Figure 4 Ulteriori informazioni sui dati bloccati

DECLARE @SQL                           nvarchar(max)
      , @CallingResourceType           varchar(30)
      , @Objectname                    sysname
      , @DBName                        sysname
      , @resource_associated_entity_id int

-- TODO: Set the variables for the object you wish to look up

SET @SQL = N'
USE     ' + @DbName + N'
DECLARE @ObjectId int

SELECT  @ObjectId = CASE
                    WHEN @CallingResourceType = ''OBJECT''
                    THEN @resource_associated_entity_id
                    WHEN @CallingResourceType IN (''HOBT'', ''RID'', ''KEY'',''PAGE'')
                    THEN (SELECT  object_id
                          FROM    sys.partitions 
                          WHERE   hobt_id = @resource_associated_entity_id)
                    WHEN @CallingResourceType = ''ALLOCATION_UNIT''
                    THEN (SELECT  CASE
                                     WHEN type IN (1, 3)
                                     THEN (SELECT  object_id
                                           FROM    sys.partitions 
                                           WHERE   hobt_id = allocation_unit_id)
                                     WHEN type = 2
                                     THEN (SELECT  object_id
                                           FROM    sys.partitions 
                                           WHERE   partition_id = allocation_unit_id)
                                     ELSE NULL
                                     END
                          FROM    sys.allocation_units 
                          WHERE   allocation_unit_id = @resource_associated_entity_id)
                    ELSE NULL
                    END

SELECT  @ObjectName = OBJECT_NAME(@ObjectId)'

EXEC    dbo.sp_executeSQL
        @SQL
      , N'@CallingResourceType varchar(30)
      , @resource_associated_entity_id int
      , @ObjectName sysname OUTPUT'
      , @resource_associated_entity_id = @resource_associated_entity_id
      , @CallingResourceType = @CallingResourceType
      , @ObjectName = @ObjectName OUTPUT

Figure 3 Acquisizione delle statistiche sui blocchi

SELECT  er.wait_time                      AS WaitMSQty
      , er.session_id                     AS CallingSpId
      , LEFT(nt_user_name, 30)            AS CallingUserName
      , LEFT(ces.program_name, 40)        AS CallingProgramName
      , er.blocking_session_id            AS BlockingSpId
      , DB_NAME(er.database_id)           AS DbName
      , CAST(csql.text AS varchar(255))   AS CallingSQL
      , clck.CallingResourceId
      , clck.CallingResourceType
      , clck.CallingRequestMode
      , CAST(bsql.text AS varchar(255))   AS BlockingSQL
      , blck.BlockingResourceType
      , blck.BlockingRequestMode
FROM    master.sys.dm_exec_requests er WITH (NOLOCK)
        JOIN master.sys.dm_exec_sessions ces WITH (NOLOCK)
          ON er.session_id = ces.session_id
        CROSS APPLY fn_get_sql (er.sql_handle) csql
        JOIN (
-- Retrieve lock information for calling process, return only one record to 
-- report information at the session level
              SELECT  cl.request_session_id                 AS CallingSpId
                    , MIN(cl.resource_associated_entity_id) AS CallingResourceId
                    , MIN(LEFT(cl.resource_type, 30))       AS CallingResourceType
                    , MIN(LEFT(cl.request_mode, 30))        AS CallingRequestMode 
-- (i.e. schema, update, etc.)
              FROM    master.sys.dm_tran_locks cl WITH (nolock)
              WHERE   cl.request_status = 'WAIT' -- Status of the lock request = waiting
              GROUP BY cl.request_session_id
              ) AS clck
           ON er.session_id = clck.CallingSpid
         JOIN (
              -- Retrieve lock information for blocking process
              -- Only one record will be returned (one possibility, for instance, 
              -- is for multiple row locks to occur)
              SELECT  bl.request_session_id            AS BlockingSpId
                    , bl.resource_associated_entity_id AS BlockingResourceId
                    , MIN(LEFT(bl.resource_type, 30))  AS BlockingResourceType
                    , MIN(LEFT(bl.request_mode, 30))   AS BlockingRequestMode
              FROM    master.sys.dm_tran_locks bl WITH (nolock)
              GROUP BY bl.request_session_id
                    , bl.resource_associated_entity_id 
              ) AS blck
           ON er.blocking_session_id = blck.BlockingSpId
          AND clck.CallingResourceId = blck.BlockingResourceId
        JOIN master.sys.dm_exec_connections ber WITH (NOLOCK)
          ON er.blocking_session_id = ber.session_id
        CROSS APPLY fn_get_sql (ber.most_recent_sql_handle) bsql
WHERE   ces.is_user_process = 1
        AND er.wait_time > 0

Si può inoltre monitorare il proprio sistema per tenere sotto controllo le escalation tramite SQL Profiler (evento Lock:Escalation), vista di gestione dinamica dm_db_index_operational_stats (index_lock_promotion_count) o polling regolare delle informazioni sui blocchi del sistema. L'informazione da ottenere mediante il monitoraggio dell'escalation è se l'elaborazione garantisce un'escalation; se non la garantisce, le relative stored procedure possono diventare una causa primaria di problemi di prestazioni. Per una corretta valutazione, tabelle con grandi quantità di dati o alti tassi di utilizzo contemporaneo devono essere al centro dell'analisi.

Dopo aver raccolta dati su blocchi ed escalation, è possibile passare all'analisi dei dati per determinare i tempi di blocco complessivi (numero di incidenze moltiplicato per la durata delle incidenze) per l'oggetto. In genere, è possibile avviare un ciclo iterativo di regolazione delle prestazioni in cui le modifiche vengano distribuite, monitorate, analizzate e, all'occorrenza, modificate nuovamente. A volte è sufficiente una semplice modifica, come l'aggiunta di un indice, per ottenere miglioramenti significativi delle prestazioni e intervenire sull'area del sistema che costituisce il collo di bottiglia più stretto per le prestazioni.

Altre informazioni sulla riduzione dei blocchi in SQL Server sono riportati nell'intestazione laterale "Ulteriori letture". Se si presta attenzione nel mantenere delle dimensioni accettabili per le transazioni in ogni parte del progetto, del codice e delle fasi di stabilizzazione, sarà possibile ridurre al minimo i problemi causati dai blocchi. Anche un hardware appropriato contribuisce a ridurre in modo considerevole la probabilità di escalation superflue. In tutti i casi, una valutazione costante dei blocchi sul sistema consente di individuare rapidamente all'origine i problemi di prestazione.

Cherié Warren è Senior Development Lead per Microsoft IT. È attualmente responsabile di uno dei più grandi database transazionali in Microsoft. Cherié viene anche consultata di frequente per l'individuazione delle cause e la risoluzione dei problemi di prestazioni correlati ai blocchi. In oltre 10 anni di lavoro, si è specializzata sui database SQL Server delle grandi aziende.

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