2018-06-02

SQL Server のチューニングについてまとめてみる - その 25 - ( パーティションを有効に使ってみる )

Name: 熟成肉 肉バル CARNE BAR KATETE 虎ノ門店
Address: 虎ノ門1-16-2 B1, 港区, 東京都, 105-0001, JP
Telephone: 03-6268-8929

SQL Server Tuning

　久しぶりの投稿です。梅雨前にもかかわらず関東はいい天気に恵まれ、来週の月曜日までこの天気が続くようです。
f:id:koogucc11:20180602164823j:plain

　長らくチューニングに関する投稿をしていませんでした。前回 ( その24 ) は7か月前ですね。
ryuchan.hatenablog.com

　MSDN では、パーティションについて以下のように記載されています。

パーティションテーブルとパーティションインデックスのデータは、データベース内の複数のファイルグループに分散できるように、行方向に複数の単位に分割されています。パーティション分割により、大きいテーブルとインデックスの管理可能性と拡張性が向上します。パーティションテーブルとパーティションインデックスは、SQL Server の Enterprise Edition、Developer Edition、および Evaluation Edition でのみ使用できます。

　管理と拡張性だけではなく、WHERE 句の条件として指定することによりパフォーマンスが飛躍的に向上します。それでは早速実験してみましょう。パーティション分割されたテーブル(パーティションID 1から20 まで存在するテーブル)にクエリを投げてみます。パーティションが 20 存在し、アクセスしたパーティションが 1..20 ですべてのパーティションにアクセスしたと判断できます。
f:id:koogucc11:20180602173656p:plain

　WHERE 句の条件に、$PARTITION.パーティション関数 ( パーティション列 ) を付加します。今回のケースで 1 から 20 のパーティションの中から、13 から 14 のみにアクセスしたい場合は、

$PARTITION.パーティション関数 ( パーティション列 ) BETWEEN 13 AND 14

と指定します。パーティション条件を指定した状態でクエリを実行すると、パーティションID の 13 から 14 のみにアクセスしていることが判断できます。CPU の推定コストも減少していますね。
f:id:koogucc11:20180602190121p:plain

　チューニングの記事もコツコツ投稿していないといけないですね。気力が続く限り、がんばります。

　iPad 2018 の付属品も色々買いました。

ESR 新しい iPad 9.7 2018/2017 ケース超軽量極薄レザー三つ折スタンドオートスリープ機能スマートカバー全10色 2017年と2018年発売の新しい9.7インチ iPad 対応(モデル番号A1822、A1823、A1893、A1954)（ダークブラック）

出版社/メーカー: Electronic Silk Road Corp
メディア: エレクトロニクス
この商品を含むブログを見る

Nimaso （2018 / 2017 新型）iPad Pro 9.7 / Air2 / Air / New iPad 9.7インチ用フィルム日本製素材旭硝子製強化ガラス液晶保護フィルム高透過率防爆裂スクラッチ防止気泡ゼロ指紋防止硬度9H

出版社/メーカー: Nimaso
メディア: エレクトロニクス
この商品を含むブログを見る

Apple Pencil 専用キャップカバー Qosea ペン先カバーアダプタ連接用カバーシリコン紛失落下防止超軽量 3点セット(ホワイト) アップルペンシル Pencil Cap Cover Lighting Adapter Cover

出版社/メーカー: Qosea
メディア: エレクトロニクス
この商品を含むブログを見る

Apple Pencil ケース Aunote Apple iPad Pro Pencil収納ケースホルダー高級PUレザーゴムバンドで固定内蔵Apple Pencilのアクセサリ紛失防止ポケットアップルペンシル保護ケースカバー(ブラック)

出版社/メーカー: Aunote
メディア: エレクトロニクス
この商品を含むブログを見る

2018-04-29

クラスタ化インデックスと非クラスタ化インデックスでこんなことやったらどうなんだろうと思ったことを実験してみる

SQL Server

　搭乗回数が若干減っています。#ダイヤモンド修行がなかなか思い通りに進みません。それはさておき、最近、ふと思ったことがあり、記事を書く回数も昨年にも増して少なくなっているため、若干記事にする意味あるかなと思いながらも…記事にしてみます。

　AdventureWorks の SalesOrderDetail テーブルに対して、クラスタ化インデックス、非クラスタ化インデックスを3種類作成してみます。

CREATE CLUSTERED INDEX ClusteredIndex ON Sales.SalesOrderDetail_
(
    ModifiedDate ASC
)

CREATE NONCLUSTERED INDEX NonClusteredIndex-1 ON Sales.SalesOrderDetail_
(
    ProductID ASC,
    CarrierTrackingNumber ASC
)

CREATE NONCLUSTERED INDEX NonClusteredIndex-2 ON Sales.SalesOrderDetail_
(
    ProductID ASC,
    CarrierTrackingNumber ASC,
    ModifiedDate ASC
)

　下記のクエリを SQL Server Management Studio で実行してみます。

SELECT
    ProductID
FROM
    Sales.SalesOrderDetail_ WITH(INDEX([NonClusteredIndex-1]))
WHERE
    ProductID = 750
AND CarrierTrackingNumber = 'E46D-4692-AC'
AND ModifiedDate BETWEEN '2011-07-07 00:00:00.000' AND '2011-12-07 00:00:00.000'

SELECT
    ProductID
FROM
    Sales.SalesOrderDetail_ WITH(INDEX([NonClusteredIndex-2]))
WHERE
    ProductID = 750
AND CarrierTrackingNumber = 'E46D-4692-AC'
AND ModifiedDate BETWEEN '2011-07-07 00:00:00.000' AND '2011-12-07 00:00:00.000'

SELECT
    ProductID
FROM
    Sales.SalesOrderDetail_
WHERE
    ProductID = 750
AND CarrierTrackingNumber = 'E46D-4692-AC'
AND ModifiedDate BETWEEN '2011-07-07 00:00:00.000' AND '2011-12-07 00:00:00.000'

　実行プランの結果は下図のとおりです。
f:id:koogucc11:20180429093812p:plain

　少しでも差が出るかと思ったんですが、コスト上変わらないですね。もう少し実験してみるか。

　iPad 9.7
www.apple.com

　Apple Pencil
www.apple.com

　Logicool Keys-to-Go Ultra Slim Keyboard with iPhone Stand
www.apple.com

　ケース

Toovren iPad Pro 10.5 ケース 9.7 air 2 三つ折りカバースタンド機能付きマグネット合皮全面保護（ネービーブルー）

出版社/メーカー: Toovren
メディア: エレクトロニクス
この商品を含むブログを見る

　フィルム

出版社/メーカー: Nimaso
メディア: エレクトロニクス
この商品を含むブログを見る

2018-03-24

SQL Server Management Studio の知っておいたほうが良い機能について挙げてみる - その12 - ( 列名の前後に [ ] を付けないへの要望 )

SQL Server SQL Server Management Studio

　ちょっと早起きしたので、記事書きます。前回の記事で、17.6 で対応されたオブジェクトエクスプローラーからの各情報をドラッグ＆ドロップで [] を付加しないようにする機能を紹介しました。
ryuchan.hatenablog.com

　個人的には下記のパターンも対応してほしいです。あと、カンマの位置とかも制御できるといいですね。

テーブルをクリックする。
右クリックで、上位 n 件をクリックする。
列名およびテーブル名に [ ] が付加された SELECT 文が生成される。

　このケースも [ ] を制御できるといいですね。ひとりごとです。

2018-03-21

SQL Server Management Studio の知っておいたほうが良い機能について挙げてみる - その11 - ( 列名の前後に [ ] を付けない )

SQL Server SQL Server Management Studio

　しばらく、SQL Server Management Studio の機能について記事を書いていませんでした。
ryuchan.hatenablog.com

　SQL Server Management Studio 17.6 がリリースされました。
https://blogs.technet.microsoft.com/dataplatforminsider/2018/03/20/ssms-17-6-is-now-available-managed-instance-and-many-bug-fixes/

　細かい機能ですが、便利な設定が追加されていました。

Object Explorer: Added settings to allow users not to force brackets around names when dragging and dropping from Object Explorer to Query Window.

　早速設定してみましょう。

SQL Server management Studio を起動し、ツール→オプションをクリックします。

SQL Server オブジェクトエクスプローラーをクリックします。

Surround Object .. を false に設定します。

テーブルを開きます。

列を選択します。

ドロップします。

列名に [ ] を付けずに展開することが可能になっています。細かい機能ですが、いい機能ですね。

　肉が食べたくなってきた。

熟成肉肉バル CARNE BAR KATETE 虎ノ門店

〒105-0001 東京都港区虎ノ門1-16-2 B1

5,000円（平均）1,000円（ランチ平均）

03-6268-8929

地図や店舗情報を見る

　夏に向けて、色々購入。

リーフツアラーシュノーケルベストスリム RA0402 ライトグリーン Lサイズ

出版社/メーカー: リーフツアラー(REEF TOURER)
メディア: スポーツ用品
この商品を含むブログを見る

リーフツアラーマスクシュノーケルフィン 3点セット RP0102 ブラック Mサイズ

出版社/メーカー: リーフツアラー(REEF TOURER)
メディア: スポーツ用品
この商品を含むブログを見る

[FANTURE] [アップデート版] スキンシューズウォーターシューズプール砂浜サーフィンヨガ有酸素運動超軽量,SKIN01-Black-L

メディア: ウェア&シューズ
この商品を含むブログを見る

　シュノーケルマスクなるものがあるかのか。
[asin:B079FH512G:detail]

2018-03-05

大量の IN 句の動作を見てみた

SQL Server

　お久しぶりです。最近は、激しい移動すなわち、「 #ダイヤモンド修行」が激しさを増しており、記事さえ中々書けない事態に陥っています。今回はそんなのやるなら初めからちゃんと JOIN したりするのを前提にクエリ設計したらいいやん！と批判を浴びそうな気がしますが、ちょっと気になったので簡単に検証してみます。

下記のような恐ろしいクエリ1 とクエリ2 を用意します。
※クエリ1

SELECT
    [SalesOrderID],
    [RevisionNumber],
    [OrderDate],
    [DueDate],
    [ShipDate],
    [Status],
    [OnlineOrderFlag],
    [SalesOrderNumber],
    [PurchaseOrderNumber],
    [AccountNumber],
    [CustomerID],
    [SalesPersonID],
    [TerritoryID],
    [BillToAddressID],
    [ShipToAddressID],
    [ShipMethodID],
    [CreditCardID],
    [CreditCardApprovalCode],
    [CurrencyRateID],
    [SubTotal],
    [TaxAmt],
    [Freight],
    [TotalDue],
    [Comment],
    [rowguid],
    [ModifiedDate]
FROM
    [AdventureWorks].[Sales].[SalesOrderHeader]
WHERE
    SalesOrderID IN('43659', '43660', '43661', '43662', '43663', '43664', '43665', '43666', '43667', '43668', '43669', '43670', '43671', '43672', '43673', '43674', '43675', '43676', '43677', '43678', '43679', '43680', '43681', '43682', '43683', '43684', '43685', '43686', '43687', '43688', '43689', '43690', '43691', '43692', '43693', '43694', '43695', '43696', '43697', '43698', '43699', '43700', '43701', '43702', '43703', '43704', '43705', '43706', '43707', '43708', '43709', '43710', '43711', '43712', '43713', '43714', '43715', '43716', '43717', '43718', '43719', '43720', '43721', '43722', '43723', '43724', '43725', '43726', '43727', '43728', '43729', '43730', '43731', '43732', '43733', '43734', '43735', '43736', '43737', '43738', '43739', '43740', '43741', '43742', '43743', '43744', '43745', '43746', '43747', '43748', '43749', '43750', '43751', '43752', '43753', '43754', '43755', '43756', '43757', '43758', '43759', '43760', '43761', '43762', '43763', '43764', '43765', '43766', '43767', '43768', '43769', '43770', '43771', '43772', '43773', '43774', '43775', '43776', '43777', '43778', '43779', '43780', '43781', '43782', '43783', '43784', '43785', '43786', '43787', '43788', '43789', '43790', '43791', '43792', '43793', '43794', '43795', '43796', '43797', '43798', '43799', '43800', '43801', '43802', '43803', '43804', '43805', '43806', '43807', '43808', '43809', '43810', '43811', '43812', '43813', '43814', '43815', '43816', '43817', '43818', '43819', '43820', '43821', '43822', '43823', '43824', '43825', '43826', '43827', '43828', '43829', '43830', '43831', '43832', '43833', '43834', '43835', '43836', '43837', '43838', '43839', '43840', '43841', '43842', '43843', '43844', '43845', '43846', '43847', '43848', '43849', '43850', '43851', '43852', '43853', '43854', '43855', '43856', '43857', '43858', '43859', '43860', '43861', '43862', '43863', '43864', '43865', '43866', '43867', '43868', '43869', '43870', '43871', '43872', '43873', '43874', '43875', '43876', '43877', '43878', '43879', '43880', '43881', '43882', '43883', '43884', '43885', '43886', '43887', '43888', '43889', '43890', '43891', '43892', '43893', '43894', '43895', '43896', '43897', '43898', '43899', '43900', '43901', '43902', '43903', '43904', '43905', '43906', '43907', '43908')

※クエリ2

SELECT
    [SalesOrderID],
    [RevisionNumber],
    [OrderDate],
    [DueDate],
    [ShipDate],
    [Status],
    [OnlineOrderFlag],
    [SalesOrderNumber],
    [PurchaseOrderNumber],
    [AccountNumber],
    [CustomerID],
    [SalesPersonID],
    [TerritoryID],
    [BillToAddressID],
    [ShipToAddressID],
    [ShipMethodID],
    [CreditCardID],
    [CreditCardApprovalCode],
    [CurrencyRateID],
    [SubTotal],
    [TaxAmt],
    [Freight],
    [TotalDue],
    [Comment],
    [rowguid],
    [ModifiedDate]
FROM
    [AdventureWorks].[Sales].[SalesOrderHeader]
WHERE
    SalesOrderID IN('43873', '43660', '43824', '43739', '43694', '43898', '43902', '43713', '43700', '43664', '43661', '43745', '43708', '43901', '43741', '43794', '43848', '43755', '43888', '43687', '43813', '43670', '43781', '43840', '43817', '43857', '43853', '43759', '43815', '43810', '43711', '43750', '43830', '43723', '43698', '43754', '43879', '43748', '43778', '43777', '43753', '43870', '43742', '43728', '43691', '43668', '43702', '43707', '43818', '43811', '43780', '43847', '43763', '43784', '43837', '43680', '43860', '43862', '43866', '43821', '43906', '43838', '43699', '43861', '43894', '43666', '43751', '43785', '43872', '43725', '43877', '43744', '43782', '43798', '43743', '43752', '43662', '43793', '43676', '43683', '43747', '43677', '43796', '43667', '43669', '43867', '43722', '43709', '43718', '43692', '43829', '43730', '43881', '43726', '43773', '43834', '43825', '43695', '43688', '43889', '43712', '43800', '43826', '43775', '43899', '43799', '43885', '43789', '43684', '43802', '43740', '43845', '43863', '43703', '43724', '43736', '43704', '43721', '43792', '43882', '43904', '43682', '43851', '43842', '43895', '43774', '43868', '43900', '43729', '43779', '43672', '43887', '43675', '43673', '43832', '43880', '43697', '43831', '43874', '43749', '43883', '43823', '43905', '43715', '43737', '43701', '43858', '43731', '43766', '43689', '43714', '43852', '43735', '43764', '43772', '43686', '43890', '43756', '43762', '43706', '43787', '43822', '43807', '43671', '43770', '43681', '43804', '43679', '43801', '43841', '43720', '43833', '43908', '43827', '43812', '43797', '43663', '43816', '43719', '43850', '43814', '43907', '43876', '43896', '43871', '43819', '43835', '43839', '43884', '43659', '43761', '43765', '43768', '43705', '43696', '43674', '43869', '43854', '43859', '43886', '43678', '43716', '43769', '43795', '43892', '43808', '43878', '43685', '43844', '43710', '43803', '43903', '43786', '43836', '43809', '43849', '43843', '43746', '43897', '43864', '43790', '43875', '43856', '43805', '43791', '43734', '43733', '43776', '43846', '43727', '43820', '43788', '43690', '43865', '43738', '43665', '43771', '43767', '43828', '43758', '43783', '43757', '43893', '43855', '43693', '43806', '43891', '43732', '43717', '43760')

　クエリ1 の IN 句の条件は、SalesOrderID が昇順で並び替えられています。クエリ2 の IN 句の条件はランダムに並び替えられています。それぞれ計測してみます。

1回目

2回目

3回目

4回目

5回目

インデックスの並び順で IN 句指定されたほうが明らかに高速ですね。もう少し詳しくみてみましょう。SET STATISTICS PROFILE ON を付加します。
f:id:koogucc11:20180305002230p:plain

指定した IN 句の条件がきちんと並び替えられていますね。レスポンス差は内部的にこのソートの負荷が生じた結果なのでしょうか。

VALUES:(((43659)),((43660)),((43661)),((43662)),((43663)),((43664)),((43665)),((43666)),((43667)),((43668)),((43669)),((43670)),((43671)),((43672)),((43673)),((43674)),((43675)),((43676)),((43677)),((43678)),((43679)),((43680)),((43681)),((43682)),((43683)),((43684)),((43685)),((43686)),((43687)),((43688)),((43689)),((43690)),((43691)),((43692)),((43693)),((43694)),((43695)),((43696)),((43697)),((43698)),((43699)),((43700)),((43701)),((43702)),((43703)),((43704)),((43705)),((43706)),((43707)),((43708)),((43709)),((43710)),((43711)),((43712)),((43713)),((43714)),((43715)),((43716)),((43717)),((43718)),((43719)),((43720)),((43721)),((43722)),((43723)),((43724)),((43725)),((43726)),((43727)),((43728)),((43729)),((43730)),((43731)),((43732)),((43733)),((43734)),((43735)),((43736)),((43737)),((43738)),((43739)),((43740)),((43741)),((43742)),((43743)),((43744)),((43745)),((43746)),((43747)),((43748)),((43749)),((43750)),((43751)),((43752)),((43753)),((43754)),((43755)),((43756)),((43757)),((43758)),((43759)),((43760)),((43761)),((43762)),((43763)),((43764)),((43765)),((43766)),((43767)),((43768)),((43769)),((43770)),((43771)),((43772)),((43773)),((43774)),((43775)),((43776)),((43777)),((43778)),((43779)),((43780)),((43781)),((43782)),((43783)),((43784)),((43785)),((43786)),((43787)),((43788)),((43789)),((43790)),((43791)),((43792)),((43793)),((43794)),((43795)),((43796)),((43797)),((43798)),((43799)),((43800)),((43801)),((43802)),((43803)),((43804)),((43805)),((43806)),((43807)),((43808)),((43809)),((43810)),((43811)),((43812)),((43813)),((43814)),((43815)),((43816)),((43817)),((43818)),((43819)),((43820)),((43821)),((43822)),((43823)),((43824)),((43825)),((43826)),((43827)),((43828)),((43829)),((43830)),((43831)),((43832)),((43833)),((43834)),((43835)),((43836)),((43837)),((43838)),((43839)),((43840)),((43841)),((43842)),((43843)),((43844)),((43845)),((43846)),((43847)),((43848)),((43849)),((43850)),((43851)),((43852)),((43853)),((43854)),((43855)),((43856)),((43857)),((43858)),((43859)),((43860)),((43861)),((43862)),((43863)),((43864)),((43865)),((43866)),((43867)),((43868)),((43869)),((43870)),((43871)),((43872)),((43873)),((43874)),((43875)),((43876)),((43877)),((43878)),((43879)),((43880)),((43881)),((43882)),((43883)),((43884)),((43885)),((43886)),((43887)),((43888)),((43889)),((43890)),((43891)),((43892)),((43893)),((43894)),((43895)),((43896)),((43897)),((43898)),((43899)),((43900)),((43901)),((43902)),((43903)),((43904)),((43905)),((43906)),((43907)),((43908)))

時間あったら、読んでみたい。