1. Home 
  2. Blog 
  3. 2020 
  4. Generate and Export SQL Server Performance Data 

SQL Serverのパフォーマンス データをファイルに生成してエクスポートする

Visual ExpertはTransact-SQLコードのパフォーマンス データからその実行を解析します。これは、T-SQL 開発者がコードのパフォーマンスをチューニングするのに役立ちます。

この機能は、データベースが生成したコード実行データをVisual Expert プロジェクトにロードすることにより解析可能になります。Visual Expert は、このデータをコードの静的分析で抽出された情報と組み合わせます。

コード実行データの生成は、お客様の構成に依存します。

  • Visual Expert を SQL Server に接続できる場合は、この手順を踏む必要はありません。代わりに この記事に従ってください。
  • Visual Expert が SQL Server に接続できない場合（例えば、本番用データベースが別の環境でホストされているなど）は、以下の手順に従って必要なデータを含むファイルを生成し、Visual Expert プロジェクトに含めます。
  1. SQL Serverデータベースに接続し、writePerformanceDataToFile ストアドプロシージャ（イベントセッション ファイルからパフォーマンス データを読み取り、"*.VETSQLPERF "ファイルに書き込むプロシージャ）を実行してファイルを作成します。

  2. PROCEDURE の作成 - WritePerformanceDataToFile (VEPerfUtils_TSQL.SQL から PROCEDURE スクリプトを実行)

  3. コマンド プロンプトを開き、以下のコマンドを貼り付けます。
    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}"

    これは、ストアド プロシージャ WritePerformanceDataToFile を実行し、出力をファイルに書き込みます。

コマンドの例：

  1. 信頼された接続を使用して複数のデータベースのすべてのレコードを書き込む場合
    2. bcp "exec [MyDb].[dbo].[WritePerformanceDataToFile] @databaseName = N'SalesDB, TestDB', 
@recordsLimit = NULL" queryout "D:\VEPerformanceFiles\MultipleDB.VETSQLPERF" -w -T
  2. 信頼された接続を使用して単一データベースのすべてのレコードを書き込む場合
    3. bcp "exec [MyDb].[dbo].[WritePerformanceDataToFile] @databaseName = N'TestDB', 
@recordsLimit = NULL" queryout "D:\VEPerformanceFiles\SingleDB.VETSQLPERF" -w -T
  3. 信頼された接続を使用して単一のデータベースに最大10000レコードを書き込む場合
    4.  bcp "exec [MyDb].[dbo].[WritePerformanceDataToFile] @databaseName = N'TestDB', 
@recordsLimit = 10000" queryout "D:\VEPerformanceFiles\SingleDB.VETSQLPERF" -w -T
  4. ログインID "SA "を使用して接続した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. ログインID「TEST」を使用して接続した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"

パラメータの注意点：

  1. {DataBaseName}: WritePerformanceDataToFileプロシージャを含むデータベース名を書き込みます。
  2. {schema}: WritePerformanceDataToFileプロシージャを含むスキーマ名
  3. @databaseName: パフォーマンスデータを読み込むデータベース名をカンマで区切ったリスト

    4. 例： 

    @databaseName = N'{DatabaseName1,DatabaseName2}' -- multiple databases

    または

    @databaseName = N'{DatabaseName1}' -- single database
  4. @recordsLimit: 出力ファイルに読み書きされるレコードの総数（NULL = すべてのレコード、任意の数 = レコードの総数）

    5. 例：

    @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
  5. queryout: .VETSQLPERF 拡張子を持つ出力ファイルのフルパス
    (VEは、拡張子が.VETSQLPERFである場合にのみ、このファイルが解析用であることを認識できることに注意してください)

    6. ご注意: ファイルのフルパスにディレクトリが存在することを確認してください。そうでなければ、bcpはエラーをスローします。

    例：

    queryout "D:\VEPerformanceFiles\alldatabase.VETSQLPERF"

    または

    queryout "D:\VEPerformanceFiles\SalesDB.VETSQLPERF"
  6. -U {username}: WritePerformanceDataToFileストアドプロシージャを実行するためにSQL Serverに接続するためのユーザ名

    7. 例：

    For "sa" USER -U "sa"
  7. -P {password}: 上記ステップ5で指定したユーザ名のパスワード

    8. ご注意： 統合されたセキュリティを使用して SQL Server を信頼された接続で接続するには、ユーザーは -U と -P の代わりに -T と書くことができます。

  8. -S "{server\instance}": マシン/サーバとsqlサーバのインスタンスを指定

    9. 例:

    • ローカルマシンのSQL Expressインスタンスの場合
      • -S "(local)\SQLExpress"
    • 名前付きサーバ（例：DB-SERVER1）と名前付きSQLサーバ インスタンス（例：SQLSERVER2019）の場合 
      • -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)"

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

実行成功のスクリーンショット：

bcpユーティリティを使用してストアドプロシージャを実行するために必要な権限 

パフォーマンス ファイルの準備ができたら、以下の手順に従ってVisual Expert で使用できます。

  1. Visual ExpertでSQL Serverプロジェクトを開きます。
  2. リボンメニューから、 [管理] > [ソースコードの管理]に移動します。
    T-SQLのパフォーマンスデータをファイルに生成してエクスポートする

  3. 「追加」をクリックして、パフォーマンス データ ファイル (*.VETSQLPERF) を新しいソース コードの場所として追加します。
    T-SQLパフォーマンスデータファイルの追加
  4. Visual Expert プロジェクト ウィザードで、新しいソースコードの場所を追加するため、"フォルダー/ファイル > ディレクトリ 'PERFDATADIR' > *VETSQLPERF ファイルを選択します。「終了」をクリックします。
    SQL Serverパフォーマンスデータファイルの追加
  5. .VETSQLPERF ファイルの新しいエントリがリストに追加されます。
    Transact-SQLのパフォーマンスデータをファイルに生成してエクスポートする
  6. このダイアログを閉じると、Visual Expert はコード解析を更新するように通知します。「はい」をクリックします。
    SQL Serverのパフォーマンスデータをファイルに生成してエクスポートする
  7. 解析が完了すると、新しい解析結果が自動的にロードされます。
     
  8. パフォーマンスを分析するために利用できる最新の機能を活用するには、この記事をご覧ください。
    コード パフォーマンスの改善.

 

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