SQL ServerでManagement Studioでは速いのにWEBアプリでのみSQLの実行が遅い場合に確認すること。
ついこないだSQL Serverのユーザファンクションの性能問題について書きましたが、実はそのあとも性能問題が発生しました。
Javaを使ったWEBアプリで検索処理が20secくらいかかるという問題が発生しました。
SQLが悪いと疑ってSQLログからSQLを抽出し、SQLをManagement Studioで実行しましたが、結果は2secで返ってきて問題はありませんでした。
ググるといろいろ実行計画のキャッシュを削除すればいいとかいろいろ試したのですが、さっぱりでした。
困ったときはプロファイラということでプロファイラを実行したあとにWEBアプリを実行すると以下のようなSQLが実行されていることがわかりました。
declare @p1 int set @p1=10 exec sp_prepare @p1 output,N'@P0 int,@P1 nvarchar(4000),@P2 nvarchar(4000)' ,N'SELECT * FROM SCHEDULE WITH (NOLOCK) WHERE RoomNo = @P0 AND STATUS = @P1)',1 select @p1
使っているWEBアプリはHibernateを使っていましたが、普通のSQLのSQLが実行されていると思っていたので驚きました。
それでプロファイラから抽出したSQL(@p1=nullに変更する必要があります)をManagement Studioで実行したのですが、結果は2secで返ってきました。
WEBアプリが実行されているのを忠実に再現してみようと考えて、eclipseのDBViewrから抽出したSQLを実行したところ、20secで返るようになり、再現しました。
JDBCについて調べたところ、どうやらSQLServerのJDBCドライバはデフォルトでUnicodeで処理されるようです。
SQL ServerではvarcharがSJIS、nvarcharがUnicodeなので、SQLがnvarcharとして実行されており、テーブルのカラムの型がvarcharだったので暗黙的な変換が行われるため、遅くなっているということがわかりました。
いくら暗黙的変換と言えども遅すぎな気はしますが。
対策として以下の2つが考えられます。
- SQLのほうでキャスト(キャスト処理に若干時間がかかるが、暗黙的変換の比ではない)
- JDBCの接続プロパティ「sendStringParametersAsUnicode」をfalseにする。(デフォルトはtrue)
接続プロパティについては以下を参照。
接続プロパティの設定 | Microsoft Docs
近年、Unicode対応が進んでいるので、デフォルト値としては当然かもしれませんが、昔からある大企業の基幹システムなどホストと連携したりしているシステムは
いまだにSJISで固められていたりします。(規約でnvarcharの使用禁止されている)
今回もそんなシステムでした。
なので接続プロパティを変更する方法で対応しました。
nvarcharがほとんどで一部のテーブルだけSJISになっているのであればキャストで対応ですね。
この問題、わりとメジャーっぽいんですが、SQL ServerでしかもSJISで縛られているシステムを開発したことなかったのでわかりませんでした。
また、開発環境ではデータベースの構築時の設定(詳しくは調べてないですが、照合順序かと)が検証環境と異なっていたらしくこの問題が起きていませんでした。
今回の事象はSQL Serverのみで起こる問題でしたが、
SQLの性能を調査する際は、プロファイラで実行されているSQLを抽出し、WEBアプリがJDBC接続だったらJDBC接続で確認するなど接続方法、データベース設定を発生する環境と同じにして再現確認するのが重要!と実感しました。
SQL Serverのユーザファンクション(ユーザー定義テーブル値関数)を使用していて性能が出ない(遅い)場合にすること。
SQL Server上の開発したシステムでSQLの共通化(複雑な部分の隠蔽)にユーザファンクション(ユーザー定義テーブル値関数)を使用しました。
ユーザー定義テーブル値関数 | Microsoft Docs
ユーザファンションは大抵は計算式とかで使うと思いますが、SQL Serverではテーブルを返すこともできるのです。
ビューではパラメタを渡せませんが、ファンクションなのでパラメタを渡すことができて便利です。
共通化はうまく作用してSQLのスキルが低い開発者でも開発ができて非常に有用でした。
ところが、結合テストでデータ件数が10,000件を超えると途端に実行速度が遅くなりました。
件数が少ないときは数秒だったのに、実行しても全然返ってきません。
いろいろ調べるとユーザファンクションとテーブルのJOINでは特に問題が確認できませんでしたが、 ユーザファンクション同士をJOINしているところが問題で、 どうやらインデックスが効いていないっぽいことがわかりました。
対策として試しにユーザファンクションのところを、ユーザファンクション内で定義しているSQLに置き換えることで性能が改善しました。
ただ、ユーザファンクションで定義しているSQLを展開しただけなので、実行されるSQLとしては同じものになると思っていたのですが、若干違うみたいです。
まとめるとユーザファンクション(ユーザー定義テーブル値関数)を使用して性能が出ない場合は、ユーザファンクション同士をJOINなどしていないか確認し、していたらユーザファンクションで定義しているSQLをそのまま置き換えると性能が改善すると思います。
Windows10でデータを残しながらシステム修復(OS上書きインストール)する方法
ついこないだWindows10で自動修復がループする自体におちいりました。
原因はあまり心当たりがないのですが、突然起きたのでかなりテンパりました。
藁にもすがる思いでいろいろググっていろいろ試しました。
原因によって対応方法は異なると思いますが、今後のために失敗した手順と一番よいと思った手順を以下に記しておきます。
ちなみに今回のエラーは「CRITICAL_SERVICE_FAILED」でした。
失敗したシステム修復手順
まずは失敗した手順です。 各々の詳細な手順はググればいくらでも出てくるので割愛します。
セーフモードで起動
セーフモードで起動したら直前にインストールしたアプリ、ドライバとか消せば復活することはしばしばありますが、今回は起動しませんでした。
それに直前にアプリやドライバをインストールしていなかったので起動したとしても何すればよかったのか。
回復オプションでシステム復元を選択
そもそもシステム復元ポイントの設定をしていませんでした。。
回復オプションのスタートアップ修復を選択
Windows10にはスタートアップ修復という便利でドンピシャな機能があります。ただこれでは修復されませんでした。
複数回実行すると効果があると記載があったので実行したけど修復されませんでした。
回復オプションでコマンドプロンプトを開いて「sfc /scannow」を実行
よくこれで修復されると記載されていましたが、これでは修復されませんでした。
回復オプションでコマンドプロンプトを開いて「bootrec /fixboot」を実行
あとあとこれを行ったのが一番まずかったです。これを実行したことでリカバリパーティションがWindowsから認識されなくなりました。(データは残っている)
よく意味を理解せずやってしまうと危険なので注意ください。
回復オプションでコマンドプロンプトを開いて「notepad」を実行
Cドライブのファイルをバックアップとるため、外付けHDDに接続した状態で起動し、notepadを開いてファイルの選択ダイアログで右クリックメニューを使って別のドライブにフォルダやファイルのコピーができます。
xcopyより手軽でこれは便利です。
リカバリパーティションから復元
もうリカバリするかと諦めて回復オプションで「PC を初期状態に戻す」を選択しようと思ったら出てこなくなりました。
おそらくですが直前の「bootrec /fixboot」を実行したのがいけなかったのかと思います。
起動オプションで「ドライバー署名の強制を無効にする」か「起動時マルウェア対策を無効にする」を選択
もうどうすればといろいろ悩んだときにだめもとで起動オプションを選択してみたら思いのほかあっさり起動できて拍子抜けしました。
とりあえず起動できるようになりましたが、毎回「ドライバー署名の強制を無効にする」を選択にしないと起動できないのは不便すぎます。
そこで設定の回復メニューからPCを初期状態に戻すを選択しましたが、やはりリカバリパーティションが認識されずに途中でエラー。
で、ここまでが失敗した手順です。
成功したシステム修復手順
以下から僕が一番いいと思った手順です。 なんか不調だけどWindowsが起動できるときは一番リスクが少ない修復手順と思います。
Windows 10 Media Creation Toolをダウンロード
以下のURLにアクセスして、「ツールを今すぐダウンロード」を実行して、MediaCreationTool.exeをゲットします。
Windows 10 Media Creation ToolでOSを上書きインストール
ダウンロードしたMediaCreationTool.exeを実行します。
「このPCを今すぐアップグレードする」を選択します。
そうです、Media Creation Toolなのにメディアを作らなくてもいいんです。
重要なのは途中で個人用ファイルとアプリを引き継ぐを必ず選択してください。
ウィザード形式なのでポチポチしていけばできます。
心配な方は以下に手順がまとめられているので参考にしてください。
Windows 10 を上書きインストールする方法 - ぼくんちのTV 別館
アプリやデータを一つも失うことなく、これで無事にシステム修復ができました。
ということでWindows10がおかしくなったときは、いろいろと修復しようとせず、
Windowsの起動ができる場合はMedia Creation Toolを使用して
Windowsの上書きインストールをおすすめします。
アプリとデータの引き継ぎが可能なので失うものはありません。
言うまでもないですが、ハードの問題のときはこの手順はまったく使えないのでご注意を。