Estatísticas SQL Server 2012/2014

September 2014

Upcoming SQLSaturdays

• Sep 27 #325 Sao Paulo

Visit www.sqlsaturday.com to register for an event near you!

http://www.sqlsaturday.com/325/eventhome.aspx

Tuning do Ínicio ao FimThiago Carlos de Alencar09:30 AM

Quem Sou Eu?

Thiago Carlos [TC] de Alencar• Premier Field Engineer• Blog: www.tcalencar.wordpress.com• E-mail: alencar.dba@outlook.com• Certificações: MCITP e MCT

Agenda

Estatísticas do SQL Servero Introdução a Estatísticaso Criando / Atualizando Estatísticaso Visualizando as Estatísticaso Lendo um Histogramao Estatísticas FiltradasNo SQL 2014?o Novo Algoritmo de Cardinalidadeo Melhorias em: ASC/ DESC Estimate - Join Estimates -

Multiple Column DependencyDemo

Introdução a Estatísticas• O SQL Server Precisa de estatísticas para tentar criar bons

planos de execução.

• Estatísticas mantem informações sobre a distribuição dos dados

• A geração de estatísticas no SQL Server automática é apenas para uma coluna.

• Histograma pode ter até 200 “passos”

• É “saudável” manter muitas estatísticas? • DEPENDE

Criando / Atualizando Estatísticas• O SQL Server Cria / Atualiza automaticamente as estatísticas

no SQL Server

• Estatísticas criadas automaticamente começam com _WA…..

• Propriedade do Banco de dados

• CREATE STATISTICS statistics_name ON { table_or_indexed_view_name }

• UPDATE STATISTICS table_or_indexed_view_name [ { { index_or_statistics__name }

Visualizando estatísticas

• DBCC SHOW_STATISTICS ( table_or_indexed_view_name , target ) [ WITH [ NO_INFOMSGS ] < option > [ , n ] ] < option > :: =     STAT_HEADER | DENSITY_VECTOR | HISTOGRAM | STATS_STREAM

• sys.stats

• sys.stats_columns

• STATS_DATE(ObjectId, Stats_id)

• sys.dm_db_stats_properties (Object_id, Stats_Id)• Nova no SQL Server 2014• Aplica-se a SQL Server 2008 R2 SP2 e SQL Server

2012

Lendo o Histograma

DBCC SHOW_STATISTICS(“Production.TransactionHistory”, TransactionDate)

RANGE_HI_KEY - Valor chave de cada amostra.

SELECT * FROM Production.TransactionHistory WHERE TransactionDate = '20070906'

RANGE_ROWS – Qtde de Linhas dos valores dentro do RANGE.Ex: O range da amostra da linha 4 vai de 2007-09-05 até 2007-09-06 (exclui o RANGE_HI_KEY)

EQ_ROWS – Qtde de Linhas exatas para os valores de RANGE_HI_KEY.Ex: Para o valor de 2007-09-03 existem 203.DISTINCT_RANGE_ROWS – Qtde de valores distintos que existe para o RANGE_ROWS.Ex: Quantos valores distintos existe entre o valor de 2007-09-05 até 2007-09-06 (exclui o RANGE_HI_KEY)

AVG_RANGE_ROWS – Quantidade média de registros no RANGEEx: RANGE_ROWS / DISTINCT_RANGE_ROWS

Estatísticas Filtradas

• Melhor desempenho de consultas e qualidade de plano

• Redução de custos de manutenção de estatísticas• Por ser um conjunto menor de dados

• Adiciona a cláusula WHERE

• CREATE STATISTICS NomeEstatistica ON Tabela (Coluna(n)) WHERE Predicado IS NOT NULL

Novo no SQL 2014

Novo algoritmo de cardinalidade• Novo “Cardinality Estimator” - CE• É um sub-componente do “Query Processor”• O nível de compatibilidade deve estar em 120 (SQL 2014)

• Não tem mudado desde o SQL Server 7.0 – 15 Anos• Tem recebido melhorias através de Services Pack,

Hotfixes, traceflags e etc.• Alterar a estrutura da arquitetura atual tornou-se difícil

• Melhora a maioria dos “Workloads”• Maioria não inclui “TODAS”• Teste é VITAL

• Regressão de Planos• TF 2312 ou Nível de compatibilidade do SQL Server 2012

(110)

Melhorias em:

• ASC / DESC Estimativas de Valores Chaves• Mudança no comportamento de “Ascending Columns”

• Múltiplas colunas – AND e OR• Old: (S¹ * S²) * Cardinalidade / (S¹ + S²) - (S¹ * S²) *

Cardinalidade• Novo: C * (S¹ * SQRT(S²) * SQRT(SQRT(S³)) ….)• O cálculo é feito apenas nas “quatro” primeiras

seletividades

• Dependência de Múltiplas Colunas*.• Independência de colunas em versões anteriores

Referências:http://msdn.microsoft.com/pt-br/library/ms190397.aspx

http://msdn.microsoft.com/pt-br/library/ms188038.aspx

http://msdn.microsoft.com/pt-br/library/ms187348.aspx

http://msdn.microsoft.com/pt-br/library/ms174384.aspx

http://technet.microsoft.com/pt-br/library/cc280372(v=sql.110).aspx

http://msdn.microsoft.com/en-us/library/dn673537.aspx

http://blogs.msdn.com/b/ianjo/archive/2006/04/24/582227.aspx

http://www.benjaminnevarez.com/2013/02/statistics-on-ascending-keys/

https://www.simple-talk.com/sql/database-administration/statistics-on-ascending-columns/

DEMO

Stay Involved!• Sign up for a free membership today at sqlpass.org

• Linked In: br.linkedin.com/in/alencardba• Facebook: Thiago Carlos de Alencar• Twitter: @alencardba• PASS: http://www.sqlpass.org