Monday, December 12, 2016

SQL Server trace flag 2388 shows negative numbers of deletes/inserts for a filtered index since previous update?

***Update 2016 December 14*** 
I fugured out part of the puzzle - why index stats can somtimes show 'unknown' leading column type (in show_stats output with trace flag 2388) when after 3 updates 'ascending' or 'stationary' would be expected.
SQL Server Ascending Key Stuff: UPDATE STATS when nothing has changed can lead to a surprising brand...
http://sql-sasquatch.blogspot.com/2016/12/sql-server-ascending-key-stuff-change.html

But there's still 2 more parts: 
-How/why are there negative numbers for inserts/deletes since last update?
-Still have to show the unexpected added work against a filtered index when trace flag 2390 or 4139 results in a quickstat update.

****** 

Still trying to figure out my long query compiles.  I first wrote about that problem here:

Three Minutes to compile a very simple #SQLServer Query plan?!?
http://sql-sasquatch.blogspot.com/2016/11/three-minutes-to-compile-very-simple.html

Some of the contributing factors: legacy cardinality estimator, trace flag 4139, rows inserted into table since most recent stats update.

I've copied stats from a troubled system, and copied them to a dev instance for investigation.

I created an empty table, and the necessary indexes as below.

 CREATE TABLE [dbo].[T4139_Fact](  
   [Bigint_Primary_Key]    Bigint         NOT NULL,  
   [Numeric_18_0__1]       Numeric(18, 0) NULL,  
   [Bigint_Alternate_Key]  Bigint         NOT NULL,  
   [Nvarchar_50__1]        Nvarchar(50)   NOT NULL,  
   [Bit__1]                Bit            NOT NULL,  
   [Nvarchar_data__2]      Nvarchar(50)   NOT NULL,  
   [Numeric_18_0__2]       Numeric(18, 0) NULL,  
   [Datetime__1]           Datetime       NULL,  
   [Datetime__2]           Datetime       NULL  
   CONSTRAINT [PK__T4139_Fact] PRIMARY KEY CLUSTERED  
 (  [Bigint_Primary_Key] ASC)  
 WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85, DATA_COMPRESSION = PAGE) ON [PRIMARY])   
 ON [PRIMARY]  
   
 CREATE NONCLUSTERED INDEX [nci_filtered_Nvarchar_50__1] ON [dbo].[T4139_Fact]  
 (  [Numeric_18_0__1] ASC,  
    [Nvarchar_50__1]  ASC,  
    [Datetime__1]     ASC,  
    [Datetime__2]     ASC)  
 INCLUDE (   [Nvarchar_data__2],  
        [Numeric_18_0__2])  
 WHERE ([Bit__1]=(1) AND [Nvarchar_50__1] IS NOT NULL)  
   
 CREATE NONCLUSTERED INDEX [Nci_Bit__1] ON [dbo].[T4139_Fact]  
 (  [Bigint_Alternate_Key] ASC,  
   [Bit__1]        ASC)   
 WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85, DATA_COMPRESSION = PAGE) ON [PRIMARY]  


I used DBCC SHOW_STATISTICS... WITH STATS_STREAM to capture the stats stream from the troubled database.

Then I transferred them to my empty table.

 UPDATE STATISTICS [dbo].[T4139_Fact](Nci_Bit__1) WITH Stats_Stream = 0x01000000030000000000000000000000E67D005900000000FD170000000000008D170000000000007F0374007F000000080013000000000000000000420043006803200068000000010001000000000000000000650072007F036F007F0000000800130000000000000000006500730007000000CFC83C01D9A6000055EE732300000000E2E21100000000008FC47F3FDBAC44310D20E7304311E7300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000009B0000009B000000030000001800000000008841B9CF0D4E00000000000000410000803F0000004100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000019000000000000000000000000000000311500000000000019160000000000002116000000000000D804000000000000F3040000000000000E05000000000000290500000000000044050000000000005F050000000000007A050000000000009505000000000000B005000000000000CB05000000000000E60500000000000001060000000000001C06000000000000370600000000000052060000000000006D060000000000008806000000000000A306000000000000BE06000000000000D906000000000000F4060000000000000F070000000000002A07000000000000450700000000000060070000000000007B070000000000009607000000000000B107000000000000CC07000000000000E70700000000000002080000000000001D08000000000000380800000000000053080000000000006E080000000000008908000000000000A408000000000000BF08000000000000DA08000000000000F50800000000000010090000000000002B09000000000000460900000000000061090000000000007C090000000000009709000000000000B209000000000000CD09000000000000E809000000000000030A0000000000001E0A000000000000390A000000000000540A0000000000006F0A0000000000008A0A000000000000A50A000000000000C00A000000000000DB0A000000000000F60A000000000000110B0000000000002C0B000000000000470B000000000000620B0000000000007D0B000000000000980B000000000000B30B000000000000CE0B000000000000E90B000000000000040C0000000000001F0C0000000000003A0C000000000000550C000000000000700C0000000000008B0C000000000000A60C000000000000C10C000000000000DC0C000000000000F70C000000000000120D0000000000002D0D000000000000480D000000000000630D0000000000007E0D000000000000990D000000000000B40D000000000000CF0D000000000000EA0D000000000000050E000000000000200E0000000000003B0E000000000000560E000000000000710E0000000000008C0E000000000000A70E000000000000C20E000000000000DD0E000000000000F80E000000000000130F0000000000002E0F000000000000490F000000000000640F0000000000007F0F0000000000009A0F000000000000B50F000000000000D00F000000000000EB0F000000000000061000000000000021100000000000003C10000000000000571000000000000072100000000000008D10000000000000A810000000000000C310000000000000DE10000000000000F91000000000000014110000000000002F110000000000004A11000000000000651100000000000080110000000000009B11000000000000B611000000000000D111000000000000EC11000000000000071200000000000022120000000000003D12000000000000581200000000000073120000000000008E12000000000000A912000000000000C412000000000000DF12000000000000FA12000000000000151300000000000030130000000000004B13000000000000661300000000000081130000000000009C13000000000000B713000000000000D213000000000000ED13000000000000081400000000000023140000000000003E14000000000000591400000000000074140000000000008F14000000000000AA14000000000000C514000000000000E014000000000000FB140000000000001615000000000000100018000000803F000000000000803F3E65000000000000040000100018000000803F2A01054ABE15B93F2164170000000000040000100018000000803F54B67D4A3976F53F7B77380000000000040000100018000000803F54B67D4A7865D43F03B15E0000000000040000100018000000803F54B67D4A47DE1440B8F5790000000000040000100018000000803F8202DE4AA505FA3F13CAB20000000000040000100018001C4A49441C73D14977D7B73FA004C50000000000040000100018000000803FC3463E4A55D92540EB5FD70000000000040000100018000000803F872FFD492DE3014007F8E60000000000040000100018000000803F54B67D4A7F5D21401D20000100000000040000100018000000803FC3463E4A612A3340211E110100000000040000100018001C4A49447FAF794A642A7B40FF05210100000000040000100018000000803FE5648A49F8BF05401D4D290100000000040000100018000000803FC3463E4AFE391F40DB6B3C0100000000040000100018000000803FC3463E4ADCAE484075974B0100000000040000100018000000803FC3463E4A293C2E4092105D0100000000040000100018000000803F8202DE4A72081F4098BC890100000000040000100018000000803F66AEFD49F5693A409E9F940100000000040000100018000000803F8202DE4AD9082440F2EEBF0100000000040000100018001C4A4944771D034A9F1467400503C90100000000040000100018000000803F54B67D4A26BCF63FAEEAE90100000000040000100018000000803F66AEFD4997183840CBF0F40100000000040000100018000000803F9B0D834A178722405DBE0E0200000000040000100018000000803F66AEFD4971D55540023C180200000000040000100018001C4A49441EAD6C4AD7BC2140D4A52F0200000000040000100018001C4A494437AEE8499C100C400AF03C0200000000040000100018000000803F54B67D4A42413F40A829520200000000040000100018000000803F2595D749FD0E68404198590200000000040000100018000000803F66AEFD490D242C405D62650200000000040000100018000000803F54B67D4A44D1F53F7769860200000000040000100018000000803FED4CF74943E120409AB5920200000000040000100018000000803FC3463E4A82C3FC3F69CCAA0200000000040000100018000000803F66AEFD4910872C40C18FB60200000000040000100018000000803F54B67D4AE0A03D40FAF7CB0200000000040000100018000000803F54B67D4A12017440F29ADC0200000000040000100018000000803F66AEFD49D4784640A5D4E60200000000040000100018000000803F33238B4A3DE131406DDCFF0200000000040000100018000000803F66AEFD49CBCE1A4075F80C0300000000040000100018000000803FC3463E4AD1BF534019591B0300000000040000100018000000803FC3463E4AB9CF33407D472C0300000000040000100018001C4A49447E09534B638922406D607F0300000000040000100018000000803F66AEFD49A2D9E93FC8BB900300000000040000100018000000803FBA4ABE4A06BF22405426B60300000000040000100018000000803FC3463E4AB1C22F409E78C70300000000040000100018000000803F66AEFD49949C0F404A9AD50300000000040000100018000000803FB93B5F4AD783864009E1E20300000000040000100018001C4A49446BAD0D4A56333940751EEF0300000000040000100018000000803FB64C3E4B27ABFA3FE44A500400000000040000100018000000803F6D8D0E4BE5770E409154900400000000040000100018001C4A4944892C2A4A302301402C6AA50400000000040000100018000000803F66AEFD49449114402B13B30400000000040000100018000000803FC3463E4A035124404A9AC50400000000040000100018000000803F66AEFD49D3BA354026C5D00400000000040000100018000000803F66AEFD499872F13FB094E10400000000040000100018000000803F54B67D4A39FE6140168BF30400000000040000100018001C4A494459D69E49C3722C4072E9FA0400000000040000100018000000803F4BBAFD4A3F413A401781260500000000040000100018000000803F54B67D4AC9310B40F2AA430500000000040000100018000000803F66AEFD49A3D54D4003874D0500000000040000100018000000803F8202DE4AB14925404582780500000000040000100018000000803F66AEFD492D7F4540E5C8820500000000040000100018000000803F4704A54AFA602A4015C7A10500000000040000100018001C4A494426EBCD498BAF13409EEEAC0500000000040000100018000000803FC3463E4A3F794140EFAABC0500000000040000100018001C4A49440112F54903190F405C5ECA0500000000040000100018000000803F66AEFD4914B93640AE79D50500000000040000100018000000803F54B67D4A7AA111409359F10500000000040000100018000000803F66AEFD497F428040EB42F90500000000040000100018000000803F66AEFD49CB08014049FD080600000000040000100018000000803F66AEFD4914DC32400556140600000000040000100018000000803F4BBAFD4AE45A1340AA6F4B0600000000040000100018000000803F54B67D4A4DB4004008FA6A0600000000040000100018000000803F54B67D4AA6D71F40795F840600000000040000100018000000803FC3463E4A873537407BFD940600000000040000100018001C4A49448EF6D649F0F726402D4A9F0600000000040000100018000000803F66AEFD49A5021140F448AD0600000000040000100018000000803F49394B4AF53BF13F2C3EC80600000000040000100018001C4A4944A597F249390217403F18D50600000000040000100018000000803FC3463E4AD24C8F409FB7DF0600000000040000100018000000803FBC84D94AF93A2A40469B080700000000040000100018001C4A494424238449EBAE874084800C0700000000040000100018000000803F54B67D4AB1A01B400796260700000000040000100018000000803F1F12014A83B80940C594350700000000040000100018000000803FC3463E4AC3C24840DEBE440700000000040000100018000000803F4A69FC4AB9EC274072D8740700000000040000100018000000803F96F2954A787E114035D3950700000000040000100018000000803F54B67D4A43D750404643A90700000000040000100018000000803FC3463E4AB03E0440AE48C00700000000040000100018000000803F66AEFD494756F03F1B2CD10700000000040000100018001C4A4944CD47B34963C83540EB0FD90700000000040000100018001C4A4944F297134A38084C40E7A2E40700000000040000100018000000803FC3463E4AEE9C694013ABF10700000000040000100018000000803F54B67D4A06042640C21E0A0800000000040000100018000000803F54B67D4A033005405599280800000000040000100018001C4A49443490E94ACA2A0740C7E45F0800000000040000100018000000803F66AEFD49ACA52040D2866C0800000000040000100018001C4A49443DB2AE4909CB4640938E730800000000040000100018001C4A49444850724AFC9634408D06890800000000040000100018000000803F54B67D4A89C76640949D9A0800000000040000100018000000803F66AEFD49F803803F2423B10B00000000040000100018000000803FA00F684A92E09B3FECC6E00B00000000040000100018000000803F66AEFD496A14E23FFFBAF20B00000000040000100018000000803FF2929E4A810F8C3FAA303B0C00000000040000100018000000803FC3463E4A312BA73F0C9D5F0C00000000040000100018000000803FC3463E4AA9D4953F6C40880C00000000040000100018000000803F66AEFD4965A2BC3FDAC49D0C00000000040000100018000000803FC3463E4A46768F3F1036C80C00000000040000100018000000803FBA4ABE4ABBB5C03F7E68070D00000000040000100018000000803FC3463E4A43FD7F3F91BCA70E00000000040000100018000000803F09B90E4BC61BAF3F3F10100F00000000040000100018000000803FC3463E4A43FD7F3F6CEE961000000000040000100018001C4A4944B6D1124A11578E3F35F0B71000000000040000100018000000803F54B67D4A66DEA93F9ABBE71000000000040000100018000000803F66AEFD498724C03F74DBFC1000000000040000100018000000803FC3463E4A43FD7F3FFDD49E1200000000040000100018000000803FC3463E4A05E5B53F7C4EC01200000000040000100018000000803FC3463E4A626D903F1477EA1200000000040000100018000000803FC3463E4A43FD7F3FA33BD91300000000040000100018000000803FC3463E4A27898F3F44A7031400000000040000100018000000803FC3463E4A43FD7F3F31688B1400000000040000100018000000803FC3463E4A43FD7F3FED7DC11400000000040000100018000000803FBA4ABE4A57C6953F23CE121500000000040000100018000000803FC3463E4A43FD7F3F38BF461500000000040000100018000000803F09B90E4B09BFDE3F0CC3981500000000040000100018000000803F54B67D4AF687FE3FB2A8B81500000000040000100018000000803FC3463E4AB4FFC83FB1F3D61500000000040000100018000000803F8202DE4A7FFB973FC970341600000000040000100018001C4A49441B1D5B4A6E52E53F1F04531600000000040000100018000000803FBA4ABE4A4E27B33FC1FE961600000000040000100018000000803FBA4ABE4A79499B3F0D6CE51600000000040000100018000000803F54B67D4A95D6CF3F317C0C1700000000040000100018000000803F54B67D4A232D813FEA554B1700000000040000100018000000803F54B67D4AECFF7F3F358B3D1800000000040000100018000000803F54B67D4AE448913FFC6C751800000000040000100018000000803F54B67D4AECFF7F3F82B4621A00000000040000100018000000803F54B67D4AA2E1803F0DB3A11A00000000040000100018000000803F54B67D4AECFF7F3F22DAE21C00000000040000100018000000803F54B67D4ABB449B3F0D24171D00000000040000100018000000803F54B67D4AEC6B8C3F46F5501D00000000040000100018000000803F54B67D4AECFF7F3F2D14BA1E00000000040000100018000000803F4BBAFD4A6490913F93A2291F00000000040000100018000000803F54B67D4AECFF7F3FBDA06B1F00000000040000100018000000803F54B67D4AECFF7F3F8C00D21F00000000040000100018000000803F54B67D4AECFF7F3FFD7F1D2000000000040000100018000000803FB64C3E4B4A9E813F766CD92000000000040000100018000000803F54B67D4AECFF7F3FA96D532100000000040000100018000000803F54B67D4A7322853F0469902100000000040000100018000000803FC3463E4AF703803F1E0CC92100000000040000100018000000803FC3463E4AF93A8A3F8C18F52100000000040000100018000000803FC3463E4A4D09803F76EB452200000000040000100018000000803FC3463E4AFA13803F36E8762200000000040000100018000000803FC3463E4AFEA1893F9A25A32200000000040000100018000000803FC3463E4AF703803FC16ADC2200000000040000100018000000803F54B67D4AECFF7F3F68BE312300000000040000100018000000803FB931754AA5FF7F3F96457123000000000400000300000080B33D01D8A600000000003E51B9C14182A272230000000091000000000000C0BA94283E7479585D101DDF4000000000000000000000000090BDF4400000000000000000930D3DD51A74BA3F53DD3D01D7A6000000000003F2B6C1410CE46D23000000009B0000000000006055F2283ED84DD1507CC21441000000000000000000000000D8F9124100000000000000003EEEAEB63576CD3F48DF3D01D6A6000000000077AAB4C141F4546923000000009C000000000000409AEA283EF49D2B9D84481041000000000000000000000000603C12410000000000000000C32553C62383BA3F55EE732300000000, Rowcount = 594865730, Pagecount = 1031555  
 UPDATE STATISTICS [dbo].[T4139_Fact](Nci_Filtered_Nvarchar_50__1) WITH Stats_Stream = 0x010000000500000000000000000000007B903F78000000006404000000000000C4030000000000006C02A7E66C000000090012000000000000000000FFFFFFFFE7030400E7000000640000000000000008D00034540000003D02A7E63D000000080017030000000000000000000000003D0204003D000000080017030000000000000000540000007F03A7E67F00000008001300000000000000000000000000070000009ACD3C01D9A6000078E4821400000000D4550A0000000000000000008C2EBA3D1A5E833985BB8736034386360F28C635000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000A0000000A0000000500000019000000194A14422417A44D5289D14C200FC440A04545411345AE405671A54000000041000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001900000000000000000000000000000068010000000000005002000000000000580200000000000050000000000000006C000000000000008800000000000000A400000000000000C000000000000000DC00000000000000F800000000000000140100000000000030010000000000004C01000000000000100019009D1D1249000000000000803F0126000000000000000400001000190085B21749000000000000803F01270000000000000004000010001900011E594C000000000000803F01280000000000000004000010001900020F504C000000000000803F01290000000000000004000010001900BAE0444B000000000000803F012A00000000000000040000100019003681574C000000000000803F012B00000000000000040000100019009013084B000000000000803F012C000000000000000400001000190000F49649000000000000803F012E0000000000000004000010001900AE78804B000000000000803F01340000000000000004000010001900ED3FC04B000000000000803F0136000000000000000400000300000065B83D01D8A60000000000CAAC85B44186A27223000000000A00000000000080D145B73F000000000000000000000000000000000000000090C7E7C000000000AC5000417AE97FBD918C923F20E23D01D7A60000000000F77ED8B34110E46D23000000000A00000000000080D145B73F0000000000000000000000000000000000000020893D5641000000A0EB0D55C1487A787A0068B03F09E43D01D6A600000000002B09DBB341F8546923000000000A00000000000080D145B73F00000000000000000000000000000000000000002027F0400000000030650C411F92D0E61FB6923F55EE732300000000, Rowcount = 337695470, Pagecount = 1090743  

This is interesting.  The December 10th stats update indicates -5510278.5 deletes since the previous update.  Hmmm.  The December 11th stats update indicates -48700.5 inserts since the previous update.  I'm not sure what could lead to negative numbers reported for inserts/deletes since previous update.  But I bet the negative numbers are the reason that this stats key, which I expected to see branded as static, was instead branded as 'Unknown'.  Usually after 3 stats updates, the key is branded Ascending or Static.  If negative numbers are unexpected for inserts/deletes since previous update - then perhaps these unexpected values are part of the explanation for the painfully long plan compiles.

 DBCC TRACEON(2388);  
 DBCC SHOW_STATISTICS('T4139_Fact', 'Nci_Filtered_Nvarchar_50__1');  
 DBCC TRACEOFF(2388);  


Let's have a look at the stats for our nonfiltered nonclustered index.

 DBCC TRACEON(2388);  
 DBCC SHOW_STATISTICS('T4139_Fact','Nci_Bit__1');  
 DBCC TRACEOFF(2388);  



Note how close the "Snapshot Ctr" values are to each other - Nci_Filtered_Nvarchar_50__1 values are 4 more than Nci_Bit__1 values for each of the 3 stats updates previous to current.

Here's something else very interesting: adding the Nci_Filtered_Nvarchar_50__1 "Deletes Since Last Update" to Nci_Filtered_Nvarchar_50__1 "Inserts Since Last Update" yields Nci_Bit__1 "Inserts Since Last Update" ("Deletes Since Last Update" is 0 for all 4 recorded updates).

  66162   +   232614   = 298776
5830180.5 + -5519278.5 = 310902
-48700.5  +   133653.5 =  84953

No comments:

Post a Comment