PowerShellでSQLServerに処理時間の掛かるSQLを発行した際、タイムアウトを回避して処理をやり切りたい

Question

前提・実現したいこと

SQL文の書かれたファイルを読み込み、それをSQLServerに発行する処理をPowerShellで作っています。
その際に発行するSQLは非常に重い処理で、手動実行すると、完了するまでに15分ほど掛かります。
その所為か、シェルから取り込んで実施すると、下記のようなエラーメッセージが出ます。

タイムアウトを回避して、SQLの処理が完了するまで待つようにしたいのですが、どのようにしたら出来ますでしょうか？
ご教示頂けたらと思います。

発生している問題・エラーメッセージ

"0" 個の引数を指定して"ExecuteNonQuery" を呼び出し中に例外が発生しました： "実行タイムアウトの期限が切れました。操作完了前にタイムアウト時間が過ぎたか、サーバーが応答していません。"
発生場所 c\:xxxxxxxx xxx.ps1:xx 文字:x
+   [object]$SqlCommand.ExecuteNonQuery()
+
    +CategoryInfo          : NotSpecified: (:) []. MethodInvocationException
    +FullQualifiedErrorId  : SqlException

該当のソースコード

　$sql_file_name = (Convert-Path .) + "\aaa.sql"

  # 接続文字列の作成
  [object]$ConnectionString = New-Object -TypeName System.Data.SqlClient.SqlConnectionStringBuilder
  [object]$ConnectionString['Data Source'] = "localhost"
  [object]$ConnectionString['Initial Catalog'] = "db_name"
  [object]$ConnectionString['Integrated Security'] = "TRUE"
  
  # SQLファイルから内容を取り込む
  [string]$SQLQuery = (Get-Content $sql_file_name)
  
  # SQLConnectionとSQLCommandを設定する
  [object]$SqlConnection = New-Object System.Data.SQLClient.SQLConnection($ConnectionString)
  [object]$SqlCommand = New-Object System.Data.SQLClient.SQLCommand($SQLQuery, $SqlConnection)
  
  # データベース接続
  [object]$SqlConnection.Open()
  
  # SQL文の実行
  [object]$SqlCommand.ExecuteNonQuery()
  
  # データベース接続解除
  [object]$SqlConnection.Close()

試したこと

下記を実施しましたが、どれもタイムアウトするまでの時間に変化がありませんでした。

1. $ConnectionString.CommandTimeOut = 6000000という一文を追加して実行

2. $SqlConnection.ConnectionTimeout = 6000000という一文を追加して実行

3. SSMSにてテーブルデザイナーの更新のための接続文字列のタイムアウト値をオーバーライドするチェックボックスを外した状態で実施

処理の軽いSQLに差し替えて実施すると、問題なく動作します。

補足情報（FW/ツールのバージョンなど）

• PowerShell Version 5.1.14393.2828
• SQLServer Version SQLServer 2017 Evaluation Edition

Comments

[dodox86]

タイムアウト値をセットするのが$ConnectionStringや$SqlConnectionの方ではなくて、$SqlCommandの方ではどうなりますか？
SqlCommand.CommandTimeout
https://docs.microsoft.com/ja-jp/dotnet/api/system.data.sqlclient.sqlcommand.commandtimeout?view=dotnet-plat-ext-3.1

[m.ts10806]

SQL自体のパフォーマンス改善は考えない前提でしょうか。

[tarou_tabi]

dodox86さま、m.ts10806さま
　早々のご返信、ありがとうございます。

dodox86さま
　$SqlCommandを作成した次の行に下記を入れてみましたが、現象変わらずでした。
　[object]$SqlCommand.Commandtimeout = 600000000

m.ts10806さま
　現状は考えておりません。
　実は既にチューニング済みで、これ以上の改善は難しい状況です。

[dodox86]

> $SqlCommand.Commandtimeout = 600000000
試すには、大き過ぎなのでは？　秒単位なので、もう少し適切な値をセットした方が良いかもしれません。（それでもダメな気はしますが）
あと、これはSQL実行のタイムアウト値ですが、コネクションやトランザクションのタイムアウト値もある（＜すでにお試しのものが該当する？）ようなので、合わせ技でないとダメなのかもしれません。

[dodox86]

あとはサーバー側の設定かもしれませんね。クライアントがどうセットしようが、サーバー側で切断してしまう、と言う。

[tarou_tabi]

dodox86さま
　時間については、もう少し調整してみます。
　15分なので、900　もう少し大きくして1000くらいで試してみます。

　＞合わせ技、サーバー側の設定
　なるほど！　ちょっと良さそうな情報がないか、探してみます。
　サーバーの設定は自由に変えられる立場にあるので、その観点でも情報を探してみます。
　ありがとうございます。

[Orlofsky]

以前、徹底的にチューニングしたけどこれ以上処理時間を改善できないのでなんとかしてくれ、ってOracleのパフォーマンス・チューニング案件で50何時間かかっていたバッチが改善点ありありで、5時間ちょっとにしたことがあります。

[tarou_tabi]

dodox86さま
大変申し訳ありません。
コーディングミスしていて、該当の処理を呼べていませんでした。
$SqlCommand.Commandtimeout = 1000
処理を直し、さらに時間を1000に変更したところ、処理が正常に完了しました。
ありがとうございました。本当にありがとうございました！！

[dodox86]

そうでしたか、解決してよかったです。回答欄にその旨（原因と対策）を書いて自己回答、解決で質問を閉じてください。

[tarou_tabi]

Orlofskyさま

ご返信ありがとうございます。
1/10とは凄いですね。私もSQLの技術を磨いて、そちらの技術も磨きたいと思います。

[tarou_tabi]

dodox86さま

回答欄の件、承知致しました。記載致します。
昨日からずっと悩んでいたので、本当に助かりました。
ありがとうございました！！！

[m.ts10806]

>実は既にチューニング済みで、これ以上の改善は難しい状況

が分かる情報が提示されてないのでなんとも。

[tarou_tabi]

m.ts10806さま

ありがとうございます。
SQLの内容を開示するのは憚られる状況なので、自身で再度検討致します。
問題を切り出して質問出来る部分がありましたら、改めて質問を立てさせて頂こうと思います。
その時はどうぞよろしくお願い致します。

Answer 1

本来は避けるべきですがものは試しで接続文字列に「Connection Timeout=0";」を足しては？

PowerShellだと

[object]$ConnectionString['Connection Timeout'] = "0"

こうかな？

Comments

[tarou_tabi]

ありがとうございます。
実は、今さっき、dodox86さまにご教示頂いた内容で解決致しました。
今後の参考にさせて頂きます。

[sousuke]

解決に至ってよかったです。
私も読み違いしてました。
ConnectionStringに設定できるのは「connect(ion) timeout」です。sqlconnectionに設定するのはconnectiontimeoutプロパティで、両方とも接続にかかる時間の設定です。
sqlcommandに設定するのはcommandtimeoutで、コマンド実行の時間ですが、connectionStringにcommandtimeoutはないので無意味でした。

[tarou_tabi]

なるほど。接続とコマンド実施で別々にタイムアウト時間が設定出来るのですね。
今ひとつ理解出来ていませんでした。
今後、同じようなことをする際に、意識して使い分けるように致します。
ありがとうございました！！

Answer 2

dodox86さまに頂いたコメントにて解決致しました。
原因と解決方法としては、下記の通りです。

• 原因

SQLコマンド実行した後の待機時間が規定値の30秒では短すぎた為、タイムアウトが発生しておりました。

• 対策

　SqlCommand.CommandTimeout プロパティにて、タイムアウトの時間を想定されるSQLコマンド実行時間より少し長めに設定することで解決致しました。
具体的には、ソースコードの「[object]$SqlCommand = New-Object System.Data.SQLClient.SQLCommand($SQLQuery, $SqlConnection)」行の直下に下記行を追加致しました。
＜追加した行＞
[object]$SqlCommand.Commandtimeout = 1000

参考ページ
リンク内容

Comments

[sazi]

やり切らないと駄目な処理なら、タイムアウト値は設定しない方が良いかと思います。

掛かる時間が問題になるとしたら、処理時間を観測する必要があるわけで、それで想定を超えそうだったらどうします？タイムアウト値を伸ばしますか？それとも限度がある？

伸ばすのだったら、それは必ず実行するという事になり、タイムアウトに制限を付けても意味が無い事になりますし、限度があるなら処理自体を見直して想定内に収まるようにするという事ですから、タイムアウトの制限は結局関係が無い事になります。

タイムアウト値というのは、処理が占有できる時間の上限としてオンライン処理では考慮したりしますが、バッチ的な処理の場合は制限を設けるというより、処理を見直して時間内に収めるのが殆どです。

[tarou_tabi]

saziさま
ご意見ありがとうございます。
確かにその通りだと思いました。
Commandtimeoutは0を設定すれば無制限になるようですので、0に変えた上で再度検証してみます。
ありがとうございました！！