都内で働くSEの技術的なひとりごと

都内でサラリーマンやってます。マイクロソフト系(たまに、OSS系などマイクロソフト以外の技術も...)の技術的なことについて書いています。日々の仕事の中で、気になったことを技術要素関係なく気まぐれに選んでいるので記事内容は開発言語、インフラ等ばらばらです。なお、当ブログで発信、発言は私個人のものであり、所属する組織、企業、団体等とは何のかかわりもございません。ブログの内容もきちんと検証して使用してください。よろしくお願いします♪

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

 久しぶりの投稿です。梅雨前にもかかわらず関東はいい天気に恵まれ、来週の月曜日までこの天気が続くようです。
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 の付属品も色々買いました。

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

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

 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

 ケース

 フィルム

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

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

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

  • テーブルをクリックする。
    f:id:koogucc11:20180324062546p:plain
  • 右クリックで、上位 n 件をクリックする。
    f:id:koogucc11:20180324065650p:plain
  • 列名およびテーブル名に [ ] が付加された SELECT 文が生成される。
    f:id:koogucc11:20180324062559p:plain

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

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

 しばらく、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 を起動し、ツール→オプションをクリックします。f:id:koogucc11:20180321195359p:plain
  • Surround Object .. を false に設定します。f:id:koogucc11:20180324060606p:plain
  • テーブルを開きます。f:id:koogucc11:20180321202327p:plain
  • 列を選択します。f:id:koogucc11:20180321202338p:plain
  • ドロップします。f:id:koogucc11:20180321202349p:plain

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

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

 夏に向けて、色々購入。

 シュノーケルマスクなるものがあるかのか。

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

 お久しぶりです。最近は、激しい移動すなわち、「 #ダイヤモンド修行 」 が激しさを増しており、記事さえ中々書けない事態に陥っています。今回はそんなのやるなら初めからちゃんと 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回目f:id:koogucc11:20180305001430p:plain
  • 2回目f:id:koogucc11:20180305001435p:plain
  • 3回目f:id:koogucc11:20180305001440p:plain
  • 4回目f:id:koogucc11:20180305001445p:plain
  • 5回目f:id:koogucc11:20180305001449p:plain

インデックスの並び順で 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)))

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

SQL Server 2017 Administrator's Guide

SQL Server 2017 Administrator's Guide