none
SQL Server 2012でテーブルを主キーで絞ってUpdateしているのにタイムアウトする RRS feed

  • 質問

  • お世話になります。Accessでお客様にソフトを開発をしているものです。

    Access2013をフロントにしてSQL Server 2012をODBCで利用しています。

    対象のテーブルは360万件ほどでそのうちの1行をWhere句を主キーで検索しフラグを

    Updateしている処理があるんですが何かの拍子にアプリがタイムアウトするまで待たされる状態が頻繁しています。

    SQL文:update 受注台帳メイン set 伝票発行F=1 where 伝票番号='0001'

    「伝票番号」は「受注台帳メイン」のPrimaryKeyです。タイムアウトする状態下で以下の状況を確認しています。

    • 同じ行はselect可能「select * from 受注台帳メイン where 伝票番号='0001'」は結果が返る
    • 別の行もタイムアウトする「update 受注台帳メイン set 伝票発行F=1 where 伝票番号='0002'」も失敗(他のいくつかの行も失敗する)
    • 他のクライアントはない状況でも発生
    • Management Studio上でもselect可、update不可
    • 10ほどある非クラスタ化インデックスに適当に変更を加えて再構成するとupdateできるようになる

    開発環境のサーバとお客様のサーバは同じ構成で2台買ったのですが開発環境ではほとんどおきません。

    昨日開発環境にSQL Server 2012のService Pack 2を当てた後に始めて発生しました。しかしインデックス再構成後また再現しなくなりました。

    Webの検索であまりヒットしないので質問させて頂きました、何かご存知の方いらっしゃいましたらご回答をよろしくお願い致します。

    2015年1月29日 1:30

すべての返信

  • SQL Server Management Studio上でも発生するとなると、環境等システム側に問題がありそうですね。ちなみに、SQLCMDで行った場合も同様なのでしょうか?
    とりあえず、以下の累積的な更新が出ていますので、まだ適用されていないのでしたら、適用されてみてはいかがでしょうか?

    SQL Server 2012年の SP2 用の累積的な更新 1
    http://support2.microsoft.com/kb/2976982/ja


    ★良い回答には回答済みマークを付けよう! MVP - .NET  http://d.hatena.ne.jp/trapemiya/

    2015年1月29日 1:52
  • 一応 2012 SP2 は累積的な更新 4(CU4) が現時点では最新の CU ですので、補足まで。
    http://support.microsoft.com/kb/3007556/

    なんとなく統計情報、分離レベル、自動拡張、データキャッシュ、Lock といった辺りが可能性がありそうですが、可能性が色々ありすぎて何とも言いづらいところです。
    UPDATE できるときと UPDATE できないときのリソース(CPU, Memory, Disk I/O)使用状況の推移にどのような差があるか、といった情報があるとアタリが付きやすいかと。
    もしくは旧式の手法ですが、UPDATE できなかった時の前後の sys.dm_os_wait_stats を見比べて差異が目立つところはどこか、といった情報でも良いかと。


    MCITP(Database Developer/Database Administrator)

    2015年1月29日 2:09
  • nagino様ご回答いただきありがとうございます。累積更新を今度当ててみようと思います。

    リソースなんですが、発症時はほとんどアイドルに近い状態です。CPUの負荷率は5%にも満たない、メモリはSQL Serverが

    ほとんどキャッシュしていたのでわかりませんが、I/Oも定期的に点滅する感じです。何かを一生懸命やっているという感じは全くしなかったです。

    サーバのスペック  CPU:Xeon E3-1220 V2、RAM:10GBなんですが

    自分の開発機  CPU:Core2Duo E8400、RAM:3GBにデータベースを復元して実行しても再現出来ませんでした。

    一応既存のインデックスで日付のフィルタをかけて、差し当たって現象は収まっています。

    sys.dm_os_wait_statsも調べて見ます。何とかして現象を再現出来るようにやってみます。

    • 編集済み sskr 2015年1月30日 7:05
    2015年1月30日 6:58
  • trapemiya様ご回答ありがとうございます。

    すみません、お恥ずかしながらSQLCMDというものを使ったことがないのですが現象が出たら試してみます。

    Management Studio上で実行できなくてSQLCMDで実行できた場合どのような状況なのでしょうか?

    重ねての質問で申し訳ありませんが可能でしたら教えてください。

    2015年1月30日 7:04
  • そうすると、まずは Lock や多重接続の辺りが疑わしいですね。

    EXEC sp_who
    EXEC sp_lock

    といった辺りを追うのが一つの手ですかね。

    なんとなくの感覚ですが、Access の強制終了であったり、ハングアップ、あるいは使用中のネットワーク切断(クライアント側の LAN ケーブルを抜く等でシミュレーション)といったあたりを起こすとどうなんだろうというのが気になります。


    MCITP(Database Developer/Database Administrator)

    2015年1月30日 7:36
  • Management Studio上で実行できなくてSQLCMDで実行できた場合どのような状況なのでしょうか?

    すみません。省略しすぎました。
    意味合いとしては、クライアントにおいて、Accessの代わりにSQLCMDでSQLを実行した場合にどうなるのかと思いました。
    ただ、おそらく、同様に問題が発生するSQL Server Management Studioは、SQL Serverのあるサーバー上で実行されていると思いますので、もしそうであれば、ネットワークを含むクライアントの環境ではなく、SQL Server自体に問題がある可能性が高いように思います。
    なお、少し古い情報ですが、以下のようにSQLCMDは単体で導入可能です。

    (参考)
    SQLCMD単体プログラムのダウンロード
    https://social.msdn.microsoft.com/Forums/ja-JP/a7600565-4f7c-47a1-8ea4-9230abe5f9cd/sqlcmd?forum=sqlserverja



    ★良い回答には回答済みマークを付けよう! MVP - .NET  http://d.hatena.ne.jp/trapemiya/

    2015年1月30日 8:40
  • 見当違いの可能性が高いですが、伝票番号列が数値型って事は無いですか?

    ※その場合「伝票番号 = '0001'」でもエラーにはなりませんが、'0001'を数値化するのではなくて伝票番号を文字列化してしまった可能性があるかも・・・

    2015年2月2日 9:15
  • aviator__様、ご回答ありがとうございます。

    ちょっと不思議に思われたかもしれませんが、伝票番号列は文字列型(nvarchar(10))で定義されています。旧データベース(SQL Server 2005)を引き継いで作ったのでこのようになっています。
    2015年2月2日 23:50
  • nagino様、ご回答ありがとうございます。あれから現象を再現することが出来ず、いろいろ実験することができなくなっています。

    また再現することが出来る状況になりましたらご報告したいと思います。
    2015年2月3日 0:06
  • nagino様、重ねての質問で申し訳ありませんが宜しければご教示ください。

    始めに申し上げた通り今回はクライアントが他にいない状況でも発生したので起こりうる状況としては多重接続になりますが、仮に別セッションにてロックされていた場合、インデックスの無効化、再構築や再構成は不可能であったかと思います。

    ですがタイムアウト発生後、「インデックスの無効化」→「再構築」によって停止したセッションを破棄せずとも症状は一旦おさまるので素人考えですが、その可能性はないと思っていました。(記載漏れがありました。「伝票発行F」列を含むインデックスはありません。非クラスタ化インデックスの付加列としては一部含まれていました。)

    詳細をお伝えすることが難しいにもかかわらずご回答いただき大変感謝しております。失礼致しますが何か見落としがあれば再度よろしくお願い致します。


    • 編集済み sskr 2015年2月3日 1:25
    2015年2月3日 1:23
  • こちらはフォーラム上の断片的な情報だけで可能性を推測しているに過ぎませんので、現場で様々な情報を確認された上でそう判断されているならそういうことかと思います。

    作業順や経過時間について記載が無かったので、作業中に時間経過によってタイムアウトなどによりロックなど状況が変化している可能性もあるので、その辺りも含めて「気になります」という書き方をしたまでです。


    MCITP(Database Developer/Database Administrator)

    2015年2月7日 15:35