Visual ExpertはTransact-SQLコードのパフォーマンス データからその実行を解析します。これは、T-SQL 開発者がコードのパフォーマンスをチューニングするのに役立ちます。
この機能は、データベースが生成したコード実行データをVisual Expert プロジェクトにロードすることにより解析可能になります。Visual Expert は、このデータをコードの静的分析で抽出された情報と組み合わせます。
コード実行データの生成は、お客様の構成に依存します。
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}"
これは、ストアド プロシージャ WritePerformanceDataToFile を実行し、出力をファイルに書き込みます。
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 databases
または
@databaseName = N'{DatabaseName1}' -- single database
例:
@recordsLimit = NULL -- all records will be read and written to output file
または
@recordsLimit = 10000 -- maximum 10000 records will be read and written to output file
ご注意: ファイルのフルパスにディレクトリが存在することを確認してください。そうでなければ、bcpはエラーをスローします。
例:
queryout "D:\VEPerformanceFiles\alldatabase.VETSQLPERF"
または
queryout "D:\VEPerformanceFiles\SalesDB.VETSQLPERF"
例:
For "sa" USER -U "sa"
ご注意: 統合されたセキュリティを使用して SQL Server を信頼された接続で接続するには、ユーザーは -U と -P の代わりに -T と書くことができます。
例:
-S "(local)\SQLExpress"
-S "DB-SERVER1\SQLSERVER2019"
bcpの使い方の詳細はこちらをご覧ください。 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)"
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];
実行成功のスクリーンショット:
パフォーマンス ファイルの準備ができたら、以下の手順に従ってVisual Expert で使用できます。
==================== 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