|
Connaître la manière dont fonctionne SQL Server lorsqu’il exécute une requête est essentiel pour bien appréhender les problématiques de performance et bien comprendre certains problèmes de blocages dans le moteur.
SQL Server est un moteur client-serveur ; lors de la connexion au moteur un dialogue va s’établir entre 2 machines qui communiquent et échangent requêtes contre données. L’application envoie ses requêtes de manière groupées, dans ce qu’on appelle un lot (en anglais batch), au serveur pour exécuter leur contenu. Le lot peut être constitué exclusivement d’une requête, d’une demande d’exécution de procédure stockée ou être beaucoup plus complexe avec des requêtes et des instructions conditionnelles. La taille d’un lot est variable est peut atteindre une taille de plusieurs mégaoctets. Le lot est le message envoyé de l’application cliente au serveur (SQL Server) pour exécution. Une fois la compilation du lot et le plan d’exécution généré, l’exécution prend place et SQL Server renvoie le ou les résultats à l’application qui peut alors demander l’exécution d’un nouveau lot. Dans l’outil Management Studio une fin de lot est symbolisée par le signe GO, qui informe l’outil de l’emplacement a partir duquel il doit considérer qu’un nouveau lot débute et lui indique que le script SQL est à exécuter sous forme de plusieurs lots. D’autres outils acceptent aussi ce séparateur de lots, comme l’utilitaire Sqlcmd ou ses prédécesseurs Osql ou Isql. Par script SQL, on entend un fichier contenant des instructions SQL découpées ou non en lot. Un script SQL peut donc comprendre un ou plusieurs lots et un lot peut contenir de une à un nombre important de requêtes. Le GO est le signe de séparation des lots, là où le point virgule est le séparateur de requête, il marque la fin d’une ligne en SQL. Le contexte du lot permet à SQL Server de valider ou non l’existence d’objets (tables, vues, etc.). La création et l’accès d’un élément dans un même lot risque de conduire à la remontée d’une erreur par le moteur de base de données. Prenons un exemple ; les 2 commandes qui suivent devront être impérativement séparées par GO : create database MaBase use MaBase Faute de quoi, une erreur est levée : Msg 911, Level 16, State 1, Line 2 La base de données 'MaBase' n'existe pas. Assurez-vous que le nom a été saisi correctement. On lui préfèrera le code suivant : create database MaBase go use MaBase go Attention Le signe GO n’est pas obligatoire après l’instruction USE, mais conseillé pour éviter toute erreur d’objet inexistant. Cependant depuis SQL Server 2005, les requêtes d’accès aux données font exceptions à cette règle. SQL Server étant capable de compiler une requête individuellement et non plus exclusivement au niveau du lot comme le faisait les versions précédentes. La création d’une table, suivi d’une insertion de données ne génèrera pas d’arreur : create table dbo.MaTable (champa int) insert dbo.MaTable values(1) SQL Server lors de la réception du lot va compiler l’opération de création de table, mais laissé l’insertion non compilée faute de trouver l’objet associé. La compilation est ainsi différée à l’exécution pour la requête d’insertion. Au moment de l’exécution la table aura déjà été crée, la compilation n’engendrera pas d’erreurs. Notez qu’une version antérieure à 2005 renverrait la même erreur que dans le premier script. Certaines instructions sont obligatoirement isolées dans un lot séparé. Ces instructions concernent toutes des objets contenant du code SQL stocké côté serveur de base de données. En voici la liste : CREATE DEFAULT CREATE FUNCTION CREATE PROCEDURE CREATE RULE CREATE SCHEMA CREATE TRIGGER CREATE VIEW Le signe GO ou la fin du script SQL marque la fin du code qui sera intégré dans l’objet créé sur le serveur. Un lot représentant l’unité d’exécution d’un ensemble de requêtes ou d’instructions SQL, les erreurs sont de fait gérées au niveau du lot et renvoyées au client à la fin de l’exécution du lot si ces dernières n’ont pas été traitées par le code SQL. Par contre lorsque SQL Server rencontre une erreur lors de l’exécution du lot, il ne l’arrêtera pas nécessairement. Seules certaines erreurs de niveau de gravité élevé arrêteront l’exécution. Voyons un lot qui réalise la création de 2 tables Produite et Couleur, ces 2 tables étant liées par une clef étrangère : if db_id('MaBase') is not null drop database MaBase go
create database MaBase go
use MaBase go if object_id('dbo.Produit') is not null drop table dbo.Produit go create table dbo.Produit ( Produit_Id int primary key, Nom varchar(100) not null, Couleur_Id int not null ) go
if object_id('dbo.Couleur') is not null drop table dbo.Couleur go create table dbo.Couleur ( Couleur_Id int primary key, Nom varchar(50) not null ) go
alter table Produit add constraint FK_Couleur foreign key (Couleur_Id) references Couleur(Couleur_Id) go Une fois les tables créées, on insère des données dans ces dernières. insert dbo.Couleur values (1, 'Rouge') insert dbo.Couleur values (2, 'Jaune') insert dbo.Couleur values (2, 'Bleu') insert dbo.Couleur values (3, 'Noir')
select * from dbo.Couleur
insert dbo.Produit values (1, 'Verre', 1) insert dbo.Produit values (2, 'Souris', 1) insert dbo.Produit values (3, 'Téléphone', 4) insert dbo.Produit values (4, 'Bureau', 2)
select * from dbo.Produit go Cette suite d’insertion et de consultation de tables est dans un lot unique. Comme vous pouvez-vous en rendre compte, lors de l’insertion de valeurs dans la table Couleur nous violons la clef primaire de la table en essayant d’insérer 2 fois la clef 2. Dans la table Produit on réalise une violation de la clef étrangère en utilisant une valeur qui n’est pas présente dans la table Couleur.
(1 row(s) affected)
(1 row(s) affected) Msg 2627, Level 14, State 1, Line 3 Violation de la contrainte PRIMARY KEY 'PK__Couleur__0B0589E86F4A8121'. Impossible d'insérer une clé en double dans l'objet 'dbo.Couleur'. L'instruction a été arrêtée.
(1 row(s) affected)
(3 row(s) affected)
(1 row(s) affected)
(1 row(s) affected) Msg 547, Level 16, State 0, Line 10 L'instruction INSERT est en conflit avec la contrainte FOREIGN KEY "FK_Couleur". Le conflit s'est produit dans la base de données "MaBase", table "dbo.Couleur", column 'Couleur_Id'. L'instruction a été arrêtée.
(1 row(s) affected)
(3 row(s) affected) L’exécution du lot a continué malgré les 2 erreurs, seules les 2 commandes d’insertions générant une erreur ont été abandonnées. Mais quand est-il si l’insertion se fait dans une seule et unique instruction comme dans l’exemple suivant. insert dbo.Couleur values (4, 'Rouge'), (5, 'Jaune'), (5, 'Bleu'), (6, 'Noir')
select * from dbo.Couleur
insert dbo.Produit values (5, 'Verre', 2), (6, 'Souris', 5), (7, 'Téléphone', 8), (8, 'Bureau', 1)
select * from dbo.Produit go Dans cette exemple, les erreurs sont les mêmes que dans le premier script d’insertion. Nouveauté SQL Server 2008 L’insertion de lignes multiples dans l’instruction INSERT VALUES est dorénavant possible. Les lignes sont entre parenthèses et séparées par des virgules. Msg 2627, Level 14, State 1, Line 1 Violation de la contrainte PRIMARY KEY 'PK__Couleur__0B0589E86F4A8121'. Impossible d'insérer une clé en double dans l'objet 'dbo.Couleur'. L'instruction a été arrêtée.
(3 row(s) affected) Msg 547, Level 16, State 0, Line 5 L'instruction INSERT est en conflit avec la contrainte FOREIGN KEY "FK_Couleur". Le conflit s'est produit dans la base de données "master", table "dbo.Couleur", column 'Couleur_Id'. L'instruction a été arrêtée.
(3 row(s) affected) Aucunes lignes n’ont été ajoutées ni à la table Couleur ni à la table Produit. L’instruction a à chaque fois été arrêté dans son ensemble, mais le lot a continué sont exécution, la preuve nous vient des 2 consultations de tables qui ont été exécutées et ont toutes 2 renvoyés 3 enregistrements. Les erreurs de violation de clef primaire (erreur 2627) et de violation de clef étrangère (erreur 547) n’abandonnent pas l’exécution du lot. Voyons maintenant un script qui en fonction d’options SET, changent la manière de s’exécuter. DECLARE @i int, @j int;
SET @i = 1; SET @j = 0;
SET ARITHABORT OFF SET ANSI_WARNINGS OFF
SELECT @i / @j
PRINT @i
SET ARITHABORT ON SET ANSI_WARNINGS OFF
SELECT @i / @j
PRINT @i Le code exécute une division entre @i et @j, @j valant 0 il est probable qu’une erreur soir levée. Mais les SET ARITHABORT et SET ANSI_WARNING changent la donne.
----------- NULL Division par zéro.
(1 row(s) affected)
1
----------- Msg 8134, Level 16, State 1, Line 17 Division par zéro. La première division renvoie Null comme valeur, un coup d’œil dans l’aide en ligne sur les 2 commandes SET précitées nous apprend que ce comportement est correct, toutes erreurs arithmétiques renvoient Null et ne lèvent pas d’erreurs si les 2 sont à OFF, l’exécution continue et la commande PRINT s’exécute. La seconde division génère une erreur, là aussi comportement confirmé par l’aide en ligne, mais l’exécution du lot est arrêtée. Le PRINT ne s’est pas exécuté, c’est l’effet attendu lorsque seul ARITH_ABORT est à ON. A noter que certaines erreurs arrêtent le lot de manière inconditionnelle, c’est à tester au cas par cas la documentation étant légèrement avare sur ce sujet. Certaines applications, telles que l’outil Management Studio, envoient systématiquement le lots suivant au serveur que le lot courant est renvoyé des erreurs ou non. Il est très important de bien comprendre ce comportant, car l’exécution de scripts SQL dans Management Studio peut se révéler traitre (notez que le comportement est identique avec l’outil SqlCmd). use master go
if db_id('MaBaseTest') is not null drop database MaBaseTest go
create database MaBaseTest
use MaBaseTest go
-- !!! Attention, vous êtes toujours sur la base de données master !!!
if object_id('dbo.Produit') is not null drop table dbo.Produit go create table dbo.Produit ( Produit_Id int primary key, Nom varchar(100) not null, Couleur_Id int not null ) go
if object_id('dbo.Couleur') is not null drop table dbo.Couleur go create table dbo.Couleur ( Couleur_Id int primary key, Nom varchar(50) not null ) go
-- !!! Produit et Couleur ont été créées dans la base de données master !!! Une erreur se produit dans le 3ème lot censé créer la base de données et se positionner dessus. Mais comme nous l’avons vu au début de cette partie, ceci génère une erreur si les 2 instructions sont placées dans un lot unique. L’outil Management Studio passera alors directement au lot suivant, créant à cette occasion les 2 tables dans la base de données courante, qui est resté Master. Pour éviter de polluer cette base de données système, on effacera les tables. drop table dbo.Produit drop table dbo.Couleur Je ne peux que le répéter, soyez très prudent lors de l’exécution de scripts SQL dans l’outil Management Studio. N’hésitez pas à exécuter une première fois le script bout par bout, pour isoler les erreurs.
|