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

quinta-feira, 29 de novembro de 2012

Memory Grant em VARCHAR(8000)