Arquivo
Movendo meu Blog, atualize seu RSS
Série Mentes Brilhantes – Parte 14
O que fazer quando você odeia a clausula WHERE ?
Simples, basta usar o filtro no INNER JOIN usando a clausula EXISTS
SELECT Tab1.Col1,
Tab1.Col2,
Tab2.Nome
FROM Tab1
INNERJOIN Tab2
ON Tab1.ID = Tab2.ID
ANDEXISTS(SELECT 1
WHERE Tab1.Col1 = 64
AND Tab1.Col2 > 10)
Prudente TechDay dia 14/08
Fabiano vs DBCC Page, 2
Sábado em um treinamento de SQL aqui na empresa, eu estava mostrando o DBCC Page quando uma pessoa falou…
– Cara o que você esta esperando para criar uma function que faz a conversão do HexaDecimal da SysIndexes ?
Pensando nisso, criei uma proc que você passa um hexadecimal… por exemplo os valores das colunas ROOT ou o FIRST da SysIndexes… e a proc retorna o comando de DBCC PAGE…
Por que eu criei uma proc e não uma function?
Por que precisei usar a sp_ExecuteSQL para converter um VarChar para um VarBinary. Se você tiver um Hexa armazenado em um VarChar e você quiser voltar ele para hexa e rodar:
SELECT CONVERT(VarBinary(200), ‘0x00043B59’)
O SQL irá retornar:
0x30783030303433423539
Ou seja não é bem isso que quero certo?….
Daí para resolver este problema, usei uma dica que li no livro do Itizk, T-SQL Programming 2005.
Usando a sp_ExecuteSQL ele converte certo… veja você mesmo o código na proc… para baixar é só usar o link abaixo.
http://cid-52eff7477e74caa6.office.live.com/self.aspx/Publica/st^_HexaToDBCCPAGE.sql
Abraços
Fabiano VS DBCC PAGE
Pessoal, estes dias eu resolvi fazer algumas brincadeiras com o SQL… e cheguei na procedure em anexo.
Para deixar as coisas mais interessantes, vou começar o artigo perguntando o seguinte: É possível ler os dados armazenados em uma tabela temporária por uma sessão que não seja a que criou a tabela?
Por padrão sabemos apenas a sessão que criou a tabela temporária pode ler os dados armazenados nela, e estes dados ficam armazenados no banco temporário do SQL Server o Tempdb. Sabendo disso podemos pensar o seguinte… ora eu poderia criar uma tabela ir no Tempdb, e tentar dar um select nesta tabela… vamos ver se isso funciona?
Primeiro vamos criar uma tabela temporária e inserir uma linha nela…
Bom agora vamos no Tempdb tentar ver se a tabela que criei na sessão acima existe lá.
Opa, achamos a tabela, ele criou com o mesmo nome que dei, mas colocou uns caracteres na frente… vamos tentar dar um select nela.
Aaaa, não é possível ler os dados… será que tem um jeito?
Programador Marreta: Ummm, mas Fabiano ele cria a tabela o TempDB, e se eu usar o DBCC PAGE para ler os dados? Será que rola?
Fabiano: Opa! SIM claro!
Programador Marreta: Aaa Fabiano mas daí vai dar muito trabalho… não é?
Fabiano: Não, claro que não.
Programador Marreta: Como não, mas e se a tabela tiver 1000 páginas, vou ter que escrever 1000 comandos de DBCC Page?
Fabiano: Imagina, claro que não. É só rodar um exec na proc que ele mostra os dados a partir do DBCC Page.
Programador Marreta: Opa, qual proc? Daí você me pegou.
Fabiano: st_SelectPAGEs !
Pois é galera, criei uma proc que usa DBCC IND e DBCC PAGE para ler os dados de uma tabela diretamente a partir das páginas de dados. J Pura geekeza!
Vamos ver se funciona? … Você pode baixar o código de criação da procedure aqui.
Pronto, desta forma conseguirmos ler o valor da tabela temporária…
Fora isso, pra que mais essa procedure pode ser útil? Vou mostrar outro exemplo com uma página corrompida.
Primeiro vamos criar um banco de dados qualquer.
USE Master
— Caso exista um banco chamado DBCorrupt, apaga ele.
IF (SELECT DB_ID(‘DBCorrupt’)) IS NOT NULL
BEGIN
USE Master
ALTER DATABASE DBCorrupt SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE DBCorrupt
END
GO
— Criar um banco de dados chamado DBMirroring
IF (SELECT DB_ID(‘DBCorrupt’)) IS NULL
BEGIN
CREATE DATABASE DBCorrupt
END
GO
— Altera o banco para utilizar CHECKSUM no Page_Verify
ALTER DATABASE [DBCorrupt] SET PAGE_VERIFY CHECKSUM
GO
Agora vamos criar uma tabela e inserir uma linha nela.
USE DBCorrupt
GO
— Vamos criar uma tabela para utilizar nos testes.
CREATE TABLE Teste(ID Int IDENTITY(1,1) PRIMARY KEY,
Nome VarChar(250))
GO
— Vamos inserir uma linha na tabela
INSERT INTO Teste(Nome) VALUES(‘Fabiano Neves Amorim’)
GO
SELECT * FROM Teste
GO
CHECKPOINT
GO
Agora vamos setar o banco para OFFLINE para poder editar o arquivo e simular uma corrupção. Para isso eu utilizei o Hex Editor Neo, você pode usar qualquer programa de edição de hexa.
USE master
GO
ALTER DATABASE DBCorrupt SET OFFLINE
GO
Procura pelo valor “Amorim” incluído na tabela.
Vou editar e trocar para XXXXXX, para causar um erro de lógico de CheckSum, leia mais e entenda o CheckSum aqui.
Após isso, vou voltar o banco para ONLINE.
ALTER DATABASE DBCorrupt SET ONLINE
GO
Agora vamos tentar rodar um select na tabela “Teste”.
USE DBCorrupt
GO
SELECT * FROM Teste
Ao tentar rodar este comando, o seguinte erro é exibido:
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xe7f12a45; actual: 0xf3e431c5). It occurred during a read of page (1:78) in database ID 18 at offset 0x0000000009c000 in file ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\DBCorrupt.mdf’. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Para recuperar a tabela, teríamos que rodar o DBCC CHECKDB com a opção REPAIR_ALLOW_DATA_LOSS.
Mas e se eu tentar ler usando minha proc? Ou seja, diretamente nas páginas de dados?
Feito, desta forma você poderia fazer um backup da tabela, apaga-la e voltar os dados a partir da tabela lida pelo DBCC Page.
Galera, fica ai a dica e o script da proc… have fun…
Oração do DBA/Developer
QUERY NOSSA QUE ESTAIS NO BANCO,
RODE RÁPIDO MINHA CONSULTA,
ASSIM NO CONSOLE QUANTO NA APLICAÇÃO
QUE RETORNE NOSSA CONSULTA
AMÉM!
Meu twitter
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)
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 