Microsoft SQL Server 総合スレ 12
Microsoft SQL Server (Transact-SQL) の総合スレッドです。
・Microsoft 公式サイト
http://www.microsoft.com/japan/sql/ >>534
Command Timeoutなので指定してなければデフォルト30秒なんだけど
開始してからタイムアウトが発生するまでの秒数と合ってる?
https://docs.microsoft.com/ja-jp/dotnet/api/system.data.sqlclient.sqlcommand.commandtimeout?view=dotnet-plat-ext-5.0
サーバー側の負荷状況や実際に処理が実行されてるかをActivity Monitorとかで確認すればいい
Indexed Viewとかじゃなくて単純なViewなら秒単位の時間がかかったりしないから
何か待たされてるんだろうけどぱっと思いつく原因はないな sql Server側で、クエリパフォーマンス分析や構造分析って
プロファイラー以外になにつかってます? 構造分析ってなんかわからんけど
クエリのパフォーマンス分析はEXPLANが基本 (SET SHOWPLAN_ALL ON)
実際実行した結果はSET STATISTICS IO ONとSET STATISTICS TIME ONで確認
トレースとるのは正常に実行されないクエリに悪影響を及ぼしてるものが
他の方法では簡単に特定できないような場合に使う
性能に影響でるから運用中の本番環境で使うのは最終手段 とりあえず流したSQLとどうやって実行したか曝せよ
ビューの登録ってのが何のことかわからんが、Create Viewがそうそうタイムアウトするとは思えん alter view ではtime outはよくある
使ってるviewをalterするとデットロックする sqlの中の括弧の対応関係を色付けしてくれる機能ってssmsであります? >>539
deadlockとtimeoutはエラーメッセージが違う 単なるタイムアウトをデッドロックとか言う人多いよね
まあSQL Serverはalter viewもトランザクション内でできたはずだから
デッドロックすることもなくはないんだろうけど 排他ロックとデッドロックを間違えるレベルはひどいなw csvファイルでテーブルをキーが一致したらレコード洗い替えたい。バッチファイルとか実行すると作動するようにしたい。
可能ですか? Accessはもう諦めたのか?
そんなんじゃ何一つ出来ないぞ sqlcmdをサーバはインストールしてるけど
クライアントはしてない場合にも
実行できる方法ない? >>550
何らかの方法でサーバーにログインして実行すればいいんじゃね? >>550
SQLServerのドライバはOS標準で組み込まれてるんだから、バッチ処理で良いんならJSやVBSを使えば良いんじゃないの
PSよりはVBSの方が情報が多いように思うし、OFFICEが使えるならVBAでも良いんじゃないの
というかOFFICEが入ってるならMSクエリとかが使えるか
>>553
クライアント環境にはできるだけツール類はいれたくないだろ >>554
>クライアント環境にはできるだけツール類はいれたくないだろ
DBを直接操作する人間のクライアントにsqlcmdとかSSMSが入ってないほうが意味がわからないんだけど?
まさかエンドユーザーにやらせようとしてんの? >>555
そう
一般利用ユーザーにテーブル更新させる
別にそういうとこあるでしょ >>556
そういうのは普通アプリケーション経由で処理するから
エンドユーザーのクライアント環境でバッチを流したりはしない
入力データが不正だったり更新が競合したりしても
エンドユーザーレベルのリテラシーで対応できるようなものを作る
各ユーザー専用のテーブルのみ更新するような場合で
データが壊れても再作成すればいいような重要度の低いものなら
そういうやり方もありえるのかもしれないが
問い合わせが増えるだけだからまずやらない >>557
クライアント側のアプリケーションだと
レコード削除はできない制約あるパッケージとか多くない?
スクラッチならやるけど クライアントアプリが制限されているから
BATでやりたいと……
なるほど!理解した! データの洗い替えをユーザーにやらせるのは聞いたことないな
マスタメンテで複数件一括更新することを洗い替えと呼んでるのかもしれないが トランザクションなら本来の意味での「洗い替え」っぽいけど
だとしたらユーザー入力のcsvファイル使って
ユーザー環境でバッチ実行させる意味がますますわからん
わかる人いたら解説してくれ こういう当たり前のアプリケーション設計知識って実務経験以外で手に入れるのは難しいのかもね >>564
話を聞いてて、うちの会社でも同じような例があった
本社のシステム部門がDBを管理していて、事業部のシステム部門がマスタを更新したいって例があった。
本社部門は、テーブルを直接操作する権限は与えたくないんだけど、更新作業を自分たちでやりたくない、インターフェースのアプリを作りたくない
というような流れ、本社部門がしっかり作りこめば良いだけなのにね システム部門がユーザーの場合は全然話が違うでしょ
やり方はいろいろ有るけど単純なのはファイル連携で定期スケジューリングしたバッチジョブ流すだけ
エラー時は入力データを用意した関係者にエラー内容を通知するようジョブを組んでおけば十分なので
ユーザーインターフェースを用意しなきゃいけないような話ではない
自分たちが管理してないDBを直接SQLで更新したいってのも論外だし
事業部のシステム部門があるような大企業でマスタの更新バッチすら作らないような本社システム部門はかなりヤバい てか、事業部がどんな大きさなのかわからんけどシステム部門を抱えてるレベルなら事業部のDB持ってないの?
コンプライアンスとかで本社のシステム部門が統括管理してるのかな?
それであっても簡単なメンテツールを事業部システム部門が作ればいいだけだと思うけど… updateなのに重複するキーを挿入できませんと怒られる
そんなこと有り得るのかな キー値を書き換えた
トリガーでなんかした
まあ他にもあるかもしれんが、ありえる >>567>>568
おっしゃる通りです
日立とかIBMのホスト系しか扱ったことがない方が多くて、人員も世代交代に失敗していて古いシステム構成のまま改悪が続いてる
事業部側も人員がいなくてシニア契約社員が主戦力になってるような会社なんです
若い有能な人は見切りをつけて転職していく
個々の担当者はそれなりなんだと思うけど、経営がちょっと抜けてる感じでどうしようもない >>570
ありえました
と同時に絶望しました
もう作った人に修正させてと思いました 付加列インデックスって関数の引数に使ってくれる?
where句、select句どっちも使う、使わない、どっちかしか使わないとかわかる資料ってどっかにあるかな? SQL Serverは関数インデックスをサポートしてないから
付加列インデックスかどうかやkeyかnon-keyかに関係なく
関数の引数に使った場合はindex seekにはならないよ
computed columnでkeyならwhere句で使える
non-keyはwhere句では使えない >>575
やっぱそうだよね。
なんか遅いクエリの実行プラン確認したときに出てきた足りないindexの中の付加列にisnull関数噛ませてるカラムが出てたんでもしやと思ったんだけど。
バージョンは2017です
ありがとうございました ん?
isnullってことはwhere句で使ってるんじゃないの?
であれば付加列インデックスの使い方間違ってる
あとちゃんとインデックス作っても
isnull自体にパフォーマンスの問題あるから違う方法に変えたほうがいいかもしれない >>577
そう。where句で使っていてselect句にはないカラムです。
付加列インデックスの使い方の件はわかってたんだけど、実行プランの足りないインデックスにisnull関数に噛ませてるカラムが付加列に入ったインデックスが出てきたんで、あれ思い違いしてたんかな?と思って質問させていただきました。
違う方法についてはよくある触りにくい、他の方法はコストがかかるってやつです。
まぁ、インデックスの追加も方法の一つとして、他の案も含めて考えます。 この一連のやり取りが理解できないのは俺だけか?
とりあえず、その他の条件が満たされるなら
関数の引数でも、付加列の目的である余計なキー参照はしなくて済むぞ >>579
分かりづらかったのならすまんね。
今回の質問は問題解決ではなく仕様確認になるのかな
とある遅いクエリ流したら実行プランの確認でこれが足りないよって言われたインデックスにwhere句のisnull関数の引数に使ってるカラムが付加列として出てきました。
関数の引数にインデックスのカラムは使えないと思ってたんだけど、付加列ならいけるの?ってことで質問しました。
とある遅いクエリの改善についてはどうでもいいです 使えないとはどういうことを言ってるんだ?
インデックスはってもそのカラムが使えなくなることなんてないぞ
実行時にそのインデックス使うかどうかはわからんがな
そのパターンでインデックス使って早くなるのは大体はキー参照しなくて済むパターン
だから通常のインデックス列ではなくて付加列で良いというお薦めだろう >>582
日本語下手でごめんね。
例えば、
create table AAAA
(
colmun01 char(4) not null
,colmun02 char(4) not null
,seq int not null
,first_date varchar(8) null
,last_date varchar(8) null
,primary key
(
colmun01
,colmun02
,seq
)
)
ってテーブルがあって
select colmun01
from AAAA
where colmun02 = 'BBBB'
and '20210401' between isnull(first_date, '00000000') and isnull(last_date, '99999999')
ってクエリを実行して実際の実行プランを確認したら
CREATE INDEX CCCC
ON DDDD( colmun02)
include(first_date,last_date )
が足りないんじゃない?
って出ました。
付加列の中のカラムはisnull関数の引数なんだけどなんで?
ってなったんで最初の質問になりました。
(WEBだけだけど)調べても出てこなかったんで
first_date、last_date が date型じゃなくてvarchar型 なのは許して。元のテーブルがそうなんで サジェストされてるインデックス追加すれば付加列としての効果はあるね
でもそもそもBETWEENの左側に固定値をもってくる書き方って有りなのかなぁ
isnull不要にしてfirst_data, last_dateをキーとしてインデックスはっても
Index Seekにはならないような気がするんだけど実際どうなんだろ >>585
付加列の効果あるんですか。
>でもそもそもBETWEENの左側に固定値をも>ってくる書き方って有りなのかなぁ
なるでしょっ、て思い込んでたんで改めて
isnull不要にしてfirst_data, last_dateをキーとしてインデックスはってfirst_data, last_dateのbetweenのみで実行してみたら、最適化の段階で大なり小なりに展開されてindex seekになってますね。実行プラン見る限りは ☓ isnull不要にしてfirst_data, last_dateをキーとしてインデックスはってfirst_data, last_dateのbetweenのみで実行してみたら
○ first_data, last_dateをキーとしてインデックスはってisnull不要にしたfirst_data, last_dateのbetweenのみで実行してみたら >>585
もうあんたの書き込みはネタとしか思えないんだけど? >>591
こだわっている理由というか、このクエリでなんでこの条件がパフォーマンス改善の候補として上がってきたのかの理由が知りたい。
自分の今持っている知識が間違っているのだろうけど、この付加列が入ってくる理由がわからないので
前にも書いたけど、元の処理のパフォーマンス改善自体はどうでもいい isnull関数を使っているから、他人には何を知りたいのかわからない。 インデックスに付加列しとけばインデックスの読み込みだけで済むからでしょう
付加列にないカラムを参照したらインデックス以外にテーブルも読み込みしないといけないから遅くなる インデックススキャンにしなければいけない→テーブル全体のインデックスを作る→このインデックスはいらないのでは? 付加列の話を出したものです。
一応簡易的にデータを作って確認してみたので結果を書きます。
環境:
Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Express Edition (64-bit)
on Windows 10 Pro 10.0 <X64> (Build 19043: ) (Hypervisor)
実施内容:
互換性レベル:SQL Server2017 のデータベース上で
create table AAAA
(
colmun01 char(4) not null
,colmun02 char(4) not null
,seq int not null
,first_date varchar(8) null
,last_date varchar(8) null
,primary key clustered
(
colmun01
,colmun02
,seq
)
)
の形のテーブルをAAAA〜EEEEの5つ作成。同一のデータを100万件投入
各々
CREATE INDEX IX_XXXX ON BBBB(colmun02)
CREATE INDEX IX_XXXX ON CCCC(colmun02) include(first_date, last_date )
CREATE INDEX IX_XXXX ON DDDD(colmun02, first_date, last_date )
CREATE INDEX IX_XXXX ON EEEE(first_date, last_date )
のindexを作成
各テーブルに対しての
select colmun01
from <テーブル名>
where colmun02 = 'BBBB'
and '20210401' between isnull(first_date, '00000000') and isnull(last_date, '99999999')
を1クエリでまとめて実行し実際の実行プランを確認 結果:
AAAA Clustered Index Scan となりクエリコスト30%
BBBB Clustered Index Scan となりクエリコスト31% ※作成したindexは効かず
CCCC IX_XXXX がindex Seek となりクエリコスト6%
DDDD IX_XXXX がindex Seek となりクエリコスト6%
DDDD IX_XXXX がindex Scan となりクエリコスト28%
となりました。
この結果からIndexのキー列、または付加列が関数の引数として使用されている場合もindexが効く場合があるようです
ちなみにBBBBテーブルの実行の際にヒント句でindexを強制した場合は
AAAA クエリコスト8%
BBBB クエリコスト81%
CCCC クエリコスト2%
DDDD クエリコスト2%
DDDD クエリコスト8%
となりました。 >>575の通りの理解だったので、なぜこのように動くのかがわかりません。
何か知っている方や理解に誤りなどありましたら教えていただけると助かります >>601
演算のパーセント表示だけで比較されてもわからんがな >>603
では、他にどのような情報がアレバ良いのですか? クエリコストの大小を比較したいなら各クエリ全体のコスト
コスト1000の6%と、コスト10の30%なら後者のほうが効率がいいからね
SET SHOWPLAN_ALL ON;
SET STATISTICS PROFILE ON;
SET STATICTICS IO ON;
↑この3つでそれぞれ表示される値の意味をdocs見て把握しておくといいと思う >>605
ありがとうございます。
ただ今回自分が知りたいことはコストはあまり関係ないんですよね。
違う結果になったことを明示するための情報としてコストを出したつもりでしたが下記の知りたいことからすると不要な情報でした。
すみません。
知りたいことは実行したクエリ
select colmun01
from <テーブル名>
where colmun02 = 'BBBB'
and '20210401' between isnull(first_date, '00000000') and isnull(last_date, '99999999')
のfirst_date,last_dateはisnull関数の引数なのになぜ付加列、またはindexのキー列が利用されて、index seekのみで解決されているのか?
になります。※1
元々indexのキー列、付加列は関数の引数や計算が含まれるとwhere句で利用されない(と思っていた)ため、CCCCテーブル、DDDDテーブルの実行はindex seek後にクラスター化indexのキー参照が起こると思っていました。
これはヒント句でindexの利用を矯正したBBBBの実行と同じ動きです。
一応テストした結果として、※1については解決できる場合があり、そもそもの自分の知識が誤っていることがわかりました。
ので「indexのキー列、付加列が関数の引数や計算が含まれてもwhere句で利用される」のはどのような場合か、明確な基準があるのかなどがわかれば教えていただければとおもいます。
一部表現が適切でなく伝わりにくい部分があるかと思いますが汲み取っていただけると助かります。 利用されないのはオプティマイザが利用しても効果がないと判断したからで、効果があると思えば利用する
それだけのこと
べつに引数や計算だろうと直接の比較だろうと同じなんだが >>606
column02だけでindex seekしてるのか
column02に加えてfirst_dateやlast_date含めてindex seekしてるのかseek predicatesで確認した? >>608
これでした。ありがとうございます。
index seekの中を確認したところ、column02のみがseekでisnull関数の引数になっているfirst_dateとlast_dateはwhereでの参照でした。
CCCCとDDDDのクエリからisnull関数を外したものの実行結果と比較していろいろ合点がいきました。
GUI上のindex seekの表示しか見ていない浅い確認でそれ以降の知識がなかったため、自力でここにたどり着くのは難しかったと思います。
あらためてありがとうございました。
ついでにで申し訳ないのですが、今まで実行プラン上でindex seek、またはindex scanになっていることを「indexが効いている」と表現していたのですが、これは一般的に間違っていますか?
質問の中で色々表現がおかしく、理解しづらい部分もあったと思いますので少しでも修正できればと思っております。 >>607
今迄はあなたと同じように「オプティマイザの気分」で済ませてたのですが、今回は少し突き詰めたかったのです SQLserverのハードを買い替えようと検討してもるんだけどストレージをSSDしても大丈夫か聞きたい。
SSDは書き込みの回数制限があり、頻繁な書き込みのあるSQLserverには向かないと昔聞いた事あったんだけど今は大丈夫か教えて下さい。 >>611
SQLserverに向かないってよりもアクセス頻度の問題のような気がするけど。
ssdって言ってもqlc,tlc,mlcのような種類もあるし。
ただ、前にベンダーに見積もりとった時に聞いたのは利用用途が中規模のwebサービスなら、5年位であればそんなに気にしなくて良いって言われた。(ここでのSSDの種類が何か、中規模がどの程度かは忘れた。参考にならずすまん)
ちなみにどんな利用用途なの?
社内用か、インターネットに公開してるサービスなのか(1日のアクセスはどの程度か)、それとも個人用か
個人用でもなければどこかのベンダーに話聞いちゃった方がいいかも >>612
アドバイスありがとう
用途は会社でデータウエアハウス的利用です。日次で基幹システムからデータ取得して蓄積するようなデータサーバです。
DELLの直販で買う予定でサーバ向けSAS接続のSSDだから大丈夫だと思う。
一応DELLに問い合わせしときました。 レプリケーションというのを初めて設定しているのですが
「スナップショットエージェントが起動していません」のMSGが
出てスナップショットが取れません。
これってSQL Server エージェントとは別物ですか?
SQL Server エージェントは起動しているのに…。
すみませんが、わかる方がいたら教えて下さい。 オブジェクト名'sysservers'が無効です。エラー208
これどういった調査をすれば良いのでしょうか? 将来のバージョンで廃止予定とかになってるけど、バージョンいくつ使ってるんだろうな 何をどうしたらそのエラーが出たのかと、エラーメッセージを正確にかけ サブスクリプションの新規ウィザードで
パブリケーションを選択して次へをクリックすると
先のメッセージと一緒に「パブリケーションの
情報が取得できません」のMSGが出て先に進めません。
SQL SERVER2008R2です。
2019の評価版で試すと問題なく設定できます。 パブリケーション側のバージョンが2008なのかな?
SSMSのバージョンを2008をサポートしてる古いやつで試すといいんでない? >>622
パブリケーション側が2008です。
SSMSのバージョンについては
全く頭にありませんでした
試してみます
ありがとうございました >>622
サブスクリプション側のSSMSをver10に変えたら出来ました。
本当にありがとうございました。 大したデータ量でもないのに「クエリの実行中にMemoryGrantを25秒間待機する必要がありました」との警告が出て処理が遅くなるんですが対処法はないでしょうか。
また値が1, (2 or 3), (4 or 5)でそれぞれ集計したいのですがどのようなSQLにすればいいのでしょうか >>625
>大したデータ量でもないのに「クエリの実行中にMemoryGrantを25秒間待機する必要がありました」との警告が出て処理が遅くなるんですが対処法はないでしょうか。
テーブルとインデックスの構成、クエリ内容、実行プランを確認して原因を突き止めてください
原因が分かったらテーブルやインデックスを変更したりクエリを変更して対処してください
>また値が1, (2 or 3), (4 or 5)でそれぞれ集計したいのですがどのようなSQLにすればいいのでしょうか
現在のテーブル構造と欲しい結果のイメージを提示してください >>625
> また値が1, (2 or 3), (4 or 5)でそれぞれ集計したいのですがどのようなSQLにすればいいのでしょうか
select
case when 値 = 1 then '1' when 値 = 2 or 値 = 3 then '2 or 3' when 値 = 4 or 値 = 5 then '4 or 5' end as 値,
sum(集計列) as 集計
from テーブル
group by
case when 値 = 1 then '1' when 値 = 2 or 値 = 3 then '2 or 3' when 値 = 4 or 値 = 5 then '4 or 5' end 開発サーバにいれるSQLServerのエディションをDeveloperにするの問題ないでしょうか
利用目的はシステム改修の開発単体テストから総合テスト、受入テスト
あとは本番サーバで不具合でたときの検証目的で使用するのは数名〜Max20人くらい
バージョンアップの準備してて、経緯不明なんですが開発サーバが今Enterprise入ってて変えて問題ないなら変えたい
本番はEnterpriseです >>625
MemoryGrantで待機ってメモリが足りてない
・SQLServer以外のプロセスがメモリ食ってSQLServerのメモリが小さくなってる可能性
→SQLServerの最大メモリ最小メモリの設定、SQLServerのサービスアカウントにメモリのページロックの特権がついてるか
・メモリめっちゃ使うクエリが同タイミングで動いてる可能性
→パフォーマンスモニタのBufferManagerカウンターで急激な変動がないか確認、動的管理ビューでその時間帯に動いてたクエリででかいのを調べる
・そのクエリがめっちゃメモリ食う
→これは626
昔使うメモリの計算に不具合あった覚えあるけど、あれは2008か2012くらいのSPで解消されてたような Express版は10GBまでは使用できるようですが、
10GBを超えそうだという警告などは設定できるのでしょうか?
定期的に使用領域を確認していないと、ある日突然なにもできなくなってしまうのでしょうか? >>630
このページにあるクエリを実行するスクリプトを作成し、タスクスケジューラで定期実行してみてはどうでしょうか
クエリの結果をもとに、自分で決めたしきいち値超えたら警告メールを送るような処理も加えると監視をほぼ自動化できそうですね >>630
ページの情報が抜けてた。。。
「データベースのデータとログの領域情報を表示する」でググってみてください ディスク容量を常に気にしていない運用という点が突っ込みどころなんだろうな