SQL Server

Réduire les blocages dans SQL Server

Cherié Warren

Vue d'ensemble:

• Raisons de l'escalade de verrous
• Éviter les blocages inutiles
• Optimisation de vos requêtes
• Surveillance de l'impact des verrous sur les performances

Les verrous sont nécessaires pour prendre en charge les activités de lecture et d'écriture simultanées sur une base de données, mais les blocages peuvent avoir un effet négatif sur les performances du système, parfois de façon subtile. Dans cet article, nous verrons comment optimiser votre base de données SQL Server 2005 ou SQL Server 2008 afin de réduire

les blocages et comment surveiller le système afin de mieux comprendre l'impact des verrous sur les performances.

Verrous et escalade

SQL Server® choisit le grain de verrou le plus approprié en se basant sur le nombre d'enregistrements qui ont été affectés et l'activité simultanée qui existe sur le système. Par défaut, SQL Server sélectionne le grain le plus fin et ne choisit des grains plus rugueux que s'il peut utiliser la mémoire système de manière plus efficace. SQL Server n'escalade un verrou que si une telle mesure a une influence positive sur les performances générales du système. Comme le montre la figure 1, les escalades surviendront lorsque le nombre de verrous dans une analyse particulière dépasse 5 000 ou lorsque la mémoire utilisée pour les verrous par le système dépasse la capacité disponible :

Figure 1** Conditions qui provoquent l'escalade de verrou **(Cliquer sur l'image pour l'agrandir)

• 24 % de la mémoire non AWE (address windowing extension) utilisée par le moteur de base de données si le paramètre des verrous est défini sur 0
• 40 % de la mémoire non AWE utilisée par le moteur de base de données si le paramètre des verrous n'est pas défini sur 0

Si une escalade survient, elle concerne toujours à un verrou de table.

Éviter les blocages inutiles

Les blocages peuvent survenir sur n'importe quel grain de verrou, mais l'exposition des blocages augmente en cas d'escalade. L'escalade de verrou peut indiquer que votre application n'est pas bien conçue, codée ou configurée.

Il est important de se conformer aux principes fondamentaux concernant la conception des bases de données (par exemple utiliser un schéma normalisé avec des clés étroites et éviter les opérations de données en bloc sur les systèmes transactionnels) pour éviter ces blocages. Si vous ne suivez pas ces règles (en séparant, par exemple, le système de création de rapports du système transactionnel ou en traitant les flux de données en dehors des heures de travail), il vous sera difficile de régler le système.

L'indexation peut être un facteur clé pour déterminer le nombre de verrous nécessaires pour accéder aux données. Un index peut limiter le nombre d'enregistrements auquel accède une requête en réduisant le nombre de recherches internes que le moteur de base de données doit exécuter. Par exemple, lorsque vous sélectionnez une seule ligne d'une table sur une colonne non indexée, chaque ligne de la table doit être temporairement verrouillée, le temps que l'enregistrement désiré soit identifié. En revanche, si cette colonne était indexée, un seul verrou serait requis.

SQL Server 2005 et SQL Server 2008 renferment tous deux des vues de gestion dynamiques (sys.dm_db_missing_index_group_stats, sys.dm_db_missing_index_groups, sys.dm_db_missing_index_details) qui révèlent les tables et les colonnes qui profitent des index, selon les statistiques d'utilisation accumulées.

La fragmentation peut également jouer un rôle dans les problèmes de performances, en ce sens que le moteur de base de données peut avoir besoin d'accéder à plus de pages. En outre, des statistiques inexactes peuvent mener l'optimiseur de requête à choisir un plan moins efficace.

N'oubliez pas que, bien que les index accélèrent l'accès aux données, ils peuvent ralentir la modification des données, car non seulement les données sous-jacentes doivent changer mais les index doivent également être mis à jour. La vue de gestion dynamique sys.dm_db_index_usage_stats souligne la fréquence d'utilisation des index. Un exemple d'indexation inefficace assez courant concerne les index composites où la même colonne est indexée à la fois séparément et en combinaison. Étant donné que SQL Server accède aux index de gauche à droite, l'index sera utilisé tant que les colonnes se trouvant à l'extrême gauche sont utiles.

Les tables de partitionnement peuvent à la fois optimiser le système (afin qu'il y ait moins d'exposition de bloc) et diviser les données en objets physiques séparés qui peuvent être traités séparément. Le partionnement des lignes est la méthode la plus utilisée pour séparer les données, mais le partionnement horizontal des données est une autre option à considérer. Vous pouvez intentionnellement choisir de dénormaliser en divisant une table en plusieurs tables séparées ayant le même nombre de lignes et de clés mais des colonnes différentes afin de réduire les risques associés au fait que des processus séparés puissent souhaiter avoir un accès exclusif aux données en même temps.

Plus une application dispose de plusieurs manières d'accéder à une ligne de données particulière et plus les colonnes pouvant être incluses dans cette ligne sont nombreuses, plus la méthode de partionnement de colonnes est intéressante. La mise en file d'attente d'applications et les tables d'état peuvent parfois profiter de cette approche. SQL Server 2008 ajoutent la capacité à désactiver les escalades de verrous par partition (ou par table lorsque les partitions ne sont pas activées pour la table).

Optimisation des requêtes

L'optimisation des requêtes joue un rôle important dans l'amélioration des performances. Voici trois approches que vous pouvez adopter :

Réduire le temps de transaction L'un des meilleurs moyens de réduire les blocages, et donc d'améliorer les performances générales, est de veiller à ce que les transactions soient aussi brèves que possible. Tout traitement qui n'est pas critique pour l'intégrité de la transaction (tel que la recherche de données apparentées, l'indexation et le nettoyage de données) devrait être supprimé.

SQL traite chaque instruction comme une transaction implicite. Si l'instruction concerne plusieurs lignes, une seule instruction peut constituer une grande transaction, surtout si les colonnes sont nombreuses ou si les colonnes contiennent un grand type de données. Une seule instruction peut également provoquer des partitionnements de page si le facteur de remplissage est élevé ou si une instruction UPDATE remplit une colonne avec une valeur plus grande que la valeur allouée. Dans de telles circonstances, il serait utile de diviser la transaction en groupes de lignes et les traiter l'une après l'autre jusqu'au bout. Le traitement par lot ne devrait être envisagé que lorsque l'instruction individuelle ou les groupes d'instructions peuvent être divisés en lots plus petits qui peuvent toujours être considérés comme une unité de travail complète qu'ils réussissent ou qu'ils échouent.

Séquencer la transaction Au sein de la transaction, le séquencement intentionnel des instructions peut réduire les risques de blocage. Il y a deux principes à garder à l'esprit. Tout d'abord, accédez aux objets dans le même ordre et dans tout le code SQL de votre système. Sans une telle cohérence, des blocages peuvent se produire lorsque deux processus concurrents accèdent aux données dans un ordre différent, provoquant une erreur de système pour l'un des processus. Ensuite, placez les objets faisant l'objet d'un accès fréquent ou qui sont difficilement accessibles à la fin de la transaction. SQL attend que les objets soient requis dans la transaction pour les verrouiller. En retardant l'accès aux zones réactives, vous permettez à ces objets de maintenir les verrous pendant une période de temps plus courte.

Utiliser des indicateurs de verrou Les indicateurs de verrou peuvent être utilisés soit au niveau de la session soit au niveau de l'instruction pour une table ou une vue spécifique. Un exemple typique d'utilisation d'indicateur de verrou au niveau de la session concerne le traitement par lot dans un entrepôt de données durant lequel le développeur sait que le processus sera le seul à être exécuté à un moment donné sur cet ensemble de données. En utilisant une commande telle que SET ISOLATION LEVEL READ UNCOMMITTED au début de la procédure enregistrée, SQL Server ne réservera pas de verrou de lecture, ce qui réduira la surcharge de verrouillage générale et améliorera les performances.

Un exemple typique d'utilisation d'indicateur de verrou au niveau de l'instruction est lorsque le développeur sait qu'une erreur de lecture ou « dirty read » peut se produire sans créer d'incident (par exemple en lisant une seule ligne d'une table dans laquelle d'autres processus simultanés n'auront jamais besoin de cette même ligne) ou lorsque tous les autres efforts de réglage des performances ont échoué (conception de schéma, conception et maintenance d'index et réglage de requête) et que le développeur souhaite forcer le compilateur à utiliser un type d'indicateur spécifique.

Les indicateurs de verrou de ligne peuvent être pratiques si la surveillance montre que des verrous à gros grains ont eu lieu là où très peu d'enregistrements sont concernés par la requête, car cela risque de réduire les blocages. Les indicateurs de verrou de table peuvent être pratiques si la surveillance montre que des verrous à petits grains sont maintenus (et non escaladés) lorsque presque tous les enregistrements de la table sont concernés par la requête, car cela risque de réduire les ressources système nécessaires pour maintenir les verrous. Notez qu'en spécifiant un indicateur de verrou, vous ne garantissez pas que le verrou ne sera pas escaladé lorsque le nombre de verrous atteindra le seuil de la mémoire système. Toutefois, ce faisant, vous empêchez toute autre escalade.

Ajuster votre configuration

Comme le montre la figure 2, il y a plusieurs facteurs à considérer lorsque vous configurez votre système SQL Server.

Figure 2** Comment SQL Server détermine la quantité de mémoire qui peut être utilisée pour le verrouillage **(Cliquer sur l'image pour l'agrandir)

Mémoire Les verrous sont toujours sont tenus dans la mémoire non AWE. En augmentant donc la taille de la mémoire non AWE, vous augmentez la capacité du système à maintenir les verrous.

Une architecture 64 bits doit être votre premier choix lorsque vous essayez d'augmenter la capacité de verrouillage, car l'architecture 32 bits est limitée à 4 Go de mémoire non AWE tandis que celle de 64 bits n'est pas du tout limitée.

Sur les systèmes 32 bits, vous pouvez prendre un gigaoctet de mémoire supplémentaire du système d'exploitation de SQL Server en ajoutant le commutateur /3GB au fichier Boot.ini.

Paramètres de configuration de SQL Server Plusieurs paramètres peuvent être ajustés via la procédure sp_configure qui affecte le verrouillage. Le paramètre des verrous configure le nombre de verrous que le système peut tenir avant de lancer une erreur. Par défaut, le paramètre est défini sur 0, ce qui signifie que le serveur ajustera dynamiquement les verrous réservés, les autres processus essayant tous d'accéder à la mémoire. SQL réservera au départ 2 500 verrous. Chaque verrou consomme 96 octets de mémoire. La mémoire paginée n'est pas utilisée.

Les paramètres de mémoire minimum et maximum réservent la quantité de mémoire utilisée par SQL Server, configurant ainsi le serveur à maintenir statiquement la mémoire. Puisque l'escalade de verrou est liée à la mémoire disponible, la réservation de la quantité de mémoire de processus concurrents peut faire la différence lorsqu'il s'agit de déterminer si les escalades auront lieu ou non.

Paramètres de connexion Par défaut, les verrous qui effectuent le blocage n'expirent pas, mais vous pouvez utiliser le paramètre @@LOCK_TIMEOUT qui provoque une erreur si le seuil d'attente spécifié pour la libération d'un verrou est dépassé.

Indicateurs de trace Deux indicateurs de trace, en particulier, sont associés aux escalades de verrous. Le premier est l'indicateur de trace 1211 qui désactive les escalades de verrous. Si le nombre de verrous consommés dépasse la mémoire disponible, une erreur est lancée. L'autre est l'indicateur de trace 1224 qui désactive les escalades de verrous pour les instructions individuelles.

Surveillance de votre système

Plus d'informations

• Inside Microsoft SQL Server 2005: The Storage Engine par Kalen Delaney
• Comment faire pour surveiller le blocage de SQL Server 2005 et SQL Server 2000
• Script des statistiques de performances SQL Server 2005
• Comment résoudre les problèmes de blocage provoqués par l'escalade de verrou dans SQL Server
• Escalade de verrou (moteur de base de données)

Il est possible de surveiller l'impact des verrouillages et blocages sur les performances générales du système en interrogeant les données d'état à des intervalles donnés (peut-être toutes les heures) et en capturant les statistiques sur les verrous maintenus. Les principales informations à rassembler sont :

• L'objet affecté, le grain et le type de verrou
• La durée des verrous et blocages
• La commande SQL émise (nom de la procédure enregistrée, instruction SQL à l'intérieur)
• Les informations sur la chaîne de blocage, le cas échéant
• Comment le système consomme la capacité de verrouillage à sa disposition

Vous pouvez exécuter un script comme celui de la figure 3 pour capturer ces informations et l'écrire sur une table avec l'horodateur approprié. Et pour décomposer davantage le ResourceId des données bloquées, vous pourriez exécuter un script tel que celui de la figure 4.

Figure 4 En savoir plus sur les données bloquées

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 Capture de statistiques de verrouillage

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

Vous pouvez également surveiller les escalades de votre système par le biais du générateur de profils SQL (événement Lock:Escalation), la vue de gestion dynamique dm_db_index_operational_stats (index_lock_promotion_count) ou l'interrogation régulière des informations de verrouillage du système. Les informations pertinentes à recueillir de la surveillance de l'escalade sont celles qui consistent à savoir si le traitement mérite une escalade. Sinon, les procédures enregistrées pertinentes peuvent préciser une cause à l'origine des problèmes de performances. L'évaluation devrait se concentrer sur les tables renfermant de grandes quantités de données ou qui font l'objet d'une grande utilisation simultanée.

Après avoir rassemblé des données sur les verrouillages, les blocages et les escalades, vous pouvez ensuite analyser les données pour déterminer le bloc cumulatif et la durée du verrouillage (nombre d'incidences multipliées par la durée des incidences) par objet. En général, ceci peut initier un cycle itératif de réglage de performances durant lequel les modifications sont déployées, contrôlées, analysées et réparées. Parfois, il suffit d'une simple modification telle que l'ajout d'un index pour améliorer considérablement les performances et modifier la zone du système qui constitue le goulot d’étranglement des performances le plus difficile.

Vous trouverez plus d'information sur la réduction des blocages dans SQL Server dans l'encadré « Plus d'informations ». Si vous veillez à limiter la taille des transactions durant les phases de conception, codage et stabilisation, vous éliminerez une bonne partie des problèmes de blocages. En outre, l'utilisation de matériel approprié peut réduire considérablement la probabilité d'escalades indésirables. Quoi qu'il en soit, l'évaluation continue des blocages sur le système peut identifier rapidement les problèmes de performances à leur source.

Cherié Warren est responsable de développement senior pour Microsoft IT. Elle est actuellement responsable de l'une des plus grandes bases de données transactionnelles de Microsoft. En outre, Cherié donne fréquemment des conseils sur les causes et problèmes de performances liés au blocage. Elle se spécialise dans les bases de données SQL Server au niveau des entreprises depuis 10 ans.

© 2008 Microsoft Corporation et CMP Media, LLC. Tous droits réservés. Toute reproduction, totale ou partielle, est interdite sans autorisation préalable.