Blog – Fabiano Neves Amorim

Hi folks, I was doing some tests with some non-documented stuff and I think that perhaps you like it.

The commands are the a new query hint and some trace flags that change a little bit the behavior of how estimation of increasing columns are used.

You can download the script to create the database treinamento used in the tests here.

Following is my test file with some notes… Be confortable to ask if you didn’t understood something.

For a better read and formatting of this post you can download an pdf here, this formatting for SharePoint sucks!

http://cid-52eff7477e74caa6.office.live.com/self.aspx/Publica/Undocumented%20OPTION^5QUERYTRACEON%20and%20Trace%20Flags%202388^J%20238

   1:  USE Treinamento

   2:  GO

   3:  SET NOCOUNT ON

   4:  GO

   5:  /*

   6:    DBCC TRACEON(2388)

   7:    

   8:    Used to change the results of the DBCC SHOW_STATISTICS

   9:  */

  10:  /*

  11:    DBCC TRACEON(2389)

  12:    

  13:    Used to brand the column has ascended

  14:    When the statistics are seen to increase three times the column is branded ascending

  15:    If trace flag 2389 is set, and a column is branded ascending, 

  16:    and a covering index exists with the ascending column as the leading key, 

  17:    then the statistics will be updated automatically at query compile time.

  18:    A statement is compiled to find the highest value and a new step is added 

  19:    at the end of the existing histogram to model the recently added data. 

  20:  */

  21:  

  22:  /*

  23:    DBCC TRACEON(2390)

  24:    Trace flag 2390 enables the same behavior of 2389 even if the ascending 

  25:    nature of the column is unknown.  

  26:    As long as the column is a leading column in an index, then the optimizer 

  27:    will refresh the statisitc (with respect to the highest value) at query compile time.

  28:  */

  29:  

  30:  /*

  31:  DBCC TRACEOFF(2388, 2389, 2390)

  32:  DBCC TRACEON(2388, 2389, 2390)

  33:  */

  34:  

  35:  -- Create an index on the table PedidosBig in the column Data_Pedido

  36:  CREATE INDEX ix_Data_Pedido on PedidosBig (Data_Pedido)

  37:  GO

  38:  

  39:  -- Check the max data_pedido and insert 10000 rows in the table

  40:  -- with a data_pedido bigger than the actual max

  41:  DBCC SHOW_STATISTICS (PedidosBig, [ix_Data_Pedido])

  42:  GO

  43:  

  44:  INSERT INTO PedidosBig (ID_Cliente, Data_Pedido, Valor)

  45:  VALUES  (106,

  46:           (SELECT MAX(Data_Pedido) + 1 FROM PedidosBig),

  47:           ABS(CONVERT(Numeric(18,2), (CheckSUM(NEWID()) / 1000000.5))))

  48:  GO 5000

  49:  /*

  50:    The following query don't trigger the auto update statistics because 

  51:    5000 is less than the 20% of changes in the column necessary 

  52:    to fire the trigger.

  53:    Also the following query is using the Index ix_Data_Pedido because

  54:    it is estimating only 1 row and will be returned what is clearly wrong

  55:  */

  56:  SELECT * 

  57:    FROM PedidosBig

  58:   WHERE Data_Pedido = '20301203'

  59:  OPTION(RECOMPILE)

  60:  GO

  61:  

  62:  /*

  63:    Let's check which is the brand of the statistics

  64:    Check the column Leading Column Type

  65:  */ 

  66:  DBCC TRACEON(2388)

  67:  DBCC SHOW_STATISTICS (PedidosBig, [ix_Data_Pedido])

  68:  DBCC TRACEOFF(2388)

  69:  GO

  70:  /*

  71:  Some columns were removed to get better formating...

  72:  

  73:  Updated              Table Cardinality      Snapshot Ctr         Steps  Density                 Leading column Type

  74:  -------------------- ---------------------- -------------------- ------ ----------------------  -------------------

  75:  May 10 2011  8:06PM  1000000                2000000              49     0,000232828868320212    Unknown

  76:  */

  77:  

  78:  

  79:  /* 

  80:    Now let's try to run the query using the hint to 

  81:    force the update of the statistics at the compile time

  82:    Now the query triggers an "kind of" Update statistics 

  83:    just to see wheter the predicate exists in the table or not.

  84:    The command is the following:

  85:    SELECT StatMan([SC0]) 

  86:      FROM (SELECT TOP 1 [Data_Pedido] AS [SC0] 

  87:              FROM [dbo].[PedidosBig] 

  88:              WITH (READUNCOMMITTED)  

  89:             ORDER BY [SC0] DESC) AS _MS_UPDSTATS_TBL  

  90:     OPTION (MAXDOP 1)

  91:     

  92:    This new value is added in the end of the actual histogram 

  93:    and then this is used to see estimate the predicate.

  94:    

  95:    The QO then uses the density of the column Data_Pedido 

  96:    to estimate how many rows will be returned

  97:    In this case we have 0.0002328289 * 1000000 = 232.8289000000

  98:  */

  99:  SELECT * 

 100:    FROM PedidosBig

 101:   WHERE Data_Pedido = '20301203'

 102:  OPTION(QUERYTRACEON 2390,QUERYTRACEON 2389, RECOMPILE)

 103:  GO

 104:  

 105:  -- Here we have an very good estimation

 106:  SELECT * 

 107:    FROM PedidosBig

 108:   WHERE Data_Pedido BETWEEN '20170326' AND '20251203'

 109:  OPTION(QUERYTRACEON 2390,QUERYTRACEON 2389, RECOMPILE)

 110:  GO