Éléments d'optimisation et de tuning pour SQL Server 2000 et 2005

Après ouverture du profiler et création d'une nouvelle trace, l'écran ci-dessus apparaît.
Vous voyez ici que vous pouvez utiliser un template, qui vous fournira une configuration existante ou sauvegardée par vous auparavant. Vous pouvez sauvegarder le résultat de la trace dans un fichier ou une table de base de données. Si vous ne faites pas ce choix à cette étape, vous pourrez toujours effectuer cette sauvegarde sur votre trace arrêtée. Vous pouvez également indiquer une heure d'arrêt de la trace. C'est utile pour la faire tourner automatiquement, en exportant son script de création pour le créer sur le serveur et en planifier le lancement : dans le menu File/Fichier.

Le deuxième onglet de la fenêtre de création de trace vous permet de sélectionner les événements à recevoir.

Vous voyez sur la copie d'écran les événements sélectionnés pour le template par défaut. Pour le sujet qui nous intéresse, les événements d'audit de sécurité sont inutiles, vous pouvez donc les enlever, ainsi que les connexions existantes, qui afficheront une suite de lignes au lancement de la session de profilage seulement.

Beaucoup d'événements sont à votre disposition. Vous pouvez les sélectionner en cochant « Show all events ». Voici une liste non exhaustive d'événements intéressants pour le profilage de performances :

Voici un exemple de trace générée par le profiler :

Les colonnes contiennent, dans l'ordre :

• le type d'événement, ici la complétion d'un batch de commandes SQL ;
• le code SQL envoyé au serveur ;
• le login de l'utilisateur ;
• la durée de la requête en temps CPU (millisecondes) ;
• le nombre de pages lues par l'exécution de la requête ;
• le nombre de pages écrites ;
• la durée totale d'exécution en millisecondes ;
• le ClientProcessId, qui correspond à l'id du process de l'application sur la machine cliente (hostprocess dans sysprocesses) ;
• le SPID (System Server Process Id) : l'id de la connexion ouverte sur SQL Server.

Sur un serveur sollicité, l'affichage du profiler peut se remplir de milliers de lignes par seconde. Il est donc utile de filtrer les événements. Cliquez sur le bouton « Column Filters… »

Vous voyez ici un filtre par nom de Login. Vous pouvez aussi filtrer par SPID, pour isoler une connexion (en sachant que le SPID est réattribué de connexion en connexion), par Hostname pour isoler une machine cliente, par ClientProcessId pour isoler une application cliente à travers toutes ses connexions ouvertes.

Le filtrage sur les colonnes numériques vous permet également de ne retourner que les requêtes ayant par exemple consommé plus d'un certain temps CPU, ou effectué un nombre minimum de reads. Cela se révèle très utile pour isoler les requêtes les plus coûteuses.

Nouveauté dans le profiler 2005 : la case à cocher « exclude rows that do not contain values », qui vous permet d'améliorer la pertinence de vos filtres. Dans le profiler 2000, les lignes dont la colonne filtrée a une valeur vide sont affichées quand même.

Vous pouvez également grouper vos événements sur les colonnes de votre choix, ce qui vous permet de changer l'ordre d'affichage des événements et de les regrouper pour un même login, SPID, ou ClientProcessId…

Ceci en cliquant sur le bouton « Organize Columns… »

Le profiler est un outil d'usage quotidien de première importance, qui vous permet à tout moment de vous rendre compte de la charge de votre serveur et d'isoler le code SQL le plus gourmand en ressources. Lorsque vous avez trouvé ces requêtes indésirables, vous pouvez en copier-coller l'appel dans SSMS pour analyser son plan d'exécution.

Le scan de la table diffère du seek. Le scan est le parcours du contenu d'une table ou du dernier niveau d'un index. Une table étant physiquement une liste doublement liée (un pointeur sur l'enregistrement précédent, un pointeur sur l'enregistrement suivant), SQL Server choisit de parcourir chaque enregistrement et d'y tester le critère désiré (s'il y en a un). Si c'est pour trouver une ligne dans une table de dix mille enregistrements, il y a, à l'évidence, un problème d'optimisation. Si la table ne comporte aucun index, c'est le comportement obligatoire de SQL Server, il n'a pas d'autre choix.

Le seek représente le parcours d'un index. Un index est une structure arborescente balancée (b-tree).

Définition de b-tree empruntée à alaide.com :

Méthode de structuration pour le stockage et l'accès aux données. Elle consiste à diviser les possibilités de choix en deux parts égales et établir des pointeurs vers les deux sous-blocs. De cette façon, pour rechercher un élément, on le compare d'abord avec l'élément de la tête de l'arbre. Si l'élément recherché est celui-là, la recherche est terminée, sinon soit elle est plus grande, soit plus petite ; dans l'un ou l'autre cas on continue la recherche dans le sous-bloc correspondant en utilisant le pointeur approprié.

En d'autres termes, si un index est présent et comporte une ou plusieurs colonnes présentes dans le critère de recherche, l'optimiseur peut choisir d'utiliser l'index, donc de parcourir l'arbre pour atteindre le pointeur vers l'enregistrement de la base (en cas d'index non ordonné), au lieu de parcourir chaque enregistrement.

Dans le plan d'exécution visuel, le scan est différencié du seek par l'icône :

Scan de table ;

Scan d'index ;

Seek d'index.

Le scan de table n'intervient que sur une table de type heap (tas…), c'est-à-dire une table qui ne comporte pas d'index ordonné. Un index ordonné force la distribution physique des enregistrements. Un scan d'index ordonné équivaut donc à un scan de table, certes plus efficace lorsqu'on cherche dans la clé de l'index ordonné.

Vous pouvez utiliser la requête suivante (valable pour SQL Server 2005) pour savoir si vos tables comportent ou non un index ordonné :

SELECT t.name as tableName, i.type_desc as tableType
FROM sys.indexes i
JOIN sys.tables t ON i.object_Id = t.object_Id
WHERE i.index_id < 2
ORDER BY t.name

Des statistiques complémentaires vous seront utiles pour juger des performances d'une requête. La principale est le nombre de pages lues par table présente dans la requête. Cette information vous est retournée par SET STATISTICS IO ON.

Exemple de retour de statistiques IO :

Table 'sysidxstats'. Scan count 6, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Sur chaque ligne de ces statistiques, vous obtenez le nom de la table, le nombre de scans effectués sur la table, et les nombres de lectures. Pour vos besoins d'optimisation, les lectures logiques vous donnent l'information dont vous avez besoin.

L'unité ici est la page. La page SQL Server est l'unité de base et en même temps l'unité maximale de stockage d'un enregistrement (avec quelques exceptions en SQL Server 2005 notamment pour les colonnes de type varchar). Elle est dimensionnée à 8 ko. Une page peut contenir plusieurs enregistrements, mais un enregistrement ne peut pas être contenu dans plus d'une page, ce qui limite concrètement la taille de vos enregistrements à 8060 octets (les blobs text et images sont stockés de façon différente, et SQL Server 2005 offre un mécanisme pour s'affranchir de cette limite pour les colonnes de type (n)varchar et varbinary). Lorsque le moteur de stockage de SQL Server lit des enregistrements sur le disque ou en cache, il lit des pages.

La lecture sur disque est l'opération la plus coûteuse d'une requête, il est donc important de limiter le nombre de pages lues, en s'assurant d'avoir assez de RAM pour permettre à SQL Server de stocker les pages les plus fréquemment accédées dans son cache, et plus important, en limitant le nombre de pages lues par la création d'index ou l'optimisation du code SQL.

Les lectures logiques affichées par SET STATISTICS IO ON vous indiquent le nombre total de pages lues sur chaque table impliquée dans la requête. Concentrez-vous sur les nombres les plus élevés, observez les parties du plan d'exécution qui affichent les opérations sur ces tables, et si vous y voyez des scans, réfléchissez à la possibilité de créer un index.

Les flèches qui relient les différentes opérations représentent le flux de l'information extraite du moteur de stockage et traité pour obtenir le résultat de la requête. Ce flux se lit de droite à gauche, le résultat final de la requête se trouvant en haut à gauche du graphique. L'épaisseur des flèches vous permet en un coup d'œil de déterminer les opérations les plus coûteuses. Plus la flèche est épaisse, plus l'opération d'où elle provient a retourné de lignes. Considérez l'exemple ci-dessous :

Vous pouvez voir que la flèche du bas est bien plus épaisse que celle du haut. Simplement parce que l'opération du bas (le Hash Join de droite) retourne 56 000 lignes, alors que la parallélisation du haut traite 26 lignes. Concentrez-vous sur les sources de flèches les plus grosses en priorité pour améliorer les performances de votre requête.

Soudainement, vous voyez le ou les CPU de votre serveur monter en flèche. Vous lancez une session de Profiler, et malgré cela vous n'arrivez pas à identifier la source de la surcharge. Vous pouvez. Grâce au Performance Monitor, identifier quelle connexion utilise le processeur intensivement. Comme c'est une opération qui prend un peu de temps, elle est surtout utile pour une connexion qui se conduit mal pendant au moins plusieurs minutes. Sélectionnez, dans les indicateurs de Threads, les instances sqlservr/# pour les compteurs % Processor Time et ID Thread. Vous risquez malheureusement d'en avoir beaucoup sur un serveur de production.

Affichez ensuite le résultat sous forme de rapport (CTRL+R) afin d'améliorer la lisibilité.

Si vous identifiez un processus qui maintient un pourcentage élevé de CPU, notez son ID Thread. Il correspond à la colonne kpid de la table système sysprocesses de la base master de SQL Server 2000, ou de la vue système sys.sysprocesses dans SQL Server 2005. Voici un exemple de requête pour obtenir les informations de processus :

SELECT blocked, lastwaittype, waittime, DB_NAME(dbid) as db, hostname, loginame, cmd, last_batch, cpu
FROM master.sys.sysprocesses 
WHERE kpid = 1792

Pour certaines requêtes qui retournent peu de colonnes, la technique de l'index couvrant (covering index) peut améliorer très nettement les performances, en évitant l'opération lourde du bookmark lookup. Couvrir une requête, c'est créer un index non ordonné dont la clé est composée de toutes les colonnes retournées par la requête, en plaçant bien sûr les colonnes cherchées par la clause WHERE en premier. Voici un exemple en trois temps, d'un SELECT sur une table de la base de démonstration de Microsoft : AdventureWorks. Comparons les plans d'exécution d'une même requête :

SELECT CarrierTrackingNumber, UnitPrice
FROM Sales.SalesOrderDetail 
WHERE UnitPrice = 5.7

Ce qui doit retourner 210 lignes sur un total de 121 317.

Premièrement, en laissant les index par défaut sur la table :

Vous noterez que, à cause de l'absence d'index sur la colonne UnitPrice, l'optimiseur est obligé de choisir un scan de la table. Donc, créons un index sur cette colonne :

CREATE NONCLUSTERED INDEX nix$Sales_SalesOrderDetail$UnitPrice
ON Sales.SalesOrderDetail (UnitPrice)

Notons au passage que cette opération est aussi traitée par l'optimiseur, à son propre plan d'exécution :

Remarquez l'épaisseur des flèches. 121 317 lignes sont à traiter.

Lançons ensuite la même requête :

Ici, une forme de bookmark lookup adaptée aux tables comportant un index ordonné. SQL server 2005 choisit de parcourir l'index ordonné pour chaque ligne trouvée depuis l'index que nous venons de créer. Chaque enregistrement trouvé dans l'index est recherché dans la table par l'index ordonné. Cela provoque donc 210 parcours de l'index ordonné.

Modifions notre index pour couvrir les besoins de la requête :

DROP INDEX Sales.SalesOrderDetail.nix$Sales_SalesOrderDetail$UnitPrice
GO
CREATE NONCLUSTERED INDEX nix$Sales_SalesOrderDetail$UnitPrice_CarrierTrackingNumber
ON Sales.SalesOrderDetail (UnitPrice, CarrierTrackingNumber)
GO

Réexécutons :

Ici toutes les colonnes de la requête ont pu être servies par l'index. SQL server n'a aucun besoin d'aller chercher des informations supplémentaires dans la table.

Une erreur commune, qui peut affecter fortement les performances, est d'écrire du code SQL avec la même approche intellectuelle que pour l'écriture de code procédural. Par exemple, en encapsulant une partie de la logique d'un SELECT dans une fonction (UDF, ou User Defined Function).

Comme nous l'avons vu, le plan d'exécution d'une requête SQL est optimisé à l'aide des informations que le moteur SQL détient sur le système et la structure des données. Sachant cela, il est important en écrivant son code de faciliter le travail de l'optimiseur pour qu'il puisse créer le meilleur plan d'exécution possible.

Par exemple, dans un SELECT, en déplaçant une partie des tables recherchées dans une fonction, on enlève à l'optimiseur la possibilité de comprendre les relations entre les tables. On oblige donc l'optimiseur à passer dans la fonction pour chaque ligne retournée par le SELECT, ce qui se révèle très contre-productif.

Prenons un exemple simple, à l'aide des tables d'AdventureWorks. Pour obtenir le total des taxes payées par territoire, la requête suivante remplit parfaitement son office :

SELECT    st.Name as Territory, SUM(soh.TaxAmt) as TaxAmt
FROM    Sales.SalesOrderHeader soh 
JOIN    Sales.SalesTerritory st ON soh.TerritoryID = st.TerritoryID
GROUP BY st.Name
ORDER BY st.Name

Pas très sophistiqué n'est-ce pas ? Pourquoi ne pas encapsuler cette recherche de total dans une fonction que nous pourrons réutiliser ?

CREATE FUNCTION Sales.GetTaxAmtPerTerritory (@TerritoryID int)
RETURNS money
AS BEGIN
    DECLARE @TaxAmt money
 
    SELECT @TaxAmt = SUM(TaxAmt)
    FROM Sales.SalesOrderHeader
    WHERE TerritoryID = @TerritoryID
 
    RETURN @TaxAmt
END

C'est le bonheur, nous avons rempli notre devoir de programmeur en faisant du code réutilisable. Relançons notre requête :

SELECT    st.Name as Territory, Sales.GetTaxAmtPerTerritory (st.TerritoryID) as TaxAmt
FROM    Sales.SalesTerritory st 
ORDER BY st.Name

Si l'idée nous prend de comparer les performances des deux requêtes, voici ce que nous obtenons :

La première version (SUM et JOIN) dure une seconde et effectue 1227 lectures de pages. La seconde 7052 lectures de pages en un peu moins de six secondes. CQFD.

Une requête de type SELECT pour des verrous partagés (shared locks) sur les données lues. Ces verrous partagés permettent de garantir le niveau d'isolation par défaut de la transaction qui est READ COMMITTED. En français, cela veut dire que lorsque vous lisez un enregistrement, vous avez la garantie qu'il est propre, et que personne n'essaie de la modifier au même moment. Il existe cinq (quatre dans SQL server 2000) niveaux d'isolation. Deux sont plus contraignants que l'isolation par défaut, et deux moins contraignants.

Vous pouvez baisser le niveau d'isolation pour permettre la lecture d'enregistrements « sales » (dirty reads), c'est-à-dire de lignes qui sont potentiellement en modification, qui font partie d'une transaction non encore terminée, ou qui sont en cours de rollback. Vous prenez de ce fait le risque d'obtenir des données erronées. En contrepartie, baisser votre niveau d'isolation vous permet d'augmenter les accès concurrentiels et dans certains cas d'augmenter les performances de traitement sur des serveurs très sollicités. Le niveau d'isolation permettant les dirty reads s'appelle READ UNCOMMITTED.

En READ UNCOMMITTED, une requête SELECT n'honore pas les verrous posés sur les enregistrements lus, et à son tour ne pose pas de verrous partagés. En me répétant, cela augmente les performances mais diminue la fiabilité de vos lectures de données. Utilisez donc ce niveau d'isolation en sachant ce que vous faites.

Pour indiquer à SQL server d'utiliser le niveau d'isolation READ UNCOMMITTED, vous pouvez soit modifier le niveau d'isolation de la connexion :

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

En le remettant dans l'état où vous l'avez trouvé à la fin de votre code (notamment dans une procédure stockée) :

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

Vous pouvez aussi le spécifier dans votre requête pour une table en particulier :

SELECT    st.Name as Territory, SUM(soh.TaxAmt) as TaxAmt
FROM    Sales.SalesOrderHeader soh WITH (READUNCOMMITTED)
JOIN    Sales.SalesTerritory st ON soh.TerritoryID = st.TerritoryID
GROUP BY st.Name
ORDER BY st.Name

Le mot-clé READUNCOMMITTED est un synonyme de NOLOCK. Vous verrez sans doute plus souvent WITH (NOLOCK). READUNCOMMITTED est simplement SQL ANSI, NOLOCK est un mot-clé T-SQL. Les effets sont strictement identiques.

SQL Server 2005 introduit un niveau d'isolation plus concurrentiel encore, appelé SNAPSHOT. Basé sur une technologie de version de ligne (row versioning), il nécessite que l'option ALLOW_SNAPSHOT_ISOLATION de votre base de données soit à ON. Pour faire simple, une copie des données est stockée dans une table temporaire de tempdb, avec une information de version. Lorsque vous lisez les données dans ce niveau d'isolation, cette version des lignes est créée et utilisée, et aucun verrou n'est posé sur la table originale. De plus, la lecture dans une transaction de niveau SNAPSHOT est consistante avec l'état des données au moment de la création de cette transaction. En interne, SQL server conserve une version des données au moment de la création de votre transaction.

Si vous modifiez des données dans ce niveau d'isolation, SQL Server adopte un mode de verrouillage optimiste, et les lignes à modifier ne seront verrouillées qu'au moment de la modification. À ce moment, si les lignes ont été modifiées entre temps (SQL server le sait, toujours grâce à son mécanisme de row-versioning), votre transaction sera automatiquement annulée (donc votre mise à jour). Il est donc plus prudent de limiter l'utilisation de ce niveau d'isolation à la lecture de données.