Blog – Fabiano Neves Amorim

Se você quer participar do treinamento no mês que vem em São Paulo, corre… só resta 1 vaga…

http://blogfabiano.com/2013/04/15/t-sql-expert-em-sp-aos-sbados1118-e-25-de-maio/

Abs.

Post rápido para não deixar meu blog ficar muito marketeiro rs…

Leve a Sr.Nimbus para fazer uma apresentação na sua empresa, de graça…

Alguns assuntos que gostei:

• Novidades do SQL Server 2012
• Futuro da engine relacional – ColumnStore e Hekaton (in-memory database)

Confira os detalhes aqui:

http://www.srnimbus.com.br/palestras-gratuitas/

Today I was working on the material for the on-demand (online) training we’ll offer in next few days at Sr.Nimbus when I find-out a very interesting thing.

“The statistics used in a query plan are stored in a cached execution plan, and you can see them using the special trace flag 8666 (there is always a traceflag).”

It is also possible to identify which statistics were used in a query plan compilation if you run the query using traceflags 9292 and 9204 as Query Optimizer Guru Paul White (blog|twitter) showed here.

What I’m saying is that you can also see in the XMLPlan the used statistics.

Let’s see a small sample of what I’m saying…

First let’s clear the cache plan memory area:

 1: DBCC FREEPROCCACHE()

Now let’s run a small query on the Northwind database:

 1: USE Northwind

 2: GO

 3: SELECT Employees.FirstName, COUNT(Orders.OrderID)

 4:   FROM Orders

 5:  INNER JOIN Employees

 6:     ON Orders.EmployeeID = Employees.EmployeeID

 7:  WHERE Employees.FirstName = 'Steven'

 8:  GROUP BY Employees.FirstName

 9: GO

This is the plan:

Running the same query with TF 8666 on, we’ve the following information in the xml query plan (right click in the select operator and select properties):

This new element InternalInfo in the XML plan is documented on the current schema http://schemas.microsoft.com/sqlserver/2004/07/showplan/showplanxml.xsd

The information we can see here are confuse and used by Microsoft people to debug query plans, but, one specific tag is more interesting than others… This is the ModTrackingInfo tag. There we can see the statistics used information, let’s dig in:

In the image above we can see two “Recompile” tags (collapsed), in fact we’ve one tag for each table in the query (in our sample, table orders and table employees).

Isn’t that nice or what?

Could be this be even more interesting? Yes.

All this information are also stored in the cached plans. That means if you query the Cached Plans DMVs with the TF turned on, we’ll be able to use xquery to query the used statistics.

For instance:

 1: DBCC TRACEON(8666)

 2: GO

 3: WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as p)

 4: SELECT qt.text AS SQLCommand,

 5:        qp.query_plan,

 6:        StatsUsed.XMLCol.value('@FieldValue','NVarChar(500)') AS StatsName

 7: FROM sys.dm_exec_cached_plans cp

 8:     CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp

 9:     CROSS APPLY sys.dm_exec_sql_text (cp.plan_handle) qt

 10:     CROSS APPLY query_plan.nodes('//p:Field[@FieldName="wszStatName"]') StatsUsed(XMLCol)

 11:  WHERE qt.text LIKE '%Steven%'

 12:    AND qt.text NOT LIKE '%sys.%'

 13: GO

 14: DBCC TRACEOFF(8666)

 15: GO

Will Return:

I’m sure you can do better xquery than I to query this from cacheplan.

Now the boring part of the this… First of all and the most important. This traceflag IS NOT DOCUMENTED so please be very very very careful to use it, and use only for tests purposes, never use it in a production environment. Also when I tried to use it on SQL Server 2008R2 sometimes I’ve got nasty errors generating dump and closing my session.

Since I‘m aware, it works fine on SQL Server 2012, at least I didn’t received any error on using it.

Let me know if you try it on an SQL 2005 instance… And also about bugs and discovers you may find with it .

Happy query.

Galera, recomendo muitíssimo o canal que meu amigo MVP Gustavo Maia Aguiar criou no Youtube para postar os vídeos e webcasts que ele já fez sobre SQL Server.

Imperdível, mais detalhes aqui:

http://gustavomaiaaguiar.wordpress.com/2011/08/12/ampliando-a-comunicao-mais-um-canal-para-falar-de-sql-server-e-banco-de-dados/

Parabéns pela iniciativa Gustavo.

Pessoal conforme combinado, segue o link para download do material da minha sessão de hoje na Semana do SQL Server.

http://blogs.solidq.com/fabianosqlserver/Documents/Semana%20SQL%20Server%20-%20PASS.zip

Pra quem perdeu, falei um pouco sobre windows functions, seguem algumas fotos dos PPTs, e das demos:

Galera, conforme combinado, seguem os links para acesso ao vídeo e a gravação do LYNC da minha sessão de hoje (14/07) no SolidQ Virtual Conference. Se você ainda não se inscreveu não perca mais tempo…

Espero que tenham gostado, e qualquer dúvida sabem onde me achar .

Link para download do material:

https://skydrive.live.com/#!/?cid=52eff7477e74caa6&sc=documents&uc=1&id=52EFF7477E74CAA6!2023

Pra variar, o Murphy deu as caras, e minha janela do LYNC travou no meio da sessão… Nada grave, mas o video ficou cortado em 2 partes.