Forum aux questions sur SQLRecherche de verrous, requêtes de grande ampleur, statistiques d'E/S et bien plus encore

Par Nancy Michell

Télécharger le code de cet article: SQLQandA2007_08.exe (151KB)

Q Je dois afficher le nom des objets de toutes les bases de données qui sont verrouillées. Comment faire ?

R Dans SQL Server™ 2000, vous pouvez interroger la table système syslocks dans la base de données master ou exécuter sp_lock pour obtenir les informations de verrouillage actuelles, comme suit :

SELECT * FROM master..syslocks
EXEC sp_lock

Cependant, imaginons que vous vouliez traduire des NOMS d'objets réels de ObjID à partir du RÉSULTAT de la procédure stockée sp_lock (ou à partir de la colonne id dans master..syslocks).

Dans SQL Server 2005 SP1 et versions antérieures, la fonction OBJECT_NAME vous permettait uniquement de transmettre un paramètre : object_id. Ainsi, pour obtenir le nom d'objet correctement, vous deviez travailler dans la base de données ACTUELLE avant d'exécuter OBJECT_NAME. Cela rendait la surveillance des verrous en cours difficile, puisque vous deviez écrire un code personnalisé qui créait une boucle dans chaque base de données pour obtenir l'OBJECT_NAME correct.

USE DBNAME  
SELECT OBJECT_NAME(object_id)

SQL Server 2005 SP2 améliore cette procédure en ajoutant un deuxième paramètre, database_id. Ce nouveau paramètre vous permet d'interroger des noms d'objet quelle que soit la base de données ACTUELLE à laquelle vous êtes connecté.

OBJECT_NAME ( object_id [, database_id ] )

Désormais, vous pouvez interroger sys.dm_tran_locks et récupérer le nom d'objet pour chaque base de données, comme ceci :

SELECT
DB_NAME(resource_database_id),
OBJECT_NAME(resource_associated_entity_id, resource_database_id)
FROM sys.dm_tran_locks
WHERE resource_type='OBJECT'

Notez, cependant, que cela fonctionne uniquement dans SQL Server 2005 SP2 et versions ultérieures. Si vous exécutez cette procédure dans une version antérieure de SQL Server 2005, vous obtiendrez l'erreur suivante :

Msg 174, Level 15, State 1, Line 1
The object_name function requires 1 argument(s).

Un autre exemple, illustré à la figure 1, s'ajoute à sys.dm_exec_sessions pour obtenir des informations sur les ID de processus de service (SPIDS) impliqués dans les verrous. Pour plus d'informations, consultez la documentation SQL Server en ligne à l'adresse technet.microsoft.com/library/ms130214(sql.90).aspx.

Figure 1 Recherche d'ID de processus de serveur

SELECT
DB_NAME(resource_database_id) as DBName,
OBJECT_NAME(resource_associated_entity_id, resource_database_id) AS ObjectName,
request_mode,
request_type,
request_session_id,
es.host_name,
es.login_name,
es.login_time
FROM
sys.dm_tran_locks tl
INNER JOIN sys.dm_exec_sessions es
ON tl.request_session_id=es.session_id
WHERE resource_type='OBJECT'

Q J'ai une large requête SQL dynamique qui semble parfois dépasser la durée de NVARCHAR(max). Existe-t-il une façon de contourner ce problème me permettant de continuer à exécuter une chaîne de grande taille ?

R Si vous dépassez la longueur de NVARCHAR(max), vous obtenez une requête de 2 Go ! Vous devez aussi probablement convertir toutes les chaînes que vous concaténez vers NVARCHAR(max). Cependant, une solution plus commode, qui a également l'avantage d'être efficace dans les versions de SQL Server antérieures à SQL Server 2005, consiste à concaténer plusieurs petites chaînes ensemble. Voici un exemple :

DECLARE @q1 NVARCHAR(4000), @q2 NVARCHAR(4000), @q3 NVARCHAR(4000)
SET @q1 = 'SELECT...'
SET @q2 = 'FROM...'
SET @q3 = 'WHERE...'
EXEC (@q1 + @q2 + @q3)

CONSEIL : Utilisation de la clause OUTPUT

Vous pouvez désormais auditer les modifications que vous effectuez en utilisant des instructions DML (Data Manipulation Language) sans même utiliser des déclencheurs. SQL Server 2005 a introduit une clause OUTPUT intégrée aux instructions DML qui peut vous aider à suivre les modifications effectuées pendant une opération DML. La clause OUTPUT peut enregistrer le jeu de résultats dans une table ou une variable de table.

Sa fonctionnalité est semblable à celle des déclencheurs dans les tables INSERTED et DELETED, accédaient aux lignes modifiées pendant l'opération DML. Pour voir comment cela fonctionne, modifions l'adresse de la table d'adresses en l'inverse de l'adresse originale, comme illustré à la figure ci-dessous. Vous pouvez également utiliser la logique affichée dans ce code pour suivre les modifications apportées aux données et les enregistrer dans une table.

Le résultat de la requête aura la forme suivante :

Original Value:'1234 One Microsoft Way, Redmond, Wa.' has been changed to: '.aW,dnomdeR 
,yaW tfosorciM enO 4321'

Modification d'une adresse

--Create the address table
Create Table Address (ProductID Int, SupplierID Int, Address Varchar(255))

--Insert data
Insert into Address Values (234,567,'1234 One Microsoft Way, Redmond, Wa.')


--Declare a table variable
Declare @Recordchanges table (change Varchar(255))

--Update the address
Update Supplier.Address
Set Address=reverse(address)

--Record the updates into the table variable
OUTPUT 'Original Value:' + DELETED.Address+' has been changed to: '+ INSERTED.Address+'' 
into @RecordChanges

--Query the changes from table variable
Select * from @RecordChanges

Q J'ai une installation SQL Server 2005 en tant que cluster stratégique sur Windows Server® 2003. Le service MS DTC (Microsoft® Distributed Transaction Coordinator) a été « clusterisé » sur le même groupe que le cluster Quorum, mais utilise une ressource disque dédiée (MS DTC partage le même nom de réseau et la même adresse IP que le groupe de clusters Quorum). Je veux aligner cette configuration sur les meilleures pratiques Microsoft. Par conséquent, j'ai besoin de conseils pour déplacer MS DTC vers un groupe de clusters dédiés. Est-il suffisant de supprimer les services MS DTC à l'aide de l'outil d'administration de cluster et de les recréer sur un groupe dédié ?

R Puisque vous disposez déjà d'une ressource disque dédiée pour MS DTC, vous pouvez simplement supprimer la ressource et la créer dans un nouveau groupe. Vous devez aussi créer un nouveau nom de réseau et une adresse IP virtuelle dans le nouveau groupe.

Vous pouvez également créer le nouveau nom de réseau et l'adresse IP dans le groupe de clusters et modifier les dépendances aux nouvelles ressources. Vous pouvez ensuite faire glisser MS DTC vers un nouveau groupe qui prendra le disque dédié ainsi que les nouvelles ressources.

Q Je voudrais afficher les statistiques d'E/S dans les fichiers de base de données physiques d'une base de données. Que dois-je utiliser ?

R La fonction système fn_virtualfilestats, disponible dans SQL Server 2000 et SQL Server 2005, ou sys.dm_io_virtual_file_stats (dans SQL Server 2005 uniquement) vous permet de faire exactement ce que vous souhaitez. Cette fonction renvoie les informations statistiques recueillies depuis le dernier démarrage de l'instance de SQL Server. Un exemple des résultats est illustré à la figure 2.

Figure 2 Affichage des statistiques d'E/S dans une base de données

La compréhension de l'impact d'E/S sur les fichiers de données sous-jacents peut vous aider à mieux planifier des éléments tels que l'emplacement physique des fichiers et des groupes de fichiers sur les volumes de données, en détectant les goulots d'étranglement d'E/S possibles, en effectuant la maintenance de base de données au niveau fichier et d'autres tâches semblables. Cette fonction est particulièrement utile pour examiner l'impact d'E/S pour les bases de données volumineuses comportant plusieurs fichiers et groupes de fichiers.

La requête permettant d'afficher les informations d'E/S des fichiers pour SQL Server 2000 ressemble à ceci :

SELECT *
FROM ::fn_virtualfilestats(default,default)
GO

Pour afficher un databaseID spécifique, transmettez l'ID de la base de données, comme suit :

SELECT *
FROM ::fn_virtualfilestats(7,default)
GO

Voici le code SQL Server 2005 qui affiche les statistiques de fichier pour toutes les bases de données du serveur :

SELECT *
FROM ::fn_virtualfilestats(NULL,NULL)
GO

La requête suivante renvoie les statistiques de fichier pour la base de données actuelle uniquement :

SELECT *
FROM ::fn_virtualfilestats(NULL,NULL)
WHERE DBID=db_id()
GO

Pour SQL Server 2005, il existe également une nouvelle fonction système appelée sys.dm_io_virtual_file_stats, qui est destinée à remplacer par la suite la fonction héritée fn_virtualfilestats :

sys.dm_io_virtual_file_stats( 
{ database_id | NULL },
{ file_id | NULL }
)

Voici comment l'utiliser :

SELECT * FROM sys.dm_io_virtual_file_stats(NULL,NULL)

Si vous souhaitez générer un rapport plus lisible qui présente les noms de base de données et de fichier réels à partir du résultat, vous pouvez utiliser le code fourni dans le téléchargement associé sur SQL Server 2000 ou SQL Server 2005. Le code est disponible sur le site WebTechNet Magazine.

Q J'ai besoin d'un moyen rapide de voir si une transaction de suppression a activé un déclencheur. Savez-vous comment je peux obtenir cette information ?

R Lors de l'utilisation d'un déclencheur qui gère les opérations de suppression, d'insertion et de mise à jour, différentes techniques permettent de déterminer si celui-ci a été activé par une transaction de suppression. Une méthode très prisée consiste à comparer le nombre de tables virtuelles insérées et supprimées pour voir s'ils correspondent. Cependant, il existe une méthode plus simple : Vous pouvez utiliser la fonction Columns_Updated à la place.

En effet, lorsqu'un déclencheur est activé par une transaction de suppression, Columns_Updated renvoie toujours la valeur varbinary 0x. Le contrôle suivant vérifiera que le déclencheur a été activé par une suppression :

IF Columns_Updated() = 0x

CONSEIL : Initialisation des fichiers de données et des fichiers journaux

Saviez-vous que les fichiers de données et les fichiers journaux sont initialisés pour remplacer les données se trouvant toujours sur le disque qui proviennent de fichiers précédemment supprimés ? Les fichiers de données et les fichiers journaux sont d'abord initialisés avec des zéros lorsque vous créez une base de données, ajoutez des fichiers, un journal ou des données à une base de données existante, augmentez la taille d'un fichier existant (opérations de croissance automatique y compris) ou restaurez une base de données ou un groupe de fichiers. L'initialisation des fichiers augmente le temps d'exécution de ces opérations. Toutefois, lorsque les données sont écrites dans le fichier pour la première fois, le système d'exploitation n'a pas besoin de remplir les fichiers de zéros.

Dans SQL Server 2005, les fichiers de données peuvent être initialisés instantanément. Cela permet une exécution rapide des opérations de fichier mentionnées précédemment. L'initialisation instantanée des fichiers récupère l'espace disque utilisé sans remplir cet espace de zéros. Au lieu de cela, le contenu de disque est remplacé à mesure que les nouvelles données sont écrites dans les fichiers. Les fichiers journaux ne peuvent pas être initialisés instantanément. L'initialisation instantanée de fichiers n'est disponible que sur Windows XP Professionnel et Windows Server 2003 ou versions ultérieures.

Comme le contenu du disque supprimé n'est remplacé que lorsque de nouvelles données sont écrites dans les fichiers, une entité de sécurité non autorisée peut accéder au contenu supprimé. Même si le fichier de base de données est attaché à l'instance SQL Server, le risque de divulgation de ces informations est limité par la liste DACL (Discretionary Access Control List) du fichier. Cette liste DACL n'autorise l'accès au fichier qu'à l'administrateur local et au compte de service SQL Server. Cependant, quand le fichier est détaché, un utilisateur ou un service ne bénéficiant pas de l'autorisation SE_MANAGE_VOLUME_NAME peut y accéder. Une menace similaire existe quand la base de données est sauvegardée Un service ou un utilisateur non autorisé peut accéder au contenu supprimé si le fichier de sauvegarde n'est pas protégé par une liste DACL appropriée.

Si le risque de divulgation du contenu supprimé constitue un problème, assurez-vous toujours que les fichiers de sauvegarde et les fichiers de données détachés possèdent des listes DACL restrictives. Désactivez l'initialisation instantanée des fichiers pour l'instance SQL Server en retirant l'autorisation SE_MANAGE_VOLUME_NAME au compte de service SQL Server.

Thanks to the following Microsoft IT pros for their technical expertise: Christian Bolton, Dan Carollo, Robert Davis, Jorge Guzman, Saleem Hakani, Ward Pond, Kalyan Yella, and Paolo Zavatarelli.

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