![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
#author("2020-03-04T05:03:45+00:00","default:admin","admin") #author("2021-12-15T01:37:27+00:00","default:admin","admin") -[[SQL Server で最低限設定が必要な内容:https://qiita.com/yiaowang/items/33b864cf691e01e2f2d9]] -[[Dr. K's SQL Serverチューニング研修:http://www.atmarkit.co.jp/ait/series/2259/]] -[[真・Dr. K's SQL Serverチューニング研修:http://www.atmarkit.co.jp/ait/series/2426/]] -[[Run Best Practices Analyzer Scans and Manage Scan Results:https://docs.microsoft.com/en-us/previous-versions/windows/it-pro/windows-server-2012-R2-and-2012/hh831400(v=ws.11)]] -[[SQL Server の再生のマークアップ言語 (RML) ユーティリティの説明:https://support.microsoft.com/ja-jp/help/944837/description-of-the-replay-markup-language-rml-utilities-for-sql-server]] -[[SQLパフォーマンスtips(SQLServer):https://qiita.com/kazuho39/items/0d3e617661670311ea05]] -[[インデックスと実行計画を理解する@SQLServer:https://qiita.com/okuzou1/items/f710bcde64beb22cd50b]] -[[ZOZOTOWNの冬セール負荷対策で実施したDBサーバーのCPUボトルネック調査手法:https://techblog.zozo.com/entry/sqlserver-tuning-for-winter-sale]] *SQL Server チューニングのポイント [#qbcbf991] -[[SQL Server のチューニングについてまとめてみる - その 22 - ( これだけ分かっていればいいと思われる記事をまとめてみる ):http://ryuchan.hatenablog.com/entry/2016/10/09/174406]] **トランザクション分離レベル [#s786a867] -[[【SQL server】トランザクション分離レベルについて:https://memorandom-nishi.hatenablog.jp/entry/2016/11/03/143533]] -[[【解決方法】SQL Serverデフォルト設定(READ COMMITTED SNAPSHOT OFF)だと同時接続でロック待ちが発生します:https://www.ksakae1216.com/entry/2017/07/18/063000]] -[[DBMSのロック・分離戦略と同時実行制御:https://techinfoofmicrosofttech.osscons.jp/index.php?DBMS%E3%81%AE%E3%83%AD%E3%83%83%E3%82%AF%E3%83%BB%E5%88%86%E9%9B%A2%E6%88%A6%E7%95%A5%E3%81%A8%E5%90%8C%E6%99%82%E5%AE%9F%E8%A1%8C%E5%88%B6%E5%BE%A1]] -SQL Server では READ_COMMITTED_SNAPSHOT がデフォルトで OFF --オーバーヘッドが大きくなることなどが理由か -※ SQL Azure では、READ_COMMITTED_SNAPSHOT がデフォルトで ON -分離レベルのデフォルト値は READ COMMITTED が設定されている -SQL Server の READ COMMITTED は、、select for update の動きとなるため、大抵のシステムでは使い物にならない -Oracle の READ COMMITTED （他で未コミットのレコードは変更前の値を参照する）にするには、READ COMMITTED SNAPSHOT にする必要がある ALTER DATABASE [Database Name] SET READ_COMMITTED_SNAPSHOT ON; -MVCC(READ_COMMITTED_SNAPSHOTをON)にすると同時実行性が高まるため、OFF の時には起きなかった問題が生じる場合があるので注意 **インデックス不足解消 [#md3eb931] **ロックの最小化 [#hab0be24] **暗黙の型変換の抑制 [#b417c5d8] -[[SQL Server のチューニングについてまとめてみる - その6 - ( CONVERT_IMPLICIT、暗黙の型変換の怖さを知ろう ):http://ryuchan.hatenablog.com/entry/2014/09/07/082145]] -条件に指定するカラムに対し、違う型の値で条件を指定すると「暗黙の型変換（CONVERT_IMPLICIT ）」が発生する -Index Scan が発生することでパフォーマンスが落ちる -Index Scan を発生させないためには、暗黙の型変換を発生させないように、正しい型で条件を設定する必要がある --例：nvarchar の列に対して、正しい型で条件を指定 ---×：WHERE NationalIDNumber = 295847284 ---○：WHERE NationalIDNumber = N'295847284' **コンパイル回数の低減 [#e3cd4cbd] *SQL Server Management Studio [#t415538b] *動的管理ビュー [#y057955f] -[[動的管理ビューおよび関数 (Transact-SQL):https://msdn.microsoft.com/ja-jp/library/ms188754(v=sql.120).aspx]] -[[SQLServer: 知ってると便利なDMV(動的管理ビュー)まとめ:https://qiita.com/emotu/items/e92474464008c3ee2ee5]] -SQL Server のチューニングには、dm_* で始まる動的管理ビューを多用する **主要テーブル [#r3a17faf] -sys.dm_db_missing_index_group_stats ... 欠落インデックス グループに関する情報を取得 -sys.dm_db_missing_index_groups ... sys.dm_db_missing_index_group_stats と sys.dm_db_missing_index_details を JOIN するために必要なテーブル -sys.dm_db_missing_index_details ... 欠落したインデックスについての詳細情報を取得 *インデックスの調査 [#f1d975b8] -[[インデックスの統計内容について参照してみる:http://ryuchan.hatenablog.com/entry/2015/02/15/170449]] **インデックス不足数の調査 [#l9a9373a] SELECT DB_NAME(database_id) as [データベース名], count(*) as [期待しているインデックスが存在しない数] FROM sys.dm_db_missing_index_details GROUP BY DB_NAME(database_id) **インデックスを作成した場合のパフォーマンス改善率 [#h40b0bb9] USE [データベース名] SELECT gs.avg_user_impact AS [予測されるクエリパフォーマンス改善率], gs.last_user_seek AS [最後にシークした時間], id.statement AS [テーブル名] , id.equality_columns AS [等値述語に使用できる列], id.inequality_columns AS [不等値述語に使用できる列] , id.included_columns AS [包括列として必要な列], gs.unique_compiles AS [コンパイルおよび再コンパイルの数], gs.user_seeks AS [クエリによって発生したシーク数] FROM sys.dm_db_missing_index_group_stats AS gs INNER JOIN sys.dm_db_missing_index_groups AS ig ON gs.group_handle = ig.index_group_handle INNER JOIN sys.dm_db_missing_index_details AS id ON ig.index_handle = id.index_handle WHERE id.[database_id] =DB_ID() Order By gs.last_user_seek ASC **インデックス統計情報の参照 [#ecb329c9] SELECT [テーブル名] = t.name, [統計名] = s.name, [統計の自動作成] = s.auto_created, [NORECOMPUTEオプション] = s.no_recompute, [フィルター定義] = s.has_filter, [フィルター定義] = s.filter_definition, [行数] = sp.rows, [フィルター化されていない行数] = sp.unfiltered_rows, [ステップ数] = sp.steps, [サンプリング行数] = sp.rows_sampled, [統計情報最終更新日] = sp.last_updated FROM sys.tables AS t INNER JOIN sys.stats AS s ON s.object_id = t.object_id CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp ORDER BY t.name OPTION (RECOMPILE); *コネクション数の調査 [#i1a555ae] -[[「.NET」と「ASP.NET」からのSQL Serverへのコネクションを確認する（１）:http://ips.nekotype.com/4414/#i]] -接続元ホスト毎のコネクション数を確認 select hostname,count(*) from master..sysprocesses where hostname!='' group by hostname -（接続元ホスト, プログラム名, ステータス）毎のコネクション数を確認 select hostname,proguram_name,status,count(*) from master..sysprocesses where hostname!='' group by hostname,proguram_name,status -生データの確認 select * from master..sysprocesses where hostname!=''; *クエリの調査 [#v00001be] **ステートメントのプロファイル情報確認 [#k0fd5efc] -SET STATISTICS PROFILE ON --SQL文の前にこれを付けて実行すると、ステートメントのプロファイル情報を表示する **クエリパフォーマンスの調査 [#h6bc5ac4] SELECT [平均実行時間(msec)] = total_elapsed_time / execution_count / 1000.00, [平均CPU時間(msec)] = total_worker_time / execution_count / 1000.00, [平均物理I/O数] = total_physical_reads / execution_count, [平均論理I/O数] = (total_logical_reads + total_logical_writes)/ execution_count, [SQL文] = SUBSTRING(est.text, (statement_start_offset / 2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(est.text) ELSE statement_end_offset END - statement_start_offset) / 2) + 1) FROM sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS est *実行プランの調査 [#r857e6a6] -[[SQL Server のチューニングについてまとめてみる - その7 - ( プランキャッシュの状態を確認する ):http://ryuchan.hatenablog.com/entry/2014/09/07/232019]] -[[SQL Server のチューニングについてまとめてみる - その19 - ( プランがちゃんと再利用されているか確認してみる ):http://ryuchan.hatenablog.com/entry/2016/05/16/000315]] *パフォーマンス低下の防止策 [#n84ac3a1] **トランザクションログの自動拡張の増分調整 [#w07baf6e] -[[トランザクション ログ ファイルのサイズの管理:https://docs.microsoft.com/ja-jp/sql/relational-databases/logs/manage-the-size-of-the-transaction-log-file]] *データベースの状態調査 [#u14c8726] -[[設定の内容とか、ログの使用量とかのデータベースの状態を把握してみる:http://ryuchan.hatenablog.com/entry/2014/07/11/134204]] *問題 [#qf3554f8] **クエリパフォーマンスの低下 [#af18d1ef] -[[どうする？ SQL Server のクエリ パフォーマンスが低下した！:https://blogs.msdn.microsoft.com/jpsql/2013/09/03/sql-server-5/]] *パフォーマンス改善の施策 [#rb7b58c2] **統計情報の更新 [#h1a138ce] -テーブル単位の更新 UPDATE STATISTICS テーブル名; -データベース全体 EXEC sp_updatestats; **実行プランの再生成 [#y2ff3abe] -インスタンス単位 DBCC FREEPROCCACHE; **インデックス再構築 [#h554e3a6] -インデックス指定 ALTER INDEX <インデックス名> ON <テーブル名> REBUILD; -テーブルに関連する全てのインデックス ALTER INDEX ALL ON <テーブル名> REBUILD; *参考URL [#xa3f9034] **[[都内で働くSEの技術的なひとりごと:http://ryuchan.hatenablog.com]] [#adb30add] 大変勉強になります。 -SQL Server のチューニングについてまとめてみる --[[- その1 - ( インデックスの調査 ):http://ryuchan.hatenablog.com/entry/2013/09/23/134554]] --[[- その2 - ( フィルタ選択されたインデックス ):http://ryuchan.hatenablog.com/entry/2014/04/15/084145]] --[[- その3 - ( パフォーマンスがどれくらい改善される？ ):http://ryuchan.hatenablog.com/entry/2014/05/25/092853]] --[[- その4 - ( クエリパフォーマンスの結果をメールで送る ):http://ryuchan.hatenablog.com/entry/2014/07/10/234507]] --[[- その5 - ( クエリプランから不足インデックス情報を取得する ):http://ryuchan.hatenablog.com/entry/2014/09/05/101526]] --[[- その6 - ( CONVERT_IMPLICIT、暗黙の型変換の怖さを知ろう ):http://ryuchan.hatenablog.com/entry/2014/09/07/082145]] --[[- その7 - ( プランキャッシュの状態を確認する ):http://ryuchan.hatenablog.com/entry/2014/09/07/232019]] --[[- その8 - ( 未使用なインデックスを抽出してみる ):http://ryuchan.hatenablog.com/entry/2014/09/25/110125]] --[[- その9 - ( TempDB の動きも気にしてみる ):http://ryuchan.hatenablog.com/entry/2014/10/05/131131]] --[[- その10 - ( 実行プランの StatementText の中に、調査用に使用するための文字列を埋め込んでみる ):http://ryuchan.hatenablog.com/entry/2014/10/18/122816]] --[[- その11 - ( dm_exec_query_optimizer_info でチューニングの効果を確認してみる ):http://ryuchan.hatenablog.com/entry/2014/10/26/093454]] --[[- その12 - ( SQL Server のメモリ関係で必要なカウンタを抽出してみる ):http://ryuchan.hatenablog.com/entry/2015/01/02/093739]] --[[- その13 - ( その 12 の補足 ):http://ryuchan.hatenablog.com/entry/2015/01/02/163239]] --[[- その14 - ( 少しでもオーバーヘッドを少なくしてみる ):http://ryuchan.hatenablog.com/entry/2015/01/03/101204]] --[[- その15 - ( 無駄なキャッシュプランを探してみる ):http://ryuchan.hatenablog.com/entry/2015/02/15/230216]] --[[- その16 - ( BCP とか、一括系の操作を高速化してみる ):http://ryuchan.hatenablog.com/entry/2016/01/27/163127]] --[[- その16の補足 - ( TABLOCK の動作を確認してみる ):http://ryuchan.hatenablog.com/entry/2016/01/29/201424]] --[[- その17 - ( FOR XML PATH の連結をもう少し高速化してみる ):http://ryuchan.hatenablog.com/entry/2016/02/02/000244]] --[[- その18 - ( とあるプロジェクト向けメモ ):http://ryuchan.hatenablog.com/entry/2016/02/14/192644]] --[[- その19 - ( プランがちゃんと再利用されているか確認してみる ):http://ryuchan.hatenablog.com/entry/2016/05/16/000315]] --[[- その20 - ( きちんとパラメータ化してみる ):http://ryuchan.hatenablog.com/entry/2016/09/24/110106]] --[[- その21 - ( あるプロジェクト向けに書いてみた ):http://ryuchan.hatenablog.com/entry/2016/09/28/114531]] --[[- その 22 - ( これだけ分かっていればいいと思われる記事をまとめてみる ):http://ryuchan.hatenablog.com/entry/2016/10/09/174406]] --[[統計情報を更新すると、『 クエリはリコンパイルされるよね普通 』と思いつつ、なんかうまくいってない感じがすごくするので、sp_recompile を実験してみる:http://ryuchan.hatenablog.com/entry/2015/09/01/233118]] --[[インデックスの統計内容について参照してみる:http://ryuchan.hatenablog.com/entry/2015/02/15/170449]] --[[こんな時、RECOMPILE したほうがいいよねと思って記事書いてみた:http://ryuchan.hatenablog.com/entry/2015/02/04/112308]] --[[データベースファイルが格納されているドライブの残容量をチェックしてみる:http://ryuchan.hatenablog.com/entry/2014/11/12/110657]] --[[設定の内容とか、ログの使用量とかのデータベースの状態を把握してみる:http://ryuchan.hatenablog.com/entry/2014/07/11/134204]] -SQL Server の構造について記事にしてみる --[[- その1 (インデックスの基礎知識) -:http://ryuchan.hatenablog.com/entry/2014/08/11/025627]] --[[- その2 ( 前回の 『 インデックスの基礎知識 』 を実際のテーブルで実験してみる ) -:http://ryuchan.hatenablog.com/entry/2014/08/13/215049]] --[[- その3 ( インデックスの構造を深堀してみる ) -:http://ryuchan.hatenablog.com/entry/2014/08/18/000526]] --[[今更ながら..... SQL Server の構造に関して触れてみる -その1- ( たぶん、何回かにわけるので、その1にしてみた ):http://ryuchan.hatenablog.com/entry/2016/04/24/212242]] **[[SQL Serverトラブルシューティング:http://www.atmarkit.co.jp/ait/series/3394/]] [#y0fb0c37] -[[「あるタイミングから」処理が遅くなり、再起動しても再現されてしまう（パフォーマンストラブル）:http://www.atmarkit.co.jp/ait/articles/1706/12/news002.html]] -[[原因不明の処理遅延が「不定期」に発生する（パフォーマンストラブル）:http://www.atmarkit.co.jp/ait/articles/1706/05/news001.html]] **その他 [#e0c4111b] -[[パフォーマンスの監視とチューニング:https://docs.microsoft.com/ja-jp/sql/relational-databases/performance/monitor-and-tune-for-performance]] -[[SQL Server性能問題の3大要因 システムリソース/クエリ/待機を検証:http://www.unisys.co.jp/solution/tec/atlasbase/s33drt000005ei89-att/dbm_1007_gui.pdf]] -[[SQL Serverのパフォーマンスを 最適化するための10のヒント:http://i.dell.com/sites/doccontent/shared-content/data-sheets/ja/Documents/top_10_tips_for_optimizing-final_jp.pdf]] -[[SQL Server のクエリチューニングの参考情報:http://blog.engineer-memo.com/2014/12/10/sql-server-%E3%81%AE%E3%82%AF%E3%82%A8%E3%83%AA%E3%83%81%E3%83%A5%E3%83%BC%E3%83%8B%E3%83%B3%E3%82%B0%E3%81%AE%E5%8F%82%E8%80%83%E6%83%85%E5%A0%B1/]] -[[SQLチューニング: ソートを発生させないインデックス設計:https://qiita.com/emotu/items/2bd538ebe662aad457c5]] -[[Microsoft SQL Server パフォーマンスチューニング方法:http://tech.kou.asia/?p=812]] -[[Sql server エンジニアに知ってもらいたい!! sql server チューニングアプローチ:https://www.slideshare.net/masayukiozawa/sql-server-sql-server-35998418]] -[[Sql server これだけはやっておこう 最終版:https://www.slideshare.net/elanlilac/sql-server-25769474]] *タイムアウト [#f9b22d69] -[[ASP.NET で SQLのコマンドがタイムアウトした場合の対応方法:https://qiita.com/c-nuts/items/93d0ff46c58c911b93f8]] -[[SQL Serverに.NETで接続しようとすると1.2秒でタイムアウトする場合がある:https://blog.jnito.com/entry/20120219/1329633868]]
