Générateur de profils SQL Server Le Générateur de profils SQL

Générateur de profils SQL Server
Le Générateur de profils SQL Server indique comment SQL Server résout les requêtes de manière interne. Les
administrateurs peuvent ainsi voir exactement quelles sont les expressions multidimensionnelles ou les
instructions Transact-SQL qui sont soumises au serveur et comment celui-ci accède à la base de données ou au
cube pour renvoyer des ensembles de résultats.
Le Générateur de profils SQL Server vous permet d'effectuer les opérations suivantes :
Créer une trace basée sur un modèle réutilisable
Observer les résultats de la trace au fil de son exécution
Stocker les résultats de la trace dans une table
Démarrer, arrêter, suspendre et modifier les résultats de la trace en fonction des besoins
Utilisez le Générateur de profils SQL Server pour surveiller uniquement les événements qui vous intéressent.
Si les traces deviennent trop volumineuses, vous pouvez les filtrer en fonction des informations de votre choix,
de manière à ce que seul un sous-ensemble des données d’événement soit recueilli. La surveillance d'un trop
grand nombre d'événements s'ajoute à la charge du serveur et du processus de surveillance, et peut
considérablement accroître la taille du fichier ou de la table de trace, en particulier si le processus de surveillance
se prolonge sur une période importante.
Modèles prédéfinis
Outre le modèle standard (celui par défaut), le Générateur de profils SQL Server contient divers modèles
prédéfinis permettant de surveiller certains types d'événements. Le tableau ci-dessous répertorie les modèles
prédéfinis, leur fonction et les classes d'événements pour lesquelles ils capturent des informations.
Nom du modèle Fonction Classe d'événements
SP_Counts
Capture le comportement de l'exécution des procédures stockées dans le temps.
SP:Starting
Standard
Point de départ générique de création d'une trace. Capture toutes les procédures stockées et tous les lots
Transact-SQL exécutés. Permet de surveiller l'activité générale du serveur de base de données.
Audit Login
Audit Logout
ExistingConnection
RPC:Completed
SQL:BatchCompleted
SQL:BatchStarting
TSQL
Capture toutes les instructions Transact-SQL soumises à SQL Server par les clients, ainsi que l'heure de leur
émission. Permet de déboguer les applications clientes.
Audit Login
Audit Logout
ExistingConnection
RPC:Starting
SQL:BatchStarting

TSQL_Duration
Capture toutes les instructions Transact-SQL soumises à SQL Server par les clients, leur délai d'exécution (en
millisecondes) et les regroupe en fonction de la durée. Permet d'identifier les requêtes lentes.
RPC:Completed
SQL:BatchCompleted
TSQL_Grouped
Capture toutes les instructions Transact-SQL soumises à SQL Server et leur heure d'émission. Regroupe les
informations en fonction du client ou de l'utilisateur qui a émis l'instruction. Permet d'analyser les requêtes d'un
client ou d'un utilisateur.
Audit Login
Audit Logout
ExistingConnection
RPC:Starting
SQL:BatchStarting
TSQL_Replay
Capture des informations détaillées sur les instructions Transact-SQL, qui sont nécessaires si la trace doit être
réexécutée. Permet d'effectuer un paramétrage itératif, tel qu'un test d'évaluation.
CursorClose
CursorExecute
CursorOpen
CursorPrepare
CursorUnprepare
Audit Login
Audit Logout
Connexion existante
RPC Output Parameter
RPC:Completed
RPC:Starting
Exec Prepared SQL
Prepare SQL
SQL:BatchCompleted
SQL:BatchStarting
TSQL_SPs
Capture des informations détaillées sur toutes les procédures stockées en cours d'exécution. Permet d'analyser les
étapes composantes des procédures stockées. Ajoutez l'événement SP:Recompile si vous pensez que des
procédures sont recompilées.
Audit Login
Audit Logout
ExistingConnection
RPC:Starting
SP:Completed
SP:Starting
SP:StmtStarting
SQL:BatchStarting
Tuning
Capture des informations sur l'exécution des procédures stockées et des lots Transact-SQL. Permet de produire la
sortie de trace que l'Assistant Paramétrage du moteur de base de données peut utiliser comme charge de travail
pour régler les bases de données.
RPC:Completed
SP:StmtCompleted
SQL:BatchCompleted

Modèle par défaut
Le Générateur de profils SQL Server définit automatiquement le modèle standard comme modèle par défaut
appliqué aux nouvelles traces. Toutefois, vous pouvez remplacer le modèle par défaut par n'importe quel autre
modèle prédéfini ou défini par l'utilisateur. Pour modifier le modèle par défaut, activez la case à cocher Utiliser
comme modèle par défaut pour le type de serveur sélectionné lorsque vous créez ou modifiez un modèle en
utilisant l'onglet Général de la boîte de dialogue Propriétés du modèle de trace.
Interrogation des vues de gestion dynamique
EXEMPLE : SELECT * FROM sys.dm_tran_locks
Déclencheurs DDL
Les déclencheurs DDL sont un type spécial de déclencheur qui s'active en réponse à des instructions DDL (Data
Definition Language). Ils peuvent être utilisés pour effectuer des tâches d'administration de la base de données,
tels que l'audit et la régulation des opérations de base de données.
L'exemple ci-dessous crée un déclencheur qui s'exécute chaque fois qu'un utilisateur exécute la commande
CREATE TABLE dans la base de données active.
CREATE TRIGGER CreateTable
ON DATABASE
FOR CREATE_TABLE
AS
...
Description des déclencheurs DDL
Comme les autres déclencheurs, les déclencheurs DDL lancent des procédures stockées en réponse à un
événement. À la différence des déclencheurs DML, ils ne réagissent pas aux instructions UPDATE, INSERT et
DELETE appliquées à une table ou à une vue. Ils sont en revanche réactifs à beaucoup d'événements DDL (Data
Definition Language). Ces événements correspondent principalement à des instructions Transact-SQL qui
commencent par les mots clés CREATE, ALTER et DROP. Certaines procédures stockées système qui
effectuent des opérations de type DDL peuvent également activer des déclencheurs DDL.

Important :
1 - Testez vos déclencheurs DDL afin de déterminer leurs réponses aux procédures stockées système qui sont
exécutées. Par exemple, l'instruction CREATE TYPE et la procédure stockée sp_addtype activeront toutes deux
un déclencheur DDL créé sur un événement CREATE_TYPE. Toutefois, la procédure stockée sp_rename
n'active pas de déclencheur DDL.
2 - Les déclencheurs DDL ne s'activent qu'après l'exécution des instructions DDL de déclenchement. Les
déclencheurs DDL ne peuvent pas être utilisés comme déclencheurs INSTEAD OF.
PS : Les déclencheurs DDL peuvent être utilisés dans des tâches d'administration telles que l'audit et la
régulation d'opérations de base de données.
Utilisez des déclencheurs DDL dans les cas suivants :
Vous voulez empêcher certaines modifications sur votre schéma de base de données.
Vous voulez qu'un événement se produise dans la base de données en réponse à une modification du schéma.
Vous voulez enregistrer des modifications ou des événements dans le schéma de la base de données.
L'exemple suivant illustre l'utilisation d'un déclencheur DDL qui empêche la modification et la suppression de
toutes les tables d'une base de données.
CREATE TRIGGER safety
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
PRINT 'You must disable Trigger "safety" to drop or alter tables !'
ROLLBACK ;
Conception des déclencheurs DDL
PS : Avant de pouvoir concevoir un déclencheur DDL, les conditions suivantes sont requises :
Vous devez comprendre l'étendue des déclencheurs DDL.
Vous devez déterminer l'instruction ou le groupe d'instructions Transact-SQL qui active le déclencheur.
NB : Les déclencheurs DDL peuvent être activés en réponse à un événement Transact-SQL traité dans la base de
données actuelle ou sur le serveur actuel. L'étendue du déclencheur dépend de l'événement. Par exemple, un
déclencheur DDL créé pour être activé en réponse à un événement CREATE TABLE se déclenchera à chaque
fois qu'un événement CREATE TABLE se produit dans la base de données. Un déclencheur DDL créé pour être
activé en réponse à un événement CREATE LOGIN se déclenchera à chaque fois qu'un événement CREATE
LOGIN se produit sur le serveur.
Les déclencheurs DDL dont l'étendue est la base de données sont stockés sous forme d'objets dans la base de
données où ils sont créés. Les déclencheurs DDL peuvent être créés dans la base de données master ; ils se
comportent exactement comme ceux créés dans les bases de données conçues par l'utilisateur. Des informations
sur les déclencheurs DDL sont accessibles dans l'affichage catalogue sys.triggers du contexte de base de données
dans lequel ils sont créés, ou en spécifiant le nom de la base de données comme identificateur, par exemple
master.sys.triggers.
Les déclencheurs DDL dont l'étendue est le serveur sont stockés sous forme d'objets dans la base de données
master. Toutefois, les informations sur les déclencheurs DDL dont l'étendue est le serveur sont accessibles dans
la vue de catalogue sys.server_triggers de tous les contextes de base de données.

Dans l'exemple suivant, un déclencheur DDL affiche un message si un événement CREATE DATABASE se
produit sur l'instance du serveur actuelle. Il utilise la fonction EVENTDATA pour extraire le texte de
l'instruction Transact-SQL correspondante.
IF EXISTS (SELECT * FROM sys.server_triggers
WHERE name = 'ddl_trig_database')
DROP TRIGGER ddl_trig_database
ON ALL SERVER
GO
CREATE TRIGGER ddl_trig_database
ON ALL SERVER
FOR CREATE_DATABASE
AS
PRINT 'Base de données créée.'
SELECT
EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
GO
DROP TRIGGER ddl_trig_database
ON ALL SERVER
GO
EVENTDATA
Les informations sur un événement qui lance un déclencheur DDL sont capturées à l'aide de la fonction
EVENTDATA. Cette fonction retourne une valeur xml. Le schéma XML inclut des informations sur les
éléments suivants :
l'heure de l'événement ;
le SPID (System Process ID) de la connexion lorsque le déclencheur s'est exécuté ;
le type d'événement qui a lancé le déclencheur.
Attention :
EVENTDATA capture les données des événements CREATE_SCHEMA ainsi que l'<élément_de_schéma> de
la définition CREATE SCHEMA correspondante, s'il existe. En outre, EVENTDATA reconnaît la définition
<élément_de_schéma> en tant qu'événement distinct. En conséquence, un déclencheur DDL créé à la fois sur un
événement CREATE_SCHEMA et sur un événement représenté par l'<élément_de_schéma> de la définition
CREATE SCHEMA, peut retourner deux fois les mêmes données d'événement, telles que les données
TSQLCommand.
Implémentation de déclencheurs DDL
Les déclencheurs DDL au niveau du serveur figurent dans le dossier Déclencheurs de l'Explorateur d'objets de
SQL Server Management Studio. Ce dossier se situe sous le dossier Objets serveur. Les déclencheurs DDL au
niveau de la base de données figurent dans le dossier Database Triggers. Ce dossier se situe sous le dossier
Programmability de la base de données correspondante.
Modification de déclencheurs DDL
Si vous devez modifier la définition d'un déclencheur DDL, vous pouvez soit l'annuler, puis le recréer, soit
redéfinir le déclencheur existant en une seule opération.
Si vous changez le nom d'un objet référencé par un déclencheur DDL, vous devez modifier le déclencheur pour
que sa définition se réfère au nouveau nom de l'objet. Par conséquent, avant de renommer un objet, affichez les
dépendances de l'objet pour savoir si des déclencheurs peuvent être concernés par la modification projetée.
Un déclencheur peut aussi être modifié pour en chiffrer la définition.
L'exemple suivant crée un déclencheur DML qui envoie un message défini par l'utilisateur au client lorsqu'un
utilisateur tente d'ajouter ou de modifier les données de la table SalesPersonQuotaHistory. Le déclencheur est
ensuite modifié à l'aide de l'instruction ALTER TRIGGER pour n'appliquer le déclencheur qu'aux activités