読者です 読者をやめる 読者になる 読者になる

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

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

統計情報をいじくってみる

 先週の木曜日くらいから調子悪くなって、金曜日に病院へいったら...アデノウィルスによる結膜炎 ( 流行性角結膜炎( EKC )) と診断されました。体は怠いし、目は痛いし最悪の状態です。アデノウィルスは感染力も強いので、出社もしばらく控えなければいけません。( 症状が出ている状態でお会いした方、ごめんなさいm(__)m )

 家では、こまめに石鹸で手を洗い

サラヤ シャボネット ユ・ム P-5泡 1kg ポンプ付

サラヤ シャボネット ユ・ム P-5泡 1kg ポンプ付

 アルコールで消毒しています。

【第2類医薬品】ヒビスコールS 500mL

【第2類医薬品】ヒビスコールS 500mL

 また、常に眼帯付けて、マスク付けて、他者に感染しないようにしています。
f:id:koogucc11:20170415183435j:plain

 久しぶりに眼帯をしたのですが、最近の眼帯って貼るタイプがあるんですね。これ、お勧めです。

貼れる眼帯 50枚入

貼れる眼帯 50枚入

 何種類か試したんですが、粘着力も程よく ( 粘着力が強すぎると、剥がすとき結構辛い。) 、とめる箇所は3点あるため安定します。
f:id:koogucc11:20170415184704p:plain

 アイパッドって(笑)
f:id:koogucc11:20170415182642j:plain

 病状報告はこれまでにして、本題に入ります。統計情報は SQL の安定したパフォーマンスを保つための大切な要素の一つです。可能な限り最新の状態に維持する必要があります。しかし、数十 TB 規模のデータベースで統計情報を最新化していく運用は容易ではありません。( 2014 以降増分統計がサポートされましたが、実運用では試したことありません。何かしら課題は出るんでしょう(´;ω;`) )

 統計情報を UPDATE STATISTICS で更新すると、非常に時間がかかり且つ運用に多少なり影響を与えることがあります。ふと、統計情報を運用系とは違う場所で作って、運用系に移せないかと考えてみました。統計情報を取得するには、DBCC SHOW_STATISTICS (Transact-SQL) | Microsoft Docs を使用します。

DBCC SHOW_STATISTICS ( table_or_indexed_view_name , target )   
[ WITH [ NO_INFOMSGS ] < option > [ , n ] ]  
< option > :: =  
    STAT_HEADER | DENSITY_VECTOR | HISTOGRAM | STATS_STREAM  

 WideWorldImporters のデータベースを使用して、統計情報を取得してみましょう。下記のクエリを SQL Server Management Studio で実行しましょう。

USE WideWorldImporters;
DBCC SHOW_STATISTICS("Sales.Orders",FK_Sales_Orders_ContactPersonID) WITH STATS_STREAM

f:id:koogucc11:20170416154505p:plain

 取得した Stats_Stream のデータを UPDATE STATISTICS (Transact-SQL) | Microsoft Docsで更新してみます。

UPDATE STATISTICS table_or_indexed_view_name   
    [   
        {   
            { index_or_statistics__name }  
          | ( { index_or_statistics_name } [ ,...n ] )   
                }  
    ]   
    [    WITH   
        [  
            FULLSCAN   
            | SAMPLE number { PERCENT | ROWS }   
            | RESAMPLE   
              [ ON PARTITIONS ( { <partition_number> | <range> } [, …n] ) ]  
            | <update_stats_stream_option> [ ,...n ]  
        ]   
        [ [ , ] [ ALL | COLUMNS | INDEX ]   
        [ [ , ] NORECOMPUTE ]   
        [ [ , ] INCREMENTAL = { ON | OFF } ]  
    ] ;  

<update_stats_stream_option> ::=  
    [ STATS_STREAM = stats_stream ]  
    [ ROWCOUNT = numeric_constant ]  
    [ PAGECOUNT = numeric_contant ]  

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

USE WideWorldImporters;

UPDATE STATISTICS Sales.Orders(FK_Sales_Orders_ContactPersonID) 
WITH STATS_STREAM = 0x010000000200000000000000000000008F837BB500000000EC1A000000000000941A000000000000380300003800000004000A000000000000000000700200003803AB663800000004000A000000000000000000F97F000007000000E52DB10019A600009F170100000000009F170100000000001FD1193C224BC63AD35F6A3700000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000C8000000C800000002000000140000000000004180CF8B470000000000008040000080400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000019000000000000000000000000000000381800000000000020190000000000002819000000000000400600000000000057060000000000006E0600000000000085060000000000009C06000000000000B306000000000000CA06000000000000E106000000000000F8060000000000000F0700000000000026070000000000003D0700000000000054070000000000006B0700000000000082070000000000009907000000000000B007000000000000C707000000000000DE07000000000000F5070000000000000C0800000000000023080000000000003A08000000000000510800000000000068080000000000007F080000000000009608000000000000AD08000000000000C408000000000000DB08000000000000F2080000000000000909000000000000200900000000000037090000000000004E0900000000000065090000000000007C090000000000009309000000000000AA09000000000000C109000000000000D809000000000000EF09000000000000060A0000000000001D0A000000000000340A0000000000004B0A000000000000620A000000000000790A000000000000900A000000000000A70A000000000000BE0A000000000000D50A000000000000EC0A000000000000030B0000000000001A0B000000000000310B000000000000480B0000000000005F0B000000000000760B0000000000008D0B000000000000A40B000000000000BB0B000000000000D20B000000000000E90B000000000000000C000000000000170C0000000000002E0C000000000000450C0000000000005C0C000000000000730C0000000000008A0C000000000000A10C000000000000B80C000000000000CF0C000000000000E60C000000000000FD0C000000000000140D0000000000002B0D000000000000420D000000000000590D000000000000700D000000000000870D0000000000009E0D000000000000B50D000000000000CC0D000000000000E30D000000000000FA0D000000000000110E000000000000280E0000000000003F0E000000000000560E0000000000006D0E000000000000840E0000000000009B0E000000000000B20E000000000000C90E000000000000E00E000000000000F70E0000000000000E0F000000000000250F0000000000003C0F000000000000530F0000000000006A0F000000000000810F000000000000980F000000000000AF0F000000000000C60F000000000000DD0F000000000000F40F0000000000000B1000000000000022100000000000003910000000000000501000000000000067100000000000007E100000000000009510000000000000AC10000000000000C310000000000000DA10000000000000F11000000000000008110000000000001F1100000000000036110000000000004D1100000000000064110000000000007B110000000000009211000000000000A911000000000000C011000000000000D711000000000000EE1100000000000005120000000000001C1200000000000033120000000000004A12000000000000611200000000000078120000000000008F12000000000000A612000000000000BD12000000000000D412000000000000EB1200000000000002130000000000001913000000000000301300000000000047130000000000005E1300000000000075130000000000008C13000000000000A313000000000000BA13000000000000D113000000000000E813000000000000FF1300000000000016140000000000002D1400000000000044140000000000005B1400000000000072140000000000008914000000000000A014000000000000B714000000000000CE14000000000000E514000000000000FC1400000000000013150000000000002A15000000000000411500000000000058150000000000006F1500000000000086150000000000009D15000000000000B415000000000000CB15000000000000E215000000000000F915000000000000101600000000000027160000000000003E1600000000000055160000000000006C1600000000000083160000000000009A16000000000000B116000000000000C816000000000000DF16000000000000F6160000000000000D1700000000000024170000000000003B170000000000005217000000000000691700000000000080170000000000009717000000000000AE17000000000000C517000000000000DC17000000000000F3170000000000000A1800000000000021180000000000001000140000000043000000000000803FE9030000040000100014000000D642000078430000F842EF0300000400001000140000000243000067430000E742F5030000040000100014000000DE420080A3430000DA42FD030000040000100014000000AA420000CA420000CA4201040000040000100014000000054300006D430000ED4207040000040000100014000000DA420000D4430000D44211040000040000100014000000D242000043430000C34217040000040000100014000000F4420000B4430000F0421F04000004000010001400000004430000AB430000E44227040000040000100014000000054300005C430000DC422D0400000400001000140000000343000064430000E44233040000040000100014000000DC4200003D430000BD4239040000040000100014000000EE420000AC435555E54241040000040000100014000000EE4200004D430000CD4247040000040000100014000000EC420000A3435555D9424F040000040000100014000000D8420000BC420000BC4253040000040000100014000000C642000063430000E34259040000040000100014000000DE420000E2420000E2425D040000040000100014000000BA4200004C430000CC4263040000040000100014000000DC420000EE420000EE42670400000400001000140000000A43000055430000D5426D0400000400001000140000000F4300006F430000EF4273040000040000100014000000DC420000A0435555D5427B04000004000010001400000001430000A0435555D54283040000040000100014000000F24200007B430000FB4289040000040000100014000000D6420000D6420000D6428D040000040000100014000000E04200009E43ABAAD242950400000400001000140000001143000076430000F6429B040000040000100014000000E64200009D435555D142A3040000040000100014000000D6420000B0420000B042A7040000040000100014000000F242000079430000F942AD040000040000100014000000014300005A430000DA42B3040000040000100014000000C642000065430000E542B9040000040000100014000000F2420000FC420000FC42BD0400000400001000140000000D4300004A430000CA42C3040000040000100014000000EC420000B0420000B042C7040000040000100014000000F44200005D430000DD42CD040000040000100014000000EE420080AF430000EA42D5040000040000100014000000EE420000A5430000DC42DD040000040000100014000000B64200005B430000DB42E304000004000010001400000001430080A243ABAAD842EB04000004000010001400000000430000034300000343EF040000040000100014000000EE4200006C430000EC42F5040000040000100014000000DA420000A3435555D942FD0400000400001000140000000D430000B043ABAAEA4205050000040000100014000000D442000061430000E1420B050000040000100014000000F642000043430000C34211050000040000100014000000DA420000E8420000E84215050000040000100014000000C84200004A430000CA421B050000040000100014000000FA42000059430000D94221050000040000100014000000D04200005A430000DA4227050000040000100014000000CA420000AC435555E5422F050000040000100014000000EC420000E8420000E84233050000040000100014000000FA4200005D430000DD4239050000040000100014000000D64200005C430000DC423F050000040000100014000000E0420080A9430000E24247050000040000100014000000D042000074430000F4424D05000004000010001400000009430000AD43ABAAE6425505000004000010001400000003430000FA420000FA4259050000040000100014000000F24200006E430000EE425F050000040000100014000000DE420080A6430000DE42670500000400001000140000000043000057430000D7426D050000040000100014000000CC42008095435555C74275050000040000100014000000E242000071430000F142D10700000400001000140000000F430000AF435555E942D9070000040000100014000000EE420000E8420000E842DD070000040000100014000000FA42000051430000D142E3070000040000100014000000EE420080A7435555DF42EB070000040000100014000000EE4200009F430000D442F3070000040000100014000000B2420080A3430000DA42FB070000040000100014000000DE42000044430000C44201080000040000100014000000DE42000041430000C14207080000040000100014000000F042000071430000F1420D080000040000100014000000D6420080A0430000D64215080000040000100014000000D4420000CA420000CA4219080000040000100014000000D842000074430000F4421F080000040000100014000000E8420000EA420000EA4223080000040000100014000000D2420000A2430000D8422B08000004000010001400000004430000E8420000E8422F080000040000100014000000F0420080A4435555DB4237080000040000100014000000D4420000D8420000D8423B080000040000100014000000D64200005A430000DA4241080000040000100014000000E4420080B5430000F24249080000040000100014000000C64200005C430000DC424F080000040000100014000000FE42000069430000E94255080000040000100014000000C042000065430000E5425B080000040000100014000000E6420000B5435555F14263080000040000100014000000B642000059430000D94269080000040000100014000000E442000068430000E8426F080000040000100014000000C6420000CE420000CE4273080000040000100014000000F04200006C430000EC4279080000040000100014000000F2420080AB43ABAAE44281080000040000100014000000064300808743ABAAB44289080000040000100014000000DE420000DE420000DE428D080000040000100014000000F842000052430000D24293080000040000100014000000CC420000DA420000DA4297080000040000100014000000D8420080AB43ABAAE4429F080000040000100014000000EE420000EA420000EA42A3080000040000100014000000F0420000A743ABAADE42AB080000040000100014000000F842000099430000CC42B3080000040000100014000000EC420080A6430000DE42BB080000040000100014000000D2420000B043ABAAEA42C3080000040000100014000000CA420000B043ABAAEA42CB080000040000100014000000CC420000B643ABAAF242D3080000040000100014000000E4420080A4435555DB42DB080000040000100014000000F4420000A0435555D542E3080000040000100014000000EC4200809B435555CF42EB08000004000010001400000005430000AA43ABAAE242F3080000040000100014000000E4420080A4435555DB42FB080000040000100014000000E8420000A3435555D94203090000040000100014000000024300809A430000CE420B090000040000100014000000F6420080A6430000DE4213090000040000100014000000E2420000C6420000C64217090000040000100014000000C04200007D430000FD421D090000040000100014000000DC420080A3430000DA4225090000040000100014000000EE4200809943ABAACC422D09000004000010001400000094420000CE420000CE42310900000400001000140000000B4300005B430000DB4237090000040000100014000000044300005A430000DA423D090000040000100014000000E4420000AE430000E84245090000040000100014000000E6420000A143ABAAD6424D090000040000100014000000E6420000AD43ABAAE642550900000400001000140000000B43000069430000E9425B090000040000100014000000FE42000051430000D142610900000400001000140000000C430080AD435555E742BC0B0000040000100014000000EA42000062430000E242BF0B0000040000100014000000FA420000DA420000DA42C10B0000040000100014000000E8420080A543ABAADC42C50B000004000010001400000001430000B4420000B442C70B0000040000100014000000AE4200006D430000ED42CA0B0000040000100014000000D64200006E430000EE42CD0B0000040000100014000000FE420000C2420000C242CF0B0000040000100014000000D842000051430000D142D20B0000040000100014000000F242000063430000E342D50B000004000010001400000012430000004300000043D70B0000040000100014000000EA42000053430000D342DA0B0000040000100014000000DC42000062430000E242DD0B0000040000100014000000F2420080A1435555D742E10B0000040000100014000000CA4200005F430000DF42E40B000004000010001400000002430000A3435555D942E80B0000040000100014000000C042000072430000F242EB0B0000040000100014000000E842000064430000E442EE0B0000040000100014000000EE42000061430000E142F10B0000040000100014000000D642000050430000D042F40B0000040000100014000000D242000071430000F142F70B0000040000100014000000CE42000044430000C442FA0B0000040000100014000000944200005B430000DB42FD0B0000040000100014000000E442000060430000E042000C0000040000100014000000E842000065430000E542030C0000040000100014000000BE42000074430000F442060C0000040000100014000000B842000072430000F242090C0000040000100014000000FA42000054430000D4420C0C0000040000100014000000B642000066430000E6420F0C0000040000100014000000E042000076430000F642120C0000040000100014000000FA42000058430000D842150C0000040000100014000000E04200004C430000CC42180C0000040000100014000000E242000069430000E9421B0C0000040000100014000000DE42000068430000E8421E0C0000040000100014000000FA4200004A430000CA42210C0000040000100014000000FA420000054300000543230C00000400001000140000000B43000063430000E342260C0000040000100014000000E84200005D430000DD42290C0000040000100014000000FA4200004D430000CD422C0C0000040000100014000000D4420000A0435555D542300C0000040000100014000000E242000041430000C142330C0000040000100014000000D04200005B430000DB42360C0000040000100014000000C04200005A430000DA42390C0000040000100014000000CC42000073430000F3423C0C0000040000100014000000DA4200006B430000EB423F0C0000040000100014000000CC42000062430000E242420C0000040000100014000000074300005D430000DD42450C0000040000100014000000BA420080A9430000E242490C0000040000100014000000CC42000065430000E5424C0C0000040000100014000000E6420000A3435555D942500C0000040000100014000000D44200007D430000FD42530C0000040000100014000000FE4200004B430000CB42560C0000040000100014000000D8420080AE43ABAAE8425A0C0000040000100014000000EC4200008043000000435D0C0000040000100014000000114300006F430000EF42600C0000040000100014000000C44200005B430000DB42630C0000040000100014000000DE42000073430000F342660C0000040000100014000000EC420080824300800243690C0000040000100014000000044300005E430000DE426C0C0000040000100014000000D04200007F430000FF426F0C0000040000100014000000D04200809E435555D342730C000004000010001400000000430080A1435555D742770C0000040000100014000000D6420000A9435555E1427B0C0000040000100014000000B0420000A443ABAADA427F0C0000040000100014000000B0420080AF430000EA42830C0000040000100014000000D842000099430000CC42870C0000040000100014000000FC420000A743ABAADE428B0C0000040000100014000000D2420000A8430000A842900C00000400001000140000009E420000A8430000A842950C00000400001000140000008A420080AF430080AF429A0C000004000010001400000092420080AF4366668C42A00C000004000010001400000044420000AA43ABAA6242A70C0000040000100014000000C8410000A643DBB63D42AF0C0000040000100014000000304100004443CDCC9C41BA0C00000400001000140000008040000000000000803FBB0C000004000003000000284AAB0019A6000000000000A00BEF405DF8000000000000C80000000000004006F0583F5D74D145171D51400000000000000000000000000042BF40000000000000000029A671FF9F73BC3F4285AA0019A6000000000000805FEB40FCDA000000000000C8000000000000E0B73E593F0000000000E850400000000000000000000000000061BD400000000000000000DD2C46FD39ECC03F650BAA0019A6000000000000E0E8E74047BF000000000000C800000000000000B252593F000000000080384000000000000000000000000000B5BB4000000000000000007EE93DC0C3E0C43F9F17010000000000, 
ROWCOUNT = 73595, PAGECOUNT = 189

f:id:koogucc11:20170416155118p:plain

 この動作を待機系または非同期のサーバで統計情報を取得して、運用系に更新する運用が取れれば統計情報更新の運用が少し楽になるかなぁ。(自身の参加しているプロジェクトで。) あと、データの分布がおおよそ判断できるものは、STATS_STREAM のデータを自分で作成して更新したいくらいです。ぱっと見ても内部仕様はわかりませんが.....(じーっと見てると、規則性がありそうですが....この情報、どこかに公開されているのかな?)

 過去に書いた記事の内容と合わせて考えると、クエリテスト時に大量レコードを発生させることなくテストができそう。できるかわかりませんが、今度実験してみます。
ryuchan.hatenablog.com

 ちょっと目の調子が良くなったので、記事書いてみましたが、また調子が悪くなってきました。嗚呼、早く完治しないかな...

最近、よく病気になります。免疫力下がってるのかな?