Ordem dos Filtros vs Tempo de Execução

Frequentemente vejo desenvolvedores com dúvidas em relação à ordem dos filtros na cláusula WHERE das consultas. Algumas perguntas como; Se colocar um campo mais seletivo primeiro faz diferença? Se colocar a chave primária como primeiro filtro é mais rápido? A resposta é NÃO!!

Quando o SQL Server gera um plano de execução ele não faz isso por partes da consulta, ou seja, ele não sai lendo a consulta e montando o plano. O Query Optimizer que é o otimizador de consultas do SQL Server lê a consulta toda, completa e após isso gera o plano de execução, então a ordem dos filtros não altera o plano de execução, resumindo não afeta o tempo de execução da consulta.
Vamos ver na prática:

Use master
go
CREATE DATABASE Testes
GO
USE Testes
GO
CREATE TABLE Pessoa
(ID INT NOT NULL IDENTITY(1,1),
 NOME VARCHAR(100))
 GO

 --Inserindo registros na tabela Pessoa
INSERT INTO Pessoa VALUES ('Allyson')
go 1000
INSERT INTO Pessoa VALUES ('Joao')
go 1000
INSERT INTO Pessoa VALUES ('Rafael')
go 1000
INSERT INTO Pessoa VALUES ('Rodrigo')
go 101000

--Criando índice não cluster para os campos
CREATE INDEX IDX_Teste1 ON Pessoa (Nome, ID)
GO

--Limpando o cache
DBCC FREESYSTEMCACHE('ALL')

--Habilita as estatísticas de tempo, para verificar o tempo exato de passo da consulta
SET STATISTICS TIME ON

SELECT * FROM Pessoa
WHERE Nome = 'Rodrigo' and ID = 10000
GO

SET STATISTICS TIME OFF
Temos o resultado na aba de mensagens:
/*
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 4 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.
(1 row(s) affected)
 SQL Server Execution Times:
   CPU time = 15 ms,  elapsed time = 8 ms.
*/  

--Limpando o cache
DBCC FREESYSTEMCACHE('ALL')

--Habilita as estatísticas de tempo, para verificar o tempo exato de passo da consulta
SET STATISTICS TIME ON

SELECT * FROM Pessoa
WHERE ID = 10000 AND Nome = 'Rodrigo'
GO

SET STATISTICS TIME OFF

Temos o resultado na aba de mensagens:
/*
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 3 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.
(1 row(s) affected)
 SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 8 ms.
*/  

Vemos então que o tempo foi exatamente igual para as duas consultas. Sendo que na primeira fazemos o primeiro filtro pelo Nome='Rodrigo', onde existem mais de 100.000 ocorrências, e depois fazemos novamente onde o primeiro filtro é o ID, como foi criado como identity ele é único na tabela, e o tempo de execução foi mesmo.

O que ocorre frequentemente é que quando executamos uma consulta o seu plano de execução vai pro cache e quando alteramos a ordem dos filtros e não limpamos o cache a consulta é executada mais rápido por seu plano já está em cache e dá a impressão de que a ordem dos filtros fez diferença.

É muito importante quando se está analisando uma consulta limpar o cache, para não se enganar com o resultado.

Abraços,
Rodrigo Figueiredo
figueiredo.rodrigo@hotmail.com

Database Snapshot - Tamanho Real

Como muitos já sabem o snapshot usa arquivos sparses, então o tamanho do arquivo que aparece no Windows não é o tamanho real, aquele é o tamanho do arquivo do banco de dados original. Então fiquei curioso para saber o tamanho real daquele database, depois de alguns dias pesquisando bastante sobre o assunto encontrei a DMV sys.dm_io_virtual_file_stats, pode-se passar como parâmetro para ela o ID do banco de dados.

Nesta DMV existe um campo size_on_disk_bytes que exibe em bytes o tamanho do arquivo no disco, fazendo alguns cáculos, chegamos no valor...

Executando a seguinte consulta no database snapshot:

select
database_id,
CAST((size_on_disk_bytes)/(1024.00*1024) AS NUMERIC(18,2)) AS Size_on_Disk_MB
from sys.dm_io_virtual_file_stats(DB_ID(), NULL)

Resultado:

Outra forma de vermos o tamanho real do banco snapshot é indo na propriedade do arquivo.

Abraços,
Rodrigo Figueiredo
figueiredo.rodrigo@hotmail.com

SQL Server Denali ou SQL 11

Galera,

   Dos dias 8 a 11 de novembro está acontecendo o evento PASS Summit, este é simplesmente o maior e melhor evento de SQL Server do mundo!

Foi divulgado no PASS o novo SQL Server, o Microsoft® SQL Server® code-named 'Denali' - Community Technology Preview 1 (CTP1).

Segue link para baixar:
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=6a04f16f-f6be-4f92-9c92-f7e5677d91f9

Segue algumas novas funcionalidades do SQL 11:

Agora poderemos trabalhar dentro do Analysis Services com uma interface gráfica idêntica ao Excel, utilizando o VS 2010, para que tenhamos o mesmo poder que o poweruser tem quando fazendo as análises dele, mas para ser usado durante o desenvolvimento, e utilizando os recursos de processamento do servidor. Na demonstração foi apresentado uma Demo trabalhando com 2 bilhões de linhas, que foram carregadas depois para o PowerPivot, e durante a ordenação em memória dos dados foi atingida uma velocidade de 1 TRILHÃO de linhas por minuto.

Column Data Store, resumindo bastante é uma nova forma de armazenar os dados, utilizando o algoritmo VertiPaq, utilizado pelo PowerPivot, mas não estamos falando de B.I., estou falando de utilizar o algoritmo para compressão dos dados utilizado no PowerPivot, dentro do banco RELACIONAL, na demonstração pegamos os mesmos 2 bilhões de linhas de antes, fazemos algumas agregações para retornar “apenas” 18 milhões de linhas.. tudo em alguns segundos!

Projeto Crescent. Nova ferramenta para relatórios, via web, sensacional, em alguns segundos o usuário final cria relatórios/dashboards com animações, gráficos e tudo que um relatório gerencial profissional merece.

Integração entre soluções de HA (alta disponibilidade) e DR (recuperação de desastres). Aqui teremos muitas novidades, onde poderemos criar grupos de disponibilidade, configurar até 5 níveis de severidade para iniciar um failover, redirecionamento automático dos clientes, ponto central para gerenciamento do HA e DR, utilização dos sistemas stand-by como servidores de relatórios, jogando a carga de leitura para esses servidores. 

Vamos esperar mais novidades que com certeza aparecerão.

Books Online: http://msdn.microsoft.com/en-us/library/ms130214(SQL.110).aspx

Referência: http://blogs.solidq.com/fferreira/Home.aspx

Abraços,
Rodrigo Figueiredo
figueiredo.rodrigo@hotmail.com

Criptografia Transparente de Dados - Final

Irei mostrar todos os testes que fiz ao desabilitar a TDE de um banco de dados.

Estou utilizando o  SQL Server 2008 Developer Edition com SP2, build 10.0.4000.

Vamos lá...

-- Desabilitando a criptografia do banco de dados   
ALTER DATABASE Criptografia
SET ENCRYPTION OFF
GO
Após executar este comando e desabilitar a TDE vamos executar a consulta:

SELECT DB_NAME(e.database_id) AS DatabaseName,
            e.encryption_state,
    CASE e.encryption_state
                WHEN 0 THEN 'No database encryption key present, no encryption'
                WHEN 1 THEN 'Unencrypted'
                WHEN 2 THEN 'Encryption in progress'
                WHEN 3 THEN 'Encrypted'
                WHEN 4 THEN 'Key change in progress'
                WHEN 5 THEN 'Decryption in progress'
    END AS encryption_state_desc,
   key_algorithm +'_'+ convert(nvarchar,key_length) [Algoritmo],
            c.name [Nome Certificado],
            set_date [Data Criptografia]
    FROM sys.dm_database_encryption_keys AS e
    LEFT JOIN master.sys.certificates AS c
    ON e.encryptor_thumbprint = c.thumbprint

Teremos o seguinte o resultado:

Vemos que a criptografia está desabilitada mas o banco de dados ainda está associado ao certificado, vamos então excluir então a chave de criptografia do banco:

USE Criptografia
GO
DROP DATABASE ENCRYPTION KEY
GO

Executando a consulta novamente veremos que somente o banco tempdb sairá no resultado:

Neste momento se reiniciarmos o serviço do SQL e executarmos a consulta não retornará nada.

Vamos agora desatachar o banco e tentar restaurar em outra instância usando o mesmo SQL Server 2008 Developer Edition com SP2, build 10.0.4000 só que sem nenhuma chave de criptografia ou certificado, teoricamente era para funcionar já que desabilitamos a TDE do banco, mas vamos ver o resultado.

--Desatachando o banco
USE master
GO   
sp_detach_db 'Criptografia'
GO

e agora vamos tentar atachar na outra instância:

EXEC sp_attach_db @dbname = 'Criptografia',
    @filename1 = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Criptografia.mdf',
    @filename2 = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Criptografia_Log.ldf'
GO

Teremos o seguinte retorno:

Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint '0xAA16E63CE30B041330332FA87A4C37E82F8ABC33'.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'Criptografia'. CREATE DATABASE is aborted.

Neste momento eu não acreditei que não seria possível tirar a criptografia do banco, então fiz um backup da banco e tentei restaurar, tive o seguinte retorno:

Processed 256 pages for database 'Criptografia', file 'Criptografia' on file 1.
Processed 1 pages for database 'Criptografia', file 'Criptografia_log' on file 1.
Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint '0xAA16E63CE30B041330332FA87A4C37E82F8ABC33'.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Após alguns dias pesquisando vi que no CU10 do SQL Server 2008 SP1 esse bug (Microsoft assumiu) foi corrigido. Decidi então fazer o teste.
Instalei o SQL Server 2008 SP1 com CU10, build 2799

Fiz os mesmos testes utilizando o SQL Server 2008 SP1 com CU10, build 2799 e realmente conseguir atachar e/ou restaurar o banco após desabilitar a criptografia TDE. Segundo a Microsoft o erro ocorria porque o arquivo .ldf do banco ficava com referências do certificado.

Agora como sabemos o SQL Server 2008 SP2 só inclui o CU1 até CU8, então aconselho que quando for usar TDE utilize o SQL Server 2008 SP1 com CU10 ou então espere mais um tempo até sair o SP3 (se sair, hehehe), agora a outra é opção é NUNCA perder o certificado, assim você pode levar o banco para qualquer servidor utilizando o certificado.

Abraços,
Rodrigo Figueiredo
figueiredo.rodrigo@hotmail.com

Recuperando o banco SUSPECT criando um novo arquivo de LOG

Galera esse é um artigo relativamente simples e bem útil para o dia a dia do DBA, quem ainda não ficou de frente com um banco de dados corrompido?
Pois é irei mostrar uma maneira de recuperar um banco de dados em modo SUSPECT, criando um novo arquivo de LOG do banco. Claro que antes de realizar este procedimento tem que olhar o log do SQL Server e ver qual motivo o banco ficou suspect, se no log tiver a mensagem que não foi possível recuperar o arquivo de log e o banco foi marcado como suspeito então este scritp resolverá seu problema.

Este scritp é válido para o SQL Server 2000, pois o comando DBCC REBUILD_LOG foi descontinuado no SQL Server 2005 e 2008.

Segue script comentado:

--VOLTA BANCO SUSPECT CRIANDO OUTRO ARQUIVO DE LOG
/*
1° Passo -> Verificar porque o banco ficou suspect, se foi porque não conseguiu fazer recovery do arquivo de log
este script resolverá o problema
*/
--EXECUTAR TODOS OS SCRIPTS SEPARADAMENTE
--Executar este primeiro comando, com este configuração é possível alterar as tabelas de sistema
sp_configure 'allow updates',1
reconfigure with override
go
--Executar e verificar se o banco e as tabelas estão acessíveis
sp_resetstatus 'NomeBancoDeDados'
go
--Caso o comando acima não funcione, executa este comando que deixará o banco em emergency mode
Update sysdatabases set status = 32768   where name ='NomeBancoDeDados'
go
--Após o comando ser executado com sucesso, executar este comando para criar novo arquivo de log para o banco,
--passa o nome do banco e o caminho onde será criado o novo arquivo
DBCC Rebuild_log ('NomeBancoDeDados','C:\CAMINHO\NomeDoArquivo.ldf')
go
-- Aqui o banco já estará on line... Mas temos que verificar se existem erros de integridade
-- Vamos colocar o banco e modo single user
Use Neo
go
sp_dboption 'NomeBancoDeDados','single_user', true
go
--Executamos o comando para reparar os erros, caso existam
use master
go
dbcc checkdb ('NomeBancoDeDados',repair_allow_data_loss)
go
--Tirar o banco do modo single user
use Neo
go
sp_dboption 'NomeBancoDeDados','single_user', false
go
--Voltar este parametro para 0
sp_configure 'allow updates',0
reconfigure with override
go

Apenas explicando o parâmetro REPAIR_ALLOW_DATA_LOSS, quando uma página está corrompida ao usar este parâmetro no DBCC CHECKDB a página anterior simplesmente muda o ponteiro e aponta para a próxima página que estiver OK, então é possível que exista perda de dados. Então em qualquer situação de banco de dados corrompido o mais adequado é recuperar o backup.

Abraços,
Rodrigo Figueiredo
figueiredo.rodrigo@hotmail.com

Criptografia Transparente de Dados - Parte II

Bem pessoal, como disse na primeira parte deste tema mostrei como implantar a criptografia transparente de dados em um banco de dados, agora irei passar para vocês todos os testes que realizei.

Continuo usando o SQL Server 2008 Developer Edition com SP2, build 10.0.4000.

1° Teste: Desatachar o banco de dados criptografado e tentar atachar em outro servidor SQL que não existe nenhuma chave nem certificado de criptografia

USE master
GO   
sp_detach_db 'Criptografia'
GO

e em seguida tentarei atachar no outro servidor:

EXEC sp_attach_db @dbname = 'Criptografia',
    @filename1 = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Criptografia.mdf',
    @filename2 = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Criptografia_log.ldf'
GO

após executar este comando receberemos a seguinte mensagem do SQL Server:

Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint '0x2400EE11A47193208473BE1225A55AC13B4405B1'.

Para conseguirmos atachar o banco de dados criptografado em outro servidor é necessário criar a Master Key com a mesma senha do servidor onde o banco foi criptogrado, levar os arquivos do certificado e da chave criptografada para este servidor e em seguinda criar um certificado passando esses arquivos.

No servidor onde queremos atachar o banco executamos os seguintes scripts:

USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKeysenha'
GO

USE master
GO
CREATE CERTIFICATE CertificadoCriptografia
 FROM FILE = 'C:\Criptografia_certificate.cer'
 WITH PRIVATE KEY (FILE = 'C:\Criptografia_certkey.pvk', DECRYPTION BY PASSWORD = 'MasterKeysenha')
GO

após criado a Master Key e o certificado vamos tentar a atachar o banco novamente:

EXEC sp_attach_db @dbname = 'Criptografia',
    @filename1 = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Criptografia.mdf',
    @filename2 = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Criptografia_log.ldf'
GO

e receberemos a mensagem:

Command(s) completed successfully.

2° Teste: Fazer backup do banco criptografado e tentar restaurar o backup em outro servidor que não possui nenhuma chave nem certificado de criptografia

BACKUP DATABASE Criptografia TO DISK='C:\Backup Criptografia\Criptografia.bak'
GO

agora vamos tentar restaurar este backup no outro servidor:

RESTORE DATABASE Criptografia FROM DISK='C:\Backup Criptografia\Criptografia.bak'

após tentar restaurar o SQL exibe a seguinte mensagem:

Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint '0x2400EE11A47193208473BE1225A55AC13B4405B1'.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.


Para conseguir restaurar o backup em outro servidor é necessário realizar o mesmo procedimento relizado no teste 1. Tem que criar a Master Key e depois criar o certificado, assim o restore é concluído com sucesso.


Com estes dois testes foi possível ver que a criptografia transparente de dados realmente funciona e é bastante interessante, pois ainda não existia nenhuma criptografia interna do SQL Server a nível dos arquivos de dados e de log.

Na próxima parte iremos realizar os teste mais interessantes, mostrarei como desabilitar a TDE de um banco de dados se é possível atachar e/ou restaurar o banco de dados em outro servidor após desabilitar a criptografia.

Abraços,
Rodrigo Figueiredo
figueiredo.rodrigo@hotmail.com

Criptografia Transparente de Dados - Parte I

Passei essa semana testando a nova funcionalidade de criptografia do SQL Server 2008, a TDE(Transparent Data Encryption), comecei a ler e fiquei muito interessado pois era exatamente o que eu precisava, pois ela funciona a nível dos arquivos (.mdf e .ldf) do banco de dados.

A TDE criptografa os arquivos isso quer dizer que após o acesso ao banco de dados todas as informações estarão disponíveis, mas o problema esta justamente ai, para ter este acesso...

Nesta primeira parte veremos como implementar esta funcionalidade em um banco de dados, nos próximos post veremos os detalhes dos testes realizados.

Estou utilizando o SQL Server 2008 Developer Edition com SP2, build 10.0.4000.

Primeiramente criei um banco de dados simples com somente uma tabela:

USE Master
GO

CREATE DATABASE Criptografia
GO

USE Criptografia
GO

CREATE TABLE Pessoa(
 Codigo int IDENTITY(1,1) NOT NULL PRIMARY KEY,
 Nome varchar(20) NOT NULL,
 Idade int NULL) ON [PRIMARY]
GO

Em seguida inserir alguns registros:

INSERT INTO Pessoa VALUES ('Rodrigo Figueiredo',25)
GO 100

Vamos começar agora a implantar a TDE no banco Criptografia:

--Primeiro passo: Criar Master Key encryption no banco master
USE master
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKeysenha'
GO

/* Segundo passo: Criar um certificado no banco master.
 A chave privada deste certificado será protegida pela Master Key*/
USE master
GO

CREATE CERTIFICATE CertificadoCriptografia
WITH SUBJECT = 'Certificado Criptografia'
GO

Agora que já criamos a Master Key e o Certificado podemos criptografar o banco de dados:

/* Terceiro passo: criar uma chave de criptografia para a base de dados,
   usando o certificado criado anteriormente.
   Podemos escolher o algoritmo de criptografia que queremos usar:
   triple DES, AES 128, AES 192 ou AES 256 */

USE Criptografia
GO

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE CertificadoCriptografia
GO

/* Este comando vai retornar um Warning,
 Lembrando que devemos fazer um backup de nosso certificado imediatamente. */

USE master
GO

BACKUP CERTIFICATE CertificadoCriptografia
TO FILE = 'C:\Backup Criptografia\Criptografia_certificate.cer'
WITH PRIVATE KEY (FILE = 'C:\Backup Criptografia\Criptografia_certkey.pvk',
 ENCRYPTION BY PASSWORD = 'MasterKeysenha');
GO

--Quarto passo: habilitar o TDE na nossa base
ALTER DATABASE Criptografia
SET ENCRYPTION ON
GO

Neste momento o banco Criptografia está criptografado utilizando o algoritmo AES_256. Se executarmos a consulta:

SELECT DB_NAME(e.database_id) AS DatabaseName,
            e.encryption_state,
    CASE e.encryption_state
                WHEN 0 THEN 'No database encryption key present, no encryption'
                WHEN 1 THEN 'Unencrypted'
                WHEN 2 THEN 'Encryption in progress'
                WHEN 3 THEN 'Encrypted'
                WHEN 4 THEN 'Key change in progress'
                WHEN 5 THEN 'Decryption in progress'
    END AS encryption_state_desc,
   key_algorithm +'_'+ convert(nvarchar,key_length) [Algoritmo],
            c.name [Nome Certificado],
            set_date [Data Criptografia]
    FROM sys.dm_database_encryption_keys AS e
    LEFT JOIN master.sys.certificates AS c
    ON e.encryptor_thumbprint = c.thumbprint

Veremos que dois bancos estão criptografados usando o algoritmo AES_256, o banco Criptografia utilizando certificado criado por nós e o banco tempdb sem utilizar nenhum certificado. O banco tempdb é criptografado sempre que qualquer banco da instância for criptgrafado, isso acontece porque o tempdb é utilizado por todos os banco de dados existentes na instância.

Como sabemos toda criptogafia tem um custo de I/O e principalmente de CPU, a criptografia após aplicada no banco de dados funciona da seguinte maneira: Toda vez no momento que a informação é gravada no disco ela é criptografada e sempre que for lida do disco para a mémoria ela é descriptograda, isso gera um custo de I/O, mas praticamente imperceptível. O custo maior fica por conta do processamento, devido ao algoritmo usado, quanto mais seguro for o algoritmo maior será o custo de CPU.

Bem galera esta foi a primeira parte e mostrei apenas como implementar TDE em um banco de dados, nós próximos posts falarei dos testes feitos, de backup e restore do banco de um servidor para outro e também detach e attach entre servidores utilizando TDE.

Abraços,
Rodrigo Figueiredo
figueiredo.rodrigo@hotmail.com