Category Archives: Administração

Movendo Arquivos dos Databases – SQL Server

OnlyWhatMatters

Neste artigo irei mostrar um método para alterar o caminho dos arquivos de log e data dos databases.

Este método pode ser utilizado do SQL Server 2000 até a mais atual, o SQL Server 2012.

Este método contém os seguintes passos:
• Obter o diretório atual dos arquivos;
• Alterar o database para SINGLE_USER, para fechar todas as conexões no banco;
• Alterar o status do database para OFFLINE para que ele não leia mais os arquivos;
• Realizar o comando T-SQL para fazer o “detach” no database, ou seja, durante este processo o db engine ignora a existência dessa base;
• Mover os arquivos para o novo diretório;
• Realizar o comando T-SQL para fazer o “attach” no database;
• Alterar o status do database para ONLINE para subir o database e para ler os arquivos;
• Alterar o database para MULTI_USER para que outras…

Ver o post original 289 mais palavras

Alterando o default collation de uma instancia SQL Server

Apesar de N referencias na internet, faço do meu blog um arquivo pessoal para que alem dos outros eu tambem possa ter um repositorio online de codificação, então este post é apenas mais um na internet visando este proposito pessoal.

Para se alterar o collation default de uma instancia:

1 – Pare o serviço correspondente do SQL Server;

2 – Abra um prompt de comando (Executar -> CMD)

3 – Vá para o diretorio de instalação da instancia do SQL Server que voce deseja alterar. No meu caso: cd C:\Program Files\Microsoft SQL Server\MSSQL10.SQL200802\MSSQL\Binn

4 – Execute o comando: sqlservr -m -sNomeDaInstancia -T4022 -T3659 -q”NomeDoNovoCollation”

Será executado N comandos e reconstruções de indices, porem no final uma mensagem de sucesso conforme abaixo deve aparecer informando que a alteração foi realizada sem maiores problemas.

Foto 01

FORCEPLAN – Utilização e explicação

Existe uma opção no SQL Server denominada FORCEPLAN que faz com que o SQL Server execute os joins na sequencia em que foram escritos na query.

O SQL Server Query Optimizer segue algumas heurísticas de otimização com a ajuda nas estatísticas para que seja encontrada a melhor tabela a ser lida primeiro para a conclusão de uma consulta, essa regra tende a ser a menor caso nenhum filtro seja aplicado. Abaixo um script e fotos da execução do FORCEPLAN.

--Object creation
IF(OBJECT_ID('Tabela01') IS NOT NULL)
	DROP TABLE Tabela01

CREATE TABLE Tabela01
(
	Id	INT	IDENTITY(1,1)	PRIMARY KEY,
	Nome	VARCHAR(100)	NOT NULL
)
	
IF(OBJECT_ID('Tabela02') IS NOT NULL)
	DROP TABLE Tabela02

CREATE TABLE Tabela02
(
	Id	INT	IDENTITY(1,1)	PRIMARY KEY,
	Sobrenome1	VARCHAR(100)	NOT NULL
)
	
IF(OBJECT_ID('Tabela03') IS NOT NULL)
	DROP TABLE Tabela03

CREATE TABLE Tabela03
(
	Id	INT	IDENTITY(1,1)	PRIMARY KEY,
	Sobrenome2	VARCHAR(100)	NOT NULL
)
GO

--Data load
INSERT INTO Tabela01(Nome)
SELECT CHECKSUM(NEWID())
GO 5000

INSERT INTO Tabela02(Sobrenome1)
SELECT CHECKSUM(NEWID())
GO 10000

INSERT INTO Tabela03(Sobrenome2)
SELECT CHECKSUM(NEWID())
GO 15000

--Sql query

--Sem forceplan
SELECT a.Sobrenome2, b.Nome, c.Sobrenome1
FROM Tabela03 a
INNER JOIN Tabela01 b
	ON (a.Id = b.Id)
INNER JOIN Tabela02 c
	ON (a.Id = c.Id)

Sem_Force_Plan

--Com forceplan
SET FORCEPLAN ON

SELECT a.Sobrenome2, b.Nome, c.Sobrenome1
FROM Tabela03 a
INNER JOIN Tabela01 b
	ON (a.Id = b.Id)
INNER JOIN Tabela02 c
	ON (a.Id = c.Id)

SET FORCEPLAN OFF

Com_Force_Plan

Ou seja, é possível observar que com o FORCEPLAN OFF a primeira tabela a ser lida foi a 01 pois possui apenas 5.000 linhas enquanto que após a ativação do FORCEPLAN para ON a primeira tabela a ser lida foi a 03 mesmo possuindo 15.000 linhas pois é a primeira tabela especificada em meu JOIN.

Outra característica interessante é que fisicamente com o FORCEPLAN OFF o método de join escolhido foi o MERGE JOIN, porem ao fazermos a alteração para FORCEPLAN ON temos apenas nasted loops, ou seja, ao habilitar o FORCEPLAN teremos priorização para Nasted Loops a não ser que seja especificado outro meio de join via join hint conforme script abaixo.

--Com forceplan e join hint
SET FORCEPLAN ON

SELECT a.Sobrenome2, b.Nome, c.Sobrenome1
FROM Tabela03 a
INNER MERGE JOIN Tabela01 b
	ON (a.Id = b.Id)
INNER MERGE JOIN Tabela02 c
	ON (a.Id = c.Id)

SET FORCEPLAN OFF

Com_Force_Plan_E_Join_Hint

Sua utilização ou não vai para cada cenário, eu particularmente não gosto pois estamos inibindo o Query Optimizer de fazer o seu trabalho alem de que será necessário muito mais conhecimento técnico e aprofundado do desenvolvedor no momento da programação.

Query últimos Backups – SQL Server Pt. 2

OnlyWhatMatters

Como dito no último post, irei mostrar uma simples query que retornará onde os Backups foram salvos e o tamanho dos mesmos.

A query irá retornar os seguintes campos:

Database_Name: o nome do database.
Physical_Device_Name: o local e nome do arquivo do backup realizado. Caso o Backup tenha sido salvo em uma fita, o resultado será parecido como esse: {7878439E-5007-4EB3-96AC-447AEE19EBE6}1
Size: o tamanho do backup em MB.
Backup_Start_Date: horário de início do backup.
Backup_Finish_Date: horário de término do backup.
Seconds_Duration: o tempo, em segundos, da realização do backup.
Backup_Type: tipo do Backup.
Server_Name: nome do servidor onde foi realizado o backup.

Nessa query estou utilizando um parâmetro de dias x backup_start_date para listar os backups.

Links úteis:
BackupSet
BackupMediaFamily

Ver o post original

Query últimos Backups – SQL Server Pt. 1

OnlyWhatMatters

Neste post irei mostrar uma query que mostra o último Backup realizado em todos os databases, exceto o tempdb, pubs, northwind e o model.

* Em um próximo post irei mostrar o tamanho dos backups realizados e onde os backups foram salvos.

A query retorna os seguintes campos:

Servidor – Instancia: retorna o servidor (hostname) e a instância dos databases.
Database_Name: nome do database
Full_Dias: diferença de dias do último backup FULL realizado.
Full_Termino: data e hora do término do backup FULL.
Full_Tamanho_MB: tamanho do backup FULL em MB.
Diff_Dias: diferença de dias do último backup DIFFERENTIAL realizado.
Diff_Termino: data e hora do término do backup DIFFERENTIAL.
Diff_Full_Diff: diferença dos dias entre o backup FULL e o DIFFERENTIAL.
Diff_Tamanho_MB: tamanho do backup DIFFERENTIAL em MB.
Tran_Minutos: diferença em minutos do último…

Ver o post original 27 mais palavras

XML – RAW – 01

Dando inicio a uma serie de pequenos artigos sobre XML e suas possiveis variações em SQL Server vou começar falando sobre a mais simples de todas, denominada FOR XML RAW. Nela o SQL Server faz uma formatação padrão nos dados e os joga em um XML simples conforme podemos observar abaixo:

--Criando a tabela com os dados de exemplo
IF(OBJECT_ID('TabelaDadosXML') IS NOT NULL)
	DROP TABLE TabelaDadosXML

CREATE TABLE TabelaDadosXML
(
	Nome		VARCHAR(100),
	SobreNome	VARCHAR(100),
	DataDeNasc	DATETIME
)

--Inserindo dados de teste
INSERT INTO TabelaDadosXML(Nome, SobreNome, DataDeNasc)
VALUES
('Nome01', 'SobreNome01', '19910921'),
('Nome02', 'SobreNome02', '19930909'),
('Nome03', 'SobreNome03', '19931009'),
('Nome04', 'SobreNome04', '19931109'),
('Nome05', 'SobreNome05', '19931209'),
('Nome06', 'SobreNome06', '19850109'),
('Nome07', 'SobreNome07', '19860109'),
('Nome08', 'SobreNome08', '19880109'),
('Nome09', 'SobreNome09', '19980109'),
('Nome10', 'SobreNome10', '19980909')

--RAW
SELECT
	a.Nome,
	a.SobreNome,
	DATEDIFF(YEAR, a.DataDeNasc, GETDATE()) As Idade
FROM TabelaDadosXML a
FOR XML RAW
/*
<row Nome="Nome01" SobreNome="SobreNome01" Idade="22" />
<row Nome="Nome02" SobreNome="SobreNome02" Idade="20" />
<row Nome="Nome03" SobreNome="SobreNome03" Idade="20" />
<row Nome="Nome04" SobreNome="SobreNome04" Idade="20" />
<row Nome="Nome05" SobreNome="SobreNome05" Idade="20" />
<row Nome="Nome06" SobreNome="SobreNome06" Idade="28" />
<row Nome="Nome07" SobreNome="SobreNome07" Idade="27" />
<row Nome="Nome08" SobreNome="SobreNome08" Idade="25" />
<row Nome="Nome09" SobreNome="SobreNome09" Idade="15" />
<row Nome="Nome10" SobreNome="SobreNome10" Idade="15" />
*/

Ou seja, o xml veio sem formatação nenhuma, colocando todos os dados como atribudos de uma TAG chamada ROW
Para se alterar o nome desta tag basta alterar para:

SELECT
	a.Nome,
	a.SobreNome,
	DATEDIFF(YEAR, a.DataDeNasc, GETDATE()) As Idade
FROM TabelaDadosXML a
FOR XML RAW('LinhaDeDados')

Fazendo com que o seguinte output seja gerado

/*
<LinhaDeDados Nome="Nome01" SobreNome="SobreNome01" Idade="22" />
<LinhaDeDados Nome="Nome02" SobreNome="SobreNome02" Idade="20" />
<LinhaDeDados Nome="Nome03" SobreNome="SobreNome03" Idade="20" />
<LinhaDeDados Nome="Nome04" SobreNome="SobreNome04" Idade="20" />
<LinhaDeDados Nome="Nome05" SobreNome="SobreNome05" Idade="20" />
<LinhaDeDados Nome="Nome06" SobreNome="SobreNome06" Idade="28" />
<LinhaDeDados Nome="Nome07" SobreNome="SobreNome07" Idade="27" />
<LinhaDeDados Nome="Nome08" SobreNome="SobreNome08" Idade="25" />
<LinhaDeDados Nome="Nome09" SobreNome="SobreNome09" Idade="15" />
<LinhaDeDados Nome="Nome10" SobreNome="SobreNome10" Idade="15" />
*/

Microsoft Dynamics CRM MVP²

Parabéns novamente a um dos profissionais mais competentes que já conheci.

Tiago Michelini Cardoso

Pessoal,

Venho mais uma vez agradecer…

A Deus, minha esposa, familiares, amigos, colegas de trabalho e principalmente a comunidade de Dynamics CRM que mais uma vez me proporcionou a premiação de Most Valuable Professional (MVP) para o produto Microsoft Dynamics CRM, ao qual dedico grande parte do meu tempo e trabalho.

Posso dizer novamente que trata-se do dia mais importante e feliz de minha vida profissional, visto que por mais uma tive a felicidade de receber o prêmio e ter a mesma alegria ao saber.

Ações como esta nos fazem cada vez mais crescer e querer ajudar mais e mais, eu realmente estou muito feliz e só tenho a agradecer.

Que venham os próximos anos, espero que continue contribuindo e merecendo participar deste grupo, formado por excelentes profissionais que só mostram o tamanho de sua importância.

MVP2

Não é preciso ser os melhores e sim os que mais querem ajudar!

Go…

Ver o post original 13 mais palavras

Arquitetura de componentes SQL Server

Neste post rapido apresento a arquitetura de componentes em um servidor SQL. Uma das duvidas mais comuns que vejo é a confusão entre o que é base de dados, o que é instancia e como elas estão conectadas. Alem do schema, que muitas pessoas não conhecem.

Arquitetura SQL Server

De forma rapida…

– Um servidor pode possui uma ou mais instancias. Mas uma instancia pertence exclusivamente a um servidor.
– Uma instancia pode ter uma ou mais bases de dados. Mas uma base de dados pertence exclusivamente a uma instancia.
– Uma base de dados pode ter um ou mais schema. Mas um schema pertence exclusivamente a uma base de dados.
– Um schema pode ter um ou mais objetos. Mas um objeto pertence exclusivamente a um schema.
– As instancias pode ser default (Limite de uma por servidor) ou nomeadas.
– Ainda que voce não trabalhe com schemas, o SQL trabalha com o schema padrão: DBO
– Voce possui ao menos 4 bases visiveis: Master, Model, MSDB e TEMPDB.

Compressão de dados – Row/Linha

Neste artigo irei comentar um pouco sobre a compressão de dados a nivel de linha disponivel no SQL Server.

Primeiramente o script utilizado para testes e abaixo as explicações:

--Cria a tabela
IF(OBJECT_ID('Vendas') IS NOT NULL)
	DROP TABLE Vendas

CREATE TABLE Vendas
(
	Id INT IDENTITY(1,1) PRIMARY KEY,
	Id_Funcionario INT,
	Id_Cliente INT,
	Id_Venda INT
)
GO

INSERT INTO Vendas(Id_Funcionario, Id_Cliente, Id_Venda)
SELECT CHECKSUM(NEWID()), CHECKSUM(NEWID()), CHECKSUM(NEWID())
GO 5000

--Vendo espaço sem compressao
EXEC sp_spaceused 'Vendas'

--Seta compressao a nivel de linha
ALTER TABLE Vendas REBUILD WITH (DATA_COMPRESSION = ROW)

--Vendo espaço com compressao
EXEC sp_spaceused 'Vendas'

/*
				name	rows	reserved	data	index_size	unused
Sem compressão: Vendas	5000    208 KB		128 KB	24 KB		56 KB
Com compressão: Vendas	5000    144 KB		104 KB	24 KB		16 KB
*/

Podemos ver claramente que houve uma redução do espaço utilizado pela tabela sem uma redução do numero de linhas.

O que aconteceu?
O SQL Server identificou todos os valores presentes na tabela e passou a ocupar apenas o numero de bytes necessarios para o armazenamento daquele valor. Por padrão o tipo de dados INT ocupa 4 bytes, porem, caso o valor a ser guardado seja inferior a 255 por exemplo, apenas 1 byte será necessario para seu armazenamento, fazendo com que seja economizado 3 bytes de espaço em disco para aquela pagina.

Todos os tipos de dados podem ser utilizados na compressão?
Sim e não! Sim pelo fato de que voce não terá erros na execução do script de compressão independente dos tipos de dados existentes na tabela, porem alguns deles simplismente não surtirão efeitos nenhum, como é o caso dos datatypes de textos (Varchar por exemplo). Veja o script abaixo:

--Cria a tabela
IF(OBJECT_ID('Vendas') IS NOT NULL)
	DROP TABLE Vendas

CREATE TABLE Vendas
(
	Id INT IDENTITY(1,1) PRIMARY KEY,
	Funcionario VARCHAR(100),
	Cliente VARCHAR(100),
	Venda VARCHAR(100)
)
GO

INSERT INTO Vendas(Funcionario, Cliente, Venda)
SELECT REPLICATE('a', 100), REPLICATE('a', 100), REPLICATE('a', 100)
GO 5000

--Vendo espaço sem compressao
EXEC sp_spaceused 'Vendas'

--Seta compressao a nivel de linha
ALTER TABLE Vendas REBUILD WITH (DATA_COMPRESSION = ROW)

--Vendo espaço com compressao
EXEC sp_spaceused 'Vendas'

/*
				name	rows	reserved	data	index_size	unused
Sem compressão: Vendas	5000    1680 KB		1600 KB	24 KB		56 KB
Com compressão: Vendas	5000    1616 KB		1600 KB	16 KB		0 KB
*/

Vejam que o tamanho dos dados não houve redução mesmo com a compressão de linha ativada, portanto é necessario avaliar muito bem cada tabela antes de efetivamente sair ativando a compressão de linha em todas as tabelas.

Para maiores informações visite o link oficial BOL: http://msdn.microsoft.com/en-us/library/cc280576.aspx

Procedure sp_configure “Affinity Mask”

Uma das configurações que podemos setar pela procedure de sistema “EXEC sp_configure” é a opção “Affinity Mask”. Neste post descrevo um pouco sua usabilidade alem de alguns testes praticos mostrando o efeito da mesma.

O que é a procedure de sistema “sp_configure”?
É uma procedure aonde é possivel setar N configurações em relação a instancia como por exemplo processadores, memorias, conexões dentre outras.

E a opção “Affinity Mask”?
Esta opção se diz respeito a qual processador fisico (Ou logico, HyperThreading) o SQL Server irá trabalhar.
Mas, como setar um valor para ele mapeando os processadores sendo que é possivel apenas passar um numero?
Simples! Essa configuração fica em uma regua de potencia de 2. mais ou menos da seguinte forma:
Se temos 8 processadores, temos então a seguinte situação

Foto01

Portanto, se quisermos utilizar os processadores parem (P2, P4, P6 e P8) devemos somar seus valores da regua, ficando 2 + 8 + 32 + 128 = 170,

então o valor a ser passado para o parametro de “Affinity Mask” é 170.

Caso voces olhem agora essa configuração é muito provavel que ela esteja como 0, fazendo com que o proprio SQL Server gerencie isso, e este valor não deve ser alterado a não ser que seja solicitado pelo time de suporte da Microsoft ou então voce não terá suporte.

Alguns testes praticos:

1 – Primeiramente eu baixei a ferramenta coreinfo.exe para poder visualisar algumas informações sobre os processadores da maquina cuja qual estou escrevendo este post.
a. Link para download: http://technet.microsoft.com/en-us/sysinternals/cc835722.aspx
b. Como executar: Extraia o .exe para uma pasta (D:\ por exemplo) e execute: d:\coreinfo.exe pelo command prompt (Executar -> CMD)

Uma das informações relevantes é que os processadores apontados pelo task manager são fisicos ou hyperthereading, mas, qual é qual? E qual o SQL Server priorizará?
O SQL Server tende a utilizar mais os fisicos e isso pode facilmente ser visto, basta deixar um looping em execução, conforme script abaixo

WHILE (1=1)
	SELECT 1+1

Que voce verá os processadores fisicos sendo mais utilizados. Veja o print dos processadores com o looping em execução.

Foto02

Todo caso, é interessante esta analise pelo coreinfo.exe, aonde é possivel ver tais informações na seguinte parte:

Foto03

Ou seja, o que é fisico, o que é logico e como eles estão agrupados.

Agora, fazendo uma alteração no meu “Affinity Mask” para 2, ou seja, utilizar apenas um processador fisico, pelo script:

EXEC sp_configure 'affinity mask', 1
RECONFIGURE WITH OVERRIDE

E executando o mesmo looping anterior, podemos ver claramente a utilização da primeira CPU (HyperThreading) sendo priorizada, os outros tambem são claro por outros processos tanto da maquina quanto do proprio SQL Server.

Foto04