SQLServerパフォーマンス関連情報

## サーバー構築時の確認事項

### 基本確認項目

* tempdbの数をCPUコア数にあわせる。
* x64環境なら「メモリ内のページロック指定」を変え
OLTP環境では、並列処理の最大限度（Max DOP）はCPUコア数の「4分の1」に設定せよ

* [tempdb データファイル数を CPU 数に一致させる](https://docs.microsoft.com/ja-jp/archive/blogs/jpsql/dosdonts-17-tempdb-259)
* [DB管理者がいますぐ確認すべき3つの設定 (1/3)](https://www.atmarkit.co.jp/ait/articles/1009/06/news093.html)
* [SQL Server で最初に設定すべき３つのこと](https://qiita.com/tanuwo/items/264390e2abe0144e2174)

### 大規模システム向け

#### パーティション分割（シャーディング）

* [データの水平的、垂直的、および機能的パーティション分割](https://docs.microsoft.com/ja-jp/azure/architecture/best-practices/data-partitioning)

#### レプリケーション

* [ミラーリングとレプリケーション（SQLServer2005環境）](https://social.msdn.microsoft.com/Forums/ja-JP/7e5d6583-4b62-4fef-bd68-f4c77c2485b8/12511125211254012522125311246412392125241250312522124651254012?forum=sqlserverja)

## テーブル設計時の確認事項

#### テーブル設計

データベースの種類に問わず、気をつけること。

[4ステップで作成する、DB論理設計の手順とチェックポイントまとめ](https://qiita.com/nishina555/items/a79ece1b54faf7240fac)
[SQLアンチパターン勉強会](https://qiita.com/iwata@github/items/9a47ddababd8e0a82662)


#### インデックス

[SQL Server のインデックス設計](https://www.slideshare.net/hamu502/sql-server-102391836)
[SQLServerのインデックスについてざっくりとまとめてみた](https://qiita.com/kz_morita/items/41291516ff3ee2650554)
[SQL Server のインデックス](https://techinfoofmicrosofttech.osscons.jp/index.php?SQL%20Server%20%E3%81%AE%E3%82%A4%E3%83%B3%E3%83%87%E3%83%83%E3%82%AF%E3%82%B9)

#### 付加列インデックス

テーブルへのアクセスを無くすため、必要なデータを全てインデックスに含めることを``カバリングインデックス``と呼ぶ。

SQLServerの場合、``カバリングインデックス``のインデックスのサイズが肥大化する問題を解決する手段として``付加列インデックス``がある。

[インデックスのみのスキャン: テーブルアクセスを 避ける](https://use-the-index-luke.com/ja/sql/clustering/index-only-scan-covering-index)
[付加列インデックスの作成](https://docs.microsoft.com/ja-jp/sql/relational-databases/indexes/create-indexes-with-included-columns?view=sql-server-ver15)

#### フルテキスト検索

* [フルテキスト検索でのクエリ](https://docs.microsoft.com/ja-jp/sql/relational-databases/search/query-with-full-text-search?view=sql-server-ver15)
* [SQL Server のフルテキストインデックスを作成する](https://www.ipentec.com/document/sql-create-fulltext-index)

## SQL実行時の確認事項

* [キャッシュを無駄遣いしないようにクエリを書く (2/3)](https://www.atmarkit.co.jp/ait/articles/1202/23/news132_2.html)
* [SQL Serverのロックについて出来る限り分かりやすく解説](https://qiita.com/maaaaaaaa/items/38fd95b142b07acf7700)
* [SQLパフォーマンスtips(SQLServer)](https://qiita.com/kazuho39/items/0d3e617661670311ea05)

### 一括処理

#### 複数レコードを一括挿入

1000件までなら以下の方法で一度にINSERT出来る。

```sql
INSERT INTO test_table VALUES
     ( 1, 'NAME1', 10 )
    ,( 2, 'NAME2', 20 )
    ,( 3, 'NAME3', 30 )
    ,( 4, 'NAME4', 40 )
    ,( 5, 'NAME5', 50 )
```

1000件以上の場合は、派生テーブルとしてテーブル値コンストラクターを使用する。

```sql
INSERT INTO dbo.Test ([Value])  
  SELECT drvd.[NewVal]
  FROM   (VALUES (0), (1), (2), (3), ..., (5000)) drvd([NewVal]);
```

[1行のINSERT文で複数のデータを挿入する方法](https://lightgauge.net/database/sqlserver/962/)
[テーブル値コンストラクター (Transact-SQL)](https://docs.microsoft.com/ja-jp/sql/t-sql/queries/table-value-constructor-transact-sql?view=sql-server-ver15)


#### Bulk Insert

Bulk Insertを使って大量のデータファイルから一括でインポートすることが出来る。

```sql
BULK INSERT AdventureWorks2012.Sales.SalesOrderDetail
   FROM 'f:\orders\lineitem.tbl'
   WITH
      (  
         FIELDTERMINATOR =' |'
         , ROWTERMINATOR =' |\n'
      );
```

[BULK INSERT (Transact-SQL)](https://docs.microsoft.com/ja-jp/sql/t-sql/statements/bulk-insert-transact-sql?view=sql-server-ver15)


#### BCPコマンド

BCPユーティリティコマンドであれば、インポートおよびエクスポート両方とも一括で行うことが出来る。

```
:: インポート
bcp データベース名.所有者.テーブル名 in "入力パス" -c -S サーバ名（インスタンス名） -U ユーザID -P パスワード

:: エクスポート
bcp データベース名.所有者.テーブル名 out "出力パス" -c -S サーバ名（インスタンス名） -U ユーザID -P パスワード
```

[bcp を使用した一括データのインポートおよびエクスポート (SQL Server)](https://docs.microsoft.com/ja-jp/sql/relational-databases/import-export/import-and-export-bulk-data-by-using-the-bcp-utility-sql-server?view=sql-server-ver15)
[【SQL Server】BCPコマンドを利用したデータのインポート／エクスポート](http://blog.livedoor.jp/akf0/archives/51354837.html)


## サーバー運用中の確認事項

稼働中のSQLが重くなっていた時の確認事項

#### 問題の調査分析

* [SQL Serverのパフォーマンストラブル調査手段集](https://qiita.com/itito/items/22fabe94d1fc0c881d1c)
* [サーバーのパフォーマンスと利用状況の監視](https://docs.microsoft.com/ja-jp/sql/relational-databases/performance/server-performance-and-activity-monitoring?view=sql-server-ver15)
* [SQLServer: CPU負荷をかけている機能を見つけ出す](https://qiita.com/maaaaaaaa/items/730ede5934ff843f5fc2)
* [【SQL server】利用状況モニターの情報取得について](http://memorandom-nishi.hatenablog.jp/entry/2016/09/22/195916)

#### インデックスの再構築・再構成

インデックスの再構成、再構築を行うことでインデックスの断片化を解消することが出来る。
オンラインで実行する必要がある場合は、再構成（REORGANIZE）を使用する。
オフラインで実行する時間を確保できる場合は、再構築(REBUILD)を使用する。

>例えば、処理に割り当てられる時間が限られていて、処理を途中でキャンセルしなければならない可能性があるのであれば、再構築よりも再編成の方が、キャンセルした場合にもすべてが無駄にならず、毎日時間の許される範囲で実行するという方法を取ることがきます。反対に、再構築に必要となる時間は確保できるので、実行時間にばらつきが出るよりもある程度一定の時間で完了することが優先されるのであれば、再構成よりも再構築の方が有利でしょう

```sql
-- 断片化されたインデックスを再構成
ALTER INDEX インデックス名 ON テーブル名 REORGANIZE;
-- テーブルのすべてのインデックスを再構成
ALTER INDEX ALL ON テーブル名 REORGANIZE;

-- 断片化されたインデックスを再構築
ALTER INDEX インデックス名 ON テーブル名 REBUILD;
-- テーブルのすべてのインデックスを再構築
ALTER INDEX ALL ON テーブル名 REBUILD;
```

* [トラブルシューティングから学ぶSQL Server統計情報の更新タイミング](https://techblog.zozo.com/entry/sqlserver-troubleshooting-statistics)
* [インデックスの再構成と再構築](https://docs.microsoft.com/ja-jp/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver15)
* [インデックス再構築と再構成の違い
](https://docs.microsoft.com/ja-jp/archive/blogs/jpsql/977)

#### 統計情報の更新

```sql
-- 1 つのインデックスの統計を更新
UPDATE STATISTICS テーブル名 インデックス名;  
-- テーブルのすべての統計を更新
UPDATE STATISTICS テーブル名;  
-- データベースのすべての統計を更新
EXEC sp_updatestats;
```

* [統計の更新](https://docs.microsoft.com/ja-jp/sql/relational-databases/statistics/update-statistics?view=sql-server-ver15)
* [【SQLServer】インデックス再構築では自動生成された統計情報は更新されない](http://souegg2.hatenablog.com/entry/2017/08/31/222917)
* [統計情報について簡単に説明してみる](http://ryuchan.hatenablog.com/entry/2015/04/11/192331)

#### 実行プランの見直し

```sql
--テーブルを指定して次回実行時に再コンパイルを指定する
EXEC sp_recompile N'テーブル名'; 
--プロシージャを指定して次回実行時に再コンパイルを指定する
EXEC sp_recompile N'プロシージャ名'; 
```

* [実行プランを作成しなおしてクエリのパフォーマンスを改善する](https://www.projectgroup.info/tips/SQLServer/MSSQL_00000028.html)
* [「パラメータースニッフィング」によって、あるタイミングから処理が遅くなった（パフォーマンストラブル）]
(https://www.atmarkit.co.jp/ait/articles/1706/19/news007.html)