En tant que développeur SQL Server, vous pouvez souvent avoir besoin de tester vos procédures stockées et vous assurer qu’elles peuvent gérer une charge de travail importante avant de les déployer en production. Bien qu’il existe des outils tiers disponibles pour les tests de stress, ils peuvent être coûteux et ne pas correspondre à votre budget. Dans cet article, nous explorerons une solution rentable en utilisant l’outil SQLQueryStress fourni par Adam Machanic.

Étape 1 : Tester la procédure stockée

Avant de commencer les tests de stress, nous devons nous assurer que notre procédure stockée fonctionne correctement. Dans cet exemple, nous utiliserons la procédure stockée “uspGetEmployeeManagers” de la base de données AdventureWorks2008R2. Vous pouvez la remplacer par votre propre procédure stockée dans votre environnement.

Pour tester la procédure stockée, ouvrez SQL Server Management Studio et exécutez la requête suivante :

EXEC [dbo].[uspGetEmployeeManagers] @BusinessEntityID = 8
GO

Étape 2 : Configurer la connectivité à la base de données

Téléchargez et installez l’outil SQLQueryStress. Une fois installé, ouvrez l’outil et collez le code utilisé pour tester la procédure stockée dans Management Studio.

Ensuite, cliquez sur le bouton “Database” pour configurer la connexion à la base de données. Connectez-vous à votre base de données AdventureWorks en utilisant l’authentification Windows. Une fois connecté, cliquez sur “Test Connection” puis sur “OK” pour enregistrer les paramètres.

Étape 3 : Vider le cache de procédures

Avant d’exécuter la procédure stockée à l’aide de SQLQueryStress, il est recommandé de vider le cache de procédures pour suivre le nombre total d’exécutions de la procédure stockée. Veuillez noter que cela ne doit pas être fait sur un système de production car cela peut entraîner des problèmes de performance.

Pour vider le cache de procédures, exécutez la commande suivante :

DBCC FREEPROCCACHE
GO

Étape 4 : Exécuter la procédure stockée à l’aide de SQLQueryStress

Maintenant que nous avons établi la connexion à la base de données et vidé le cache de procédures, nous pouvons exécuter la procédure stockée à l’aide de SQLQueryStress. Définissez le nombre d’itérations et le nombre de threads sur “un” pour exécuter la procédure stockée une fois.

Cliquez sur le bouton “GO” pour exécuter la procédure stockée. Une fois l’exécution terminée, vous verrez des statistiques telles que les secondes réelles, l’utilisation du processeur, les lectures logiques et le temps écoulé.

Étape 5 : Afficher le nombre total d’exécutions via T-SQL

Pour afficher le nombre d’exécutions de la procédure stockée, exécutez le script T-SQL suivant :

SELECT  DB_NAME(st.dbid) DBNamee
        ,OBJECT_SCHEMA_NAME(st.objectid,dbid) SchemaName
        ,OBJECT_NAME(st.objectid,dbid) StoredProcedure
        ,MAX(cp.usecounts) Execution_count
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE DB_NAME(st.dbid) IS NOT NULL 
    AND cp.objtype = 'proc'
    AND OBJECT_NAME(st.objectid, dbid) LIKE 'uspGetEmployeeManagers'
GROUP BY cp.plan_handle, DB_NAME(st.dbid),
        OBJECT_SCHEMA_NAME(objectid,st.dbid),
        OBJECT_NAME(objectid,st.dbid)
ORDER BY MAX(cp.usecounts) DESC
GO

Ce script fournira le nombre d’exécutions de la procédure stockée. Dans notre cas, puisque nous avons vidé le cache de procédures, le nombre d’exécutions sera de un.

Étape 6 : Ajouter un stress supplémentaire avec plusieurs threads

Pour ajouter un stress supplémentaire à la procédure stockée, nous pouvons modifier les valeurs du nombre de threads et du nombre d’itérations dans SQLQueryStress. Le nombre d’itérations détermine combien de fois la requête sera exécutée pour chaque thread, tandis que le nombre de threads spécifie le nombre de threads concurrents (SPIDs) utilisés pour l’exécution.

Par exemple, si nous définissons le nombre d’itérations et le nombre de threads sur cinq, le nombre total d’itérations effectuées sera de vingt-cinq (5 threads * 5 itérations).

Étape 7 : Utiliser des valeurs aléatoires pour les paramètres

En plus des tests de stress, nous pouvons également utiliser SQLQueryStress pour fournir des valeurs de paramètres aléatoires pour notre procédure stockée. Actuellement, nous utilisons une valeur codée en dur de huit pour le paramètre BusinessEntityID. Pour utiliser des valeurs aléatoires, cliquez sur le bouton “Parameter Substitution” dans SQLQueryStress.

Dans la fenêtre de substitution de paramètres, vous pouvez utiliser un script T-SQL pour générer un ensemble de valeurs pour le paramètre. Par exemple :

SELECT [BusinessEntityID]
FROM [AdventureWorks2008R2].[HumanResources].[Employee]
GO

Sélectionnez la colonne que vous souhaitez mapper sur le paramètre de votre procédure stockée. Cela remplacera la valeur codée en dur par une valeur aléatoire provenant de l’ensemble de valeurs généré.

Étape 8 : Conclusion des résultats

En suivant ces étapes, vous pouvez contrôler la charge de travail et ajouter un stress supplémentaire à vos procédures stockées à l’aide de SQLQueryStress. Si vous capturez une trace SQL et rejouez la charge de travail, vous devriez obtenir des résultats similaires à ceux fournis par l’outil.

N’oubliez pas de toujours tester soigneusement vos procédures stockées avant de les déployer en production pour garantir des performances et une fiabilité optimales.