トランザクション領域の制限があり、トランザクションログがいっぱいになってしまう。
http://www.sqlpassj.org/bbs/ml_disp.aspx?forum_id=4¤t_page=1&disp_mode=4&detail_mode=1&message_id=19322
そこで、トランザクションモードをRECOVERY FULL から BULK_LOGGED に一旦変更し
その後、またBULK_LOGGED からRECOVERY FULL に戻すことで
トランザクション領域制限の問題をクリアできました。
>> フルモードから一括記録モードに変更する前に一旦バックアップが取られ
>> 一括記録モードからフルモードに変更する前にバックアップが取られて
>> 保存されるのでしょうか??
>バックアップは明示的に取得しない限り自動ではとられません。
>
>一括ログ記録に変更するとトランザクションログに CheckPoint が生成されるだ
>けで、変更はありません。
>
>復旧モデルを一括ログ記録に変更すると日時指定の復旧ができなくなりますので、
>そのあたりは検討してください。
http://www.atmarkit.co.jp/fdb/rensai/sqlstune01/sqlstune01_1.html
・SQL Serverのチューニングについて
・動的メモリ管理のメカニズム
http://www.atmarkit.co.jp/fdb/rensai/sqlstune01/sqlstune01_2.html
・システム管理者が行うチューニング作業
・メモリの使用状況の監視
・SQL Serverの監視機能を使用する
◎DBCC SQLPERF（LRUSTATS）が返す値の意味と推奨される閾値
物理メモリの空きが少なくなると、メモリ プール内のバッファキャッシュに必要なデータページが見つからず、ページングが発生し、物理ディスクへのIOが増加することになります。
各値が閾値を超えるようになった場合、物理メモリが不足していることが考えられますので、メモリの追加を検討します。また、フリーバッファプール内の現在のキャッシュバッファ数を示すFree Buffersが0の場合も、ページングが発生した状態になりますので、メモリを追加
http://www.atmarkit.co.jp/fdb/rensai/sqlstune01/sqlstune01_3.html
・混合サーバ構成時のメモリチューニング
・物理メモリの予約
◎通常、サーバをSQL Server専用で稼働させる場合は、これらの設定を変更する必要はありませんが、1台のサーバに複数のSQL Serverインスタンスを稼働させる場合やIIS…
◎夜間は分析用のSQL Serverインスタンスで、データロードからOLAPストアの再構築のために多くの負荷がかけられるような場合…
バッチ処理速度を改善するため、SQLServer2005からSQLServer2005にUPすることを検討していたが、よく考えてからにした方が良さそう。最適な環境が何であるか…下記のサイトは参考になった。
http://forums.microsoft.com/MSDN-JA/ShowPost.aspx?PostID=851335&SiteID=7
Windows Vista(RC1)
SQL Server 2005 Express(SP1)
速度面も問題なく、動作しています。快適です。開発環境になにか問題があるようです。
http://www7.big.or.jp/~pinball/discus/sqls/30176.html
SQLServer2005にしたら処理速度等が上がるかなと安易に考えていたのですが…
データベースモデル毎のトランザクションログについて
SQL Serverのトランザクションログは、運用上けっこう扱いが難しいと Ver6.5の時から感じている。大量データのバッチ処理では、数GB以上が必要になることがあるが、運用してみないと実際に必要とされるサイズは分らない。自動拡張は親切なようで、不親切だと思う。使い始めは楽だが、その後が大変。十分な知識と調査が予め必要だと思う。
下記のサイトは参考になった
http://www.sqlpassj.org/bbs/ml_disp.aspx?forum_id=4¤t_page=1&disp_mode=4&detail_mode=1&message_id=12789
http://www.sqlpassj.org/bbs/ml_disp.aspx?forum_id=4¤t_page=1&disp_mode=4&detail_mode=1&message_id=12813
http://www.sqlpassj.org/bbs/ml_disp.aspx?forum_id=4¤t_page=1&disp_mode=4&detail_mode=1&message_id=12815
http://www.sqlpassj.org/bbs/ml_disp.aspx?forum_id=4¤t_page=1&disp_mode=4&detail_mode=1&message_id=12820
http://www.sqlpassj.org/bbs/ml_disp.aspx?forum_id=4¤t_page=1&disp_mode=4&detail_mode=1&message_id=12834
http://www.sqlpassj.org/bbs/ml_disp.aspx?forum_id=4¤t_page=1&disp_mode=4&detail_mode=1&message_id=12852
http://www.sqlpassj.org/bbs/ml_disp.aspx?forum_id=4¤t_page=1&disp_mode=4&detail_mode=1&message_id=13235
ひとつのクエリーの中で集計関数を繰り返し利用する
場合、パフォーマンスは落ちないのか ?
http://www.sqlpassj.org/bbs/ml_disp.aspx?forum_id=4¤t_page=1&disp_mode=4&detail_mode=1&message_id=13237
早かったですが、その差は数ミリ秒でした。
やはり実行プランが表示する通り、SUMの結果を使い回しているようですね。
CHAR と VARCHARは、どちらかというとCHARの方が優れているように言われているが、VARCHARの方がいろいろな面で使いやすいと個人的には思う。
http://www.sqlpassj.org/bbs/ml_disp.aspx?forum_id=4¤t_page=1&disp_mode=4&detail_mode=1&message_id=14849
■質問
CHAR と VARCHAR (NCHAR/NVARCHAR) はどのように
使い分けられていますでしょうか ?
* 基本的に CHAR を利用している
* 基本的に VARCHAR を利用している
* 必要な場合に CHAR を利用している
- 固定長
- 頻繁に更新がある
- インデックスに利用している
■背景
データベースを設計する場合、複雑性を下げる意味で、
データタイプを統一する場合があります。
CHAR, VARCHAR を使い分けるよりも、VARCHAR で
統一してしまうというものです。
また、可変長の文字列を CHAR で扱うと、あちこちで、
TRIM 処理が必要になります。
■例
1. VARCHAR に統一
サーバーに十分なパフォーマンスがあるので、
CHAR にするメリットがない。
2. CHAR に統一
パフォーマンスや設計の経験上、CHAR にしている。
TRIM による適宜、末尾の空白を除去している。
Null を許可するデータタイプの選択は ?
http://www.sqlpassj.org/bbs/ml_disp.aspx?forum_id=4¤t_page=1&disp_mode=4&detail_mode=1&message_id=14847
■質問
システムを設計する場合、Null を許可する引数のデータタイプは
なにを使われていますか ?
* 文字列 string
* Null を許可する専用データタイプ
* SqlInt16 など Sql の提供するデータタイプ
. ストアドプロシージャの引数まで、文字列として処理し、
クエリーをかける段階で、CAST または、CONVERT し、
適切なデータ型にする。
2. ビジネス層までは、文字列として処理し、データ層に渡す段階で、
SqlInt16 などに変換する
そもそも、Null をできるだけ許可しないという方法もあるかと思います。
★DBCC DBREINDEX(テーブル名) を実行したところ、バッチ処理の速度が半分の時間で処理されるように
なった。効果が高い(2007/1/17)
「ＳQL Sｅｒｖｅｒ2005の次のバージョンからは、削除される可能性があるので、新規の開発作業ではこの機能を使用しないようにし、現在この機能を使用しているアプリケーションは修正することを検討してください。 代わりに ALTER INDEX を使用してください。」とのことです。
DBCC SHOWCONTIG と DBCC DBREINDEX について
http://www.sqlpassj.org/bbs/ml_disp.aspx?forum_id=4¤t_page=1&disp_mode=4&detail_mode=1&message_id=15627
インデックスの再構築を実行しましたがスキャン密度が改善されません。
【インデックスの再構築実行】
USE MKGWare
GO
DBCC DBREINDEX (てーぶる名,'',60)
GO
http://www.sqlpassj.org/bbs/ml_disp.aspx?forum_id=4¤t_page=1&disp_mode=4&detail_mode=1&message_id=15636
インデックス ID が 0 は ヒープ(実データ、インデックスではない) なので、断片化は解消できません。
どうしても解消したい場合は、bcp とか DTS とかでデータをファイルにエクスポートして、
インポートしますが、データ量が 5 エクステントなので、断片化を解消しても、速度はほとんど
変わらないと思います。それよりも、次のコマンドを実行して、
他のインデックスが断片化しているかどうかを調べた方がよいと思います
(速度が遅くて困っているのであれば、ですが...)。
DBCC SHOWCONTIG('テーブル名') WITH ALL_INDEXES
http://www.sqlpassj.org/bbs/ml_disp.aspx?forum_id=4¤t_page=35&disp_mode=4&ispre=0&mid=7682
http://www.sqlpassj.org/bbs/ml_disp.aspx?forum_id=4¤t_page=1&disp_mode=4&detail_mode=1&message_id=7663
DBCC INDEXDEFRAGを実行すればよいです。
なお、やたらめったにインデックスの再構築をするのではなく、インデックスの状態を確認してからにしたほうがよいとおもいますが。DBCC SHOWCONTIG と実行して、スキャン密度が低いテーブル、論理スキャンフラグメンテーションの大きなテーブル、ページ数の多いテーブル、アプリケーションからの利用が多いテーブルにはというところをチェックしてみてください。
効率的なインデックスの作成について
http://www.sqlpassj.org/bbs/ml_disp.aspx?forum_id=4¤t_page=1&disp_mode=4&detail_mode=1&message_id=7421
DBCC SHOWCONTIG('テーブル名') WITH ALL_INDEXES
で調べて
DBCC INDEXDEFRAG (データベース名,テーブル名,インデックス名)
でインデックスの断片化を実行
無理をして一つの巨大なSQLを組み立てるより、分割して効率よく処理した方がデータ量による処理時間の増加率が把握しやすい。下記のサイトで説明されていることは、もっともだと思う。
http://www.sqlpassj.org/bbs/ml_disp.aspx?forum_id=4¤t_page=1&disp_mode=4&detail_mode=1&message_id=16173
私のSQL Server(のクエリ)の師匠からは
・クエリを考えるときには「データの束」を意識する
・Notは極力使わない
・カーソルも極力使わない
・Or In も極力使わない
・ネストの深いサブクエリやUnionを使うぐらいなら一時テーブルを使え
というようなアドバイスを受けている
http://www.sqlpassj.org/bbs/ml_disp.aspx?forum_id=4¤t_page=1&disp_mode=4&detail_mode=1&message_id=16176
ネストの深いサブクエリ等ではレコードを生成する都度サブクエリを実行する？
ため、最悪「サブクエリ１回の実行時間×レコード数×テーブル参照時間」が
必要であるのに対して、一時テーブルを作成するケースではテーブルを参照
するだけなので「サブクエリ１回の実行時間＋レコード数×テーブル参照時間」
となり、生成されるレコード数が多いほど顕著に差が出た様子です。
２０万レコード程度のテーブルから結果を集計して抽出するのにあたり、
１分近くかかっていたものが一時テーブルの利用で一気に１秒以下まで
短縮できた実績があります。
http://www.sqlpassj.org/bbs/ml_disp.aspx?forum_id=4¤t_page=1&disp_mode=4&detail_mode=1&message_id=16190
・結合する前にフィルタをかける。
・フィルタは絞り込み効果の高い順にかける。
実際に同じような現象が発生した。Windos2003 Server 2CPU Xeon で2GBの物理メモリ、WindowXP Pro Pentium4で1GBの物理メモリの２台で試したが結果は後者の方が良かった。おかしな現象である。
http://www.sqlpassj.org/bbs/ml_disp.aspx?forum_id=4¤t_page=1&disp_mode=4&detail_mode=1&message_id=16686
開発用のノートPCやデスクトップPCで動作させるより，
実機として用意したサーバーPCの方が遅くて悩んでいます。
http://www.sqlpassj.org/bbs/ml_disp.aspx?forum_id=4¤t_page=1&disp_mode=4&detail_mode=1&message_id=16689
特に何か根拠があるわけではないのですが、P4ベースのCPUになってから
どれを使ってもSQL Serverの処理が遅くなったような感じがします。
当時はなぜかPⅢベースのマシンの方がP4ベースのマシンより
パフォーマンスが出ていたので、手に入るギリギリまでPⅢベースの
マシンを導入していた記憶があります。
http://www.sqlpassj.org/bbs/ml_disp.aspx?forum_id=4¤t_page=1&disp_mode=4&detail_mode=1&message_id=16693
う～ん、これだと疑わしいのは RAID かな。
それと Xeon 2.8G ってことは HyperThreading が有効になっていると思われます。
http://www.sqlpassj.org/bbs/ml_disp.aspx?forum_id=4¤t_page=1&disp_mode=4&detail_mode=1&message_id=15373
本番サーバ ：SQL2K sp2 CPU2個 メモリ2G RAID5
テスト機 ：SQL2K SPなし CPU1個 メモリ256MB
と言う両方のマシンで同じＤＢに対してあるクエリーを実行
した時に、テスト機の方が2時間のところを、本番機は２０時間
たっても終わらないという状況です。
SQLSERVERのパラメータは同じ設定になっています
http://www.sqlpassj.org/bbs/ml_disp.aspx?forum_id=4¤t_page=1&disp_mode=4&detail_mode=1&message_id=15375
max degree of parallelismは確か規定値は０のなんで
積んでいるだけのＣＰＵを使うと認識しています。
【SQL】
select
SUBSTRING(CONVERT(char,DATEADD(m,-6,'2006/5/01'),112),1,6) AS MAE6,
SUBSTRING(CONVERT(char,DATEADD(m,-5,'2006/5/01'),112),1,6) AS MAE5,
SUBSTRING(CONVERT(char,DATEADD(m,-4,'2006/5/01'),112),1,6) AS MAE4,
SUBSTRING(CONVERT(char,DATEADD(m,-3,'2006/5/01'),112),1,6) AS MAE3,
SUBSTRING(CONVERT(char,DATEADD(m,-2,'2006/5/01'),112),1,6) AS MAE2,
SUBSTRING(CONVERT(char,DATEADD(m,-1,'2006/5/01'),112),1,6) AS MAE1,
SUBSTRING(CONVERT(char,DATEADD(m,0,'2006/5/01'),112),1,6) AS TOU,
SUBSTRING(CONVERT(char,DATEADD(m,1,'2006/5/01'),112),1,6) AS ATO1,
SUBSTRING(CONVERT(char,DATEADD(m,2,'2006/5/01'),112),1,6) AS ATO2,
SUBSTRING(CONVERT(char,DATEADD(m,3,'2006/5/01'),112),1,6) AS ATO3,
SUBSTRING(CONVERT(char,DATEADD(m,4,'2006/5/01'),112),1,6) AS ATO4,
SUBSTRING(CONVERT(char,DATEADD(m,5,'2006/5/01'),112),1,6) AS ATO5,
SUBSTRING(CONVERT(char,DATEADD(m,6,'2006/5/01'),112),1,6) AS ATO6
【結果】
200511 200512 200601 200602 200603 200604 200605 200606 200607 200608 200609 200610 200611
参考
http://oshiete1.goo.ne.jp/kotaeru.php3?q=1310728
http://www.syswat.com/sqltips.html#tpc5020
技術者は処理を一度に行うように考えるせいか、巨大なＳＱＬを組み立てることがある。データ量が少なければ何の問題もないのですが、１００万件以上になってくると、処理時間が予想不可能になる。
昔のファイルの扱いのように、データをシーケンシャル読んで処理する方法の方が優れてる場合もあるため、実装方針を決める際にSQLの規約を決めておかないと、結合・システムテストで大変なことになる。
http://www.atmarkit.co.jp/bbs/phpBB/viewtopic.php?topic=31846&forum=26&36
一般的に集計関数を使用したサブクエリを自己結合した場合に、 クエリのレスポンスが著しく悪化することがあります。 その場合集計結果を一時テーブルに出力して、一時テーブルの結合条件にインデックスを作成し
本体と結合すると大幅にレスポンスが改善すると思います。
クエリの条件の部分に変数を使用すると処理が遅くなる事があります。
この場合、検索条件にユニークインデックスが含まれないことが考えられます。
クエリオプティマイザが変数だとテーブルスキャンを選択するようです。
回避策としては、ヒント文を入れるといいでしょう。
http://www.atmarkit.co.jp/bbs/phpBB/viewtopic.php?topic=20423&forum=26&5
ヒント文の記述例
select * from shouhin_M with (index = shouhin_M_index01)
DBCC DROPCLEANBUFFERS を実行してデータバッファキャッシュをクリアしておかないと、処理時間の正確な比較ができない。
http://www.sqlpassj.org/bbs/bbs_disp.aspx?forum_id=1¤t_page=1&disp_mode=2&detail_mode=1&message_id=2372
ユーザ定義関数とストアドの速度面での違いが、今ひとつ理解出来ないのだが、結果として「ユーザー定義関数」をむやみに使うと速度が著しく低下することが有った。下記のサイトで説明されたているとおり使い方については要注意だと思った。下記のサイトから、改善のためのヒントが得られた。
http://comfair2.blog24.fc2.com/blog-date-200512.html
■ユーザー定義関数
最近、性能改善をやっていて身にしみて感じるのは「ユーザ定義関数」 のオーバーヘッドです。 多くの場合、性能改善の対象となるのはバッチ処理だと思いますが、 モノによってはコストの2/3を占めている場合があります。
「ユーザ定義関数」のコストはアナライザの実行プランに表示されな いため、「なぜ遅いんだろう？」ということになりがちです。 プロファイラでトレースを取ると、「え！なにこれ？」っていうほど表示されるのでなんとなくわかるのですが。
ユーザ定義関数は、簡単なサブルーチン的な処理（例えば端数処理とか） に使用すると開発効率は上がります。同じようなコードを埋め込まなくてもよいのでメンテナンス性もよいです。しかし、バッチの性能面から
見た場合は・・・。 「ユーザ定義関数」をどうしてもバッチで使用したい場合はselect句で。from句以降は使用しないこと。
■性能改善---時間計測時の注意
旧処理と新処理の処理時間比較計測・・・性能改善を行った場合は必ず行う作業です。一番大変なのはボトルネックを見つけるのはもちろんなのですが、テストデータを作成することでしょうか。
実際に蓄えられるデータ件数を見積もって、それと同等のデータを作成するわけです。
最近は個人データの流出事件等もあってユーザさんのデータを簡単にもらってくるわけにはいきませんから、なおさらです。
まあ、基本的なことなので、みなさんお分かりのこととは思いますが・・・。処理時間計測を行う場合は必ずリブートまたはサービスの停止／開始を行い、キャッシュをクリアしてから行いましょう。特に行数の多いSQLの場合、構文解析だけで数秒から数十秒かかる場合がありますから、へたをすると2倍くらい計測時間が違ってきます。
■ただいま性能改善中----(3)
性能改善、やっと終了しました。（というかさせました。） どうも、性能改善というものは凝りだしたらキリがないので困りものです。 今回、最後までボトルネックだったのは、巨大な実績テーブルの扱い方でした。from句のなかでテーブルをjoinする際、できるだけ小さな結果セットにしたほうが速いということは解っていたのですが、これを実現するのが・・・。
２分ほどかかっていた１つのSQL文が数秒で終わるようになりました。 やはり、性能改善は処理内容がある程度わかっていないと難しいものです。（時間もかかりますし。）
■パフォーマンス向上のためのヒント
SQLServerのパフォーマンスの向上に役立つ７つの方法には以下のようなものがあります。
1.プロファイラを使用する
2.パフォーマンスモニタを活用する
3.クエリアナライザのプラン表示機能を活用する
4.インデックスチューニングウィザードを使用する
5.ディスクの使用方法を考える
6.充分なメモリを確保する
7.SQLServerの自己管理機能に任せる（下手にさわらない）
このなかで面白いのは 7.でしょうか。
下手に設定するとSQLServerは機嫌をそこねたりします。
SQL Server 2000 のインデックス付きビューによるパフォーマンスの向上
https://www.microsoft.com/japan/msdn/sqlserver/sql2000/indexedviews.aspx#indexedviews_topic4
MicrosoftR SQL ServerTM 2000 製品紹介
http://www.ace.comp.nec.co.jp/sqlserver/sql2000-prodover.html
TOP / ・SQL Server ノート
http://hehao1.seesaa.net/category/430938-1.html
http://hehao1.seesaa.net/article/7792142.html
MS Access/SQLServer/Oracle最新リンク2005
http://www2.famille.ne.jp/~akio1998/l_x106.html
