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

Semana passada eu estava ministrando um treinamento de SQL Server, e estava falando sobre Indexação. Em um determinado momento eu explicava que não é necessário informar a chave do índice clustered na criação dos índices nonclustered, pois as colunas chave do clustered já fazem parte do nonclustered.

Mesmo não sendo necessário, na criação do índice nonclustered você pode informar a chave do clustered que o SQL não “duplica” os dados da(s) coluna(s), ou seja, por ter incluído a coluna do clustered no comando de create index, o SQL não mantém duas cópias dos dados no índice nonclustered.

O espaço do índice nonclustered será o mesmo espaço que se você não tivesse especificado a coluna chave do cluster.

Neste momento a seguinte pergunta foi feita por um aluno:

– Pelo fato de não incluir duas vezes a coluna chave do clustered nos nonclustered, o que acontece se eu mudar a chave do índice cluster? Os índices nonclustered mantém a coluna chave antiga?

Resposta: Se você especificou a coluna na criação do índice nonclustered o SQL mantém a coluna mesmo que você mude a chave cluster. Mas se você não especificou a chave o SQL não mantém a coluna. (o que é o comportamento esperado)

Normalmente eu não gosto de especificar a coluna cluster na criação de meus índices nonclustered, mas fique atento de que caso você mude a coluna chave do cluster, isso irá mudar todos os índices nonclustered.

OBS.: Caramba, viu quantas vezes eu escrevi, clustered e nonclustered …

Abraços

Folks, here is a new article on simple-talk.

http://www.simple-talk.com/sql/learn-sql-server/showplan-operator-of-the-week—split,-sort,-collapse/

Regards

Galera, a convite do pessoal da MS Brasil, gravei alguns vídeos que farão parte de um pacote de treinamento em SQL Server 2008 R2.

Gravei 5 vídeos sobre variados temas, segue ai para quem se interessar.

Gostei da idéia e quero deixar uma pergunta, tem algum assunto que vocês gostariam que eu fale um pouco em um vídeo ? Deixe um comentário.

Abraços

Policy Based Management

Novos Comandos T-SQL

Manipulação de XML

Alta Disponibilidade – Database Mirroring

Utilizando Resource Governor

There are many cases when QO can create an execution plan that access the table using a join operator, and using parallelism. To see a sample I’ll show the index intersection.

It is used when SQL can join the result of two indexes to return the query.

An interesting sample could be the following query using AdventureWorksDWR2:

   SELECT [ProductAlternateKey], [ProductSubcategoryKey]

   FROM DimProduct

   WHERE [ProductAlternateKey] = 'BA-8327'

   OR [ProductSubcategoryKey] = 12

   OPTION (RECOMPILE)

The query is using the indexes by ProductAlternateKey and ProductSubcategoryKey on the table DimProduct to read all the data, joining the results using the Hash Join and then it applies the filter on the where clause.

Now to simulate a parallel plan, we can do some good tricks :-). First you can fool SQL about the number of CPUs that you have. Second you can fool SQL about the size of your table.

To fool SQL about the number of CPUs you can start the SQL using the parameter –P16.

SQL Server Configuration Manager:

Error Log file after restart SQL Server:

Alternatively you can check how many CPUs do you have using the DMV sys.dm_os_sys_info.

   SELECT cpu_count FROM sys.dm_os_sys_info

cpu_count

———–

16

And to fool SQL Server about the size of the table, we could change the statistics of the clustered index of the table. For that, we can use the UPDATE STATISTICS and specify the RowCount and PageCount options.

   UPDATE STATISTICS DimProduct [PK_DimProduct_ProductKey] WITH RowCount = 500000, 

   PageCount = 100000

After run the update statistics our query starts to use parallelism to perform the reads and join using all CPUs in parallel.

   SELECT [ProductAlternateKey], [ProductSubcategoryKey]

   FROM DimProduct

   WHERE [ProductAlternateKey] = 'BA-8327'

   OR [ProductSubcategoryKey] = 12

   OPTION (RECOMPILE)

Estou em um cliente fazendo alguns trabalhos de segurança e precisei esconder um código SQL dos DBAs que tem permissão de ALTER TRACE (podem usar o Profiler), mas que não tem acesso aos objetos (procs, functions, views e etc…) do banco.

Ou seja, o cara pode ver os códigos enviados para o SQL Server, mas não tem acesso para manipulação dos objetos nos bancos de dados.

Uma maneira seria criptografar a procedure, isso faz com que o Profiler não mostre o código executado.

Se você não quiser criar uma proc para encapsular este código, você pode usar uma método, digamos que, alternativo. (quem nunca pecou que atire a primeira pedra. Eu poderia estar matando, roubando ou coisa pior, mas não, só estou criando um métodozinho alternativo).

Use a function EncryptByPassPhrase, veja um exemplo:

SELECT 'Teste Código que ninguem pode ver pelo Profiler' 

WHERE EncryptByPassPhrase('','') <> '' 

Veja o que aparece no Profiler:

Is it nice or what?

#Fica_a_dica

Introduction

As we know SQL Server uses a cost based optimizer to create the execution plans. That means SQL Server evaluates many possibilities to create an execution plan and chose for the plan with the lower cost.

One of the problems we have today is that if we want to predict how a query will perform adding a new index on the table, we need to wait for the creation of the index.

Sometimes waiting for the creation of the index is a nightmare, especially if the table is too big. Furthermore, after wait for 20 minutes for the creation of the index, when you go look at the query plan it is not using the index .

So the question is, how to create an hypothetically index? Just to test if the index really will be useful for the query.

WITH STATISTICS_ONLY

To create an hypothetically index you could use an undocumented syntax in the create index command.

For instance:

USE AdventureWorksDW
GO
CREATE INDEX ix_FirstName ON DimCustomer(FirstName) WITH STATISTICS_ONLY = -1
GO

This will create the index only with the statistics (density and histogram stuff). You could check the index using the sp_helpindex:

sp_HelpIndex DimCustomer

DBCC SHOW_STATISTICS(DimCustomer, ix_FirstName)

My friend Benjamin Nevares did a very good explanation about this here:

http://sqlblog.com/blogs/ben_nevarez/archive/2009/11/11/database-engine-tuning-advisor-and-the-query-optimizer.aspx

PS: If you create the index using “WITH STATISTICS_ONLY = 0” SQL will not create the statistic for your table. Just the hypothetical index.

DBCC AUTOPILOT and SET AUTOPILOT

Now that we have the hypothetical index, how to use it?

Let’s try to use the “index” hint :

SELECT * FROM DimCustomer WITH(index=ix_FirstName)

WHERE FirstName = N’Eugene’

Msg 308, Level 16, State 1, Line 1

Index ‘ix_FirstName’ on table ‘DimCustomer’ (specified in the FROM clause) does not exist.

What about use the indexid?

SELECT * FROM DimCustomer WITH(index=5)

WHERE FirstName = N’Eugene’

Msg 307, Level 16, State 1, Line 1

Index ID 5 on table ‘DimCustomer’ (specified in the FROM clause) does not exist.

So how we can create a new query plan that consider this index?

Now the fun stuff.

The DBCC AUTOPILOT is used to tell to the Optimizer to consider an specific index to the query plan creation. This DBCC plus the SET AUTOPILOT ON it’s the way to allow the use of the index.

Following is the syntax of the commands:

SET AUTOPILOT ON|OFF

/*

  DBCC TRACEON (2588)

  DBCC HELP(‘AUTOPILOT’)

*/

DBCC AUTOPILOT (typeid [, dbid [, {maxQueryCost | tabid [, indid [, pages [, flag [, rowcounts]]]]} ]])

Following is a complete scenario with an sample of the commands:

— Current Cost = 0,762133

— Clustered Index Scan on pk

SELECT * FROM DimCustomer

WHERE FirstName = N’Eugene’

GO

— creating the index

— DROP INDEX ix_FirstName ON DimCustomer

CREATE INDEX ix_FirstName ON DimCustomer(FirstName) WITH STATISTICS_ONLY = -1

GO

 
— Looking at the info necessary in the DBCC AUTOPILOT comand

SELECT name, id, Indid, Dpages, rowcnt

  FROM sysindexes

WHERE id = object_id(‘DimCustomer’)

GO

DBCC AUTOPILOT (5, 9, 0, 0, 0, 0, 0) — Starting with the TypeID

DBCC AUTOPILOT (6, 9, 37575172, 1, 0, 0, 0) — Clustered Index with TypeID 6

DBCC AUTOPILOT (0, 9, 37575172, 2, 0, 0, 0) — All other index with TypeID 0

DBCC AUTOPILOT (0, 9, 37575172, 3, 0, 0, 0) — All other index with TypeID 0

DBCC AUTOPILOT (0, 9, 37575172, 5, 0, 0, 0) — All other index with TypeID 0

GO

SET AUTOPILOT ON

GO

— Query to create the estimated execution plan with the cost = 0,0750712

SELECT * FROM dbo.DimCustomer

WHERE FirstName = N’Eugene’

OPTION (RECOMPILE)

GO

SET AUTOPILOT OFF

GO

I realized that you can fool the Optimizer telling different numbers for the Pages and RowCount parameters of the DBCC AUTOPILOT. If you let it zero it use the Clustered index values.

Conclusion

There is a lot of mystery about this features, but I’m sure this will be a good start of tests for you.

I’m still playing with this, so be confortable to ask something or share a new discovery.

I don’t need to tell you to don’t use this is a production environment do I? This is a undocumented stuff so nobody can guaranty what it is really doing unless MS folks make it officially public and documented.

That’s all folks

Responda rápido!  O que é melhor?

   1: SELECT COUNT(1)

   2:   FROM Produtos

   3: GO

   4: SELECT COUNT(*)

   5:   FROM Produtos

   6: GO

   7: SELECT COUNT(ID_Produto) -- PK

   8:   FROM Produtos

   9: GO

Tanto faz. Na verdade o Query Optimizer traduz tudo para COUNT(*).

O que ????

Pois é, quer que eu prove?

Fácil, veja os planos: 

   1: SELECT COUNT(1) FROM Produtos

   2:   |--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1004],0)))

   3:        |--Stream Aggregate(DEFINE:([Expr1004]=Count(*)))

   4:             |--Clustered Index Scan(OBJECT:([Produtos].[xpk_Produtos]))

   5:  

   6: SELECT COUNT(*) FROM Produtos

   7:   |--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1004],0)))

   8:        |--Stream Aggregate(DEFINE:([Expr1004]= Count(*)))

   9:             |--Clustered Index Scan(OBJECT:([Produtos].[xpk_Produtos]))

  10:  

  11: SELECT COUNT(ID_Produto) FROM Produtos

  12:   |--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1004],0)))

  13:        |--Stream Aggregate(DEFINE:([Expr1004]= Count(*)))

Como alguns já sabem o CTP1 do SQL Server 11 Codinome “Delani” foi lançado oficialmente esta semana no PASS.

Infelizmente a documentação liberada junto com o download do CTP1 tem pouquíssimas informações sobre o que realmente mudou e nesta versão.

Eu estou fazendo alguns testes e encontrei algumas coisas interessantes:

Novos Objetos

• sp_server_diagnostics – Como o nome já diz, esta SP faz um diagnostico do servidor e retorna o resultado separados em 5 categorias.
• System
• Resource
• Query_Processing
• Is_subSystem
• Events

Eu achei os resultados fantásticos, informações valiosíssimas sobre o que está acontecendo no seu servidor.

Segue um print da execução da proc e os resultados que ela apresenta.

Utilizei o XML Notepad para visualizar os dados XML da columa data, veja abaixo alguns prints:

• DMV sys.dm_exec_describe_first_result_set, dm_exec_describe_first_result_set_for_object – Pelo que o que percebi, estas DMVs retornam um metadata completíssimo de uma determinada consulta ou objeto… por ex:

select * from sys.dm_exec_describe_first_result_set(‘SELECT * FROM Produtos ORDER BY ID_Produto DESC’, ”, 0)

O Interessante é que da pra passar um T-SQL com erro, dai você pode ler o erro nas colunas de erro…

select * from sys.dm_exec_describe_first_result_set(‘SELECT * FROM Produtos ORDER BY ID_Produto DESCC’, ”, 1)

CREATE PROC st_Test

AS

SELECT * FROM Pedidos

GO

select * from sys.dm_exec_describe_first_result_set_for_object(Object_ID(‘st_Test’), 0)

• Novas regas para otimização das consultas, para encontrar estas regras utilizei a DVM sys.dm_exec_query_transformation_stats. Comparando a versão do SQL 11 com meu SQL 2008 R2.

Agora nos resta entender o que estas regras fazem 🙂

select * from sys.dm_exec_query_transformation_stats a

where not exists (select 1

from [NB_FABIANO\SQL2008R2_1].master.sys.dm_exec_query_transformation_stats b

where a.name = b.name)

Fique de olho.. logo devo postar mais sobre algumas novidades bem interessantes.. .

Abraços

I really love to writing about something especially about SQL Server, I also like to read good stuff and it is much better if the text is funny, I call this a “clean read”.

I’m not talking about a lot of jokes and nothing related to the content of the material, but sometimes the read is better when we see some comparisons with our life…

I’ve seen and tried to write good texts using this, for instance, comparisons with movies characters, famous quotes by someone (Developers, Developers, Developers… J) or predictions ("Two years from now, spam will be solved." Bill Gates, 2004) and so on.

One day in a chat with Fernando Guerreiro (Blog | Twitter) I asked: – Why text editors don’t like jokes? And he wisely said, Jokes don’t work at a global level.

I struggle with this problem myself and now I know that, if we want to reach global readers we need be very carefully about the different cultures. I also learn that It’s impossible write something that will receive 100% of approbations for everybody.

So now, some questions rises: – What do you think about it? Do we have to be political correctly and write regular texts? Do we have to write a book or an article using those things? Why IT books in general have to looks like a Manual, and even sometimes quite boring?

I would love to hear your opinion, please leave a comment about your thoughts.