Support Center
Explorez, Analysez & Documentez votre Code

Générer et Exporter les Données de Performance SQL Server dans un fichier

Visual Expert effectue le suivi des performances de votre code Transact-SQL et produit une analyse de son exécution. Ces informations aident les développeurs T-SQL à optimiser les performances de leur code.

Cette fonctionnalité repose sur les données d'exécution du code générées par votre base de données, et qui sont ensuite chargées dans votre projet Visual Expert. VisuVisual Expert combinera ensuite ces données avec les informations extraites lors de l'analyse statique de votre code.

La génération de ces données d'exécution du code dépend de votre configuration :

  • Si Visual Expert peut se connecter à votre SQL Server, vous n'avez pas besoin de suivre cette procédure. Suivez simplement la procédure décrite dans cet article.
  • Si Visual Expert NE PEUT PAS se connecter à SQL Server - par exemple avec une base de données de production hébergée dans un environnement séparé - suivez la procédure ci-dessous pour générer un fichier contenant les données requises, et l'inclure dans votre projet Visual Expert.
  1. Connectez-vous à la base de données SQL Server où vous souhaitez créer et exécuter la procédure stockée writePerformanceDataToFile (procédure qui lit les données de performance à partir du ou des fichiers de session d'événement et écrit dans un fichier "*.VETSQLPERF")

  2. Créer la procédure WritePerformanceDataToFile (exécutez les script de la procédure depuis VEPerfUtils_TSQL.SQL)

  3. Ouvrez l'invite de commande et collez la commande ci-dessous :
    4. bcp "exec [{DataBaseName}].[{schema}].[WritePerformanceDataToFile] @databaseName = 
N'{DatabaseName1,DatabaseName2,...}', @recordsLimit = {NULL|any number}" queryout 
"{output file full path}.VETSQLPERF" -w {-T|-U "{username}" -P "{password}"} -S 
"{server\instance}"

    Cette opération exécutera la procédure stockée - WritePerformanceDataToFile - et écrira le résultat dans un fichier.

Exemples de commande :

  1. Pour écrire tous les enregistrements de plusieurs bases de données en utilisant une connexion sécurisée.
    2. bcp "exec [MyDb].[dbo].[WritePerformanceDataToFile] @databaseName = N'SalesDB, TestDB', 
@recordsLimit = NULL" queryout "D:\VEPerformanceFiles\MultipleDB.VETSQLPERF" -w -T
  2. Pour écrire tous les enregistrements d'une base de données unique en utilisant une connexion sécurisée.
    3. bcp "exec [MyDb].[dbo].[WritePerformanceDataToFile] @databaseName = N'TestDB', 
@recordsLimit = NULL" queryout "D:\VEPerformanceFiles\SingleDB.VETSQLPERF" -w -T
  3. Pour écrire un maximum de 10 000 enregistrements pour une seule base de données en utilisant une connexion sécurisée.
    4.  bcp "exec [MyDb].[dbo].[WritePerformanceDataToFile] @databaseName = N'TestDB', 
@recordsLimit = 10000" queryout "D:\VEPerformanceFiles\SingleDB.VETSQLPERF" -w -T
  4. For writing all records for Single Database using a login ID "SA" used to connect to SQL Server
    5. bcp "exec [MyDb].[dbo].[WritePerformanceDataToFile] @databaseName = N'TestDB', 
@recordsLimit = NULL" queryout "D:\VEPerformanceFiles\SingleDB.VETSQLPERF" -w -U "
TEST" -P "test user password" -S "server\SQL-Server-instance"
  5. Pour écrire tous les enregistrements de plusieurs bases de données en utilisant un identifiant de connexion "TEST" utilisé pour se connecter à SQL Server
    6. bcp "exec [MyDb].[dbo].[WritePerformanceDataToFile] @databaseName = N'SalesDB, TestDB', 
@recordsLimit = NULL" queryout "D:\VEPerformanceFiles\MultipleDB.VETSQLPERF" -w -U 
"TEST" -P "test user password" -S "server\ SQL-Server-instance"

Note relative aux paramètres :

  1. {DataBaseName} : Écrire le nom de la base de données contenant la procédure WritePerformanceDataToFile.
  2. {schema}: Nom du schéma contenant la procédure WritePerformanceDataToFile.
  3. @databaseName: Liste des noms de bases de données séparés par des virgules pour lesquels les données de performance doivent être lues.

    Par Exemple : 
    @databaseName = N'{DatabaseName1,DatabaseName2}' -- multiple databases
    Ou
    @databaseName = N'{DatabaseName1}' -- single database
  4. @recordsLimit : Nombre total d'enregistrements à lire et à écrire dans le fichier (NULL = tous les enregistrements, nombre quelconque = nombre total d'enregistrements)

    Par Exemple : 
    @recordsLimit = NULL -- all records will be read and written to output file
    Ou 
    @recordsLimit = 10000 -- maximum 10000 records will be read and written to output file
  5. queryout: Chemin complet du fichier final avec l'extension .VETSQLPERF
    (Veuillez noter que VE ne reconnait que les fichiers portant l'extension .VETSQLPERF pour l'analyse)

    Note :Veuillez vous assurer que le répertoire indiqué dans le chemin du fichier existe bien, sinon bcp indiquera une erreur.

    Par Exemple : 
    queryout "D:\VEPerformanceFiles\alldatabase.VETSQLPERF"
    Ou 
    queryout "D:\VEPerformanceFiles\SalesDB.VETSQLPERF"
  6. -U {username}: nom d'utilisateur pour se connecter à SQL Server afin d'exécuter la procédure stockée WritePerformanceDataToFile.

    Par Exemple : 
    For "sa" USER -U "sa"
  7. -P {password}: Mot de passe pour le nom d'utilisateur spécifié à l'étape précédente
    Note : Pour se connecter à SQL Server avec une connexion sécurisée utilisant la sécurité intégrée, l'utilisateur peut écrire -T au lieu de -U et -P

  8. -S "{server\instance}": Préciser la machine/serveur et l'instance SQL Server
    Exemples :
    • Pour une instance SQL Express sur une machine locale 
      -S "(local)\SQLExpress"
    • Pour un serveur nommé (par exemple, DB-SERVER1) et une instance SQL Server nommée (par exemple, SQLSERVER2019) 
      -S "DB-SERVER1\SQLSERVER2019"

Pour plus de détails sur l'utilisation de bcp, veuillez consulter : https://docs.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-ver15

bcp "exec [TestDatabase].[dbo].[WritePerformanceDataToFile] @databaseName = N'TestDB1', 
@recordsLimit = NULL" queryout "D:\VEPerformanceFiles\SingleDB.VETSQLPERF" 
-w -U "SA" -P "testpassword" -S "(local)"

Autorisations requises pour exécuter une procédure stockée à l'aide de l'utilitaire bcp :

  1. GRANT VIEW SERVER STATE to {user} 
USE master
GO
GRANT VIEW SERVER STATE TO test;

  2. GRANT EXECUTE ON {database}.{schema}.[WritePerformanceDataToFile] to {user};
Use {database}
Go
GRANT EXECUTE to [test];

Capture d'écran d'une exécution réussie :

Permissions required to execute a stored procedure using bcp utility  

Une fois que le fichier de performance est prêt, il peut être consommé par Visual Expert.
Pour cela, suivez les étapes ci-dessous :

  1. Ouvrez votre projet SQL Server dans Visual Expert.
  2. Depuis le menu ruban, accédez à Paramètres > Gérer votre code source.
    Generate and Export T-SQL Performance Data in a file

  3. Cliquez sur "Ajouter" pour ajouter votre fichier de données de performance (*.VETSQLPERF) comme nouvel emplacement de code source.
    Add T-SQL Performance Data file
  4. Dans l'assistant de projet Visual Expert, sélectionnez "dossiers et/ou fichiers" > localisez le répertoire 'PERFDATADIR' > puis sélectionnez le fichier *VETSQLPERF pour l'ajouter comme nouvel emplacement de code source. Cliquez sur Terminer.
    Add SQL Server Performance Data file
  5. Une nouvelle entrée est ajoutée dans la liste pour le fichier *.VETSQLPERF.
    Generate and Export Transact-SQL Performance Data in a file
  6. A la fermeture de cette boîte de dialogue, Visual Expert envoie une notification pour rafraîchir l'analyse du code.
    Cliquez sur "Oui".
    Generate and Export SQL Server Performance Data in a file
  7. Une fois le processus d'analyse terminé, la nouvelle analyse est automatiquement chargée.

Consultez cet article pour profiter des dernières fonctionnalités disponibles pour l'analyse des performances :
AMÉLIORER LES PERFORMANCES DU CODE

==================== Source of the file VEPerfUtils_TSQL.SQL =========================

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'WritePerformanceDataToFile')  
	DROP PROCEDURE [dbo].[WritePerformanceDataToFile]  
GO  


CREATE PROCEDURE [dbo].[WritePerformanceDataToFile] (@databaseName nvarchar(Max), @recordsLimit int = NULL) 
AS
BEGIN

Declare @columdelimiter nvarchar(10);
Declare @rowdelimiter nvarchar(10);

Declare @eventFileFullPath nvarchar(Max) = NULL;
Declare @eventName nvarchar(Max);
Declare @index int;

DECLARE @recordLimitsString varchar(30);  
DECLARE @SQLString nvarchar(2500);  
DECLARE @ParmDefinition nvarchar(200); 
Declare @sliptDelimiter VARCHAR(10) = ',';
Declare @dbName nvarchar(Max);
Declare @position int;

SET @databaseName = CONCAT(@databaseName , @sliptDelimiter);

SET @ParmDefinition = N'@recordLimit int, @colum_delimiter nvarchar(10), @row_delimiter nvarchar(10), @eventFilePath nvarchar(260)';  

SET @columdelimiter = CONCAT( N'«',N'¬',N'»');
SET @rowdelimiter = CONCAT(N'«',N'®',N'»');
SET @eventName = Concat(N'VE_SQLPerformanceSession', '_',  DB_ID(@databaseName));

IF @recordsLimit IS NOT NULL
	SET @recordLimitsString = 'TOP ' + CAST(@recordsLimit AS VARCHAR(15)) + ' ';
ELSE
	SET @recordLimitsString = ' ';

SET @SQLString = 'SELECT ' + @recordLimitsString + ' Concat(ALLRECORDS.duration, @colum_delimiter, ALLRECORDS.sqlFullText, @row_delimiter) as record FROM ' + 
	'(SELECT ' +
		'qs.total_elapsed_time/qs.execution_count AS duration, ' +    
		'SUBSTRING(st.text, (qs.statement_start_offset/2)+1, ' +
			'((CASE qs.statement_end_offset ' +
				'WHEN -1 THEN DATALENGTH(st.text)  ' + 
				'ELSE qs.statement_end_offset  ' + 
				'END - qs.statement_start_offset)/2) + 1) AS sqlFullText ' +
	'FROM sys.dm_exec_query_stats AS qs  ' + 
	'CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st  ' + 
	'UNION ALL ' +
	'SELECT ' +	
		'qs.total_elapsed_time/qs.execution_count AS duration, ' +  	                                               
		'st.text AS sqlFullText ' +
	'FROM sys.dm_exec_procedure_stats AS qs ' +  
	'CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st ' +  
	'UNION ALL ' +
	'SELECT ' +	
		'ts.total_elapsed_time/ts.execution_count AS duration, ' +  	                                             
		'st.text AS sqlFullText ' +
	'FROM sys.dm_exec_trigger_stats AS ts ' +  
	'CROSS APPLY sys.dm_exec_sql_text(ts.sql_handle) AS st ';

SET @position = CHARINDEX(@sliptDelimiter, @databaseName, 0);

WHILE @position <> 0
BEGIN
	SELECT
	@dbName = RTRIM(LTRIM(SUBSTRING(@databaseName, 1, @position-1))),
	@databaseName = RTRIM(LTRIM(SUBSTRING(@databaseName, @position + LEN(@sliptDelimiter), LEN(@databaseName))))
 
	SET @dbName = RTRIM(LTRIM(@dbName));

	IF LEN(@dbName) > 0
	BEGIN

		IF(Substring(@dbName, 1, 1) = '[' and Substring(@dbName, LEN(@dbName) , LEN(@dbName)) = ']')
		 SET @dbName = Substring(@dbName, 2, LEN(@dbName) - 2)

		SET @eventName = Concat(N'VE_SQLPerformanceSession', '_',  DB_ID(@dbName));


		SET @eventFileFullPath = (SELECT TOP 1 cast(f.value as NVARCHAR (Max))
		FROM sys.server_event_sessions AS s
		    JOIN  sys.server_event_session_targets  AS t ON  t.event_session_id = s.event_session_id
			JOIN  sys.server_event_session_fields   As f ON  f.event_session_id = s.event_session_id AND f.object_id = t.target_id
		WHERE s.name = @eventName);


		SET @index = CHARINDEX(@eventName, @eventFileFullPath);

		IF @index > -1  
		Begin
			SET @eventFileFullPath = SUBSTRING(@eventFileFullPath, 0, @index);
			SET @eventFileFullPath = Concat(@eventFileFullPath, @eventName);
		END 

		IF @eventFileFullPath IS NOT NULL 
		BEGIN
		SET @SQLString =  @SQLString + 
			'UNION ALL ' +
			'SELECT ' +	
				'CAST(event_Data AS xml).value(''(event/data[@name="duration"]/value)[1]'',''bigint'') AS duration, ' +
				'CASE ' +
					'WHEN CAST(event_Data AS xml).value(''(event/@name)[1]'', ''varchar(250)'') = ''module_end'' THEN CAST(event_Data AS xml).value(''(event/data[@name="object_name"]/value)[1]'',''nvarchar(250)'')  ' +
					'Else CAST(event_Data AS xml).value(''(event/data[@name="statement"]/value)[1]'',''nvarchar(MAX)'') ' + 
					'END AS sqlFullText ' +
				'FROM sys.fn_xe_file_target_read_file(CONCAT(@eventFilePath ,''*.xel''), CONCAT(@eventFilePath ,''*.xem''), NULL, NULL) ';
		END 

	END


	SET	@position = CHARINDEX(@sliptDelimiter, @databaseName, 0);
END

SET @SQLString =  @SQLString + ') AS ALLRECORDS';


EXECUTE sp_executesql @SQLString, @ParmDefinition, @recordLimit = @recordsLimit, @colum_delimiter = @columdelimiter, @row_delimiter = @rowdelimiter, @eventFilePath = @eventFileFullPath;

END

Articles recommandés