Support Center
Explore, Analyze & Document your Code

Generate and Export SQL Server Performance Data in a File

Visual Expert monitors the performance of your Transact-SQL code and produces analysis about its execution. This helps the T-SQL developers in tuning their code performance.

This feature relies on code execution data that your database will generate, and that you will load in your Visual Expert project. Visual Expert will then combine this data with the information extracted during the static analysis of your code.

Generating this code execution data depends on your configuration:

  • If Visual Expert can connect to your SQL Server, you DON’T NEED to follow this procedure. Just follow this article instead.
  • If Visual Expert CANNOT connect to SQL Server - for instance with a production database hosted in a separate environment - follow the procedure below to generate a file containing the required data, and include it in your Visual Expert project.
  1. Connect to SQL Server Database where you want to create and execute writePerformanceDataToFile stored procedure (a procedure that reads performance data from event session file(s) and writes to a “*.VETSQLPERF” file)

  2. Create PROCEDURE - WritePerformanceDataToFile (execute PROCEDURE script from VEPerfUtils_TSQL.SQL)

  3. Open command prompt and paste the command below:
    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}"

    This will execute the stored procedure - WritePerformanceDataToFile and write output to a file.

Command Examples:

  1. For writing all records for Multiple Databases using trusted connection
    2. bcp "exec [MyDb].[dbo].[WritePerformanceDataToFile] @databaseName = N'SalesDB, TestDB', 
@recordsLimit = NULL" queryout "D:\VEPerformanceFiles\MultipleDB.VETSQLPERF" -w -T
  2. For writing all records for Single Database using trusted connection
    3. bcp "exec [MyDb].[dbo].[WritePerformanceDataToFile] @databaseName = N'TestDB', 
@recordsLimit = NULL" queryout "D:\VEPerformanceFiles\SingleDB.VETSQLPERF" -w -T
  3. For writing maximum 10000 records for Single Database using trusted connection
    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. For writing all records for Multiple Databases using a login ID "TEST" used to connect to 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 for Parameters:

  1. {DataBaseName}: Write database name containing WritePerformanceDataToFile procedure.
  2. {schema}: Schema name containing WritePerformanceDataToFile procedure.
  3. @databaseName: List of database names separated by commas for which the performance data is to be read.

    For Example: 
    4. @databaseName = N'{DatabaseName1,DatabaseName2}' -- multiple databases
    OR 
    @databaseName = N'{DatabaseName1}' -- single database
  4. @recordsLimit: Total number of records to be read and written to the output file (NULL = all records, any number = total no. of records)

    5. For Example:

    @recordsLimit = NULL -- all records will be read and written to output file

    OR

    @recordsLimit = 10000 -- maximum 10000 records will be read and written to output file
  5. queryout: Output file full path with .VETSQLPERF extension
    (Please note that VE can recognize this file for analysis only if the extension is .VETSQLPERF)

    6. Note: Please ensure that the directory exists is mentioned in the file full path, otherwise bcp will throw an error.

    For Example:

    queryout "D:\VEPerformanceFiles\alldatabase.VETSQLPERF"

    OR

    queryout "D:\VEPerformanceFiles\SalesDB.VETSQLPERF"
  6. -U {username}: username to connect to SQL Server to execute WritePerformanceDataToFile stored procedure

    7. For Example:

    For "sa" USER -U "sa"
  7. -P {password}: Password for username specified in step 5 above

    Note: To connect SQL Server with a trusted connection using integrated security, user can write -T instead of -U and -P
     
  8. -S "{server\instance}": Specify machine/server and SQL Server instance
    Examples: 

    9. For local machine SQL Express instance

    -S "(local)\SQLExpress"

    For a named server (e.g., DB-SERVER1) and named sql server instance (e.g., SQLSERVER2019) 

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

Permissions required to execute a stored procedure using bcp utility:

  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];

Screenshot of successful execution:

Permissions required to execute a stored procedure using bcp utility 

Once the performance file is ready, it can be consumed by your Visual Expert. Follow these steps to do so:

  1. Open your SQL Server project in Visual Expert.
  2. From the ribbon menu, navigate to Settings > Manage your source code.
    Generate and Export T-SQL Performance Data in a file

  3. Click on “Add” to add your performance data file (*.VETSQLPERF) as a new source code location.
    Add T-SQL Performance Data file
  4. In the Visual Expert Project Wizard, select “folders and/or files” > locate the directory 'PERFDATADIR' > *VETSQLPERF file to add as a new source code location. Click on “Finish”.
    Add SQL Server Performance Data file
  5. A new entry is added in the list for the *.VETSQLPERF file.
    Generate and Export Transact-SQL Performance Data in a file
  6. Upon closing this dialog, Visual Expert notifies to refresh the code analysis. Click on “Yes”.
    Generate and Export SQL Server Performance Data in a file
  7. After the completion of the Analysis Process, a new analysis is loaded automatically.
     
  8. Visit this article to leverage the latest features available for analyzing performances:
    IMPROVE CODE PERFORMANCE.

 

==================== 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

Visual Expert Featured Resources


Videos