Início > Não categorizado > Undocumented OPTION(QUERYTRACEON ) and Trace Flags 2388, 2389, 2390

Undocumented OPTION(QUERYTRACEON ) and Trace Flags 2388, 2389, 2390

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
Anúncios
Categorias:Não categorizado
  1. Nenhum comentário ainda.
  1. No trackbacks yet.

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

%d blogueiros gostam disto: