Blog do GB: 2012

quinta-feira, 29 de novembro de 2012

Memory Grant em VARCHAR(8000)

Este é um dos posts sobre os exemplos apresentados na palestra sobre modelagem no 24 Hours of PASS - PT. Você pode ver a série de posts em Impactos da Modelagem na Performance do SQL Server.

Esta foi uma dica do Fabiano para colocarmos como exemplo na palestra.

O Otimizador de Consultas (QO - Query Optimizer) do SQL Server estima quanto de memória a execução de uma consulta necessitará. Isto é feito, p.e., para que operações de intenso acesso aos dados, como ordenações e agrupamentos, possam trabalhar com estes dados em memória, evitando o acesso a disco, o que causaria uma acentuada queda na performance. Para estimar esta quantidade de RAM, o QO leva em conta uma série de fatores, dentre os quais pode entrar o tamanho dos dados que serão trabalhados. Se uma tabela é declarada com muitas colunas varchar(8000), o SQL Server não sabe quanto espaço é usado em cada linha para esta coluna, então toma uma média - 4.000 bytes de espaço usado por linha. Então a estimativa do QO pode ser muito maior do que o tamanho da massa de dados - decorrente de uma modelagem "preguiçosa". Isto incorre em maior uso de memória e, em cenários de pressão de memória no servidor, em contenção ocorrendo entre as consultas - consultas bloqueadas esperando a liberação da memória estimada para sua execução.

O script a seguir demonstra isto. Copie ele para uma nova query no Management Studio (não esqueça de mudar para um banco de testes), vá executando os pedaços de código entre os comentários e veja os resultados. Qualquer dúvida posta uma pergunta!

/* ===============================================================================================================================
   Alocação errada de RAM devido a má modelagem das colunas varchar.
   O Query Optimizer (QO) para execução de determinadas consultas aloca RAM baseando-se no tamanho médio dos dados das colunas.
   No exemplo, ao usar varchar(8000), você estará informando ao QO que o tamanho médio destas colunas é 4000 bytes.
   Além disto, se o SQL Server está sob pressão de memória, pedidos de alocação de grandes quantias de RAM podem gerar bloqueios
   (waits) por liberação desta quantidade de memória, o que pode impactar severamente a performance de execução das queries.
   =============================================================================================================================== */

-- Vamos simular um SQL Server com pressão de memória

EXEC sp_configure 'max server memory'
EXEC sp_configure 'show advanced options', 1
reconfigure
EXEC sp_configure 'max server memory', 1024 -- 1GB de RAM para o SQL Server
reconfigure
GO

DROP TABLE TestMemoryGrant8000
CREATE TABLE TestMemoryGrant8000(ID  INT IDENTITY (1,1),
                             Col1 VarChar(8000) DEFAULT NEWID() PRIMARY KEY,
                             Col2 VarChar(8000) DEFAULT NEWID(),
                             Col3 VarChar(8000) DEFAULT NEWID(),
                             Col4 VarChar(8000) DEFAULT NEWID(),
                             Col5 VarChar(8000) DEFAULT NEWID(),
                             Col6 VarChar(8000) DEFAULT NEWID(),
                             Col7 VarChar(8000) DEFAULT NEWID(),
                             Col8 VarChar(8000) DEFAULT NEWID(),
                             Col9 VarChar(8000) DEFAULT NEWID(),
                             Col10 VarChar(8000) DEFAULT NEWID())
GO

INSERT INTO TestMemoryGrant8000 DEFAULT VALUES
GO 5000

-- Plano de execução: Memory Grant no SELECT dá 189MB. 
-- OBS: Tamanho de tabela nesse momento é 3MB (coluna page_count em sys.dm_db_index_physical_stats
SELECT * 
  FROM TestMemoryGrant8000
 ORDER BY Col4

SELECT * FROM sys.dm_db_index_physical_stats(
 DB_ID('Testes'), OBJECT_ID('TestMemoryGrant8000'), DEFAULT, DEFAULT, 'DETAILED')

-- Mesma estrutura, tabela com tamanhos "certos" nas colunas varchar

DROP TABLE TestMemoryGrant250
CREATE TABLE TestMemoryGrant250(ID  INT IDENTITY (1,1),
                             Col1 VarChar(250) DEFAULT NEWID() PRIMARY KEY,
                             Col2 VarChar(250) DEFAULT NEWID(),
                             Col3 VarChar(250) DEFAULT NEWID(),
                             Col4 VarChar(250) DEFAULT NEWID(),
                             Col5 VarChar(250) DEFAULT NEWID(),
                             Col6 VarChar(250) DEFAULT NEWID(),
                             Col7 VarChar(250) DEFAULT NEWID(),
                             Col8 VarChar(250) DEFAULT NEWID(),
                             Col9 VarChar(250) DEFAULT NEWID(),
                             Col10 VarChar(250) DEFAULT NEWID())
GO

INSERT INTO TestMemoryGrant250 DEFAULT VALUES
GO 5000

-- Plano de execução: Memory Grant no SELECT dá 8MB!!!
SELECT * 
  FROM TestMemoryGrant250
 ORDER BY Col4

-- Sintoma 2: Contenção por espaço em RAM

-- Executar SELECT’s no SQLQueryStress (http://www.datamanipulation.net/sqlquerystress/sqlquerystressdownload.asp)
-- com 100 threads e 2 comandos. Vai ocorrer um monte de wait do tipo RESOURCE_SEMAPHORE na tabela com campos grandes 
-- (não ocorre na tabela com campos pequenos)
-- Notar a diferença de tempo na execução (sensível mesmo em tabelas minúsculas como a do exemplo)
SELECT * FROM sys.dm_os_waiting_tasks 
where session_id >= 50
-- WAIT_TYPE = RESOURCE_SEMAPHORE 
-- "Occurs when a query memory request cannot be granted immediately due to other concurrent queries. 
-- High waits and wait times may indicate excessive number of concurrent queries, *or excessive memory request amounts*."
-- http://technet.microsoft.com/en-us/library/ms179984.aspx

TEXT x VARCHAR(MAX)

Este é um dos posts sobre os exemplos apresentados na palestra sobre modelagem no 24 Hours of PASS - PT. Você pode ver a série de posts em Impactos da Modelagem na Performance do SQL Server.

Este eu peguei emprestado pra palestra a partir de um post que o Luti colocou no site da Sr Nimbus, mas como minha didática é melhor que a dele, vou colocar o exemplo com meus comentários aqui. ;-)

Colunas TEXT e VARCHAR(MAX) armazenam objetos grandes, ou LOB's (Large Objects). Sintaticamente são iguais: uma coluna TEXT ou VARCHAR(MAX) armazena um valor string de até 2GB por registro. No entanto há uma diferença que pode impactar severamente na quantidade de IO realizada por uma consulta em uma tabela que contenha colunas destes tipos, e também na performance do Data Cache, um dos principais mecanismos de otimização do SQL Server, que é a área de memória que serve de buffer para as páginas lidas de disco. Tudo isto por uma "pequena" diferença na forma como o armazenamento dos dados LOB é feita, dependendo do tipo usado para declarar a coluna.

O script a seguir demonstra isto. Copie ele para uma nova query no Management Studio (não esqueça de mudar para um banco de testes), vá executando os pedaços de código entre os comentários e veja os resultados. Qualquer dúvida posta uma pergunta!

/* ===============================================================================================================================
   Comparação entre modos de armazenamento de dados para os tipos de dados "large data type values" (varchar(max), nvarchar(max) e
   varbinary(max) e "large object values" (text, ntext, image)
   Por default os "large data types" armazenam dados "in row", o que pode ocasinar muito IO desnecessário e mau uso do data cache.
   Este comportamento pode ser modificado através da opção de tabela "large value types out of row"
   http://msdn.microsoft.com/en-us/library/ms189087(v=sql.105).aspx
   =============================================================================================================================== */

-- Tabela com campo LOB armazenado em coluna VARCHAR(MAX)

DROP TABLE dbo.TabelaTEXT 
CREATE TABLE dbo.TabelaTEXT (
ID INT IDENTITY NOT NULL CONSTRAINT PK_TabelaTEXT PRIMARY KEY
, Nome VARCHAR(100) NOT NULL DEFAULT ('Sr. Nimbus')
, DataRegistro DATETIME2 NOT NULL DEFAULT(SYSDATETIME())
, Texto TEXT NOT NULL DEFAULT (REPLICATE('A', 2000))
)
GO

-- Mesma estrutura, exceto campo LOB que é armazenado em coluna TEXT

DROP TABLE dbo.TabelaVARMAX 
CREATE TABLE dbo.TabelaVARMAX (
ID INT IDENTITY NOT NULL CONSTRAINT PK_TabelaVARMAX PRIMARY KEY
, Nome VARCHAR(100) NOT NULL DEFAULT ('Sr. Nimbus')
, DataRegistro DATETIME2 NOT NULL DEFAULT(SYSDATETIME())
, Texto VARCHAR(MAX) NOT NULL DEFAULT (REPLICATE('A', 2000))
)
GO

-- Popula as 2 tabelas

INSERT INTO dbo.TabelaTEXT DEFAULT VALUES
INSERT INTO dbo.TabelaVARMAX DEFAULT VALUES
GO 10000

select count(*) from TabelaTEXT
select count(*) from TabelaVARMAX

-- Vamos verificar a quantidade de IO realizada pelas consultas abaixo

SET STATISTICS IO ON

SELECT ID, Nome FROM dbo.TabelaTEXT
SELECT ID, Nome FROM dbo.TabelaVARMAX
-- TabelaTEXT > TabelaVARMAX
-- Em TabelaTEXT a área de dados contém campos LOB
-- SELECT não requisitou estes campos!

SET STATISTICS IO OFF

-- Tamanho e localização dos dados nas tabelas

SELECT
 OBJECT_NAME(object_id) AS ObjectName ,
 AU.type ,
 AU.type_desc ,
 AU.container_id ,
 AU.filegroup_id ,
 AU.total_pages ,
 AU.used_pages ,
 AU.data_pages
FROM SYS.system_internals_allocation_units AS AU
  INNER JOIN SYS.Partitions AS P
ON AU.Container_id = P.Partition_id
WHERE Object_ID IN (object_id('TabelaTEXT'), object_id('TabelaVARMAX'))
ORDER BY object_id, type

-- Mudando o comportamento de VARCHAR(MAX): opção "large value types out of row"

EXEC sp_tableoption 'TabelaVARMAX', 'large value types out of row', 1
GO

-- Para dados existentes, armazenamento só muda quando linha é modificada

UPDATE dbo.TabelaVARMAX SET Texto = Texto

-- Rebuild do índice para compactar tabela

ALTER INDEX PK_TabelaVARMAX ON dbo.TabelaVARMAX REBUILD

-- Vamos verificar a quantidade de IO realizada pelas consultas abaixo

SET STATISTICS IO ON

SELECT ID, Nome FROM dbo.TabelaTEXT
SELECT ID, Nome FROM dbo.TabelaVARMAX
-- TabelaTEXT == TabelaVARMAX
-- Ambas agora usam armazenamento de LOB's out of row

SET STATISTICS IO OFF

IDENTITY na PK - Espaço e IO

Este é um dos posts sobre os exemplos apresentados na palestra sobre modelagem no 24 Hours of PASS - PT. Você pode ver a série de posts em Impactos da Modelagem na Performance do SQL Server.

O SQL Server cria automaticamente um índice para uma coluna declarada como chave primária (constraint PRIMARY KEY). Por default, este índice é do tipo clustered (se você não está familiarizado com os tipos de índice do SQL Server, veja este vídeo sobre o assunto [ainda não disponível]). Caso a coluna usada como chave do índice clustered seja "grande", isto irá aumentar o espaço de armazenamento da tabela, pois os outros índices (non-clustered) irão incorporar o valor da chave do índice clustered para referenciar cada linha da tabela. Se a coluna usada como chave do índice clustered for uma coluna passível de alteração, pode haver um aumento de IO nas operações que modifiquem o valor desta coluna, pois esta modificação deverá ser propagada para todos os índices non-clustered.

O script a seguir demonstra isto. Copie ele para uma nova query no Management Studio (não esqueça de mudar para um banco de testes), vá executando os pedaços de código entre os comentários e veja os resultados. Qualquer dúvida posta uma pergunta!

/* ===============================================================================================================================
   - Chave pequena: diminui espaço usado
   - Chave imutável: menos IO 
   Neste script criamos duas tabelas de teste, uma com chave pequena e imutável (int identity) e outra com chave "grande" e 
   passível de modificação (Email varchar(200)). Após incluir a mesma massa de testes nas duas, vemos que a tabela com chave identity 
   ocupa menos espaço que a tabela com chave natural. Ao realizar um UPDATE na coluna Email, vemos que o comando gera mais IO 
   quando esta coluna é parte da chave primária. Ambas as situações se devem ao fato de que os valores do índice clustered são
   usados pelos índices non-clustered (como especificado em http://msdn.microsoft.com/en-us/library/ms177484(v=sql.105).aspx)
   =============================================================================================================================== */


-- Tabela Pessoas_PKEMAIL: PK é Email - "grande" e passível de modificação 

IF EXISTS(SELECT * FROM sys.tables WHERE name = 'Pessoas_PKEMAIL') DROP TABLE Pessoas_PKEMAIL
CREATE TABLE Pessoas_PKEMAIL
(
 Codigo int not null identity UNIQUE,    -- Índice (non-clustered) é criado para reforçar constraint UNIQUE
 Email varchar(200) not null PRIMARY KEY,   -- Índice (clustered) é criado para reforçar constraint PK
 Nome varchar(50) not null, 
 Sobrenome varchar(50) not null,
 CPF char(11) not null UNIQUE,     -- Índice (non-clustered) é criado para reforçar constraint UNIQUE
 UF char(2)
)

CREATE INDEX IX_Pessoas_PKEMAIL_UF ON Pessoas_PKEMAIL(UF) -- Alguém ainda colocou um índice em UF (não foi culpa minha)
GO

-- Tabela Pessoas_PKIDENTITY: PK é Codigo - "pequena" e imutável

IF EXISTS(SELECT * FROM sys.tables WHERE name = 'Pessoas_PKIDENTITY') DROP TABLE Pessoas_PKIDENTITY
CREATE TABLE Pessoas_PKIDENTITY
(
 Codigo int not null identity PRIMARY KEY,   -- Índice (clustered) é criado para reforçar constraint PK
 Email varchar(200) not null UNIQUE,    -- Índice (non-clustered) é criado para reforçar constraint UNIQUE
 Nome varchar(50) not null, 
 Sobrenome varchar(50) not null,
 CPF char(11) not null UNIQUE,     -- Índice (non-clustered) é criado para reforçar constraint UNIQUE
 UF char(2)
)

CREATE INDEX IX_Pessoas_PKIDENTITY_UF ON Pessoas_PKIDENTITY(UF) -- Alguém ainda colocou um índice em UF (não foi culpa minha)
GO

-- Inclui os mesmos dados de teste em cada tabela

INSERT INTO Pessoas_PKEMAIL
SELECT  Email = p1.FirstName + '.' + p1.LastName + '@srnimbus.com.br', 
  Nome = p1.FirstName,
  Sobrenome = p1.LastName, 
  CPF = LEFT(NEWID(), 11),
  UF = LEFT(p1.FirstName,1) + LEFT(p1.LastName,1)
 FROM  
 (SELECT DISTINCT FirstName, LastName = isnull(MiddleName,'') + LastName
  FROM AdventureWorks2012.Person.Person) P1 

INSERT INTO Pessoas_PKIDENTITY
SELECT  Email = p1.FirstName + '.' + p1.LastName + '@srnimbus.com.br', 
  Nome = p1.FirstName,
  Sobrenome = p1.LastName, 
  CPF = LEFT(NEWID(), 11),
  UF = LEFT(p1.FirstName,1) + LEFT(p1.LastName,1)
 FROM  
 (SELECT DISTINCT FirstName, LastName = isnull(MiddleName,'') + LastName
  FROM AdventureWorks2012.Person.Person) P1 

GO

-- Quantidade de péginas por tabela, incluíndo os índices: coluna sys.dm_db_index_physical_stats().page_count
-- Veja que a tabela com chave IDENTITY ocupa menos espaço que a tabela com chave Email, pois os valores
-- chaves de cada registro são copiados para as entradas dos índices non-clustered.

SELECT name, SUM(page_count)
FROM sys.tables t INNER JOIN sys.dm_db_index_physical_stats(DB_ID('Testes'), NULL, DEFAULT, DEFAULT, 'DETAILED') ips
ON t.object_id = ips.object_id
WHERE t.name IN ('Pessoas_PKIDENTITY', 'Pessoas_PKEMAIL')
GROUP BY name

-- Quantidade de IO: logical reads - páginas acessadas do cache de dados. 
-- Veja que o UPDATE na tabela com PK Email consistentemente acessa mais páginas que o UPDATE na tabela com PK IDENTITY,
-- pois a atualização de uma coluna que é usada em um índice clustered deve ser propagada para todos os índices 
-- non-clustered.
-- (Obviamente que um UPDATE na coluna Codigo geraria mais IO, mas como esta coluna é "artificial", não há necessidade
--  de a aplicação atualizá-la, ao contrário da coluna Email.)

SET STATISTICS IO ON

UPDATE Pessoas_PKEMAIL SET Email = CAST(NEWID() as varchar(36)) + '@srnimbus.com.br' WHERE Codigo = 1
UPDATE Pessoas_PKIDENTITY SET Email = CAST(NEWID() as varchar(36)) + '@srnimbus.com.br' WHERE Codigo = 1

SET STATISTICS IO OFF

IDENTITY na PK - Fragmentação

Este é um dos posts sobre os exemplos apresentados na palestra sobre modelagem no 24 Hours of PASS - PT. Você pode ver a série de posts em Impactos da Modelagem na Performance do SQL Server.

Por default, no SQL Server uma coluna declarada como chave primária (constraint PRIMARY KEY) cria automaticamente um índice clustered (se você não está familiarizado com os tipos de índice do SQL Server, veja este vídeo sobre o assunto [ainda não disponível]). No índice clustered, como a ordem lógica dos registros em disco é mesma ordem da chave do índice, se os valores da chave nas linhas sendo inseridas são monotônicos (sempre crescente ou sempre decrescente) as inclusões não causam fragmentação na tabela. Se a chave não for monotônica a tabela sofrerá fragmentação.

O script a seguir demonstra isto. Copie ele para uma nova query no Management Studio (não esqueça de mudar para um banco de testes), vá executando os pedaços de código entre os comentários e veja os resultados. Qualquer dúvida posta uma pergunta!

/* ===============================================================================================================================
   1 - Chave não-monotônica: causa fragmentação    
   Neste script criamos uma tabela de testes (Pessoas) que tem uma PK na coluna EMail (varchar).
   Incluimos 8.000 linhas com valores de e-mails "aleatórios" (não ordenados), e vemos a fragmentação causada.
   Reconstruímos o índice base da PK e vemos a fragmentação ser eliminada pela reordenação dos registros e das páginas da tabela.
   Inserimos mais 8.000 linhas e novamente a tabela se fragmenta. 
   A PK não é monotônica.
   =============================================================================================================================== */

-- Criação de tabela com PK "natural" (E-mail)

DROP TABLE Pessoas
CREATE TABLE Pessoas
(
 Codigo int not null identity,
 Email varchar(200) not null,
 Nome varchar(50) not null, 
 Sobrenome varchar(50) not null,
 CPF char(11) not null,
 UF char(2)

 CONSTRAINT PK_Pessoas PRIMARY KEY (Email),
)
GO

-- Pega nome e sobrenome de AdventureWorks2012.Person.Person (download em http://msftdbprodsamples.codeplex.com/releases/view/93587) pra gerar dados de teste
-- O uso de um cursor na inserção é para simular uma aplicação transacional inserindo linhas na tabela
-- (Se usarmos um INSERT INTO ... SELECT ... o Query Optimizer ordena as linhas antes de fazer a inserção, e a fragmentação não ocorre)

-- Pega os 1os 8.000 registros do SELECT que gera os dados de teste e os insere na tabela

DECLARE @nome varchar(100), @sobrenome varchar(100)
DECLARE c CURSOR FOR 
 SELECT DISTINCT TOP 8000 FirstName, LastName = isnull(MiddleName,'') + LastName
 FROM AdventureWorks2012.Person.Person
 ORDER BY LastName ASC
OPEN c
FETCH NEXT FROM c INTO @nome, @sobrenome
WHILE @@FETCH_STATUS = 0
BEGIN
 INSERT INTO Pessoas VALUES (
  @nome + '.' + @sobrenome + '@srnimbus.com.br', -- Email
  @nome,      -- Nome
  @sobrenome,     -- Sobrenome
  LEFT(NEWID(), 11),    -- CPF
  LEFT(@nome,1) + LEFT(@sobrenome,1)  -- UF
 )
 FETCH NEXT FROM c INTO @nome, @sobrenome
END
CLOSE c
DEALLOCATE c
GO

SELECT COUNT(*) FROM Pessoas

-- PK EMail não é monotônica: fragmentação altíssima

DBCC SHOWCONTIG('Pessoas')
GO
-- Se reconstruirmos o índice a fragmentação é eliminada pela reordenação das páginas, mas isto é só temporário - novas
-- operações de inclusão e alteração irão fragmentar novamente a tabela (o que não ocorre numa PK IDENTITY)

ALTER INDEX PK_Pessoas ON Pessoas REBUILD
DBCC SHOWCONTIG('Pessoas')
GO

-- Inserção de mais 8.000 linhas fragmenta a tabela de novo

DECLARE @nome varchar(100), @sobrenome varchar(100)
DECLARE c CURSOR FOR 
 SELECT DISTINCT TOP 8000  FirstName, LastName = isnull(MiddleName,'') + LastName
 FROM AdventureWorks2012.Person.Person
 order by LastName DESC
OPEN c
FETCH NEXT FROM c INTO @nome, @sobrenome
WHILE @@FETCH_STATUS = 0
BEGIN
 INSERT INTO Pessoas VALUES (
  @nome + '.' + @sobrenome + '@srnimbus.com.br', -- Email
  @nome,      -- Nome
  @sobrenome,     -- Sobrenome
  LEFT(NEWID(), 11),    -- CPF
  LEFT(@nome,1) + LEFT(@sobrenome,1)  -- UF
 )
 FETCH NEXT FROM c INTO @nome, @sobrenome
END
CLOSE c
DEALLOCATE c
GO

DBCC SHOWCONTIG('Pessoas')




/* ===============================================================================================================================
   2 - Chave monotônica: não gera fragmentação.
   Neste script repetimos a sequencia do script anterior, com a diferença de que a PK agora é monotônica
   (uma coluna IDENTITY).
   Incluimos 8.000 linhas com valores de e-mails "aleatórios" (não ordenados), e não ocorre fragmentação, pois as chaves dos registros é 
   crescente.
   Inserimos mais 8.000 linhas e nenhuma fragmentação ocorre. 
   A PK é monotônica.
   =============================================================================================================================== */

-- Criação de tabela com PK artificial (Codigo - identity)

DROP TABLE Pessoas
CREATE TABLE Pessoas
(
 Codigo int not null identity,
 Email varchar(200) not null,
 Nome varchar(50) not null, 
 Sobrenome varchar(50) not null,
 CPF char(11) not null,
 UF char(2)

 CONSTRAINT PK_Pessoas PRIMARY KEY (Codigo),
)
GO

-- Pega os 1os 8.000 registros do SELECT que gera os dados de teste e os insere na tabela

DECLARE @nome varchar(100), @sobrenome varchar(100)
DECLARE c CURSOR FOR 
 SELECT DISTINCT TOP 8000 FirstName, LastName = isnull(MiddleName,'') + LastName
 FROM AdventureWorks2012.Person.Person
 ORDER BY LastName ASC
OPEN c
FETCH NEXT FROM c INTO @nome, @sobrenome
WHILE @@FETCH_STATUS = 0
BEGIN
 INSERT INTO Pessoas (Email, Nome, Sobrenome, CPF, UF)
 VALUES (
  @nome + '.' + @sobrenome + '@srnimbus.com.br', -- Email
  @nome,      -- Nome
  @sobrenome,     -- Sobrenome
  LEFT(NEWID(), 11),    -- CPF
  LEFT(@nome,1) + LEFT(@sobrenome,1)  -- UF
 )
 FETCH NEXT FROM c INTO @nome, @sobrenome
END
CLOSE c
DEALLOCATE c
GO

SELECT COUNT(*) FROM Pessoas

-- PK Codigo é monotônica: nenhuma fragmentação ocorre - inserção é sempre no "fim" da tabela

DBCC SHOWCONTIG('Pessoas')
GO

-- Inserção de mais 8.000 linhas: tabela se mantém compacta

DECLARE @nome varchar(100), @sobrenome varchar(100)
DECLARE c CURSOR FOR 
 SELECT DISTINCT TOP 8000  FirstName, LastName = isnull(MiddleName,'') + LastName
 FROM AdventureWorks2012.Person.Person
 order by LastName DESC
OPEN c
FETCH NEXT FROM c INTO @nome, @sobrenome
WHILE @@FETCH_STATUS = 0
BEGIN
 INSERT INTO Pessoas (Email, Nome, Sobrenome, CPF, UF)
 VALUES (
  @nome + '.' + @sobrenome + '@srnimbus.com.br', -- Email
  @nome,      -- Nome
  @sobrenome,     -- Sobrenome
  LEFT(NEWID(), 11),    -- CPF
  LEFT(@nome,1) + LEFT(@sobrenome,1)  -- UF
 )
 FETCH NEXT FROM c INTO @nome, @sobrenome
END
CLOSE c
DEALLOCATE c
GO

DBCC SHOWCONTIG('Pessoas')

Impactos da Modelagem na Performance do SQL Server

Oi Gente,

O pessoal da Sr. Nimbus apresentou 3 palestras no 1º dia do evento "24 Hours of PASS - Portuguese Edition". Minha palestra, juntamente com o Ivan, foi "Como (Não) Modelar Seu Banco de Dados". Nós buscamos mostrar situações nas quais uma modelagem pobre pode impactar severamente a performance do SQL Server, através do aumento de uso de recursos no armazenamento e na execução de consultas.

A palestra foi dividida em 3 "episódios": Chaves Primárias, Modelagem e o Otimizador, e Qual o Tamanho da Coluna?. Apresentei "Chaves Primárias" e "Qual o Tamanho da Coluna", e como os exemplos ficaram interessantes, irei publicá-los aqui em uma pequena série de posts:

Chaves Primárias:

Introdução aos tipos de índices no SQL Server [ainda não disponível]. Pré-requisito para os artigos sobre chaves primárias, que no SQL Server são baseadas por default em índices do tipo clustered.
IDENTITY na PK - Fragmentação. Uma chave não-monotônica pode causar fragmentação na sua tabela, o que aumenta o espaço de armazenamento e degrada o tempo de IO.
IDENTITY na PK - Espaço e IO. PK's "grandes" e passíveis de modificação incham a tabela e suas páginas de índice, e causam mais IO do que chaves "pequenas" e imutáveis.

Qual o Tamanho da Coluna?

TEXT x VARCHAR(MAX). Apesar de sintaticamente equivalentes, há uma característica de armazenamento que pode impactar (e muito) na performance de IO e no uso de RAM para consultas em tabelas que armazenam LOB's (Large Objects - informação > 8.000 bytes).
Memory Grant em varchar(8000). Cuidado com o espaço declarado para suas colunas de tamanho variável. Ela só ocupa o espaço necessário para o armazenamento dos dados, mas aquele "(8000)" pode trazer um uso desnecessário e pesado de memória, e até causar contenção entre as consultas sendo executadas.

Estes exemplos são legais porque nos cenários mostrados não há mensagens de erro, mas o uso exagerado de recursos devido a detalhes de modelagem podem afetar negativamente o uso de recursos e a performance do seu servidor.

A palestra será publicada pelo pessoal do PASS em http://www.sqlpass.org/Default.aspx?TabId=62.

Espero que seja útil!

[]s,

quarta-feira, 28 de novembro de 2012

SSMS 2012: Cadê o CTRL+R ?

No SQL Server 2012, o Management Studio (SSMS) é baseado na shell (a IDE "crua") do Visual Studio. A primeira coisa que notei foi que o atalho preferido de DBA's e desenvolvedores, o CTRL+R que serve pra mostrar e esconder o Result Pane, sumiu. Mas sem CTRL+R não dá. Simplesmente não dá.

Pra voltar ele pro ambiente:

Vá em "Tools | Customize | Keyboard..."
Selecione o comando "Window.ShowResultPane"
Na lista "Use new shortcut in" selecione "SQL Query Editor"
Coloque o cursor no campo "Press shortcut keys" e pressione "CTRL+R"
Clique em "Assign" e "Ok".

Ufa. Alívio!

[]s,

quinta-feira, 5 de julho de 2012

Recursos de Formação – TFS

Pra quem quer começar ou se especializar um pouco mais em TFS, aí vão alguns recursos gratuitos:

Visual Studio 2010 and .NET Framework 4 Training Course > Application Lifecycle Management
Laboratórios baseados na Visual Studio 2010 RTM Virtual Machine with Sample Data and Hands-on-Labs, cobrindo a parte de testes (Test Manager 2010), planejamento de projetos, branching e merging, e mais.
Introduction to Visual Studio Team Foundation Server 2010 Training Kit
Apresentações, hands-on labs e demos sobre TFS 2010.
Visual Studio ALM Rangers > Rangers Solutions and Projects
Guias de práticas recomendadas em diversas áreas do TFS (branching, planejamento, customização de builds, testes, banco de dados, etc).
MSDN Virtual Lab: Team Foundation Server 2010 Training
VM’s acessadas via Internet Explorer contendo dados e software para praticar conceitos básicos do TFS:
Parte 1: Team projects e work items
Parte 2: Source Control e Portal do Projeto (SharePoint)
Parte 3: Gerenciamento de Requisitos com work items
Alguns bons blogs de TFS
Brian Harry: http://blogs.msdn.com/b/bharry/
Visual Studio ALM + Team Foundation Server Blog: http://blogs.msdn.com/b/visualstudioalm/

sexta-feira, 22 de junho de 2012

Como clonar VM's no Hyper-V

Biblioteca de VM's - ou biblioteca de VHD's - são excelente ferramentas para desenvolvedores criarem rapidamente um ambiente de testes. Mas você pode esbarrar em ferramentas instáveis e as famosas Blue Screen of Death se você não souber clonar corretamente os VHD's de sua biblioteca para a criação de novas VM's. A combinação de VHD's diferenciais com o sysprep reduz bastante o trabalho de subir corretamente clones de uma VM que precisam rodar em um domínio.

Contexto

Instalei um Windows 2008 R2 em uma VM do Hyper-V pra testes. Pra não ter que passar pelo tempo de instalação quando criasse novas VM's, separei o VHD dela, marquei como read-only, e comecei a criar novas VM's usando o recurso de discos diferenciais do Hyper-V, que permite criar discos baseado em um disco "pai". O disco "filho" - o disco diferencial - contém tudo o que o disco pai contém, e modificações nos arquivos são gravados no disco diferencial, deixando o disco pai intacto para servir de base para outros discos. (Se estiver curioso sobre discos diferenciais, dê uma olhada no artigo Virtual Hard Disk (VHD) Architecture explained).

Problema

Até o momento, eu tinha instalado tudo que eu queria testar em uma única VM. P.e., uma VM tinha SharePoint + SQL Server. Outra tinha um DC + Visual Studio + TFS + SQL Server. Eu testava tudo subindo só uma VM. Meu problema foi quando quis criar um domínio e subir várias VM's simultaneamente, usando o disco pai pra criar estas VM's.

O problema é que o Windows gera "coisas" durante a instalação que devem ser únicas em um domínio. Parece que existem várias destas "coisas", mas a que me deu problema foram os SID's das máquinas e dos usuários. Um SID é, basicamente, o identificador único de um objeto (máquina, usuário, grupo, etc) em um domínio. Se você simplesmente copia o VHD de uma VM e cria uma nova VM usando este VHD como disco, os SID's são duplicados. Isto causa problemas e erros quando estas VM's estão no mesmo domínio pois o Windows não espera encontrar SID's duplicados no domínio.

Solução

Como qualquer problema, tem várias formas de resolver isto. A que mais se adequou ao meu objetivo, que é criar uma biblioteca de VHD's para subir rapidamente ambientes Windows de desenvolvimento e testes que serão usados e depois descartados, foi:

Criar uma VM e instalar o sistema operacional (Windows 2008 R2).
Configurar opções comuns a todas as VM's que se basearão nesta instalação. Em particular: retirar a obrigatoriedade de troca de senha do administrador, customizar propriedades do Menu Iniciar, instalar o BgInfo para identificar facilmente as VM's.
Executar o sysprep para colocar o Windows em um estado "acabei de ser instalado". O sysprep é uma ferramenta da Microsoft que prepara uma imagem de disco com uma instalação Windows para clonagem.
Marcar o VHD como read-only.
Criar novas VM's usando discos diferenciais baseados neste VHD "sysprep'd".

Para executar o passo 3 acima, após sua VM base estar completamente customizada, abra um command prompt, mude para C:\Windows\System32\Sysprep, e execute sysprep.exe. Selecione a opção "Enter System Out-of-Box Experience (OOB)", marque a opção "Generalize" (que faz com que o sysprep reinicialize tudo que tem que ser reinicializado - como os SID's), e selecione a opção "shutdown" para que a VM seja desligada após a execução do sysprep. Agora você tem um disco virtual com um Windows instalado e pronto para receber novos nome, SID, e as outras "coisas".

Quando você criar novas VM's que usam discos diferenciais baseados neste VHD, no primeiro boot será apresentado um "mini-setup" do Windows aonde ele pedirá somente algumas informações, gerará um nome para a nova máquina, e pronto - em menos de 2 minutos você tem uma nova VM limpinha pra trabalhar.

Observações

O nome da nova máquina é auto-gerado, então geralmente é a primeira coisa que eu mudo.
A máquina entra com IP auto-configurado ("APIPA" - 169.254.blablabla). Caso você esteja montando uma rede de VM's, tem que configurar o IP para ela entrar na rede.
Dá dor de cabeça montar uma VM para fazer o sysprep se esta VM tem o SQL Server já instalado. Não é suportado até o SQL Server 2008. O que tenho feito é subir uma VM sysprep'd "limpa" - só o Windows -, colocar ela no domínio e instalar o SQL Server. Perco menos tempo assim do que ficar tentando solucionar problemas devido a fazer o syspred de uma VM já com SQL Server instalado. No SQL Server 2008 *R2* diz a MS que é suportado, mas nunca usei pra ver se dá muito trabalho ou não.
Comecei a receber o erro "An attempt to resolve the DNS name of a domain controller in the domain being joined has failed" quando ia acrescentar uma nova VM no domínio - apesar de ter configurado o DC do domínio como o servidor DNS primário da nova VM, e conseguir pingar de uma máquina pra outra. Resumindo (muito), desabilitei o IPv6 em todas as máquinas e tudo voltou ao normal.

[]s,

GB

terça-feira, 8 de maio de 2012

Meu site SharePoint está com segurança integrada mas ainda aparece a janela de logon

Coloca ele como “Local Intranet” no IE que pára de aparecer.

(Forte concorrente a menor post do mundo se não fosse essa última frase)

quinta-feira, 3 de maio de 2012

SharePoint: Erro de deploy | Momento revolta :-P

Estava fazendo o deploy de uma solução no SharePoint e apareceu o erro "Error occurred in deployment step 'Activate Features': Operation is not valid due to the current state of the object.". Aí eu pensei "Hein?" e fui pro Google. E a primeira solução que encontrei foi:

1. Feche o VS2010

2. de um IISRESET no server

3. Abra o Task Manager e de um KILL em todos os processos w3wp.exe

"Tu tá de sacanagem" eu pensei. Mas fiz isso e, sem mudar nada no código, o deploy passou a funcionar. Desenvolvimento no SharePoint é isso: faz sua estimativa, multiplica por 3 por causa dessas "peculiaridades", e reza pra ter alguém que já passou pela mesma coisa e postou na Internet. Se não chegasse tanta demanda nesse troço...

segunda-feira, 27 de fevereiro de 2012

Ambiente de Testes Virtualizado com Controlador de Domínio

Não sei direito porque, mas ouvi o pessoal de infra falando sobre problemas se serviços do seu domínio (tais como SQL e Exchange) sobem antes do controlador de domínio. Montei um domínio de testes para desenvolvimento no TFS e esbarrei nesse problema: serviços não subiam ou a conectividade entre eles não funcionava. Como as VM’s estão no Hyper-V, dá pra colocar um “atraso” no boot das máquinas, então configurei o DC sem o delay, e as outras máquinas com um delay de 30 segundos. Na tela de configuração de cada VM que deve subir com o delay:

Desta forma, o controlador de domínio sobe antes das outras VM’s, evitando esse tipo de problema.

terça-feira, 14 de fevereiro de 2012

Como publicar seu banco para o SQL Azure com o Management Studio

Essa é velhinha mas vale a pena, porque tá escondida lá nas profundezas do Wizard de geração de scripts do Management Studio.

O SQL Azure só suporta um subconjunto das instruções e opções de objeto do SQL Server “normal”. Se você gerar um script de criação do banco no Management Studio, esse script só vai rodar se você retirar manualmente um monte de coisa dele (a lista está nesse artigo do Technet: Develop and Deploy With SQL Azure). Mas se você usar o Management Studio do SQL Server 2008 *R2*, tem umas opções no Wizard de geração de scripts que já retira esse monte de tranqueira que não roda no SQL Azure.

Entre no Wizard de geração de scripts do Management Studio (no Object Explorer: selecionar o banco | botão direito | Tasks | Generate scripts…). Quando chegar na tela “Set Scripting Options”, selecione o destino (file | clipboard | new query window) e pressione o botão “Advanced”. As opções interessantes são:

Script for the database engine type: stand-alone para SQL Server “normal” ou SQL Azure Database para o SQL Azure.
Types of data to script: Schema only gera os comandos de criação de objetos, Schema and data gera também os INSERT’s para as linhas de cada tabela incluída no script (obviamente só use isto se você tiver poucos dados nas tabelas).

E pronto. Conecte o Management Studio no seu banco SQL Azure e rode o script gerado.

PS: Se você não tem o SQL Server 2008 R2, pode baixar o SQL Server 2008 R2 Express with Advanced Tools. No setup desmarque a instalação de uma instância do SQL Server (Database Engine, ou Database Services, algo assim), e ele instalará apenas as ferramentas de gerenciamento, dentre elas o Management Studio versão 2008 R2. O melhor é que é de “grástis”: http://www.microsoft.com/download/en/details.aspx?id=23650.

segunda-feira, 23 de janeiro de 2012

Haja Produtividade

Quando for fazer um projeto pra nuvem, lembre-se de acrescentar no cronograma uma gordurinha extra por conta dos deploys da aplicação. Olha um log de deploy de um projeto Cloud no VS2010:

16:22:27 - Warning: There are package validation warnings.
16:22:28 - Preparing...
16:22:28 - Connecting...
16:22:31 - Uploading...
16:30:31 - Creating...
16:31:41 - Starting...
16:32:16 - Initializing...
16:32:16 - Instance 0 of role _____________ is initializing
16:32:16 - Instance 0 of role _____________ is initializing
16:36:12 - Instance 0 of role _____________ is busy
16:36:12 - Instance 0 of role _____________ is busy
16:37:19 - Instance 0 of role _____________ is ready
16:37:52 - Instance 0 of role _____________ is ready
16:37:54 - Complete.

16:22 pra 16:37... só 15 minutos esperando o deploy :-P Tá me lembrando a época da bolsa de computação gráfica da UnB, que a gente mandava compilar o aplicativo e demorava 5 minutos. Aí testava, corrigia alguma coisa e mais 5 minutos pra compilar. Haja produtividade ;-)

sexta-feira, 20 de janeiro de 2012

"Unable to load the specified metadata resource", ou "Como perder 2 dias por causa de um asterisco"

Mais uma pra pilha "Azure - Mais Um Detalhe De Desenvolvimento". Resolvemos migrar as tabelas de uma aplicação Azure do ATS para o SQL Azure.

OBS1: Se você vai começar seu projeto agora, *não* use o ATS como um banco relacional. A lista de problemas é tão grande que não vou nem colocar aqui.

Bem, fizemos os testes locais e tudo rodou tranquilo.

OBS2: Conselho para desenvolvimento no Azure: "Não escreve muito código sem um deploy e teste na nuvem". Os Compute/Storage Emulators e a nuvem tem vários comportamentos e particularidades diferentes entre si. Desenvolva um trecho, estabilize o suficiente para um teste, e faça um deploy na nuvem para teste lá. Obrigado ao Vitor por emprestar a frase.

Na hora que passamos para a nuvem, no primeiro acesso ao BD no SQL Azure, aparecia uma exceção "Unable to load the specified metada". Resumindo (muito), a estória é a seguinte:

Nossa camada de dados é uma DLL separada (AcessoDados.dll), que contém um modelo Entity Framework apontando para o BD no SQL Azure, e mais um tanto de código customizado.
A aplicação tem um web role e um worker role. Esta DLL é referenciada por ambos, já que ambos manipulam as mesmas tabelas.
A string de conexão ao modelo EF começa assim:
connectionString="metadata=res://*/Entidades.BDAplicacao.csdl|res://*/Entidades. BDAplicacao .ssdl|res://*/Entidades.BDAplicacao.msl; ...". Estes arquivos são necessários para o Entity Framework realizar o acesso ao banco de dados representado pelo modelo.
O modelo EF tem uma propriedade chamada "Metadata Artifact Processing", que por default vale "Embed in Output Assembly". Isto significa que os 3 arquivos na string de conexão (BDAplicacao.csdl, BDAplicacao.ssdl e BDAplicacao.msl) são incorporados à DLL como recursos durante a compilação.

A mensagem de erro indicava que, na hora que o worker role ia acessa o banco, não encontrava os 3 arquivos necessários para a geração dos comandos de acesso ao banco. Então a exceção "Unable to load the specified metadata resource" ocorria.

A string de conexão no item 3 acima é a string padrão gerado pelo Designer de Modelos Entity Framework do Visual Studio 2010. Basicamente, os asteriscos na string de conexão significam "procure estes arquivos nos recursos de todos os assemblies carregados no momento". Não sei exatamente porque, mas o fato é que o worker role, quando rodando na nuvem, não encontrava os arquivos dentro da DLL (quando rodando no Compute Emulator, tudo funcionava ok). Tivemos então que especificar o nome da DLL em vez dos asteriscos, e a aplicação passou a funcionar também na nuvem. A string de conexão ficou assim:

connectionString="metadata=res://AcessoDados/Entidades.BDAplicacao.csdl|res:// AcessoDados /Entidades. BDAplicacao .ssdl|res://AcessoDados/Entidades.BDAplicacao.msl; ...".

Mais alguns detalhes pra fechar:

Repare que colocamos o nome da DLL, mas não sua extensão. Caso você coloque a extensão, será gerada uma exceção "Não foi possível localizar o arquivo ...".
O modelo EF se chama "BDAplicacao", e está dentro de um sub-namespace "Entidades". Então veja que o nome dos arquivos não é simplemente o nome do modelo com as extensões .csdl, .ssdl e .msl; o nome dos arquivos é "Entidades.BDAplicacao"; tem que ser acrescentado o namespace ao nome dos arquivos, exceto pela parte "raiz" do namespace - que é o nome da DLL.

E aí? Pouco detalhe, né não?