Tag Archives: server

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

Anúncios

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

Erro DTS Designer SQL Server Management Studio 2008 – SQL Server 2000 DTS Designer components are required to edit DTS packages. Install the special Web download, “SQL Server 2000 DTS Designer Components” to use this feature.

Ainda me surpreende em pleno ano de 2013, com o SQL Server 2012 já estável e um service pack lançado alguns lugares estarem ainda com o SQL Server 2000, não entrarei em detalhes do por que disso (Custo de mudança, licença, pessoas, hardware…Enfim!) mas de vez em quando temos de aceitar o fato com as coisas que temos de lidar. Ontem precisei fazer algumas alterações em um pacote DTS, Sim! DTS e não DTSX.

O primeiro passo a ser feito é ter o designer de pacotes DTS em sua maquina, aqui eu só tinha uma versão Enterprise do SQL Server 2008R2 tanto em relação Database Engine como em relação as ferramentas (Business Inteligence Development Studio e SQL Server Management Studio) o que faz com que o primeiro passo seja baixar tal designer. O mesmo pode ser obtido neste link: Aqui

O arquivo especifico que baixei foi o: SQLServer2005_DTS.msi de 5.0MB

E realizei a instalação do mesmo, conforme solicitado fiz um reboot na maquina e para minha surpresa ao tentar pelo SQL Server Management Studio -> Management -> Legacy -> Data Transformation Services -> Botão direito do mouse -> Open Package File, eu tive o erro abaixo:

SQL Server 2000 DTS Designer components are required to edit DTS packages. Install the special Web download, “SQL Server 2000 DTS Designer Components” to use this feature. (Microsoft.SqlServer.DtsObjectExplorerUI)SSMS

Ou seja, mesmo depois de instalado o erro de que o componente não foi encontrado persistia…. Depois de algumas tentativas frustradas de solução e muita pesquisa, encontrei o maravilho link cujo qual foi a solução do meu problema.

Uma solução simples, mas que talvez seja difícil de ser encontrada a força por uma pessoa de banco de dados…

A solução consiste em uma simples alteração de uma variável de ambiente do windows. No meu caso estou utilizando o windows 7 e os passos foram os seguintes:

1 – Abrir o painel de controle
01

2 – Clicar em “Sistemas e segurança”
02

3 – Clicar em “Sistema”
03

4 – Clicar em “Configurações avançadas do sistema”
04

5 – Ir para a aba “Avançados” e clicar em “Variáveis de ambiente”
05

6 – Buscar a variável “Path”
06

7 – E alterar seu conteúdo de forma que a linha

C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\;

Fique acima da linha

C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\;

Ou seja, em minha maquina o conteudo da mesma estava:

C:\Program Files (x86)\NVIDIA Corporation\PhysX\Common;
%SystemRoot%\system32;
%SystemRoot%;
%SystemRoot%\System32\Wbem;
%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\;
C:\Program Files\Intel\DMIX;
C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\;
C:\Program Files\Microsoft SQL Server\100\Tools\Binn\;
C:\Program Files\Microsoft SQL Server\100\DTS\Binn\;
C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\;
C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\;
C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\;
C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\;
C:\Program Files (x86)\Borland\StarTeam SDK 11.0\Lib;
C:\Program Files (x86)\Borland\StarTeam SDK 11.0\Bin

E foi alterado para:

C:\Program Files (x86)\NVIDIA Corporation\PhysX\Common;
%SystemRoot%\system32;
%SystemRoot%;
%SystemRoot%\System32\Wbem;
%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\;
C:\Program Files\Intel\DMIX;
C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\;
C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\;
C:\Program Files\Microsoft SQL Server\100\Tools\Binn\;
C:\Program Files\Microsoft SQL Server\100\DTS\Binn\;
C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\;
C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\;
C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\;
C:\Program Files (x86)\Borland\StarTeam SDK 11.0\Lib;
C:\Program Files (x86)\Borland\StarTeam SDK 11.0\Bin

Erro FileStream .FilePath()

Resolvi escrever este forum rapido ao pegar um problema no forum de um outro usuario, ao pesquisar na internet não encontrei muitas referencias portanto deixo aqui a solução.
No SQL Server, na maioria dos casos os comandos não são CASE_SENSITIVE, ou seja, um “CREATE TABLE” funciona normalmente assim como um “create table” porem para toda regra há sua exceção e o PathName do FileStream é uma delas:

O erro gerado foi o seguinte:
Msg 4121, Level 16, State 1, Line 1
Cannot find either column “FS_ARQUIVO” or the user-defined function or aggregate “FS_ARQUIVO.pathname”, or the name is ambiguous.

O Script utilizado foi:

CREATE DATABASE FileStreamDB ON PRIMARY  (

 NAME = FileStreamDB_data,

    FILENAME = 'D:\Filestream\FileStreamDB_data.mdf'),

FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM

  ( NAME = FileStreamDB_FILE,

    FILENAME = 'D:\Filestream\arquivos')

LOG ON

  ( NAME = 'FileStreamDB_log',

    FILENAME = 'D:\Filestream\FileStreamDB_log.ldf');
GO
    
USE FileStreamDB;
GO
CREATE TABLE FSDESC (
IDFSDESC INT IDENTITY NOT NULL,
DESCRICAO VARCHAR(50) NOT NULL
)
GO
ALTER TABLE FSDESC ADD CONSTRAINT PK_FSDESC PRIMARY KEY (IDFSDESC)
GO

USE FileStreamDB;
GO
CREATE TABLE FSFILE (
ID_FSARQUIVO INT IDENTITY NOT NULL,
ID_DESC INT NOT NULL,
ID_GUID UNIQUEIDENTIFIER ROWGUIDCOL UNIQUE NOT NULL,
FS_ARQUIVO VARBINARY(MAX) FILESTREAM NULL
)
FILESTREAM_ON FileStreamGroup1;

ALTER TABLE FSFILE ADD CONSTRAINT PK_IDFSARQUIVO PRIMARY KEY (ID_FSARQUIVO)
GO
ALTER TABLE FSFILE ADD CONSTRAINT FK_DESC_ARQUIVO FOREIGN KEY (ID_DESC) REFERENCES FSDESC(IDFSDESC);
GO

--insere descrição
INSERT INTO FSDESC (DESCRICAO)
VALUES('IMAGEM .JPG'),('E-BOOK .PDF'),('OFFICE .DOC')
GO

--insere documento jpg, pdf, doc etc...
INSERT INTO FSFILE (ID_DESC,ID_GUID,FS_ARQUIVO)
SELECT 1,NEWID(),*
FROM OPENROWSET (BULK 'D:\FileStream\Elo_Perdido.jpg',SINGLE_BLOB) AS Document
GO
INSERT INTO FSFILE (ID_DESC,ID_GUID,FS_ARQUIVO)
SELECT 3,NEWID(),*
FROM OPENROWSET (BULK 'D:\FileStream\ArquivoWord.docx',SINGLE_BLOB) AS Document
GO

SELECT [FS_ARQUIVO].PathName()  FROM dbo.FSFILE --> Executado com sucesso
SELECT [FS_ARQUIVO].pathname()  FROM dbo.FSFILE --> Erro

Resumo:
Sem erro:

SELECT [FS_ARQUIVO].PathName()  FROM dbo.FSFILE

Com erro:

SELECT [FS_ARQUIVO].pathname()  FROM dbo.FSFILE

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

Overlaping de dados – QO

O Query optimizer (QO) vem evoluindo ao londo dos anos e das versões do SQL Server. Dentre algumas melhorias feitas esta a identificação de Overlaping de valores.

O que é o OverLaping?
Overlaping ocorre quando em um predicado de uma query há sobreposição de valores.

Exemplo:
001_Overlaping

No QO é possivel visualizar pelo plano de execução da query que tal situação é tratada pelo SQL Server criando apenas um Seek Predicate, conforme imagem abaixo:
002_Overlaping

Abaixo o script utilizado para a demonstração:

USE tempdb
GO

IF(EXISTS(SELECT TOP 1 1 FROM sys.tables WHERE name LIKE 'DemonstracaoQO'))
	DROP TABLE DemonstracaoQO

CREATE TABLE DemonstracaoQO
(
	Numero BIGINT
)
GO

INSERT INTO DemonstracaoQO(Numero)
SELECT (CHECKSUM(NEWID()))/10000000
GO 100

SELECT MIN(Numero) As Minimo, MAX(Numero) As Maximo
FROM DemonstracaoQO
GO

SELECT Numero
FROM DemonstracaoQO
WHERE
	Numero BETWEEN -206 AND 100 --Menor valor AND algum numero no meio entre menor e maior
	OR Numero BETWEEN 50 AND 208 -- Algum numero entre os 2 do between acima e o maior valor

Então podemos afirmar que o Query Optimizer é inteligente o sulficiente para identificar sobreposição de predicados e reduzilos ao maximo melhorando assim a performance de sua consulta, aonde no exemplo acima duas condições foram transformadas em apenas uma.

Como criar um LinkedServer para uma porta especifica

Esses dias, na empresa aonde trabalho, temos uma conexão via Linked Server com uma outra empresa para a execução remota de um procedure, porem, essa empresa remota iria bloquear a sua porta 1433 do SQL Server e colocar a instancia em outra porta, o que causaria uma quebra em nossa comunicação.

Pensei em algumas alternativas mais simples como simplismente abstrair tal alteração e deixar o SQL Server Browser se virar do outro lado, porem, obviamente o mesmo não funciona devido ao meio de acesso, o que acaba com essa possibilidade, então, depois de algumas pesquisas, consegui criar um linked server para uma porta especifica.

Tentei varias coisas, até uma unica string de conexão configurando todo o linked server em apenas um parametro, porem de forma alguma funcionou, não consegui tambem criar um linked server com tal caracteristica pela ferramenta grafica (SSMS 2008/2012), então, executei o comando:

EXEC sp_addlinkedserver
@server=’NomeDoLinkedServer’,
@srvproduct=”,
@provider=’SQLNCLI’,
@datasrc=’tcp:ip1.ip2.ip3.ip4,Porta’

Funcionou perfeitamente e acabou sendo mais facil que todas as outras tentativas rs..Enfim, espero que possa ajuda quem esteja precisando.