Arquivo
Plano de manutenção, REINDEX VS Estatisticas
Hoje conversando com meu amigo truquêro Laerte Junior, surgiu uma dúvida,
Se eu tenho uma rotina de manutenção que faz o REBUILD de todos os índices que existem no banco de dados, mesmo assim eu preciso atualizar as estatísticas das tabelas?
Sabemos que eu posso criar estatísticas sobre uma determinada coluna manualmente utilizando o comando CREATE STATISTICS, e o SQL pode criar uma estatística (aquelas que começam com “_WA_Sys_”) sobre uma coluna automaticamente caso o parâmetro de auto create statistics estiver ligado.
Melhorando a pergunta. Após terminar de rodar o REBUILD, o SQL terá atualizado todas as estatísticas das tabelas? Inclusive esta criadas automaticamente pelo SQL?
Resposta rápida: Depende J
Vou resumir pra não perder muito tempo.
Se sua rotina utiliza o comando ALTER INDEX <ALGUMINDICE> ON <TABELA> REBUILD, então SIM você precisa atualizar as estatísticas que não pertencem aos índices.
Se sua rotina utiliza o comando ALTER INDEX ALL ON <TABELA> REBUILD, então SIM você precisa atualizar as estatísticas que não pertencem aos índices.
Se sua rotina utiliza o comando DBCC DBREINDEX(<TABELA>, <ALGUMINDICE>), então SIM você precisa atualizar as estatísticas que não pertencem aos índices.
E por fim, se sua rotina utiliza o comando DBCC DBREINDEX(<TABELA>), então NÃO você não precisa atualizar as estatísticas, pois o SQL irá atualizar todas as estatísticas pra você.
Segue um exemplo do que eu falei.
select name, Stats_date(id, indid) DataAtualizacao
from sysindexes
where id = Object_id(‘<tabela>’)
/*
name DataAtualizacao
XPKTabela 2009-08-05 10:37:05.467
ix_Coluna 2009-08-05 10:37:05.513
_WA_Sys_ID_Pessoa_0DFC52CF 2009-08-05 10:37:05.527
_WA_Sys_ID_Endereco_0DFC52CF 2009-08-05 10:37:05.527
*/
— Se você especificar um índice, ele só atualiza estatistica deste índice
DBCC DBREINDEX (‘<tabela>’, XPKTabela)
/*
name DataAtualizacao
XPKTabela 2010-05-25 14:20:52.637 — ATUALIZOU O INDICE
ix_Coluna 2009-08-05 10:37:05.513
_WA_Sys_ID_Pessoa_0DFC52CF 2009-08-05 10:37:05.527
_WA_Sys_ID_Endereco_0DFC52CF 2009-08-05 10:37:05.527
*/
— Se você NÃO especificar um índice, ele atualiza estatistica de todos os índices e estatisticas
DBCC DBREINDEX (‘<tabela>’)
/*
name DataAtualizacao
XPKTabela 2010-05-25 14:25:16.733 — ATUALIZOU TUDO
ix_Coluna 2010-05-25 14:25:17.070 — ATUALIZOU TUDO
_WA_Sys_ID_Pessoa_0DFC52CF 2010-05-25 14:25:15.127 — ATUALIZOU TUDO
_WA_Sys_ID_Endereco_0DFC52CF 2010-05-25 14:25:15.140 — ATUALIZOU TUDO
*/
— Se você usar o ALTER INDEX REBUILD passando um índice ele só atualiza as estatisticas deste índice
ALTER INDEX XPKTabela ON <tabela> REBUILD
/*
name DataAtualizacao
XPKTabela 2010-05-25 14:26:07.607 — ATUALIZAOU O INDICE
ix_Coluna 2010-05-25 14:25:17.070
_WA_Sys_ID_Pessoa_0DFC52CF 2010-05-25 14:25:15.127
_WA_Sys_ID_Endereco_0DFC52CF 2010-05-25 14:25:15.140
*/
— Se você usar o ALTER INDEX REBUILD passando ALL ele só atualiza dos índices e não das estatisticas
ALTER INDEX ALL ON <tabela> REBUILD
/*
name DataAtualizacao
XPKTabela 2010-05-25 14:27:14.517 — ATUALIZAOU SÓ OS INDICES
ix_Coluna 2010-05-25 14:27:14.617 — ATUALIZAOU SÓ OS INDICES
_WA_Sys_ID_Pessoa_0DFC52CF 2010-05-25 14:25:15.127
_WA_Sys_ID_Endereco_0DFC52CF 2010-05-25 14:25:15.140
*/
Conclusão, como o DBCC DBREINDEX será removido do SQL, altere sua rotina de manutenção para usar o ALTER INDEX ALL REBUILD e crie outra rotina para rodar o UPDATE STATISTICS, ou então rode a sp_UpdateStats.
Abraços
CTEs + Tabelas Sequênciais
Hoje precisei utilizar uma tabela sequencial e lembrei que li um texto do Itzik Ben-Gan mostrando uma alterativa para criar uma tabela sequêncial, eu lembro que ele usou CTEs, CROSS JOIN e ROWNUMBER para fazer a bagaça funcionar… portanto fui testar.
Antigamente eu usava o comando abaixo para obter uma tabela sequencial. Cheguei a escrever sobre isso por aqui… Como remover uso de cursores, utilizando CTEs
WITH CTE_Temp (ID)
AS
(
SELECT 1 AS ID
UNION ALL
SELECT 1 + ID AS ID
FROM CTE_Temp
WHERE ID < 50000
)
SELECT * FROM CTE_Temp
OPTION(MAXRECURSION 0)
GO
Este comando é prático e bem interessante, pois usa a recursividade das CTEs para gerar os números sequenciais… Porém veja a quantidade de IOs que este comando faz.
(50000 row(s) affected)
Tabela ‘Worktable’. Número de verificações 2, leituras lógicas 300001, leituras físicas 0, leituras read-ahead 0, leituras lógicas lob 0, leituras físicas lob 0, leituras read-ahead lob 0.
Uma outra opção seria usar as CTEs em conjunto com CROSS JOINs. Segue um exemplo:
WITH CTE_Temp (ID)
AS
(
SELECT 1 AS ID
UNION ALL
SELECT 1 + ID AS ID
FROM CTE_Temp
WHERE ID < 50000
)
SELECT * FROM CTE_Temp
OPTION(MAXRECURSION 0)
GO
WITH t1 (Tot) AS(SELECT 1 UNION ALL SELECT 1), — 2 linhas
t2 (Tot) AS(SELECT 1 FROM t1 CROSS JOIN t1 AS B), — 4 linhas
t3 (Tot) AS(SELECT 1 FROM t2 CROSS JOIN t2 AS B), — 16 linhas
t4 (Tot) AS(SELECT 1 FROM t3 CROSS JOIN t3 AS B), — 256 linhas
t5 (Tot) AS(SELECT 1 FROM t4 CROSS JOIN t4 AS B), — 65536 linhas
Seq (ID) AS(SELECT TOP 50000 ROW_NUMBER() OVER(ORDER BY (SELECT 1)) FROM t5)
SELECT * FROM Seq
Qual foi o número de IOs que ele fez?… veja o resultado do STATISTICS IO.
(50000 row(s) affected)
Pois é… nenhum… duvida?. Veja uma comparação entre os comandos no profiler.
Show J, vivendo e aprendendo, créditos ao Itzik Ben-Gan gênio.
Série Mentes Brilhantes – Parte 13
O que fazer quando você gosta muito de usar a clausula IN?
Use-a, independente da quantidade de itens
SELECT*FROM Clientes
WHERE ID_Cliente IN(1,
2,
3,
4,
5,
16,
17,
18,
19,
20,
21,
22,
23,
24,
25,
26,
27,
28,
29,
30,
31,
32,
33,
34,
35,
36,
37,
38,
39,
40,
41,
42,
43,
44,
45,
46,
47,
48,
49,
50,
51,
52,
53,
54,
55,
56,
57,
58,
59,
60,
61,
62,
63,
64,
65,
66,
67,
68,
69,
70,
71,
72,
73,
74,
75,
76,
77,
78,
79,
80,
81,
82,
83,
84,
85,
86,
87,
88,
89,
90,
91,
92,
93,
94,
95,
96,
97,
98,
99,
100,
101,
102,
103,
104,
105,
106,
107,
108,
109,
110,
111,
112,
113,
114,
115,
116,
117,
118,
119,
120,
121,
122,
123,
124,
125,
126,
127,
128,
129,
130,
131,
132,
133,
134,
135,
136,
137,
138,
139,
140,
141,
142,
143,
144,
145,
146,
147,
148,
149,
150)
Novo artigo no Simple-Talk
New article at Simple-Talk, thriller than ever to talk about Spool Operator and Halloween Problem, here is an little preview…
To show the functionality of the Eager Spool we’ll go back in time a bit, to the time when I was just a project being planned, but some other geeks working intensively with databases.
It was Halloween; the cold winter’s night was black as pitch, (I really don’t know if was winter, but I thought it would sound more thrilling) and the wind howled in the trees. It was 1976 and the children was demanding “tricks or treat” in the houses. The full moon shone and illuminated the whole city when suddenly, some clouds crossed the moon making the night even more dark and gloomy. It was possible to smell and taste the tension in the air like a stretched rubber band so close to burst. People walking in the street felt that someone was observing them, and when they looked closely in their back, they see two red eyes waiting and looking out for a prey unprotected.
Was that their imagination? Or just the wrong night to work with databases?
See the Full Article here, http://www.simple-talk.com/content/article.aspx?article=1034
CASE + CTEs
Aaa que beleza, Sexta-feira… o dia começa melhor… todo mundo feliz… porque será?
Mas vamos lá… aproveitando o embalo que no último post falei sobre o CASE, vou dar outra dica.
Por ex:
CREATE FUNCTION dbo.fn_Situacao(ID Integer)
RETURNS TABLE
AS
RETURN (SELECT Col1,
Col2
CASE dbo.fn_SuperUltraFunctionRetornaTudo(@ID) WHEN ‘S’ THEN 1 ELSE –1 END as Status1,
CASE dbo.fn_SuperUltraFunctionRetornaTudo(@ID) WHEN ‘N’ THEN 0 ELSE –1 END as Status2
FROM Tab1
WHERE ID = @ID)
Repare que a function fn_SuperUltraFunctionRetornaTudo esta sendo utilizada duas vezes para o mesmo ID, ou seja tem um certo desperdício ai concorda? Se eu conseguir fazer com que a chamada desta function seja única, vou ganhar tempo… para isso podemos fazer o seguinte:
CREATE FUNCTION dbo.fn_Situacao(ID Integer)
RETURNS TABLE
AS
RETURN (WITH Temp_CTE (Situacao)
AS
(
SELECT dbo.fn_SuperUltraFunctionRetornaTudo(@ID)
)
SELECT Col1,
Col2
CASE Temp_CTE.Situacao WHEN ‘S’ THEN 1 ELSE –1 END as Status1,
CASE Temp_CTE.Situacao WHEN ‘N’ THEN 0 ELSE –1 END as Status2
FROM Tab1
CROSS JOIN Temp_CTE
WHERE ID = @ID)
Feito, usando a CTE eu tiro a duplicidade. Isso pode salvar seu dia…
Case + SubQueries
Galera, estes dias o MVP Adam Haines postou uma caso bem interessante que precisamos tomar cuidado.
SubQueries + CASE,
Veja um exemplo:
SELECT CASE (SELECT Col1 FROM Test)
WHEN 30 THEN ‘Pequeno’
WHEN 60 THEN ‘Médio’
ELSE ‘Grande’
END, *
FROM Test
Veja o plano de execução:
Ou seja, o SQL vai ler a tabela Test para cada opção no CASE, portanto cuidado. Uma alternativa para escrever esta consulta seria o seguinte:
SELECT (SELECT CASE Col1
WHEN 30 THEN ‘Pequeno’
WHEN 60 THEN ‘Médio’
ELSE ‘Grande’
END
FROM Test), *
FROM Test
Veja o plano de execução:
Captou a mensagem?
Abraços
SQL em Português, Mais uma…
Bixo, SQL em português é uma desgraça…
Hoje fui criar um índice em uma view e veja a mensagem que recebi:
WTF ? Não entendi bulufas do erro…
Mudei a língua para inglês eeeee? Tâdâm…
Descobri que tava faltando o WITH SCHEMABINDING na View…
Série Mentes Brilhantes – Parte 12
O que fazer quando você quer dar um ar mais profissional para seus Inserts?
Use um cursor para inserir os dados linha a linha
DECLARE tmp_Cursor CURSORSTATICREAD_ONLYLOCAL
FORSELECT Col1,
Col2,
Col3,
Col4,
Col5,
Col6,
Col7,
Col8
FROM dbo.fn_RetornaDados(@Col9,
@Col10,
@Col11,
@Col12)
OPEN tmp_Cursor;
FETCHNEXTFROM tmp_Cursor
INTO @Col1,
@Col2,
@Col3,
@Col4,
@Col5,
@Col6,
@Col7,
@Col8;
WHILE@@FETCH_STATUS= 0
BEGIN
INSERTINTO Tabela(Col1,
Col2,
Col3,
Col4,
Col5,
Col6,
Col7,
Col8)
VALUES (@Col1,
@Col2,
@Col3,
@Col4,
@Col5,
@Col6,
@Col7,
@Col8)
FETCHNEXTFROM tmp_Cursor
INTO @Col1,
@Col2,
@Col3,
@Col4,
@Col5,
@Col6,
@Col7,
@Col8;
END
CLOSE tmp_Cursor;
DEALLOCATE tmp_Cursor;
Kalen Delaney VS o mundo não técnico
Série Mentes Brilhantes – Parte 11
O que fazer quando você gosta muito de fazer selects?
Faça um select para ler cada coluna, desta forma você consegue deixar o código mais completo.
SELECT @ID_E = ID_E
FROM Tab1
INNER JOIN Tab2
ON Tab1.ID_Grupo = Tab2.ID_Grupo
WHERE Tab2.ID_Ass = 1;
SELECT @DT = DT,
@HR = HR,
@MN = MN,
@ID_G = ID
FROM Tab2
WHERE ID_Ass = 1;
SELECT @ID_A = ID_A
FROM Tab1
WHERE ID_G = 1;
Fabiano Amorim
SQL Server MVP
Meu livro, Complete ShowPlan Operators
Livro, SQL Server além do conceito
Livro, Diálogos ao vento
Livro, Diálogos com D
Blog - Fabiano Neves Amorim 