Skip to content

Instantly share code, notes, and snippets.

Created January 12, 2016 16:04
Show Gist options
  • Save anonymous/d928d7b50cd9cf077609 to your computer and use it in GitHub Desktop.
Save anonymous/d928d7b50cd9cf077609 to your computer and use it in GitHub Desktop.
SELECT @@VERSION
--Microsoft SQL Server 2014 (SP1-CU3) (KB3094221) - 12.0.4427.24 (X64)
-- Oct 10 2015 17:18:26
-- Copyright (c) Microsoft Corporation
-- Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
GO
/***************************************************************************
SETUP -- create a stats-only version of the table
***************************************************************************/
CREATE DATABASE tempStats
GO
USE tempStats
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE dbo.BigFactTable(
factCol1 [int] NOT NULL,
factCol2 [int] NOT NULL,
factCol3 [datetime] NOT NULL,
factCol4 [bigint] NOT NULL,
factCol5 [bigint] NOT NULL
CONSTRAINT PK_BigFactTable PRIMARY KEY CLUSTERED
(
factCol2 ASC,
factCol3 ASC,
factCol4 ASC,
factCol1 ASC,
factCol5 ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
/****** Object: Statistic [_WA_Sys_00000001_24927208] Script Date: 1/6/2016 2:27:46 PM ******/
CREATE STATISTICS [_WA_Sys_00000001_24927208] ON dbo.BigFactTable(factCol1) WITH STATS_STREAM = 0x010000000100000000000000000000000BFD7D9700000000D41A000000000000941A000000000000380300003800000004000A000000000000000000380000000700000049E20F0085A50000299001CD000000008B792D0000000000BB0B4D3914F1123B0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000C8000000C800000001000000140000000000804090014D4F0000000000008040000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000019000000000000000000000000000000381800000000000020190000000000002819000000000000400600000000000057060000000000006E0600000000000085060000000000009C06000000000000B306000000000000CA06000000000000E106000000000000F8060000000000000F0700000000000026070000000000003D0700000000000054070000000000006B0700000000000082070000000000009907000000000000B007000000000000C707000000000000DE07000000000000F5070000000000000C0800000000000023080000000000003A08000000000000510800000000000068080000000000007F080000000000009608000000000000AD08000000000000C408000000000000DB08000000000000F2080000000000000909000000000000200900000000000037090000000000004E0900000000000065090000000000007C090000000000009309000000000000AA09000000000000C109000000000000D809000000000000EF09000000000000060A0000000000001D0A000000000000340A0000000000004B0A000000000000620A000000000000790A000000000000900A000000000000A70A000000000000BE0A000000000000D50A000000000000EC0A000000000000030B0000000000001A0B000000000000310B000000000000480B0000000000005F0B000000000000760B0000000000008D0B000000000000A40B000000000000BB0B000000000000D20B000000000000E90B000000000000000C000000000000170C0000000000002E0C000000000000450C0000000000005C0C000000000000730C0000000000008A0C000000000000A10C000000000000B80C000000000000CF0C000000000000E60C000000000000FD0C000000000000140D0000000000002B0D000000000000420D000000000000590D000000000000700D000000000000870D0000000000009E0D000000000000B50D000000000000CC0D000000000000E30D000000000000FA0D000000000000110E000000000000280E0000000000003F0E000000000000560E0000000000006D0E000000000000840E0000000000009B0E000000000000B20E000000000000C90E000000000000E00E000000000000F70E0000000000000E0F000000000000250F0000000000003C0F000000000000530F0000000000006A0F000000000000810F000000000000980F000000000000AF0F000000000000C60F000000000000DD0F000000000000F40F0000000000000B1000000000000022100000000000003910000000000000501000000000000067100000000000007E100000000000009510000000000000AC10000000000000C310000000000000DA10000000000000F11000000000000008110000000000001F1100000000000036110000000000004D1100000000000064110000000000007B110000000000009211000000000000A911000000000000C011000000000000D711000000000000EE1100000000000005120000000000001C1200000000000033120000000000004A12000000000000611200000000000078120000000000008F12000000000000A612000000000000BD12000000000000D412000000000000EB1200000000000002130000000000001913000000000000301300000000000047130000000000005E1300000000000075130000000000008C13000000000000A313000000000000BA13000000000000D113000000000000E813000000000000FF1300000000000016140000000000002D1400000000000044140000000000005B1400000000000072140000000000008914000000000000A014000000000000B714000000000000CE14000000000000E514000000000000FC1400000000000013150000000000002A15000000000000411500000000000058150000000000006F1500000000000086150000000000009D15000000000000B415000000000000CB15000000000000E215000000000000F915000000000000101600000000000027160000000000003E1600000000000055160000000000006C1600000000000083160000000000009A16000000000000B116000000000000C816000000000000DF16000000000000F6160000000000000D1700000000000024170000000000003B170000000000005217000000000000691700000000000080170000000000009717000000000000AE17000000000000C517000000000000DC17000000000000F3170000000000000A180000000000002118000000000000100014007D9A354B000000000000803F7B01000004000010001400D690464B000000000000803F7C01000004000010001400F956394BC527934AC527934A7E010000040000100014007606314BA23B4C4BA23BCC4A8201000004000010001400DD0B9A4A8226014B8226014B8401000004000010001400CF7B224BB52D274BB52D274B860100000400001000140050DF104B09AF094B09AF094B88010000040000100014007743604B0B731A4B0B739A4A8B01000004000010001400B3730C4B58B5024B58B5024B8D01000004000010001400B43C284B000000000000803F8E01000004000010001400DD244B4BE2120C4BE2120C4B9001000004000010001400C2D3D14A000000000000803F910100000400001000140074C5E64A955EFA4A955EFA4A930100000400001000140013CD174B53731E4B53731E4B9501000004000010001400FB93684BCD75214BCD75214B970100000400001000140057308C4BC860F94AC860F94A990100000400001000140091F5154B115E324B115E324B9C01000004000010001400B26E194B64D34B4B64D3CB4AA0010000040000100014006BED004BB244CC4AB244CC4AA3010000040000100014009DA6D74A4610E84A4610E84AA50100000400001000140030125D4B4AB3054B4AB3054BA701000004000010001400D1A4134B1E54E64A1E54E64AA90100000400001000140064A3374B4095FE4A4095FE4AAB010000040000100014005787534B000000000000803FAC01000004000010001400FAD5994A70DDD94A70DDD94AAE01000004000010001400D137324B797F4A4B797F4A4BB001000004000010001400A483BF4AC4070F4BC4070F4BB201000004000010001400C3D8C44A73C1EE4A73C1EE4AB401000004000010001400D22AA64B000000000000803FB501000004000010001400CF0EC14ABFA7824ABFA7824AB7010000040000100014006BB1294BF4A6174BF4A6174BB901000004000010001400EA35E24A93394F4B9339CF4ABC01000004000010001400D9DD354B000000000000803FBD01000004000010001400266D404B06833C4B0683BC4AC0010000040000100014008695C14A6CF41D4B6CF41D4BC2010000040000100014000742134B43F0024B43F0024BC401000004000010001400E71D184BDBD8384BDBD8384BC60100000400001000140097ED6D4B000000000000803FC701000004000010001400D54D3D4B000000000000803FC801000004000010001400262A374BC9104F4BC910CF4ACB0100000400001000140038564C4BACE9054BACE9054BCD0100000400001000140054883A4B7E2CB64A7E2CB64ACF01000004000010001400D30B1B4BA850554BA850554BD101000004000010001400A0EC144BF97BF94AF97BF94AD30100000400001000140054A0134BAE7EDF4AAE7EDF4AD5010000040000100014005223724B000000000000803FD601000004000010001400D0D7DC4A2EED374B2EEDB74AD901000004000010001400C4E3114B1AB8094B1AB8094BDB0100000400001000140022938C4B1ACC444B1ACC444BDD01000004000010001400C0220F4B9248024B9248024BDF010000040000100014008DFBA84B000000000000803FE00100000400001000140000D9BE4A2262EC4A2262EC4AE201000004000010001400F4C1CD4A9316004B9316004BE4010000040000100014006F34164B000000000000803FE501000004000010001400CB1CB44A9ED1D24A9ED1D24AE7010000040000100014003782E34ABD440C4BBD440C4BE9010000040000100014002FD5AD4B000000000000803FEA0100000400001000140053F0284BE954D14AE954D14AEC01000004000010001400DFE05A4BDC022A4BDC022A4BEE01000004000010001400DD6D2E4B000000000000803FEF010000040000100014008609554BDCE4014BDCE4014BF1010000040000100014006548554B1808344B1808344BF301000004000010001400EE63C64AB0BE064BB0BE064BF5010000040000100014003F6B704B000000000000803FF601000004000010001400F501EA4B95DA104B95DA104BF801000004000010001400C6B8D24BBEE8184BBEE8184BFA01000004000010001400C706A94A7D63DF4A7D63DF4AFC01000004000010001400C0411A4B32B3244B32B3244BFE0100000400001000140022DDC74A000000000000803FFF010000040000100014007B04E34AFB30F64AFB30F64A0102000004000010001400E5683A4B000000000000803F020200000400001000140036F51E4BC420714AC420714A04020000040000100014004392A24ABD15D54ABD15D54A0602000004000010001400828A834BB561054BB561054B08020000040000100014003794624B4ABDD34A4ABDD34A0A02000004000010001400D3E1424B41EE264B41EE264B0C02000004000010001400149D654B7CB5044B7CB5844A0F0200000400001000140051F8414B23C5014B23C5014B11020000040000100014007116E34A5024024B5024024B13020000040000100014007E8D294B000000000000803F140200000400001000140026CFD44A5F5F044B5F5F044B16020000040000100014007922164B000000000000803F1702000004000010001400FB5B2C4B1E97D84A1E97D84A190200000400001000140021D1224B7E0E0E4B7E0E0E4B1B0200000400001000140095730F4BA26A034BA26A034B1D020000040000100014006BC3284B000000000000803F1E02000004000010001400B928DB4A13F5064B13F5064B2002000004000010001400CAF8354B2BBC434B2BBC434B2202000004000010001400F738154B000000000000803F23020000040000100014004F7F3B4B5E78054B5E78054B2502000004000010001400C189164B000000000000803F260200000400001000140039420E4B1B34014B1B34014B280200000400001000140002F7624B8258E44A8258E44A2A0200000400001000140073CB404B000000000000803F2B02000004000010001400EA90C44A6F70F64A6F70F64A2D02000004000010001400E4BEA94B000000000000803F2E02000004000010001400C7683D4B2B7EF84A2B7EF84A3002000004000010001400AEA2494BD7DEED4AD7DEED4A320200000400001000140064054C4B20A8484B20A8C84A3502000004000010001400F241154B56F6184B56F6184B3702000004000010001400E2AEE94AFE0FE44AFE0FE44A3902000004000010001400A4165E4B000000000000803F3A020000040000100014001A0C944B000000000000803F3B02000004000010001400CD28594B529B334B529B334B3D02000004000010001400A074E64AD1D0064BD1D0064B3F0200000400001000140094F42E4B000000000000803F4002000004000010001400614BFB4A50B2D84A50B2D84A420200000400001000140012E1D54B000000000000803F430200000400001000140040883C4B8FFE0C4B8FFE0C4B450200000400001000140023E8944B1FBC224B1FBC224B47020000040000100014002977A64A000000000000803F4802000004000010001400346A194B40BF0E4B40BF0E4B4A02000004000010001400E74E224B000000000000803F4B020000040000100014002635044B0D4BE64A0D4BE64A4D02000004000010001400538AF94A2770F24A2770F24A50020000040000100014009DFA874B000000000000803F5102000004000010001400550ECD4A75F2814A75F2014A54020000040000100014001142124B2253584B2253D84A5702000004000010001400D48B534BDE74344BDE74B44A5B020000040000100014000F20D34A959CC54A959CC54A5D02000004000010001400DB4F0A4BE11FC44AE11FC44A5F020000040000100014009CD6094BACE2E34AACE2E34A6102000004000010001400602B184B8ADA504B8ADAD04A64020000040000100014008FA97E4B000000000000803F6502000004000010001400C960244BE187804AE187804A6702000004000010001400E164AE4A464E334A464E334A69020000040000100014004C4BFD4A396CBA4A396CBA4A6B020000040000100014008348D24A65D0804A65D0804A6D02000004000010001400D30B1B4B3FDD364B3FDDB64A71020000040000100014004462F04A16624B4B1662CB4A7402000004000010001400361B5C4B47F7E64A47F7E64A76020000040000100014009D2B124B86A4354B86A4354B78020000040000100014009566834B2E7D094B2E7D894A7B020000040000100014007463D24AA57BD44AA57BD44A7D02000004000010001400BF949C4B9F3EB64A9F3EB64A7F020000040000100014001FD93B4B706D2B4B706D2B4B81020000040000100014007A34154B000000000000803F82020000040000100014001764C24AEABC0D4BEABC8D4A850200000400001000140051C7374BCB82D94ACB82D94A87020000040000100014001DCD164B1EDACA4A1EDACA4A8902000004000010001400D324CC4AF871AB4AF871AB4A8B020000040000100014000637C64AD869F94AD869F94A8D02000004000010001400CC588B4A1451DB4A1451DB4A8F02000004000010001400294D4E4B76492F4B76492F4B91020000040000100014009A16DF4A014BC54A014BC54A93020000040000100014008154064BE1A5A84AE1A5A84A95020000040000100014002A7C194B07CBD54A07CBD54A970200000400001000140020D3E14ABE5DEE4ABE5DEE4A99020000040000100014009562E84A976FEA4A976FEA4A9B020000040000100014000532D34A03250C4B03250C4B9D02000004000010001400FB9E354B3D32A74A3D32A74A9F0200000400001000140078CD0D4BBA36E74ABA36E74AA1020000040000100014003043E74A3284ED4A3284ED4AA30200000400001000140052FDB44A1C25CE4A1C25CE4AA502000004000010001400410ECF4AFF1BAD4AFF1B2D4AA802000004000010001400C5DDB74AAAAEA44AAAAEA44AAA02000004000010001400A136D04ACB213F4ACB213F4AAC02000004000010001400BB94554B92A91C4B92A99C4AAF020000040000100014003B4EB34A460BC14A460BC14AB102000004000010001400217D724B7283424B7283C24AB402000004000010001400BDB6144B1C81414B1C81C14AB7020000040000100014004D06B54A23269C4A23269C4AB9020000040000100014006EBB0F4B74DC4B4B4EE8874ABF0200000400001000140000D93E4AD6F7EE4AD6F7EE4AC1020000040000100014003B59004B20ADEF4A20ADEF4AC3020000040000100014009E8E7E4ADFCBE14ADFCBE14AC5020000040000100014000656D14A98339A4A98339A4AC702000004000010001400EDDDB34AD0E9074BD0E9074BC902000004000010001400D05C174B44A84A4BD81A874ACE020000040000100014000C52C44AFE4DAF4AFE4D2F4AD1020000040000100014001C97994AB881EA4AB8816A4AD402000004000010001400B003F74A47F7E64A47F7E64AD602000004000010001400908A734A4418F94A4418F94AD8020000040000100014004D61974ACE05D44ACE05D44ADA02000004000010001400317FBE4A2029864A2029864ADC020000040000100014001D67674AD097014BD097014BDE0200000400001000140054AE774A0894AC4A0894AC4AE00200000400001000140082FE9648B6A91E4BB6A99E4AE402000004000010001400D7BBF64AC1EA554B819C8E4AE80200000400001000140057FBE649ED0B2A4BED0BAA4AEB020000040000100014003BBB144BCEE2844ACEE2044AEF020000040000100014005675D44ABE5D6E4ABE5DEE49F2020000040000100014001E29514B84877C4A84877C4AF902000004000010001400F757204B62B3C74A62B3474AFD02000004000010001400E07EC64AAFF0084B3E96364A0203000004000010001400914C5D4A9316004B9316004B040300000400001000140069CBC149AFC1514BAFC1514A0B03000004000010001400F57C854AA703154B3F6CEE4912030000040000100014008384294A7562304B911B0D4A18030000040000100014000886F44A0640CA4A0640CA4A1D030000040000100014005C270C4BD7D9C64AD7D9464A22030000040000100014003BB0C7497FB7414B7FB7414A2903000004000010001400369A3C47A1F3324BA1F3324A6703000004000010001400E8DF014AC842514BC027EF49770300000400001000140064722D493155554B8DAA2A4A7F03000004000010001400F3969D4A3093204B9619564A8703000004000010001400C33AD94969764A4BC162E7499503000004000010001400216F0E4AAF564A4B7F3EE7499F030000040000100014008D36524914515B4A0D369249DC0300000400001000140099278648000000000000803FDD030000040000030000002EED0D0085A500000000E0E0DD6DE941F398709203000000C800000000000080225E623F0000000000000000000000000000000000000090152A79410000000000804B40573C3B342C15B23F8199CC0084A50000000060ACE77AE941974A089203000000C800000000000080225E623F0000000000000000000000000000000000000000000000000000000097135A4136F553B59394983F54978E0082A50000000060ACE77AE941BF120A9603000000C800000000000080225E623F00000000000000000000000000000000000000A0200780C1000000A0200780C126F6F36618DD463F299001CD00000000
GO
/****** Object: Statistic [_WA_Sys_00000003_24927208] Script Date: 1/6/2016 2:27:46 PM ******/
CREATE STATISTICS [_WA_Sys_00000003_24927208] ON dbo.BigFactTable(factCol3) WITH STATS_STREAM = 0x0100000001000000000000000000000010AB360700000000221D000000000000E21C0000000000003D0300003D0000000800170300000000000000000000000007000000D5DB0F0085A50000299001CD000000008B792D0000000000CB2F243C69D50D380000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000C2000000C200000001000000180000000000004190014D4F0000000000000041000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000019000000000000000000000000000000861A0000000000006E1B000000000000761B00000000000010060000000000002B06000000000000460600000000000061060000000000007C060000000000009706000000000000B206000000000000CD06000000000000E80600000000000003070000000000001E07000000000000390700000000000054070000000000006F070000000000008A07000000000000A507000000000000C007000000000000DB07000000000000F60700000000000011080000000000002C08000000000000470800000000000062080000000000007D080000000000009808000000000000B308000000000000CE08000000000000E90800000000000004090000000000001F090000000000003A09000000000000550900000000000070090000000000008B09000000000000A609000000000000C109000000000000DC09000000000000F709000000000000120A0000000000002D0A000000000000480A000000000000630A0000000000007E0A000000000000990A000000000000B40A000000000000CF0A000000000000EA0A000000000000050B000000000000200B0000000000003B0B000000000000560B000000000000710B0000000000008C0B000000000000A70B000000000000C20B000000000000DD0B000000000000F80B000000000000130C0000000000002E0C000000000000490C000000000000640C0000000000007F0C0000000000009A0C000000000000B50C000000000000D00C000000000000EB0C000000000000060D000000000000210D0000000000003C0D000000000000570D000000000000720D0000000000008D0D000000000000A80D000000000000C30D000000000000DE0D000000000000F90D000000000000140E0000000000002F0E0000000000004A0E000000000000650E000000000000800E0000000000009B0E000000000000B60E000000000000D10E000000000000EC0E000000000000070F000000000000220F0000000000003D0F000000000000580F000000000000730F0000000000008E0F000000000000A90F000000000000C40F000000000000DF0F000000000000FA0F000000000000151000000000000030100000000000004B10000000000000661000000000000081100000000000009C10000000000000B710000000000000D210000000000000ED10000000000000081100000000000023110000000000003E11000000000000591100000000000074110000000000008F11000000000000AA11000000000000C511000000000000E011000000000000FB11000000000000161200000000000031120000000000004C12000000000000671200000000000082120000000000009D12000000000000B812000000000000D312000000000000EE12000000000000091300000000000024130000000000003F130000000000005A1300000000000075130000000000009013000000000000AB13000000000000C613000000000000E113000000000000FC13000000000000171400000000000032140000000000004D14000000000000681400000000000083140000000000009E14000000000000B914000000000000D414000000000000EF140000000000000A15000000000000251500000000000040150000000000005B1500000000000076150000000000009115000000000000AC15000000000000C715000000000000E215000000000000FD15000000000000181600000000000033160000000000004E16000000000000691600000000000084160000000000009F16000000000000BA16000000000000D516000000000000F0160000000000000B17000000000000261700000000000041170000000000005C1700000000000077170000000000009217000000000000AD17000000000000C817000000000000E317000000000000FE17000000000000191800000000000034180000000000004F180000000000006A180000000000008518000000000000A018000000000000BB18000000000000D618000000000000F1180000000000000C19000000000000271900000000000042190000000000005D1900000000000078190000000000009319000000000000AE19000000000000C919000000000000E419000000000000FF190000000000001A1A000000000000351A000000000000501A0000000000006B1A000000000000100018002C2B9D48000000000000803F90939F00F1A1000004000010001800D7DDB548A4952F4B2764FE47402A0D01F4A1000004000010001800A7300449A3EB544BBB651048803C2401F8A1000004000010001800B9CBB948BFDD004C8B6602481008140101A2000004000010001800D757A348B341A34BDE96D647A01EB90008A2000004000010001800359ABC48F559174C45F9F1470084E40013A2000004000010001800B845A7481BF8F34BCB43AE4760C8D6001DA2000004000010001800A3AEEF481717784B1A48BC4720D7F80021A20000040000100018005B0FB348A329964B93ECE347305B280127A2000004000010001800063CB948B959404B85F20C47E0EC04012CA2000004000010001800E729BD488498524B74D80B4800D42A0130A20000040000100018002D37C24815F1AE4B34E0B9475036D30036A20000040000100018006B5BBA487DB8574B0F983248C0FEF4003AA2000004000010001800E729BD48199E6A4B95F3DB47D090F8003FA20000040000100018009AB9BD489168664BF7E0CE47300BE20044A2000004000010001800D757A3484D41554BC226D1472022AE0048A2000004000010001800D07ABB48BECB2F4B937413483041D9004BA2000004000010001800B0E2AC481B16774B13DCD44720BDA9004FA2000004000010001800A11CB848630E2F4B26BCA147E0C4E10052A2000004000010001800D96FED485208B24B2BC5C947C078B70059A2000004000010001800E7A3AA4823E88E4B2F86DB47D0C6EF0060A200000400001000180054ACB8485831A74BF82EA947E0749B0067A20000040000100018008A6DB648FFA8D54AF36A8C47A0B9B4006AA20000040000100018006372AD4808FB884BA8269247B02F10016EA2000004000010001800A11CB848963A7C4BA325CE47A0CC190172A2000004000010001800063CB948D702E84A920D04486099C90075A200000400001000180073BEB44870B5864B450AD44700E2FE007AA2000004000010001800A11CB8483D7B0B4BB30BFE47E09CBE007EA200000400001000180054ACB848088E6C4BBDE01948005CC10082A20000040000100018005C1BD8481ACC8D4BED61BA47100FFE0087A200000400001000180073BEB4482868E14B40C2CE47E0FAD8008FA2000004000010001800FFD8BE4818ED034B559CE94720E5CC0092A2000004000010001800ED3D0949AF30944B195FCD47D0F5FC0098A200000400001000180010B7FD48F8DBA44B792CEC47501A2B019DA20000040000100018008A6DB6485E79574B4180CB476099C900A3A20000040000100018004C49BE489516254BE5121D4820E5CC00A7A2000004000010001800521A8148A025B24BC1C7DF474017A800AFA20000040000100018006B5BBA48E5456C4B2415EF471089C000B3A200000400001000180098AD9848D933354B9424DE4770FCBF00B6A20000040000100018004460B148BA40594BC161DF478022D500BAA200000400001000180010B77D480A41104B50C1964780140101BDA2000004000010001800B9CBB948FFBC574B3BE92B483070E600C1A200000400001000180071B20F4986EC874BDF490348F0C9BD00C6A20000040000100018003CFDB64882B2714B4C28074810D90601CBA2000004000010001800A8F99F48B65F5D4BBD7C1148201BC400CEA2000004000010001800CEE88348AA7A0F4B638ACD47C0B59800D1A2000004000010001800330885480E408F4BBA8FB747C01F2E01D4A20000040000100018006504E548F8E65D4B15731B4850CAE400D9A20000040000100018006B5BBA488BFB5B4B3299DE479020C700DCA2000004000010001800B0E2AC489A7A664BDC0B1048A0B2CA00E1A200000400001000180098AD9848F930924B17580248500EB000E6A2000004000010001800330885489E20964BF0B7CC4720958600ECA20000040000100018007A21AF489618974B31FC76474031F700F2A2000004000010001800EF8CB748381D1E4B1B2AED4720C92401F4A2000004000010001800063CB94865E6614B36780A480018F600F8A2000004000010001800E7718D45DF48A84BBDF10648B0F22E01FEA20000040000100018002DB1AF4863556E4BAF740648005CC10004A3000004000010001800C891AE481E320A4B0B170E48C0CFE70006A3000004000010001800063CB9482DFC804BE356EE4700D42A010AA30000040000100018000E9FB348F5A53B4B53680E48E058F3000EA3000004000010001800D07ABB481421424B5F19BA4720D00E0111A30000040000100018006AD5A748E61EA24B7E76DF4770C6C80017A300000400001000180093DCD548E98F574B757AFE47100FFE001AA3000004000010001800C891AE48A3EB544B630FEC475044A7001EA3000004000010001800B9CBB9482C7F0F4B01BAF04720BDA90020A30000040000100018008A6DB6488FA56D4B5D48104800C6560124A300000400001000180043540C49E43F984BD2D8184890BBC2002AA3000004000010001800063CB948DDD3454BE9F1974760FECD002DA300000400001000180062668848F138684B52CEF84750570C0130A30000040000100018003CFD36498F0E5D4B610BC147B05E1D0134A3000004000010001800820ABC48D5B25F4B07CF0B48D09ECC0038A300000400001000180054ACB8487CFA7A4B2C36E64740DAC6003CA300000400001000180052A013497DF9424B39230048F07034013FA3000004000010001800359ABC48756F6D4B02460048A0311E0144A3000004000010001800E729BD48F6819B4B940CBB47406ED8004AA3000004000010001800A11CB8485876744B845EF74740F415014FA3000004000010001800A8F99F4871058F4BB6B31648D0D9540154A3000004000010001800B9CBB94800788A4BDD6ED347201BC40058A3000004000010001800C817C148FA95544B7BDEE147A010E5005CA3000004000010001800AF1911493D998E4B3C8217482064200160A3000004000010001800160E53484CF6684B32BCE247206B0A0165A30000040000100018006B5BBA485C5D0F4C0F010B48F0AD15016FA3000004000010001800914A9E4864EA0E4B2281294770E0170173A3000004000010001800EF8CB7489CC06D4B14DBA347B086400176A300000400001000180005309448DADD8F4BA859164840DAC6007CA3000004000010001800D79A2C4982908C4BA9CE084830EF390181A30000040000100018006372AD484231904BBADCDA47E0FAD80087A30000040000100018004B3D1949BF930D4B29B90448C092060189A30000040000100018007B2DD448F9D3954BF8720848E08EEA008EA3000004000010001800B9CBB9487B53954B3250E147C02D020193A3000004000010001800DEBA9D48C2909A4B546DDD4750E4330199A30000040000100018009A3FD048CDE0924B8309F847103E0B019EA3000004000010001800A97FB24867621B4B253B9747B044CE00A1A3000004000010001800B0E2AC4856CD9C4BF297EC47909F1A01A7A30000040000100018007BA7C148CEC7AB4BACD5004810E0F000AEA30000040000100018006CE1CC48BD55954BBD16D047A017CF00B4A3000004000010001800E0C6C2481DD31B4B51D21848C0A7C400B7A30000040000100018001DEBBA48C4E0A24B5E39F747C0BA2901BDA3000004000010001800F6699F48B79F104BBE4CB1474096FB00C0A300000400001000180073BEB44811C5C64B536EFD47F0FFB400C8A3000004000010001800CFF4A8487179374BB1FF94470018F600CBA3000004000010001800820ABC488011A94B2DF7C34760C8D600D1A30000040000100018003CFDB648EC1F934B54DA9047C0E93601D5A3000004000010001800EF8CB748B6E1B34B99E7EE4700DB1401DAA3000004000010001800E729BD482690AE4BC767174870C6C800E1A3000004000010001800E9BBF4489C30B44B2334C5477061C400E6A30000040000100018007344C7486F56184BA107054830621201E8A30000040000100018001502AE4857A0B34BB00FBE479027B100EEA3000004000010001800A8730D482205584B411DDE4760FECD00F2A30000040000100018004AB706483728574B25661848E0BDF700F6A30000040000100018002EBDD448E009AF4B6212A947707B1301FAA3000004000010001800A97F32488EDC574B4B76C0470018F600FFA3000004000010001800C70B9C4815EAD74B4F24B84790041F0106A40000040000100018003CFDB648FBB3574BD0C9DD4750424E0109A4000004000010001800FDCC1948BE35574B2267214810A30F010DA4000004000010001800986A0F495D0DBE4B88E10C4860A0B30015A40000040000100018003CFDB648C647574BD09BE847D0E7280118A4000004000010001800C70B9C48F297D64B9D9BF747A0E8C1001FA4000004000010001800160E5348260E584B3E849C4780062D0122A4000004000010001800C891AE4808CF574B62025D4740154F0125A40000040000100018001502AE48260E584BA53AB5473033050129A4000004000010001800053094485E5B544B4D901148F0D538012BA40000040000100018006A4F954893E5574B33FCF747A0E1D70030A4000004000010001800B05C9A48AC11574BA5A91648A0DAED0032A4000004000010001800E1587A486B8A564BB2A3FF47600A490137A400000400001000180080788447DDA65C4BC41014480092B8003BA40000040000100018006A4F954878A5564BD6BE19483069FC003FA40000040000100018004AB78648383C594B532BCA47A010E50043A40000040000100018001CDF9548BF675C4BE0AEF847605CE80047A40000040000100018005B0FB3480E32614B2F16CD47600A49014AA40000040000100018006AD5A748F11A654B1194034850F207014EA40000040000100018005326A6485036534B1B34A4470092B80053A4000004000010001800CFF4A848B523574B47B61648B0A9D20056A40000040000100018004DCFD048567B594BBCFCEA47F0C2D3005AA40000040000100018006B5BBA4853B8604BA18A2A48305B28015DA40000040000100018004BC3AB4834475B4B11F11748101DD20061A40000040000100018003CFDB648A01E5B4B3B8C0248D02BF40065A400000400001000180073BEB448CE87784B5C7CFB47705ADA0069A40000040000100018003414AA482A0D574BDF8BFB47E01B12016DA4000004000010001800C817C148DBE9824BE07D2048A07CD30072A40000040000100018002DB1AF48D33A534BA1AC9B472072F40076A4000004000010001800DF40B048C9AB614BC6C802480032450179A40000040000100018004DCFD04898C16E4BCFF4104880E5F3007DA40000040000100018008A6DB648D5945C4B6654CA476026F10080A4000004000010001800456CD6487EE05B4B0D9BEF473098090184A4000004000010001800B9CBB948F633854BC68FDC47A0D3030188A4000004000010001800A11CB84802F4784BFAF9FF47A0311E018CA40000040000100018005A038E48BF9D8E4BEDE9204810585A0190A4000004000010001800EF8CB748C655854B95DBF24730C7160194A4000004000010001800C817C148A5E28D4BFD121348001FE00099A40000040000100018008AF3C848F0F2604B340EF347906923019EA40000040000100018000E9FB346391F904BA36A9A4700BADB00A3A400000400001000180073BEB44890B58D4BCEB4E447F08CDC00A7A40000040000100018003BF1914887AD8E4B6DBBC547B0CA0B01ABA40000040000100018004AB78646C83E904BACCF1048E0AF2301AEA4000004000010001800D20CF3485ADA7E4B5089B247E0142801B2A4000004000010001800EF8CB748A812584BE1F6A647F085F200B7A4000004000010001800348E1748B71A904B5DC9D04730B25801BCA4000004000010001800A3AEEF4878AF574B9D2208484075C200C0A4000004000010001800063CB9485516974B87A40D4820205501C6A4000004000010001800B3FA76480ED9914BA910DC4790E3E500CBA40000040000100018009AB9BD482721A24BE09A1248D0240A01D1A4000004000010001800EF8CB7480B7D964B2D11E34720B6BF00D6A40000040000100018001502AE4828768F4B71C9A447D0B13101DAA4000004000010001800DF40B0481A518E4B698FF347A07CD300DFA4000004000010001800EF8CB74813E2914B6607D7471082D600E4A4000004000010001800A8738D479827B44B685200488087D900EAA4000004000010001800F6EFB148D3AA994BB0EEEE47F035AC00F0A4000004000010001800C02EB44859AE984BC916094810CB3201F5A4000004000010001800359ABC47D9A4B34B2FB2184870160F01FCA4000004000010001800D863C848A8E8224C232C1C4870A3360107A5000004000010001800C02EB44858D98F4B840CBF4740ED2B010BA500000400001000180005B6A6489E0C944BD762AC47C063F90011A50000040000100018002D37C248C0BB914B94EF014860FECD0015A50000040000100018001C5983483DEFB34BAF330A48100814011AA50000040000100018007D39F9481371934BB3B91148A0D303011FA5000004000010001800E7A3AA47ADCFB34B45C12348A083BD0027A50000040000100018006AD5A748C377B34BD5330848D0B81B012DA5000004000010001800FDCC99481E21B24BFA0A044820ECB60035A50000040000100018006B5BBA48A215944BBBC29A47001FE00039A5000004000010001800063CB948B1059D4BA339CA47C03BD60040A5000004000010001800521A81481EA6B24B53410F4870FCBF0047A50000040000100018005C95C5472BCBB34B1D4DF1477003AA004DA5000004000010001800A97FB248B65CB34BE0980A48A0ABE00052A5000004000010001800254EB548111AB44BF1BE0D48509BD70059A5000004000010001800E8AF4F4804FFB34B575AE447B09414015EA5000004000010001800F6EFB148A5678E4BE7626447B05E1D0163A500000400001000180054ACB8484173B34BCFD9D3473004F8006AA5000004000010001800EF8CB748E198B04B10209F47C00CC90070A50000040000100018004460B14834C9B14BAD6EE04760E90F0175A50000040000100018007A21AF474E13B44BF2FEDF47C0CFE7007CA5000004000010001800914A9E48EE42B24B879119489098300182A5000004000010001800E9BB74485BCFC5487F46834750715B0182A500000400001000180053262647EC09E3438329E347A0B75B0182A5000004000003000000BDE60D0085A500000000E0E0DD6DE941F398709203000000BF0000000000002085C4013F06C79F815F247A41000000000000000000000090152A79410000000000804B40F8B6EB1817F6C03F3C544B0084A50000000060ACE77AE941974A089203000000C2000000000000804BB9013F0000000000000000000000000000000000000000000000000000000097135A4181C4A69AE67EB43F26908E0082A50000000060ACE77AE941BF120A9603000000C2000000000000C0E6BD013F00000000000000000000000000000000000000A0200780C1000000A0200780C172579B48D2BEB03F299001CD00000000
GO
/****** Object: Statistic [_WA_Sys_00000005_24927208] Script Date: 1/6/2016 2:27:46 PM ******/
CREATE STATISTICS [_WA_Sys_00000005_24927208] ON dbo.BigFactTable(factCol5) WITH STATS_STREAM = 0x0100000001000000000000000000000029B2731300000000AF0D0000000000006F0D0000000000007F0300007F00000008001300000000000000000000000000070000006FD80F0085A50000299001CD000000008B792D000000000083897F3FFCB87E300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000510000005100000001000000180000000000004190014D4F0000000000000041000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000019000000000000000000000000000000130B000000000000FB0B000000000000030C0000000000008802000000000000A302000000000000BE02000000000000D902000000000000F4020000000000000F030000000000002A03000000000000450300000000000060030000000000007B030000000000009603000000000000B103000000000000CC03000000000000E70300000000000002040000000000001D04000000000000380400000000000053040000000000006E040000000000008904000000000000A404000000000000BF04000000000000DA04000000000000F50400000000000010050000000000002B05000000000000460500000000000061050000000000007C050000000000009705000000000000B205000000000000CD05000000000000E80500000000000003060000000000001E06000000000000390600000000000054060000000000006F060000000000008A06000000000000A506000000000000C006000000000000DB06000000000000F60600000000000011070000000000002C07000000000000470700000000000062070000000000007D070000000000009807000000000000B307000000000000CE07000000000000E90700000000000004080000000000001F080000000000003A08000000000000550800000000000070080000000000008B08000000000000A608000000000000C108000000000000DC08000000000000F70800000000000012090000000000002D09000000000000480900000000000063090000000000007E090000000000009909000000000000B409000000000000CF09000000000000EA09000000000000050A000000000000200A0000000000003B0A000000000000560A000000000000710A0000000000008C0A000000000000A70A000000000000C20A000000000000DD0A000000000000F80A000000000000100018000000803F000000000000803FDE0000000000000004000010001800D4D6E444A3DEB54C05B66140E68D9C010000000004000010001800D4D6E44470E6524B0745EE3F2D2313020000000004000010001800D4D6E4445EFD3C4D9732EE3F656E691600000000040000100018000000803FC062D84B13C3ED3FFF8629370000000004000010001800D4D6E44411D2AF4BD72A6040D6EB8D370000000004000010001800D1B84B4569A2714CB0174B40EF80BE380000000004000010001800D4D6E44467D8474C0C455F4037A5A3390000000004000010001800D4D6E44473382A4B9B334440A52BDB3900000000040000100018000000803F7C829E4BB2F05A403BD7373A0000000004000010001800D4D6E444C55E2A4C4ED57240DD72EB3A0000000004000010001800D1B84B457F29734BE14E52405972353B0000000004000010001800D4D6E444C8BFED4CC6B25840222F673D00000000040000100018000000803F571F8C4C124C6D404C84953E00000000040000100018000000803F4B6C6A4C93168140B4F67D3F00000000040000100018000000803F0CA47C4C137E5140E5B0B24000000000040000100018000000803F9573FC4B4AB56B408CC83B410000000004000010001800D4D6E444B830294C4B4C47401C1C154200000000040000100018000000803FC062D84B98C25B40F72493420000000004000010001800D4D6E4448587F04B43B175405F7410430000000004000010001800D4D6E444F978BA4C645A5A4045B3C5440000000004000010001800D4D6E44487117C4C66CA6F40E0CED24500000000040000100018000000803FE063584CABCF3E403820F54600000000040000100018000000803F1541904BCAD44D4044D54E4700000000040000100018000000803FCA0F474CC8887E40800A17480000000004000010001800D4D6E444293B604B9848574073B359480000000004000010001800D4D6E4442646704B9D423C402362AB4800000000040000100018000000803FC062D84B36CF5D40E640284900000000040000100018000000803F1541904B2DC032401F8D8F4900000000040000100018000000803FEBD0E84B58EA5C405572164A00000000040000100018000000803FC062D84B9A1647405491A14A00000000040000100018000000803F0B53344C59F576407D7E5C4B00000000040000100018000000803F1541904B3EAC3E403E55BD4B0000000004000010001800D4D6E4446F58124CDDF35440F2426D4C0000000004000010001800D4D6E444AE82AB4CEC074340D6832F4E00000000040000100018000000803FC062D84B623C3340990BCA4E00000000040000100018000000803F52AB474C51345740B390B74F00000000040000100018000000803F03EADF4C29DF6E40468197510000000004000010001800D4D6E444C7F5A34BD1C35340129CFA5100000000040000100018000000803F7F60584BB837764064DA325200000000040000100018000000803F7F60584BB45B8B40D889645200000000040000100018000000803F7F60584BF8F87B40517F9B5200000000040000100018000000803FCF84BA4B05973D40846C195300000000040000100018000000803FCE0F4E4C6A715F409382055400000000040000100018000000803F304BA24C1C037340F5715B5500000000040000100018000000803FC062D84B6B558040535BC7550000000004000010001800D4D6E444F696EB4B92566D40ED69465600000000040000100018000000803F7F60584BA7F2504075B0885600000000040000100018000000803F3542104C02563740B81F525700000000040000100018000000803F1541904B55605340517AA95700000000040000100018000000803F3542104CC2D26A40F4BE465800000000040000100018000000803F1541904B05873540AC76AC580000000004000010001800D4D6E44451AE8D4CD9185440D47A025A0000000004000010001800D4D6E4443CD1664BA76141408ADE4E5A0000000004000010001800D4D6E44487768D4B3F08304090BBB55A0000000004000010001800D4D6E4449DC5DA4B2C9D504021F73B5B00000000040000100018000000803FC062D84BA9DF6F408F6EAF5B00000000040000100018000000803F6B8DD94CD4FC5A408C13AC5D0000000004000010001800D4D6E4441AB6034C67C73640198D645E0000000004000010001800D4D6E444FD52DF4BFEA47140CAD8DA5E0000000004000010001800D4D6E444E18EDE4B02FB8240FE97475F0000000004000010001800D4D6E444A25B4C4C6C1344402F68526000000000040000100018000000803FE32D024CE28E374041F6076100000000040000100018000000803F4675FC4CC26E59402F705A6300000000040000100018000000803F3A7AAB4C316C4940EB510E650000000004000010001800D4D6E4445FE6964CD63F5D40A9856B6600000000040000100018000000803FEA51B44B9CC673400534CA6600000000040000100018000000803FEA51B44BDFA7494013A93C6700000000040000100018000000803F0B53344C1A5A314076F340680000000004000010001800D4D6E444DD76924B1E7124401DF5B2680000000004000010001800D4D6E444D2BA974BA3EC8C405DDDF76800000000040000100018000000803FEA51B44BEBF21F40B32A886900000000040000100018000000803F4350224C72394340A9025D6A00000000040000100018000000803FEA51B44B9B0F6D40955FBE6A00000000040000100018000000803FEA51B44B92173740776F3C6B00000000040000100018000000803FEA51B44B8FE26940D91E9F6B00000000040000100018000000803FEA51B44B00B2514085300D6C0000000004000010001800D4D6E44449CBA84B8E7039402DB3816C00000000040000100018000000803F02378C4C1AA961402BD5BF6D00000000040000100018000000803FBE98AE4B09BC4E403BEF2B6E00000000040000100018000000803F594A9044492EEE3F76F22B6E00000000040000030000006FE30D0085A500000000E0E0DD6DE941F3987092030000004D000000000000C09323103E102BD94A11377841000000000000000000000090152A79410000000000804B40EA1E6604F021B23F99764B0084A50000000060ACE77AE941974A0892030000005000000000000000F9FB0F3E0000000000000000000000000000000000000000000000000000000097135A41F8DACEE85451AF3FC68C8E0082A50000000060ACE77AE941BF120A960300000050000000000000207CED0F3E00000000000000000000000000000000000000A0200780C1000000A0200780C134DF0C96F318A43F299001CD00000000
GO
/****** Object: Statistic PK_BigFactTable Script Date: 1/6/2016 2:27:46 PM ******/
UPDATE STATISTICS dbo.BigFactTable(PK_BigFactTable) WITH STATS_STREAM = 0x0100000005000000000000000000000047FF808D000000006006000000000000C005000000000000380300003800000004000A0000000000000000004F0000003D0300003D000000080017030000000000000000000000007F0300007F0000000800130000000000000000000000000038038B273800000004000A000000000000000000000000007F0300007F000000080013000000000000000000000000000700000014D30F0085A50000299001CD000000008B792D0000000000000000002549123D53770B38BC16D6349686B734D124B434000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001C0000001C00000005000000140000000000004290014D4F000000000000804000000041000000410000804000000041000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001900000000000000000000000000000064030000000000004C040000000000005404000000000000E000000000000000F7000000000000000E0100000000000025010000000000003C0100000000000053010000000000006A0100000000000081010000000000009801000000000000AF01000000000000C601000000000000DD01000000000000F4010000000000000B0200000000000022020000000000003902000000000000500200000000000067020000000000007E020000000000009502000000000000AC02000000000000C302000000000000DA02000000000000F10200000000000008030000000000001F0300000000000036030000000000004D030000000000001000140046B61B4D000000000000803FD8363101040000100014002C24894C000000000000803FD9363101040000100014009B1CF04D000000000000803FDA36310104000010001400401FE54C000000000000803FDB36310104000010001400DB0FA34D000000000000803FDC363101040000100014002392E24C000000000000803FDD36310104000010001400AB52954B000000000000803FDE3631010400001000140069EDCA4B000000000000803FDF36310104000010001400C719E44C000000000000803FE0363101040000100014000F64504C000000000000803FE13631010400001000140070E8884C000000000000803FE236310104000010001400570C8A4C000000000000803FE336310104000010001400168FC14B000000000000803FE436310104000010001400614B264C000000000000803FE53631010400001000140047999A4D000000000000803FE636310104000010001400012FBB4D000000000000803FE736310104000010001400CC843B4C000000000000803FE836310104000010001400C1CB1249000000000000803FE936310104000010001400616BCA4D000000000000803FEA363101040000100014004EA90F4C000000000000803FEB36310104000010001400AD80A44B000000000000803FEC36310104000010001400EE58D64C000000000000803FED36310104000010001400D08CC84B000000000000803FEE363101040000100014002F1F434D000000000000803FEF36310104000010001400BB34124C000000000000803FF036310104000010001400022FEC4A000000000000803FF136310104000010001400C3508C4A000000000000803FF2363101040000100014006EB7204C000000000000803FF33631010400000300000017DE0D0085A500000000E0E0DD6DE941F3987092030000001C000000000000A02449A23F0000000000000000000000000000000000000090152A79410000000000804B402F00609D21FD753F7C924B0084A50000000060ACE77AE941974A0892030000001C000000000000A02449A23F0000000000000000000000000000000000000000000000000000000097135A41BF7AADD71A326D3F2C878E0082A50000000060ACE77AE941BF120A96030000001C000000000000A02449A23F00000000000000000000000000000000000000A0200780C1000000A0200780C1B9CE3CB31B6F683F299001CD00000000, ROWCOUNT = 3439431721, PAGECOUNT = 11089388
GO
/***************************************************************************
TEST CASE
***************************************************************************/
/***************************************************************************
TEST CASE
View the estimated plan for the query using both the SQL 2014 and SQL 2012 Cardinality Estimators
SQL Server 2012 is able to produce a very good estimate, leading to a high-quality query plan
that asks for an appropriately large memory grant and avoids spills (to the extent the server
is able to grant enough memory for the fairly large hash aggregate.)
SQL Server 2014 produces an estimate that is far too low, reserves too little memory,
spills to tempdb, and produces a dramatically slower query
NOTE: This is a very simplified version; the problem is often worse if this table is then
joined with other tables as part of a more complex query
***************************************************************************/
-- Actual rows: 1,011,719,166
-- SQL 2012 estimated rows: 1,079,130,000 (106% of actual)
-- SQL 2014 estimated rows: 2,980,240 (0.29% of actual)
SELECT COUNT(DISTINCT factCol5)
FROM BigFactTable
OPTION (RECOMPILE, QUERYTRACEON 9481) -- Include this line to use SQL 2012 CE
OPTION (RECOMPILE, QUERYTRACEON 4199) -- Note that TF 4199 does not fix the situation
-- Turning on all kinds of optimizer information also did not provide any insight
-- (Reference: http://sqlblog.com/blogs/paul_white/archive/2013/06/11/hello-operator-my-switch-is-bored.aspx)
OPTION (RECOMPILE, QUERYTRACEON 3604, QUERYTRACEON 8606, QUERYTRACEON 8607, QUERYTRACEON 8608, QUERYTRACEON 8612)
GO
-- Look up the stats for this table
SELECT * FROM sys.stats WHERE object_id = OBJECT_ID('BigFactTable')
-- _WA_Sys_00000005_24927208 corresponds to factCol5
GO
-- Stats for the factCol5 column show that there are ~1 billion distinct values
-- This is a good estimate, and it appears to be what the SQL 2012 CE uses
DBCC SHOW_STATISTICS (BigFactTable, _WA_Sys_00000005_24927208)
--All density Average Length Columns
--9.266754E-10 8 factCol5
SELECT 1 / 9.266754E-10
-- 1079126520.46229
GO
/***************************************************************************
CLEANUP
***************************************************************************/
USE master
GO
DROP DATABASE tempStats
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment