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:
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.
bcp "exec [MyDb].[dbo].[WritePerformanceDataToFile] @databaseName = N'SalesDB, TestDB', @recordsLimit = NULL" queryout "D:\VEPerformanceFiles\MultipleDB.VETSQLPERF" -w -T
bcp "exec [MyDb].[dbo].[WritePerformanceDataToFile] @databaseName = N'TestDB', @recordsLimit = NULL" queryout "D:\VEPerformanceFiles\SingleDB.VETSQLPERF" -w -T
bcp "exec [MyDb].[dbo].[WritePerformanceDataToFile] @databaseName = N'TestDB', @recordsLimit = 10000" queryout "D:\VEPerformanceFiles\SingleDB.VETSQLPERF" -w -T
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"
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"
@databaseName = N'{DatabaseName1,DatabaseName2}' -- multiple databasesOR
@databaseName = N'{DatabaseName1}' -- single database
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
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"
For Example:
For "sa" USER -U "sa"
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)"
GRANT VIEW SERVER STATE to {user} USE master GO GRANT VIEW SERVER STATE TO test;
GRANT EXECUTE ON {database}.{schema}.[WritePerformanceDataToFile] to {user}; Use {database} Go GRANT EXECUTE to [test];
Screenshot of successful execution:
Once the performance file is ready, it can be consumed by your Visual Expert. Follow these steps to do so:
==================== 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