2019年1月20日日曜日

PowerShellのループ処理

PowerShellを使ったループ処理のサンプルを作成する。
お題:任意のディレクトリ内のファイルについて列挙する。
※SQL Serverのインストールディレクトリ内の「DATA」フォルダを参照するサンプルを作成する。

enter image description here


まずはディレクトリを移動
PS C:\Users\kimiyuki> cd "C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEVA\MSSQL\DATA"

PS C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEVA\MSSQL\DATA> 

ディレクトリ内のファイルの列挙
・Get-ChildItemでディレクトリ以下のファイルを列挙出来る。
 -Includeオプションで拡張子を指定している。
PS C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEVA\MSSQL\DATA> 
Get-ChildItem -Recurse -File -Include *.mdf


    ディレクトリ: C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEVA\MSSQL\DATA


Mode                LastWriteTime     Length Name                                                                                                                                                                                 
----                -------------     ------ ----                                                                                                                                                                                 
-a---        2019/01/03     20:55  130088960 AdventureWorksDW2012.mdf                                                                                                                                                             
-a---        2019/01/03     20:55   23003136 AdventureWorksLT2012.mdf                                                                                                                                                             
-a---        2019/01/03     20:55    5111808 master.mdf                                                                                                                                                                           
-a---        2019/01/03     20:55    4259840 model.mdf                                                                                                                                                                            
-a---        2019/01/03     20:55   17498112 MSDBData.mdf                                                                                                                                                                         
-a---        2019/01/20      5:10  148897792 MyStockDB.mdf                                                                                                                                                                        
-a---        2019/01/05      2:10   12582912 SalesDB.mdf                                                                                                                                                                          
-a---        2019/01/03     20:55   41943040 SSISDB.mdf                                                                                                                                                                           
-a---        2019/01/03     21:01    8388608 tempdb.mdf                                                                                                                                                                           
-a---        2019/01/03     20:55   14680064 TestDB.mdf       
※列挙するだけならGet-ChildItemコマンドで十分であるが、
ループ毎に何らかの処理をしたい場合は不十分…

foreachによるループ処理
・Get-ChildItemの結果を$array にObject型の配列として格納する。
・LastWriteTimeはDateTime型なのでToStringでフォーマットを指定できる。
PS C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEVA\MSSQL\DATA> 
$array = (Get-ChildItem -Recurse -File -Include *.mdf )
foreach($a in $array) 
{
    echo $a.LastWriteTime.ToString("yyyyMMdd_HHmmss")  
} 
    
20190103_205520 
20190103_205518 
20190103_205524 
20190103_205522 
20190103_205522 
20190120_051006 
20190105_021043 
20190103_205517 
20190103_210146 
20190103_205518 
※foreachのループ処理部分で個別のロジックの記述が可能。
感覚的にはC#のループ処理と同じイメージ。


foreach-objectによるループ処理
foreachの例と同様にGet-ChildItemの結果を加工する。
PS C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEVA\MSSQL\DATA> 
Get-ChildItem -Recurse -File -Include *.mdf | ForEach-Object -Begin{$array= @();} -Process{$array += $_.LastWriteTime.ToString("yyyyMMdd_HHmmss")} -End{$array}

20190103_205520
20190103_205518
20190103_205524
20190103_205522
20190103_205522
20190120_051006
20190105_021043
20190103_205517
20190103_210146
20190103_205518
※ForEach-Object コマンドレットでは、パイプに渡して処理を書くので1行で収まっている(可読性は悪い気が…)
-Beginオプション:前処理を記述(データを格納する配列を宣言)
-Processオプション:処理内容を記述(配列にデータを格納)
 「$_」で元のデータにアクセスできる
-Endオプション:後処理(結果の表示)


まとめ

色々調べると、foreachステートメントよりforeach-objectコマンドレットの方がメモリ量が少なくて済むそうなので処理の高速化が期待できる。
でも、使ってみるとforeachステートメントの方がC#等のループ処理に近い感覚で記述できるので分かりやすい気がします。
この辺の感じは未だにラムダ式に慣れずクエリ式でLinqを書いている感覚に近いものがあります…

参考

Powershellのforeach(ForEach-Object)の使い方

【ForEach-Object】PowerShellでパイプからforeachループするテクニック

PowerShellの落とし穴!ForEach-Objectとforeachは違う?

PowerShellのGet-ChildItemコマンドレットでファイル名の一覧を取得する(応用編)


2019年1月4日金曜日

SQL Server のIndexの効果検証2

前回の投稿で単純なSELECTクエリに対してIndexの効果を検証した。
今回は完全一致、部分検索(Where句で範囲を指定)についてNon-Clustered Indexの複合Index、付加Indexの作成と効果検証を行う。

環境

前回の投稿と同じ
  • DB Instance: SQL Server 2017 (14.0.2002.14) Developer
  • DB: AdventureWorksLT2012 から Import Wizard でデータをImportして作成したDB(SalesDB)
検証対象のTable: SalesLT.Product
Base Query:
SELECT ProductID, ProductNumber, Color 
FROM SalesLT.Product
ProductID ProductNumber Color
680 FR-R92B-58 Black
706 FR-R92R-58 Red
707 HL-U509-R Red
708 HL-U509 Black
709 SO-B909-M White
710 SO-B909-L White
計:295行
※元のAdventureWorksLT2012 にはIndexの設定があるが、データをImportしたDBにはIndexの設定は無い。
(スキーマは移る)
Index作成後にQueryをテストする前にキャッシュのクリアと統計情報の出力ために下記クエリを実行する。
USE[SalesDB]

DBCC DROPCLEANBUFFERS;

SET STATISTICS IO ON;
GO
テストするNon-Clustered Indexの種類は以下の通り
  • ProductIDのみ(前回テスト済)
  • 複合Index:ProductID, ProductNumber, Color の3列を複合Indexとして設定
  • 付加列Index:ProductIDをIndex Keyとして設定し、付加列としてProductNumber, Color を設定

テスト1(完全一致)

以下の完全一致クエリ(結果セットが1行)に対して、Non-Clustered Index の構成を変えて実行プランを比較する。
※各設定のクエリを実行前にキャッシュをクリアすること。
SELECT ProductID, ProductNumber, Color
FROM SalesLT.Product
WHERE ProductID = 714

enter image description here

統計情報の結果
STATISTICS IO ProductIDのみ 複合Index 付加列Index
スキャン回数 0 1 0
論理読み取り数 3 2 2
物理読み取り数 1 0 0
先行読み取り数 0 0 0

実行プランのコスト比較
ProductIDのみ 複合Index 付加列Index
完全一致(1行) 0.0065704 0.0032831 0.0032831

  • 複合Indexと付加列Indexではコストが同じ
  • ProductIDのみのIndexに比べコスト比:50%

メモ
  • 複合Indexと付加列Indexでコストが同じになる。(今回の例では?)
  • 付加列Indexの方がスキャン回数が少なくなるので規模が大きくなると有利か。
  • 複合Index、付加列Indexの場合は「RID Lookup」が不要になる。

テスト2(部分検索)

以下の部分検索クエリ(結果セットが5行)に対して、Non-Clustered Index の構成を変えて実行プランを比較する。
※各設定のクエリを実行前にキャッシュをクリアすること。
SELECT ProductID, ProductNumber, Color
FROM SalesLT.Product
WHERE ProductID Between 710 AND 714;

enter image description here

統計情報の結果
STATISTICS IO ProductIDのみ 複合Index 付加列Index
スキャン回数 1 1 1
論理読み取り数 7 2 2
物理読み取り数 1 0 0
先行読み取り数 0 0 0

実行プランのコスト比較
ProductIDのみ 複合Index 付加列Index
部分検索(5行) 0.0164145 0.0032875 0.0032875
  • 複合Indexと付加列Indexではコストが同じ
  • ProductIDのみのIndexに比べコスト比:20%

メモ
  • 完全一致の場合と同じく複合Indexと付加列Indexでコストが同じになる。(今回の例では?)
  • 完全一致より部分検索の方が複合Index、付加列Indexの効果が大きい。
  • 複合Index、付加列Indexの場合は「RID Lookup」が不要になる。

まとめ

今回の事例では、パフォーマンス的に複合Indexと付加列Indexは同等になっている。Indexの構成を考えると、付加列Indexの方がIndexサイズが小さく出来るので付加列Indexの方がよいのかと思う。
今回の事例でIndexのサイズをシステムストアドプロシージャ「sp_spaceused」で計算すると、どちらも 1040KBとなってしまう…
(テーブルのサイズが小さいことが要因か?)
・Indexのサイズ計算(sp_spaceused)
DECLARE @Object_Name nvarchar(1000) = '';
USE SalesDB  
SET @Object_Name =  N'SalesLT.Product';

EXEC sp_spaceused @Object_Name;
今回の事例のようにSELECT文の結果セットを包含するIndexのことを「カバリングインデックス」と呼ぶ。
カバリングインデックスにすることでリーフページからデータを参照することがなくなるので、RID Lookupが削減できるということらしい。
実際、上記Index(ProductID, ProductNumber, Color)以外の列をSELECT文に追加するとパフォーマンスが悪化する。(下図参照)

結果セットを増やした場合
SELECT  ProductID,  ProductNumber, Color, Size,  SellStartDate
FROM  SalesLT.Product
WHERE  ProductID  Between 710 AND 714;

enter image description here

複合IndexでProductID, ProductNumber, Color の3列を設定している状態で、結果セットとして「Size」、「SellStartDate」を追加すると実行プランでは RID Lookupが発生してしまい実質的には「ProductID」のみをNon-Clustered Indexとした場合と同じになる。
複合Index、付加列Indexは上手く設定できればパフォーマンスの向上が図れるが結果セットの作成側(アプリケーション開発側)との調整が必要かと。
また、現実的には単独のTableからの結果セットではなく、複数のTableの結合、集計が組み合わされるのでトータルでどのようにIndexを付与するのが良いのかは難しい問題と思います。
DB管理者側の視点だけでは意味がないので…
(今までちゃんとしてこなかった事への言い訳)

参考

SQLServerのインデックスについてざっくりとまとめてみた
付加列インデックスの作成

2019年1月3日木曜日

Microsoft Excel 2010 (KB4461627) 32 ビット版 の更新プログラム 不具合について

2019/1/2 にリリースされているExcel 2010 の更新プログラム:KB4461627
について 1/3にインストールしたところExcel 新規起動後にセルに文字列を記入、若しくは文字列が記入済のExcelを開くと「動作を停止しました」とのこと。
※既存のExcelの場合は起動してすぐに落ちます…
ちょっと調べると類似の不具合報告が上がっているようなので、
更新プログラムのアンインストールで対応し問題が解消されたことを確認しました。
暫く、KB4461627 の更新は避けようと思います…

環境

Windows 7 Professional SP1 x64
Model: HP h8-1180jp
CPU: Intel Core i7-2600 @ 3.40 GHz
Memory: 16GB
Office: Microsoft Office Professional 2010
Excel Version: 14.0.7224.5000 (32bit)


・KB4461627 インストール後の更新履歴

enter image description here


・Excelのエラー状態(起動後にセルに文字を入力で発生)

enter image description here


・インストールされた更新プログラムの削除(アンインストール)
  • 下記手順で「更新プログラムのアンインストール」画面を表示する。
    コントロールパネル→プログラムと機能→インストールされた更新プログラム
  • 右上の検索ウィンドウで「KB4461627」と入力しインストールされていることを確認する。
  • 右クリック→アンインストール
    ※今回の更新はOSの再起動は不要なようです。

enter image description here


アンインストールが終わったらExcel起動できます。
※再度インストールされてしまうことを嫌う場合は「WindowsUpdateのインストールする更新プログラムの選択」画面で対象の更新プログラム(この場合はKB4461627)を右クリック→「更新プログラムの非表示」を選択してください。
再度インストールしたい場合はWindowsUpdateの左ペインから「非表示の更新プログラムの再表示」からチェックを入れてください。

更新プログラムの非表示


非表示の更新プログラムの再表示

参考

Excel 2010 (KB4461627)、2019 年 1 月 2日の更新プログラム


やれやれ…



SQL Server のIndexの効果検証1

SQL Server のIndexの設定有無によるSELECT文の実行プランを比較してみる。

環境

  • DB Instance: SQL Server 2017 (14.0.2002.14) Developer
  • DB: AdventureWorksLT2012 から Import Wizard でデータをImportして作成したDB(SalesDB)
検証対象のTable: SalesLT.Product
Base Query:
SELECT ProductID, ProductNumber, Color 
FROM SalesLT.Product
ProductID ProductNumber Color
680 FR-R92B-58 Black
706 FR-R92R-58 Red
707 HL-U509-R Red
708 HL-U509 Black
709 SO-B909-M White
710 SO-B909-L White
計:295行
※元のAdventureWorksLT2012 にはIndexの設定があるが、データをImportしたDBにはIndexの設定は無い。
(スキーマは移る)
Index作成後にQueryをテストする前にキャッシュのクリアと統計情報の出力ために下記クエリを実行する。
USE[SalesDB]

DBCC DROPCLEANBUFFERS;

SET STATISTICS IO ON;
GO

テスト1(完全一致)

以下の完全一致のクエリ(結果セットが1行)に対して、「Index無し」、「Clustered Index」、「Non-Clustered Index」の実行プランを比較する。
※Indexは「ProductID」について設定する。
※各設定のクエリを実行前にキャッシュをクリアすること。
USE[SalesDB]
SELECT ProductID, ProductNumber, Color
FROM SalesLT.Product
WHERE ProductID = 714

enter image description here

統計情報の結果
STATISTICS IO None Clustered Non Clustered
スキャン回数 1 0 0
論理読み取り数 101 2 3
物理読み取り数 0 0 1
先行読み取り数 101 0 0

実行プランのコスト比較
None Clustered Non Clustered
1行検索Cost 0.0776806 0.0032831 0.0065704
  • Clusterd Index のコスト比:4%
  • Non-Clustered Indexのコスト比:8%

メモ
  • Index無し(None)の場合はTable Scanが実行されすべてのPage(101)を読み取っている。
  • Index有りの場合はCluster Index, Non-Cluster Index 共にIndex Scanが実行されており読取りPage数が非常に少なくなっている。 (Tableの規模が大きくなるほど効果が差が大きくなる)

テスト2(全件検索/OrderBy)

以下の全件検索クエリ(結果セットが295行、OrderBy付き)に対して、「Index無し」、「Clustered Index」、「Non-Clustered Index」の実行プランを比較する。
※Indexは「ProductID」について設定する。
※各設定のクエリを実行前にキャッシュをクリアすること。
SELECT ProductID, ProductNumber, Color
FROM SalesLT.Product
ORDER BY ProductID

enter image description here

統計情報の結果
STATISTICS IO None Clustered Non Clustered
スキャン回数 1 1 1
論理読み取り数 101 103 101
物理読み取り数 0 0 0
先行読み取り数 101 0 101

実行プランのコスト比較
None Clustered Non Clustered
全件検索(295行:OrderBy) 0.0928176 0.0776806 0.0928176

  • Clusterd Index のコスト比:84%
  • Non-Clustered Indexのコスト比:100%

メモ
  • Tableの全件検索ではIndex無し(None)とNon-Clustered Indexが同じ結果になる。(Non-Clustered Indexの効果が無い)
  • Index無し、Non-Clustered Indexのコストを見るとTable Scan(84%) と Sort(16%)でコストが消費されている。
  • Clusterd Index では、リーフページがProductIDでSortされているのでSortのコストがかからない。
  • Clusterd Index ではコスト低減の効果があるが、1行検索のような大きな低減効果はない。

テスト3(部分検索)

以下の部分検索クエリ(結果セットが5行)に対して、「Index無し」、「Clustered Index」、「Non-Clustered Index」の実行プランを比較する。
※Indexは「ProductID」について設定する。
※各設定のクエリを実行前にキャッシュをクリアすること。
SELECT ProductID, ProductNumber, Color
FROM SalesLT.Product
WHERE ProductID Between 710 AND 714;

enter image description here

統計情報の結果
STATISTICS IO None Clustered Non Clustered
スキャン回数 1 1 1
論理読み取り数 101 5 7
物理読み取り数 0 0 1
先行読み取り数 101 0 0

実行プランのコスト比較
None Clustered Non Clustered
部分検索(5行) 0.0776806 0.0040282 0.0164145

  • Clusterd Index のコスト比:5%
  • Non-Clustered Indexのコスト比:21%

メモ
  • 部分検索の場合は Non-Clustered Index の効果が得られる。ただし、RID Lookupのコストが高いのでTableの規模が大きくなると悪化すると思われる。

まとめ

単純なSELECTクエリについてIndexの有無によるPage検索数、コストについて調べてました。
現実的にはTableのPrimary Key がClustered Index になってしまうので実運用上のIndexは Non-Clustered で作成する必要がありそうです。
Non-Clustered IndexについてはRID Lookupのコストをどのように下げるかが問題になりそうです。
これについては、複合Index、付加Indexを構築してカバーインデックスにすることで改善されるようです。その辺については別途まとめようと思います。

参考

なぜBTreeがIndexに使われているのか
SQLServerのインデックスについてざっくりとまとめてみた