Blog do GB: novembro 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,