Mostrando postagens com marcador SQL Server. Mostrar todas as postagens
Mostrando postagens com marcador SQL Server. Mostrar todas as postagens

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,

GB

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.

domingo, 18 de outubro de 2009

AdventureWorks: Só o MDF!!!

"Pra quê simplificar se você pode complicar". Criaram um projeto no Codeplex pra instalação dos bancos AventureWorks, um conjunto de exemplos que a Microsoft começou a distribuir com o SQL Server 2005. Baixei o "troço". Tem lista de pré-requisitos - pra instalar uns BD´s de exemplo!!! E tem que ter "FILESTREAM" habilitada, o que - depois de muito apanhar - eu fiquei sabendo que não tem como habilitar na minha instalação: um SQL Server 2008 Express 32 bits em um sistema operacional de 64 bits. "By design", dizia um post no blog de um cara do time do SQL Server da M$. FILESTREAM não roda em WoW - Windows On Windows, i.e., aplicações de 32 bits rodando sobre uma emulação de um Windows 32 bits num Windows de 64 bits. A pergunta óbvia, já que existe uma versão 64 bits do SQL Server Express, é por que eu não instalei essa versão. Sei lá. Tava com sono. Tava distraído. Tava trocando a fralda da Júlia enquanto baixava o SQL Server Express.

Bem, precisei do "troço", aí baixei e instalei. O "troço" cria um monte de sub-diretório, instala uns scripts que criam a estrutura do banco, vazia, e depois carregam - via o tal de FILESTREAM - um monte de arquivos CSV nas tabelas do banco. Caraca, isso é que é complicar o que é simples. E um .BAK com um backup do banco, ou um MDF pra poder anexar o banco ao servidor, nada.

Acho que o tal do time que tá trabalhando no projeto do Codeplex deve ter alguma ótima razão pra criar um projeto de setup pra isso (tomara!!!). Mas depois de perder a paciência com esse "troço", resolvi dar um chute: baixei, lá do Codeplex mesmo, o projeto de setup do AdventureWorks da versão *2005* do SQl Server. Um MSI que tem dentro os arquivos MDF e LDF do banco AdventureWorks. Pronto! Simples. Descompactei o MSI, e anexei o BD ao meu SQL Server Express, que apesar de ser 2008, lê perfeitamente os arquivos do SQL 2005.

Pra baixar o MSI com o banco, vá em http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004. A opção é o "AdventureWorksDB.msi".

Pra descompactar o MSI, rode em um command prompt o seguinte comando:
msiexec -a NomeArquivo.MSI -qb TARGETDIR=caminho_completo_local_descompactação
Pra anexar o BD, abra o Management Studio e, no Object Browser, clique com o botão direito em "Databases" e selecione "Attach..".

Precisa de um projeto de setup pra isso???

sábado, 17 de outubro de 2009

INSERT em bloco de transação

Geek que é geek se fascina com essas coisas. Estou eu neste sábado *desesperado* estudando para o curso de SQL Server que vou começar a dar na segunda. Olhando o capítulo sobre execução de consultas no Inside SQL Server 2005: Query Tunning and Optimization, o cara (não foi a Delaney que escreveu esse capítulo - ela agora está "sub-contratando" autores pra série Inside...) me solta essa sem nem um comentariozinho sobre o código:

O cara tá fazendo uma sequência de 250.000 INSERT´s. Pra otimizar, ele abre uma transação antes do laço, e a cada 1.000 INSERT's dá um COMMIT e abre outra transação. Isso evita que seja feita uma transação pra cada comando INSERT executado. (Sim, Gafanhoto. Quando você não faz BEGIN TRAN, o SQL Server abre automaticamente uma transação pros comandos executados fora de uma transação. Isso permite, p.e., que você se arrependa e dê um ROLLBACK dentro de um trigger, mesmo sem aparentemente haver uma transação aberta).

Usando esse bloco de transações, ele roda os INSERT's em 250 transações, em vez de 250 mil transações. Lindo. Simplesmente lindo.

E acho que não precisa nem dizer, mas sim: sou geek. :-)

quinta-feira, 8 de outubro de 2009

SQL Server Compact is not intended for ASP.NET development

O SQL Server Compact é uma versão do SQL Server bastante restrita em termos de recursos, mas que roda in-process, ou seja, não roda como um serviço. Desta forma, aplicações que o usam não precisam de direitos daministrativos na sua instalação, o deploy destas aplicações é simplificado (em relação a instalar outras edições do SQL Server), e o uso de recursos é baixo. Roda até em Windows Mobile!

Bem, tentei usar um banco do SQL Server Compact Edition em um site ASP.NET de teste que estava fazendo pra dar aula. Aí apareceu a mensagem de erro "SQL Server Compact is not intended for ASP.NET development". Isto porque, como a mensagem diz, o SQL Server Compact não foi projetado para desenvolvimento ASP.NET. Algo a ver com problemas de acesso multi-usuário. Mas se você quiser usá-lo só para um teste rápido, basta colocar no seu Global.Asax o seguinte código:


Sub Application_Start(ByVal sender As Object, ByVal e As EventArgs
AppDomain.CurrentDomain.SetData("SQLServerCompactEditionUnderWebHosting", True)
End Sub

Desta forma você "está se responsabilizando" pelo uso do SQL CE na sua aplicação ASP.NET, e o erro não volta a ocorrer.

terça-feira, 4 de agosto de 2009

SQL Server Management Studio Express

(Aposto que muita gente já conhece, mas como estive fora da área por muito tempo, aí vai)

Prático e gratuito:
http://www.microsoft.com/downloadS/details.aspx?familyid=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en
Microsoft SQL Server Management Studio Express (SSMSE) is a free, easy-to-use graphical management tool for managing SQL Server 2005 Express Edition and SQL Server 2005 Express Edition with Advanced Services.



Só um update, tem pro SQL 2008 também: http://www.microsoft.com/Downloads/details.aspx?familyid=08E52AC2-1D62-45F6-9A4A-4B76A8564A2B&displaylang=pt-br