本日の参加セッションはこちら。

[本日の参加セッション]

1. [T4-305] スケールしないシステムにおける開発者の過ち ? SQL Server の場合 ?
2. [T1-301] 企業内 PC の運?を変える Windows Intune
3. [T1-312] クラウド時代のデータベース、ベスト プラクティス ? 企業情報システムをクラウドへ移せ ?
4. [TH-403] SQL Server 現役サポートエンジニア直伝 ? SQL Server トラブル シューティング実践編 ?
5. [T4-401] SQL Server による?規模 Mission Critical システムの展開

?

■[T4-305] スケールしないシステムにおける開発者の過ち ? SQL Server の場合 ?

クラウドではなく、レガシー SQL Server のセッション
ロック待ちへの対策 / SQL の書き方の問題

スケールしないシステム

開発 / テスト環境では問題がなかったのに本番環境だと性能が出ない
→ テスト環境では 1 ユーザーでのみ動作検証をしていない。(多重度を考慮した負荷テストを実施していない)
　 複数ユーザーで実施しないとロックの問題に気付きづらい

検索結果のレスポンスはミリ秒 / マイクロ秒を意識する。
→ 1 秒は速いわけではない。

ピークタイムに耐えられない
→ 通常は問題ないが、ピーク時に問題が発生

?

スケールしないシステムへの対策

1. DB 設計時の対策
2. アプリケーション開発時の対策
   →今回のセッション内容
   　ロック待ちへの対応
   　SQL の書き方
3. DBA での対応

?

分離レベルが原因のロック待ち

TransactionScope をそのまま使っていないか?
ADO.NET では既定値は分離レベルが Serializable となっている。
→ トランザクションが終了するまで、共有ロックがかかった状態になる。
　 →更新系がブロックされる。

Read Committed を使用することを検討する。

?

Serializable の弊害

Serializable だどデッドロックが簡単に発生

?

デッドロックの監視

プロファイラ (Deadlock Graph / Chain) やトレースフラグ (1222) で監視ができる。
デッドロックが発生すると片側のトランザクションが取り消される。

?

回避策
Serializable を避ける。
ショートトランザクションになるようにする。

楽観的同時実行制御を利用する。

デモでは、Read Committed に分離レベルを変更してデモを実施。

?

ロックエスカレーションによる待ち

獲得するロック数が多い場合に、エスカレートする機能
ロックエスカレーションは言われているほど発生しない。

ロックエスカレーションは トレースフラグ 1211 でサーバー全体で禁止できる。
SQL Server 2008 以降はテーブル単位で禁止できる。

?

テーブルスキャンによるロック待ち

SQL Server で最も注意すべき内部動作

?

回避策

Serializable 分離レベルを避ける。
ショートトランザクションにする。
Index Seek になるようにする。
→ インデックスをつける。
　 必要に応じて、FORCESEKK ヒントを検討

?

Index Scan によるロック待ち

Index Scan は、インデックスの全スキャン。
Index Seek は該当データをピンポイント検索。

?

Index Scan になりえるケース

LINE ‘%aaaa’ : 後方一致 (FORCESEEK をつけるとエラーになる)
列名 + “AAA” = “TESTAAAA” : 列に対して演算 (左辺)
LEFT(列名,1) = “1” : 列に対して関数 (左辺)
<> : 否定演算
異なる照合順序で検索

回避策

前方一致になるように UI を工夫
列データに対する計算処理を止める (右辺に移動できないか)
列データに対する関数処理への対策
ダーティ リードに注意して NOLOCK ヒントを利用する
Read Committed Snapshot を利用する
計算、関数、照合順序の変更をしなくて済むようにデータ格納時に加工する。
Visual Studio 2008 の LINQ to Entities は CHARINDEX 関数に変換されてしまうので注意

ユーザー定義関数の注意

列データに対して、ユーザー定義関数を利用する場合も Index Scan が発生することに注意

?

暗黙の型変換に注意

varchar 列に、nvarchar のパラメーターに注意
実行プランで、CONVERT_IMPLICIT が出力されていたら注意。
LINQ では、一部のメソッド (StartsWith) が nvarchar へ変換される。
暗黙の型変換により、かなりの性能差が出ることもある。

?

プロシージャキャッシュの確認

dm_exec_cache_plan で確認
AddWithValue の場合は、パラメーターのサイズ指定ができないので、パラメータの文字列の長さが、
変更される度に別プランとしてキャッシュされてしまう。
VS 2010 では改善されている。 (nvarchar(4000) または、 nvarchar(8000) に変換される。)

?

大量 Insert 時は SqlBulkCopy!

大量データのインサートには、SQLBulkCopy クラスや BULK INSERTを使用する。

?

?

■[T1-301] 企業内 PC の運?を変える Windows Intune Windows Intuhe とは?

Windows Intune
→ System Center の流れをくんでいる。

クラウド型の管理サービス
アンチウイルス / アンチマルウェアを含む
最新 OS で標準化 (Windows 7 EE SA 付き)
ライセンスもクラウド型 = サブスクリプション $11/ 月
MDOP (オプション) $1/月

運用管理ツールの運用管理を止めましょう。
→ 契約をするとクラウド上の運用管理サービスを使えるようになる。

?

Windows Intune 管理サービスの利用

サインインをすると管理画面にいける。
→ Federation は現在対応していないが、今後 拡張される予定らしい。

自前サーバーなしでの運用管理サービス。
→複数ユーザーの状態をひと目で確認
　サーバー不要で能動的な管理サービス

SaaS 型のコンピューター管理機能

• 資産管理
  ハードウェア / ソフトウェアインベントリ
  ライセンス
• 更新管理
• マルウェアからの保護
• 稼働監視
  → OpsMgr のエージェント相当が導入される。
• ポリシーと構成管理

?

利用手順

1. Windows Live でログオン
2. クライアントソフト ()x86 / x64) のダウンロード
3. ダウンロードしたモジュールをクライアントソフトのインストール
4. コンピューターを Intune の管理コンソールでグループ化
5. 管理

?

インストールのデモ

クライアントモジュールのインストールはインストーラーを実行するだけで完了する。
→クライアントモジュール自体に署名等が埋め込まれている。
　自社の Intune で管理されるようにするためのインストーラーなので、モジュールの取り扱いには注意する。

インストール後、Windows Update を経由して 11 個のモジュールがダウンロードされる。

?

ライセンス
接続クライアントライセンス

管理
管理コンソールを使うためには、Silverlight 3.0 が必要になる。
XP SP2 を管理する場合、別途 2 つのモジュールをダウンロードする必要がある。

?

コンピュータのグループ化
管理しやすいようにグルーピングする。

?

Windows Intune 管理サービス解説

デモ中心のセクション
現在は Beta 版

機能ごとに管理者のアカウントを設定することが可能になっている。

アラートをトップ画面のダッシュボードから確認することができる。
→ブラウザ上の管理コンソールであるが、画面の繊維はシームレスに行われる。

アラートに対してのヘルプも用意されている。
→ アラートは 606 種類ある。
　 アラートをメールで連携することも可能。

マルウェア対策は ForeFront 相当のエンジンが実装されている。
→テストウィルスを使ったマルウェア対策のデモ

ライセンス管理も、管理コンソールから実施できる。
→ 仮想マシンも含めて管理できるので、最終的にはライセンス管理は物理 / 仮想を分けて管理できるはず。
　 (仮想はライセンスの考え方が異なるため)

ハードウェア情報でモデルが Virtual Machine となっている場合は、仮想マシン

ソフトウェアは App-V で配信されているかも確認することが可能
→ AppV Cache ？？ を見ているらしい。

Intune のグループと AD の OU は別。
グループのネストは 7 つ ? まで可能。

WSUS 相当の機能が含まれているため、更新プログラムの承認 / インストールするグループの設定が可能。
→ 自動承認機能もある。

ポリシーの機能も備えている。
ポリシーをテンプレート化して、使ってもらえるポリシーの設定を目指している。

ソフトウェアのインストール状況はコンピュータ単位、全台を対象とした管理をすることができる。
CSV / Excel でデータをエクスポート可能。

ボリュームライセンスの管理も Intune 上で実施できる。
→ CSV / HTML?? でエクスポート可能

?

資産管理

ソフトウェア インベントリは App-V にも対応している

1. MSI ソフトウェア リポジトリ
2. プログラムの追加と削除
3. App-V キャッシュ

から情報を取得

?

ライセンス管理

既存の契約情報をアップロードし、Windows Intune にアップロード可能

?

マルウェア対策

既存のマルウェアソフトを活かすこともできる。
→ ポリシーで Intune のマルウェア対策を無効にする。

?

稼働監視
現在のバージョンは、サーバー OS にもインストールできる。

?

ポリシーと構成管理
SCVMM VNext の DCM に基づいた構成になっている。(次世代ポリシー基盤)

グループ階層の一番下のレベルのポリシーが適用される。
→ AD の GPO は優先される。

サンプルのMOFの先頭に? [#pragma autorecover] が必要

MOF COMP をたたくと WMI クエリの名前空間に [rootWindowsIntune] を選択できるようになる。

SCO (System Center Online) という表記になっている個所もあるかもしれない。
→ 旧バージョンの名称

新しいポリシー基盤 ”Lantern”

Active Directory 非依存の共通ポリシー基盤の確立
→ SCCM VNext DCN / Windows Intune → クラウド時代のポリシー管理になるか？

今回のポリシー基盤には、SML が使われているはず (確定情報ではない)

?

サービスステータスの確認

現状のサービスの提供状況を確認する、ポータル画面が用意されている。

?

リモートアシスタンス

Microsoft Easy Assist を使って、クライアントからリモート アシスタンス要求をだせる。
→モジュールは Intune をインストールすると導入される。

?

まとめ

企業内の利用シナリオ

Windows 7 EE の機能 / 2008 R2 と連携

• BitLocker
• ダイレクトアクセス / ブランチキャッシュ
• AppLocker
• MDOP

SA 付きなので必要なバージョンへのダウングレード権がある。

9/3 夕方に新宿で Windows Intune の Tech Fielders セミナーがある。

?

■[T1-312] クラウド時代のデータベース、ベスト プラクティス ? 企業情報システムをクラウドへ移せ ?

SQL Azure の活用について

SQL Azure 概要

Relational Database as a Service
自動化された管理
→物理サーバーや仮想マシンの管理が不要 (DB の管理が不要ということではないはず)

スケーラビリティ

開発しやすさ

?

SQL Server と SQL Azure の関係

同一アーキテクチャを採用
同一の管理ツールで管理できる

SQL Azure Service Update 4

2010 / 8 / 25 更新

同一サーバー / 別サーバーにデータベースをコピー
CREATE DATABASE に COPY OF を設定する。
master データベースで実行する。

Northwind データベースをコピーするデモの実施。
ステートメントの実行は瞬間的に完了するが、非同期処理でコピーされている。

?

データベース ノード

1 サーバーに 1 物理データベース
データベースファイルとログファイルは、すべての論理データベースにて共有
master データベースはシステムデータベースではない

?

SQL Azure の運用

ファイルグループがない
ネットワーク遅延 (サーバーが遠隔地にある)
すべてが高可用性構成
SQL 認証のみ
データベース暗号化が未サポート
SQL Agent がない

?

Project ”Houston” CTP 1

クライアント環境にとらわれない DB 管理
現時点では master データベースにうまく接続できないことがある。
Update されたことにより、場所を選択する画面が増えた

Houstonを使うと 1433 のルール追加が不要になる。(443 で通信)

?

データベースの移行ステップ

スキーマ移行
データ移行
→ 移行したデータを戻すこともできる。

?

SQL Azure Migration Wizard

MS の公式サポート ツールではないが良くできている。
→ CodePlex で公開されている。

SQL Azure では、クラスター化インデックスが必要。
→ツールでクラスター化インデックスを作ってくれる

英語環境を意識して作られているため、日本語を使用するためには config を書き変える必要がある。

?

SQL Server Management Studio

SQL Server 2008 R2 の SSMS を使用する。
SQL Azure データベース用のスクリプトを作れる。

?

DAC

SQL Azure では DAC (データ層アプリケーション) も使える。
→ スキーマ情報しか扱えない。

?

インポート / エクスポートウィザード

SQL Azure 上に DB を事前に作っておく。

?

異なる RDBMS から 移行

SSMA
SSMA for MySQL 1.0
SSMA for Oracle / Sybase / Access

SSMA は SQL Azure に直接接続できる。
→ SQL Azure 接続時は暗号化接続が有効でグレーアウトしていた。

スキーマ移行とデータ移行が可能

?

ネットワーク パフォーマンス

遅延 / 帯域幅 / 接続性
インターネット経由で長距離間のデータ転送をする必要がある。

?

BCP 1GB データ転送検証

ストリームの数を増やすと比較的早く転送が終わる。
→マルチストリーム転送は極めて有効

?

Azure to On-Premise のデータ連携

SQL Azure Data Sync サービス
SQL Azure ? オンプレミス (SQL Azure Data Sync Tool)
SQL Azure ? B2B (SQL Azure Data Sync Tool)
SQL Azure 内 (SQL Azure Data Sync Service) (データセンター間も対応)
SQL Azure – オフライン端末 (Sync Framework)

設定は、SQL Azure のポータルサイトから

1. Add Hub で起点を設定
2. Add Member でメンバーを追加
3. テーブルを選択

?

データ競合時の設定はない。

?

Blob データを含めての移行シナリオ

Blob は Windows Azure Blob に移行するというシナリオも考えられる。

?

今後提供が予定される機能

Reporting / Business Analytics
Data Sync (CTP) / OData (CTP) → データのやり取りを HTTPS で可能。(Anonymous User Access の設定がでいる。)

PDC で発表があるかも知れない更新
DB おける複数 Collation
操作レポート
フルテキストサーチ
スケールアップ

?

■[TH-403] SQL Server 現役サポートエンジニア直伝 ? SQL Server トラブル シューティング実践編 ?

SQL Server の障害をどのように切り分けていくか

資料の確認 11 / 17~ 19 / 21 / 28 / 41 / 53 / 59 / 60 /69

エラー発生
一時的 / 継続的なエラー

処理完了までの時間が長い
処理が止まっている / 遅い

メモリ不足

バッファプール
ページ (8KB) 単位で管理
max server memory で定義可能

非バッファ領域
32bit 環境 : 環境ごとにプロセッサに応じて自動定義
64bit 環境 : 制限なし

フリーページ
lazy writer スレッドで確保される
→ 参照頻度が低いページをフリーにする

OS からメモリ不足の通知を受けても SQL Server はメモリを解放しつつ正常に動作可能
→min server memory まで解放
他プロセスから大量のメモリ要求が続いたら
→フリーページ確保、解放が頻繁に発生
　メモリがページアウトされる。
　ページングによりフリーページを確保しにくい

メモリ不足は、ERRORLOG に記録される。

対処法
メモリを増設
→ 他のアプリケーションもメモリを使用する。
max server memory を設定
→ SQL Server のメモリ上限を設定

非バッファ領域
max worker thread は 2005 移行は CPU の数によって自動チューニングされる
-g オプションの部分も含まれる。

?

ワーカースレッドの枯渇
SQL Server が管理するスレッド
一般的に最大数に達する状況は、根本的な原因を解決しないと最大値を増やしても達してしまうことがありえる。

ワーカースレッドはプールされ、再利用される
→ 過度のブロッキング / 並列度の高いクエリの多数同時実行で枯渇の可能性がある。

最大数に達した場合
スレッドがアイドルになるまで待機、もしくはタイムアウトで処理が失敗
→ Deadlocked Schedulers が ERRORlOG に出力される。

並列クエリの場合、ワーカーは複数アクティブ
同時実行によりワーカーが増加

?

過度のブロッキング
ショートトランザクションにする。
データ参照 / 更新範囲の重複をなくす (Scan 操作をなくす)
分離レベルの変更

並列度の高いクエリの多数同時実行
並列度を制限
→MAXDOP (OPTION 句の MAXDOP がサーバーレベルより優先される)

sys.dm_os_tasks と sys.dm_os_workers を Join して並列度を確認
→ ワーカーが枯渇している場合は DAC で接続

?

サービス停止時間を短くするために

死活監視
select @@servername
ワーカーが枯渇している場合、@@versionを実行できないのでフェールオーバーしてしまう。

デッドロック

ロールバックコストが低い方をロールバックする。

デッドロックの発生状況の確認

• トレースフラグ
  1222 (1204)
• Profiler イベント
  Deadlock Graph
  Lock: Escalation → エスカレーションのきっかけになったクエリを取得できる。

?

サイクルデッドロックの一般的な対処方法

リトライ処理
ロックエスカレーションの回避、防止
→ Scan 操作を Seek になるようにす

サーバー全体で、エスカレーションを禁止した際のメモリ不足対応のため、2008 ではテーブル単位の
ロックエスカレーションの禁止が実装された。

fn_trace_gettable でトレースファイルをインポート

?

システムデータベースの破損

破損する DB によって、SQL Server / Agent サービスが起動できなくなる。
対処の基本はバックアップからの復元
→破損しているバックアップを戻しても破損した状態になる。

定期的な DBCC CHECKDB によるデータベースの整合性チェック

バックアップファイルの整合性チェック
→RESTORE VERIFYONLY

バックアップファイルがない場合
システム DB の再構築 (全システム DB が再構築)

?

管理者のロックアウト　

2008 以降は、BUILTINAdministrator は既定で管理者ではない
BOL にロックアウト時の解決策が掲載されている。

?

SQL Server へ接続できない

OS レベルのセッション確立
F/W の例外設定が意外と多い
接続プロトコル
DNS
SQL Browser サービス
ローカルから接続できるか

SQL Server の認証
認証方式
ドメイン / ローカルアカウント
特定のログインだけか

?

データベースアクセス
AUTO_CLOSE 設定
master に接続できるか
特定のデータベースだけ

?

クライアントのエラーメッセージ / 時間 / SQL Server の ERROLOG が必要

?

ログファイル拡張

ログ自動拡張のタイムアウト
ログファイルがいっぱいでレコードが書き込みできない

データベース復旧モデル も要確認

2008 のログ切り捨ては、復旧モデルを単純にする
→no_log / truncate only が2008　以降ではないため。

?

ブロッキング

DMV / DMF / DBCC から調査することができる。

?

処理の中断

ロールバック時間を考慮する。

?

チューニング

Web Cast で 5 minute を公開中

?

■[T4-401] SQL Server による?規模 Mission Critical システムの展開

4 ソケット 32 コア 128 MB のメモリのマシン
# 8 ソケット 64 コアマシンも後で登場

バランスドシステムという考え方

トランザクション処理設計

トランザクションの境界
暗黙的な宣言と、明示的な宣言 (BEGIN TRAN)

ブロッキング防止
NOLOCK / RCSI (Read Committed Snapshot Isolation)

デッドロックへの考慮
変換デッドロック発生の防止
更新順序の統一化と回収処理 (リトライ処理をアプリケーションで実装)

クエリ実行時の考慮
アドホッククエリとパラメーター化クエリ
適切なフィルタリングによる結果セットサイズ

?

データベース物理設計

データファイルの分割と配置
非クラスター化インデックスをファイルグループをつかうと、分離格納が可能
tempdb は CPU コア数と同じファイル数に分割する

?

データファイルの配置
ファイルグループは異なる LUN に配置

?

領域サイズの見積もり
将来のデータ量も見込んでおく
増分はあくまでも保険
→ 拡張中は、トランザクションが停止する。

?

ログファイルの配置
ユーザーデータベースごとに一つ必要
RAID 1+ 0 の独立した LUN に配置
シーケンシャルな書き込み処理のみが発生
I/O 帯域がサーバー上の処理能力を左右する (先行書き込み)

定期的なログのアーカイブにより肥大化を防ぐ。

?

データパーティショニング
分割をするためのキー項目が必要になる
クエリは複数のパーティションを透過的にアクセス可能

スライディングウィンドウズ機能

インデックスの役割
テーブルにはのクラスター化インデックスを定義する

発生するデータの特性を理解する
→ インデックスをどの列に設定すればよいかに影響する。

クラスター化インデックスはキーの内容が変更されない列に設定する。

再構築と再構成

2008 ではオンラインで再構築が可能
→ 再構築前、後のデータを使うため、データサイズの見積もりに影響する。

ページ分割
Update Inprace : 更新処理でもページ分割が発生する (同一領域に格納できない場合に発生)
FILLFACTOR を設定する。

統計情報
データ作成時に作成される (自動作成有効の場合)
新は一定の閾値を超えた場合に実行される。
INDEX の REBUILD 時にも更新される

手動更新で統計情報を更新することも重要。

?

バランスドシステムとは

SQL Server リレーショナルエンジンに最適化された H/W 構成
専用サーバー上に配置する (インスタンスも一つにする)

プロセッサ
主流は x64
NUMA 対応 CPU
4 ソケット以上の NUMA 対応 CPU を推奨

メモリ
x64 64 ビットアドレス方式が主流
NUMA ノードあたり、8-16 GB を推奨
SMP の場合 コア辺り 4GB をスタートラインに
OLTP の場合ユーザー DB の10% は目安

ストレージ
HBA 経由ファイバー
→ 複数の HBA で MPIO
iSCSI
DAS

トランザクション処理には複数の高回転デバイスを利用

SQL Server 2008 / R2 のベンチマークテストの比較

トランザクションミックスで負荷テストを実施

SQL Server 2008 → R2 で性能は向上している。
2008 は HT を有効にすると 10% 程度、過負荷状態の処理効率が向上している

Runnable Queue から CPU の待ち行列を確認
PAGELATCH でページ分割の待ち事象を確認

?

サプライズ

8 ソケット 64 コア / SQL Server Datacenter Edition
# HT を有効化すると 128 コア

?

設定関連
Lock Page in Memory が有効になっていると LOG に Large page Extensions が表示される。