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

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

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

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

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

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

　また、常に眼帯付けて、マスク付けて、他者に感染しないようにしています。

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

貼れる眼帯 50枚入

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

　アイパッドって(笑)

　病状報告はこれまでにして、本題に入ります。統計情報は 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

　取得した 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

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

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

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

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

風邪予防、虚弱体質改善から始める 最強の免疫力 (頼りになるお医者さんシリーズ)