Guia de Referência do Fast Track Data Warehouse para o SQL

Guia de Referência do Fast Track Data Warehouse para o SQL Server 2012
Artigo técnico do SQL Server
Autores: Eric Kraemer, Mike Bassett, Eric Lemoine, Dave Withers
Revisores técnicos: Claude Lorenson, Susan Price, Ralph Kemperdick, Henk van der Valk,
Alexi Khalyako, Oliver Chiu
Publicado em: março de 2012
Aplica-se a: SQL Server 2012
Resumo: este documento define um modelo de configuração de referência (conhecido
como Fast Track Data Warehouse) que usa uma abordagem equilibrada de recursos para
a implementação de uma arquitetura de sistema de banco de dados do SQL Server baseada
em SMP (multiprocessador simétrico), com desempenho e escalabilidade comprovados para
cargas de trabalho de data warehouse. A meta de uma arquitetura de referência do Fast Track
Data Warehouse é atingir um equilíbrio eficiente de recursos entre a capacidade de processamento
de dados do SQL Server e a taxa de transferência de hardware do componente.
Direitos autorais
Este documento é fornecido "no estado em que se encontra". As informações e opiniões expressas nele,
inclusive URLs e outras referências a sites da Internet, poderão ser alteradas sem aviso prévio.
Você assume integralmente o risco de usá-lo.
Este documento não fornece direitos legais a nenhuma propriedade intelectual de qualquer produto
Microsoft. Você pode copiar e usar este documento para sua referência interna.
© 2012 Microsoft. Todos os direitos reservados.
2
Sumário
Histórico de alterações do FTDW.................................................................................................................. 6
Introdução..................................................................................................................................................... 6
Público-alvo............................................................................................................................................... 6
Fast Track Data Warehouse .......................................................................................................................... 6
Fast Track .................................................................................................................................................. 7
Proposta de valor ...................................................................................................................................... 7
Metodologia .................................................................................................................................................. 7
Arquitetura de componentes holística ..................................................................................................... 7
Abordagem otimizada de carga de trabalho ............................................................................................ 8
Configurações de referência do SQL Server Fast Track validadas ............................................................ 9
Resumo ..................................................................................................................................................... 9
Carga de trabalho do FTDW .......................................................................................................................... 9
Padrões de carga de trabalho de data warehouse ................................................................................... 9
Avaliação da carga de trabalho ............................................................................................................... 10
Atributos qualitativos de carga de trabalho de data warehouse ........................................................... 12
Escolhendo uma configuração de referência do FTDW .............................................................................. 13
Opção 1: Avaliação Básica....................................................................................................................... 14
Etapa 1: Avaliar o caso de uso do cliente................................................................................................ 14
Etapa 2: Escolher uma arquitetura de referência do FTDW publicada .............................................. 15
Opção 2: Avaliação Completa ................................................................................................................. 15
Visão geral do processo ...................................................................................................................... 16
Etapa 1: Avaliar o caso de uso do cliente............................................................................................ 16
Etapa 2: Estabelecer métricas de avaliação ........................................................................................ 17
Etapa 3: Escolher uma arquitetura de referência do Fast Track Data Warehouse............................. 18
Opção 3: Arquiteturas de referência definidas pelo usuário .................................................................. 18
Etapa 1: Definir a carga de trabalho ................................................................................................... 18
Etapa 2: Estabelecer avaliações de desempenho da arquitetura de componentes........................... 18
Escolhendo um resumo de FTRA ............................................................................................................ 19
Configuração padrão do FTDW ................................................................................................................... 20
Arquitetura de componentes de hardware ............................................................................................ 20
Requisitos e configuração do componente ........................................................................................ 20
3
Configuração de aplicativos .................................................................................................................... 22
Windows Server 2008 R2 .................................................................................................................... 22
SQL Server 2012 Enterprise ................................................................................................................ 23
Sistema de armazenamento ............................................................................................................... 24
Práticas recomendadas do SQL Server para FTDW .................................................................................... 29
Arquitetura de dados .............................................................................................................................. 29
Estrutura de tabela ............................................................................................................................. 29
Particionamento de tabela ................................................................................................................. 30
Indexação ............................................................................................................................................ 31
Índices columnstore na memória xVelocity ........................................................................................ 31
Estatísticas de banco de dados ........................................................................................................... 33
Compactação ...................................................................................................................................... 34
Gerenciando a fragmentação de dados .................................................................................................. 34
Fragmentação do sistema de arquivos ............................................................................................... 35
Vários grupos de arquivos................................................................................................................... 37
Carregando dados ................................................................................................................................... 37
Cargas incrementais ............................................................................................................................ 38
Migração de dados.............................................................................................................................. 40
Avaliação de desempenho e validação ....................................................................................................... 42
Executando a validação do FTDW de linha de base................................................................................ 43
Testes de linha de base com o SQLIO ................................................................................................. 44
Executando a avaliação de desempenho de banco de dados Fast Track ............................................... 46
Calculando a MCR ............................................................................................................................... 47
Calculando a BCR ................................................................................................................................ 49
Arquiteturas de referência do FTDW publicadas........................................................................................ 52
Conclusão .................................................................................................................................................... 52
Apêndice ..................................................................................................................................................... 54
Ferramenta de dimensionamento do sistema FTDW ............................................................................. 54
Validando uma FTRA definida pelo usuário ............................................................................................ 54
Testes sintéticos de E/S....................................................................................................................... 54
Gerando arquivos de teste com o SQLIO ............................................................................................ 54
4
Testes de carga de trabalho .................................................................................................................... 57
Medindo a MCR para seu servidor (opcional) .................................................................................... 57
Medindo a BCR para sua carga de trabalho ........................................................................................ 57
Fatores que afetam a taxa de consumo da consulta .......................................................................... 61
5
Histórico de alterações do FTDW
A tabela a seguir fornece uma lista das principais alterações ou atualizações das versões do
Guia de Referência do Fast Track Data Warehouse.
Descrição
Versão
Novidade no SQL
Server 2012
4.0
Novidade no SQL
4.0
Server 2012
Novidade no SQL
4.0
Server 2012
Novidade no SQL
4.0
Server 2012
Novidade no SQL
4.0
Server 2012
Novidade no SQL
4.0
Server 2012
Novidade no SQL
4.0
Server 2012
Tabela 1: Histórico de alterações
Observação
Links para outros documentos
de práticas recomendadas do
SQL Server
Avaliação de desempenho
e validação
Local
Requisitos de memória
RAM
Índices columnstore otimizados
pela memória xVelocity
Armazenamento de estado
sólido
Validação e índices
columnstore
Validação de E/S de linha
de base
Importante
Cuidado
Índices columnstore
Estado sólido
Validação
SQLIO
Introdução
Este documento define a arquitetura de componentes e a metodologia do programa FTDW
(Fast Track Data Warehouse) do SQL Server. O resultado dessa abordagem é a validação
de uma arquitetura mínima de sistema de banco de dados do Microsoft SQL Server, incluindo
software e hardware, necessária para você obter e manter uma linha de base de desempenho
pronta para uso com muitas cargas de trabalho de data warehouse.
Público-alvo
O público-alvo deste documento inclui planejadores de TI, arquitetos, DBAs e usuários de BI
(business intelligence) interessados em escolher arquiteturas comprovadas do sistema padrão
para cargas de trabalho do SQL Server em conformidade com o FTDW.
Fast Track Data Warehouse
A iniciativa Fast Track Data Warehouse do SQL Server oferece uma metodologia básica
e exemplos concretos para a implantação de configuração equilibrada de hardware e banco
de dados para uma carga de trabalho de data warehouse. Para obter mais informações,
consulte a seção Carga de trabalho do FTDW deste documento.
6
O equilíbrio é uma medida dos principais componentes de uma instalação do SQL Server:
armazenamento, servidor, rede de armazenamento, banco de dados e sistema operacional.
Cada um desses componentes é ajustado para uma configuração ideal. A meta é atingir um
equilíbrio imediato e eficiente entre a capacidade de processamento de dados do SQL Server
e os recursos dos componentes de hardware. De modo ideal, sua configuração incluirá
hardware mínimo de sistema para atender aos requisitos de armazenamento e desempenho
para uma carga de trabalho de data warehouse.
Fast Track
A marca SQL Server Fast Track identifica uma configuração de hardware de componente que
está de acordo com os princípios da FTRA (arquitetura de referência do FTDW). Cada FTRA
é definida por uma carga de trabalho e um conjunto principal de diretrizes de práticas
recomendadas, configuração, validação e banco de dados. Veja a seguir importantes princípios
do programa Fast Track:



Avaliações de desempenho específicas à carga de trabalho. O design e a configuração
do sistema se baseiam nas cargas de trabalho reais de consultas simultâneas.
Especificações detalhadas e validadas do componente de hardware.
Equilíbrio da arquitetura de componentes entre a capacidade do banco de dados e os
principais recursos de hardware.
Proposta de valor
Os seguintes princípios criam a base da proposta de valor do FTDW:



Equilíbrio predeterminado entre os principais componentes do sistema.
Isso minimiza o risco de gasto excessivo de recursos de CPU ou armazenamento
que nunca será percebido no nível de aplicativo.
Desempenho imediato e previsível. As configurações de Fast Track são integradas
à capacidade que já é compatível com os recursos do aplicativo SQL Server para uma
carga de trabalho e um servidor selecionados.
Centrado na carga de trabalho. Em vez de ser uma abordagem única para todas as
configurações de bancos de dados, a abordagem FTDW é especificamente alinhada
a um caso de uso de data warehouse.
Metodologia
Arquitetura de componentes holística
As arquiteturas de referência do FTDW do SQL Server fornecem uma estrutura prática para
equilibrar as relações complexas entre os principais componentes da arquitetura do sistema de
banco de dados. Conhecida de forma genérica como uma pilha, a arquitetura de componentes
é ilustrada na Figura 1.
7
Figura 1: Exemplo de arquitetura de componentes de banco de dados Fast Track
Cada componente da pilha é um link em uma cadeia de operações necessárias para processar
dados no SQL Server. Avaliar a pilha como um sistema integrado permite fazer uma avaliação
de desempenho que estabelece a largura de banda real de cada componente. Isso assegura
que os componentes individuais forneçam uma taxa de transferência suficiente para os
recursos do aplicativo SQL Server da pilha prescrita.
Abordagem otimizada de carga de trabalho
As diferentes cargas de trabalho do aplicativo de banco de dados podem exigir arquiteturas
de componentes muito diferentes para atingir um equilíbrio de recursos ideal. Um exemplo
clássico disso pode ser o contraste entre cargas de trabalho OLTP (processamento de
transação online) baseadas em pesquisa com solicitações pequenas e data warehouses
analíticos com um volume intenso de exames e solicitações grandes. Os casos de uso do
OLTP foram estritamente indexados para oferecer suporte à recuperação de baixa latência
de pequenos números de linhas de conjuntos de dados que, muitas vezes, apresentam
pouco volume de dados históricos. Esses tipos de operações de banco de dados induzem um
movimento significativo do cabeçote de disco e geram padrões clássicos de exame aleatório
de E/S. Os casos de uso analíticos, como o data warehouse, podem envolver solicitações de
dados muito grandes e se beneficiam bastante do potencial aumento da taxa de transferência
total de exames de disco sequenciais.
Para esses casos de uso opostos, as implicações de uma pilha de componentes equilibrada
são significativas. Em média, as taxas de exame aleatório de E/S por disco para as unidades
de disco SAS modernas podem ser 10 vezes mais lentas quando comparadas às taxas de
exame sequencial no mesmo hardware. Com cargas de trabalho de data warehouse Fast
Track, o foco é atingir altas taxas de exame de E/S de modo consistente (medidas em MB/s),
tirando o foco mais tradicional das operações por segundo (medidas em IOPS).
8
O desafio de cargas de trabalho muito diferentes é resolvido pela definição clara de atributos
das cargas de trabalho do cliente. As cargas de trabalho Fast Track do SQL Server incluem
uma lista qualitativa de atributos que definem de modo exclusivo um caso de uso comum
do aplicativo de banco de dados. Além disso, cada carga de trabalho é representada pelas
medidas quantitativas que incluem consultas padrão de análise de desempenho. A avaliação
de desempenho específica à carga de trabalho é usada para validar a configuração do banco
de dados, as práticas recomendadas e as recomendações de hardware de componentes.
Configurações de referência do SQL Server Fast Track validadas
Todas as arquiteturas de referência Fast Track publicadas são validadas de acordo com
o conjunto de princípios e diretrizes fornecidos neste guia de referência. Exemplos desse
processo podem ser encontrados em seções posteriores deste documento.
Resumo
A especificação FTDW do SQL Server descrita neste guia de referência é centrada na carga de
trabalho e equilibrada por componente. Essa abordagem reconhece que um provisionamento
único pode ser ineficaz e oneroso para muitos casos de uso do banco de dados. Os requisitos
empresariais cada vez mais complexos combinados aos volumes de dados cada vez maiores
exigem uma abordagem mais realista. Ao apresentar uma combinação de arquiteturas de
referência prescritiva, avaliação de componentes de hardware e software e cargas de trabalho
claramente direcionadas, este documento fornece uma abordagem prática para se obter
arquiteturas de componentes equilibradas.
Carga de trabalho do FTDW
Padrões de carga de trabalho de data warehouse
Geralmente, as perguntas feitas em data warehouses exigem acesso a grandes volumes
de dados. Os data warehouses precisam dar suporte a uma variedade de consultas de um
público-alvo abrangente (por exemplo: equipes de finanças, marketing, operações e pesquisa).
Para superar as restrições de sistemas de data warehouse tradicionais, as organizações
recorreram ao uso de técnicas tradicionais de otimização do RDBMS, como criação de índices,
pré-agregação de dados e limitação de acesso a níveis de dados inferiores. As sobrecargas
de manutenção associadas a essas abordagens, muitas vezes, podem oprimir até mesmo as
janelas de lote generosas. À medida que um data warehouse se desenvolve e o público-alvo
aumenta, o suporte a essas otimizações específicas ao caso de uso torna-se cada vez mais
desafiador, principalmente no caso de dados ou correções de dados que chegam de última hora.
9
Uma solução comum para esse desafio é simplesmente adicionar unidades; não é incomum
ver centenas de discos oferecendo suporte a um data warehouse relativamente pequeno na
tentativa de superar as limitações de desempenho de E/S no mapeamento de uma infraestrutura
de E/S baseada em busca para uma carga de trabalho baseada em exame. Isso é visto com
frequência em grandes ambientes de rede SAN compartilhada que tradicionalmente são
otimizados para buscas. Vários padrões e técnicas de referência de E/S de armazenamento
incentivam o acesso aleatório de E/S, introduzindo a latência de disco e reduzindo a taxa de
transferência geral do subsistema de armazenamento para uma carga de trabalho de data
warehouse com grande volume de exames.
O Fast Track Data Warehouse é uma maneira diferente de otimizar as cargas de trabalho
do data warehouse. Ao alinhar os arquivos de banco de dados e a configuração ao acesso
eficiente de exame de disco (em vez de busca), o desempenho obtido de discos individuais
pode ser muito mais alto. O aumento do desempenho por disco resultante reduz o número
de discos necessários para gerar uma taxa de transferência de E/S suficiente para atender
à capacidade do SQL Server de processar dados para uma determinada carga de trabalho.
Além disso, você pode evitar algumas técnicas de otimização baseadas em índice usadas para
melhorar a busca no disco.
Avaliação da carga de trabalho
Ao analisar as cargas de trabalho de sistemas baseados em FTDW, é importante considerar
o ajuste em relação às práticas e configurações do sistema descritas neste documento.
Os requisitos de data warehouse podem variar de acordo com o cliente e determinados
requisitos, como a replicação do banco de dados, que podem não ser apropriados para todos
os sistemas desenvolvidos para FTDW. Os principais critérios iniciais desse tipo de avaliação
de carga de trabalho são descritos abaixo.
Alto volume de exames
As consultas em uma carga de trabalho do data warehouse frequentemente examinam
um grande número de linhas. Por esse motivo, o desempenho do exame de disco se torna
cada vez mais uma prioridade em comparação com as cargas de trabalho transacionais,
que enfatizam o tempo de busca no disco. A arquitetura de referência do FTDW otimiza
o hardware e os componentes de software do banco de dados priorizando o desempenho do
exame de disco. Isso resulta em leituras de disco mais eficientes e um aumento correlacionado
na taxa de transferência de E/S do disco por unidade.
Não volátil
Depois que os dados são gravados, eles raramente são alterados. As operações DML, como
a atualização do SQL, que movem páginas associadas à mesma tabela de banco de dados para
fora do alinhamento contíguo, devem ser cuidadosamente gerenciadas. As cargas de trabalho
que normalmente introduzem tal volatilidade podem não estar bem-alinhadas ao FTDW. Onde
ocorre a volatilidade, recomendamos a manutenção periódica para reduzir a fragmentação.
10
Menos índices
O acréscimo de índices não clusterizados costuma adicionar desempenho às pesquisas de
um ou alguns registros. Se índices não clusterizados forem aplicados às tabelas em que um
grande número de linhas deve ser recuperado, o aumento resultante nas operações de busca
aleatórias no disco poderá diminuir o desempenho geral do sistema. A manutenção dos índices
também pode adicionar sobrecarga significativa de gerenciamento de dados, o que pode
colocar em risco o SLA (contrato de nível de serviço) e a capacidade de atender às janelas
de carregamento do banco de dados.
Em contrapartida, as taxas de exames sequenciais podem ser muito mais altas (10 vezes ou
mais) do que as taxas de acesso aleatório. Um sistema que minimiza o uso de busca aleatória,
induzindo a índices secundários, geralmente obtém uma média mais alta de taxas de E/S
sustentadas. Isso significa um uso mais eficiente de recursos de E/S de armazenamento e um
desempenho mais previsível para consultas grandes do tipo exame.
A metodologia do FTDW prescreve técnicas de otimização do banco de dados que se alinham
com as características da carga de trabalho de destino. O particionamento por intervalos
e o índice clusterizado são exemplos de estruturas de dados que oferecem suporte à E/S
de disco baseada em exame, e nós os recomendamos como as principais ferramentas de
otimização baseada na arquitetura de dados para ambientes FTDW.
Alinhamento por partição
Uma característica comum das cargas de trabalho do FTDW é a capacidade de aproveitar
o particionamento do SQL Server. O particionamento pode simplificar o gerenciamento do
ciclo de vida dos dados e ajudar a minimizar a fragmentação ao longo do tempo. Além disso,
os padrões de consulta para grandes exames podem aproveitar a qualificação da partição
de intervalos e reduzir significativamente o tamanho dos exames de tabela, sem sacrificar
a fragmentação ou a taxa de transferência de E/S do disco.
Considerações adicionais
As considerações adicionais a seguir devem ser levadas em consideração durante a avaliação
de uma carga de trabalho do banco de dados:


11
A implementação e o gerenciamento de uma estratégia de otimização do banco de
dados com poucos índices é um requisito fundamental para cargas de trabalho do FTDW.
Supõe-se que seja mantida uma fragmentação de dados mínima no data warehouse.
Isso implica no seguinte:
o O tipo de fragmentação de maior preocupação pode ser medido em termos
de tamanho do fragmento. Um fragmento representa alocações contíguas de
páginas de banco de dados de 8K.
o A expansão do servidor adicionando armazenamento exige que todas as tabelas
sensíveis ao desempenho sejam novamente populadas de modo consistente
com as diretrizes descritas neste documento.
o

A implementação de estruturas de dados voláteis, como tabelas com atividade
de atualização regular no nível de linha, pode exigir manutenção frequente
(como desfragmentação ou recompilações de índice) para reduzir a fragmentação.
o O carregamento de tabelas de índice de cluster com lotes de IDs de chave
de cluster que sobrepõem intervalos existentes é uma fonte frequente de
fragmentação. Esse carregamento deve ser cuidadosamente monitorado
e gerenciado de acordo com as práticas recomendadas fornecidas neste guia
de referência.
O data warehouse pode ter significados diferentes para públicos distintos. É preciso
tomar cuidado ao avaliar as necessidades do cliente em relação aos atributos de carga
de trabalho do FTDW.
Atributos qualitativos de carga de trabalho de data warehouse
Você pode definir a carga de trabalho do FTDW usando as propriedades das áreas de assunto
a seguir relacionadas às operações de banco de dados:




Requisitos de usuário e padrão de acesso
Modelo de dados
Arquitetura de dados
Otimização do banco de dados
A tabela a seguir resume atributos de carga de trabalho de data warehouse comparados a uma
carga de trabalho OLTP ou ODS (repositório de dados operacional).
Atributo
Descrição do
caso de uso
12
Afinidade da carga de trabalho:
Data warehouse
 Basicamente leitura (90%-10%)
 Atualizações geralmente
limitadas aos requisitos de
qualidade de dados
 Inserções em massa de alto
volume
 Simultaneidade geral de consulta
de média a baixa; solicitação
máxima de consultas
simultâneas variando de 10 a 30
 Taxa de transferência de consulta
simultânea caracterizada pelas
necessidades de análise
e relatório
 Agregações e/ou exames de
intervalos grandes
 Consultas complexas
(filtro, junção, agrupamento,
agregação)
OLTP/ODS
 Proporção equilibrada de leitura
e atualização (60%-40%)
 Taxa de transferência de
consulta simultânea
caracterizada pelas
necessidades operacionais
 Inserções e atualizações
refinadas
 Alta taxa de transferência de
transações (por exemplo,
10s K/s)
 Simultaneidade geral de
usuários de média a alta;
solicitação máxima de
consultas simultâneas variando
de 50 a 100 ou mais
 Transações normalmente muito
curtas (por exemplo, pesquisas
mínimas e discretas de linhas)
Atributo
Afinidade da carga de trabalho:
Data warehouse
OLTP/ODS
Modelo de dados  Modelo centralizado de data
 Modelo de dados operacional
warehouse altamente normalizado
altamente normalizado
 Desnormalização como suporte
 Desnormalização frequente
aos requisitos de relatório,
para apoio à decisão;
geralmente atendidos por
pesquisas discretas de baixa
aplicativos de BI, como o SQL
latência e alta simultaneidade
Server Analysis Services
 A retenção de dados históricos
 Estruturas de dados
é limitada
dimensionais hospedadas no
 Modelos de dados
banco de dados com alto volume
desnormalizados extraídos
de solicitações analíticas
de outros sistemas de origem
e simultaneidade relativamente
como suporte à tomada de
baixa
decisões de eventos
 Os exames de intervalos grandes
operacionais
são comuns

Casos de uso analíticos ad hoc
Arquitetura de
 Uso significativo de estruturas
 Uso mínimo de estruturas de
dados
de tabela heap
tabela heap
 Tabelas grandes particionadas
 Estruturas de tabela de índice
com índices clusterizados
clusterizado que oferecem
oferecem suporte a exames
suporte a pesquisas detalhadas
restritos por intervalo
de registros (de uma a algumas
linhas por solicitação)
 Tabelas de fatos muito grandes
(por exemplo, de centenas de
 Tabelas de fatos menores
gigabytes a vários terabytes)
(por exemplo, de menos de
100 GB)
 Tamanhos de dados muito
grandes (por exemplo, de
 Tamanhos de dados
centenas de terabytes a um
relativamente pequenos (por
petabyte)
exemplo, de alguns terabytes)
Otimização do
 Uso mínimo de índices
 Otimização da utilização
banco de dados
secundários (anteriormente
intensa de índices secundários
descrito como menos índice)
 O particionamento é comum
Tabela 2: Atributos de carga de trabalho de data warehouse
Escolhendo uma configuração de referência do FTDW
Há três abordagens gerais para usar a metodologia FTDW descrita neste documento. As duas
primeiras são específicas ao uso de arquiteturas de referência publicadas e em conformidade
com Fast Track para data warehouse. Essas abordagens permitem a seleção de sistemas
previamente projetados publicados como parte do programa FTDW. A terceira abordagem trata
a principal metodologia Fast Track como uma diretriz para a criação de um sistema de data
warehouse definido pelo usuário. Essa abordagem final requer a criação detalhada de perfis de
carga de trabalho e a avaliação de desempenho do sistema antes da compra ou implantação.
Ela exige um alto conhecimento técnico das áreas de configuração de armazenamento
e servidor empresarial, bem como da otimização de banco de dados do SQL Server.
13
Opção 1: Avaliação Básica
Neste cenário, o cliente já escolheu uma configuração de referência do FTDW ou tem métodos
alternativos para determinar os requisitos de CPU e servidor. Ao usar esta opção, você não
precisará fazer uma avaliação completa da plataforma (ou seja, uma prova de conceito).
Etapa 1: Avaliar o caso de uso do cliente
As configurações de referência do Fast Track Data Warehouse não são configurações
únicas de software e hardware. Em vez disso, as configurações são feitas de acordo com as
características de uma carga de trabalho de data warehouse. A primeira etapa da escolha de
uma configuração é identificar essas características; comece examinando as principais áreas
de requisitos e padrões de uso do seu cliente.
Carga de trabalho
As definições de carga de trabalho do FTDW fornecem dois pontos essenciais para a avaliação
do caso de uso. O primeiro é um conjunto de princípios básicos que definem elementos
fundamentais da carga de trabalho em relação ao desempenho do SQL Server. Esses princípios
devem ser cuidadosamente medidos para um determinado caso de uso, pois os conflitos
podem indicar que uma carga de trabalho de destino não é adequada para uma arquitetura de
referência do FTDW.
O segundo componente para uma carga de trabalho é uma descrição geral do caso de uso
pretendido. Isso oferece uma descrição útil de alto nível do caso de uso, além de fornecer
um ponto de partida razoável para avaliar a adequação da carga de trabalho.
Avaliação da carga de trabalho
A lista a seguir detalha um processo básico para avaliação da carga de trabalho do cliente.
Essa é uma avaliação qualitativa e deve ser considerada uma diretriz:
1. Defina os requisitos da carga de trabalho de meta. Compare e diferencie os atributos de
carga de trabalho do FTDW. Para obter mais informações, consulte a seção Carga de
trabalho do FTDW deste documento.
2. Avalie as práticas recomendadas do FTDW. As práticas relacionadas ao gerenciamento
do banco de dados e à otimização do sistema e da arquitetura de dados devem ser
avaliadas em relação ao caso de uso e ao ambiente operacional de destino.
Tomando uma decisão
A meta dessa avaliação de carga de trabalho é garantir que uma decisão totalmente informada
possa ser tomada na escolha de uma arquitetura de referência do FTDW validada. Na realidade,
a maioria dos cenários de data warehouse representa uma mistura de atributos em conformidade
e conflitantes relativos à carga de trabalho do FTDW. Os atributos de carga de trabalho de alta
prioridade com uma forte afinidade para configurações de referência Fast Track são listados
aqui; os principais casos de uso de clientes que entram em conflito direto com qualquer um
desses atributos devem ser cuidadosamente avaliados, pois podem invalidar a metodologia
para o caso de uso.
14
Carga de trabalho
Os seguintes atributos de carga de trabalho são de alta prioridade:



As cargas de trabalho críticas apresentam padrões de acesso a dados de alto volume
de exames (isto é, aqueles que podem se beneficiar do posicionamento sequencial de
dados). Em geral, as solicitações de consultas individuais envolvem leituras de dezenas
de milhares a milhões (ou mais) de linhas.
Alta capacidade de dados e baixa simultaneidade em relação a cargas de trabalho
OLTP comuns.
Baixa volatilidade de dados. A atividade DML frequente de atualização/exclusão deve
ser limitada a uma pequena porcentagem da superfície geral do data warehouse.
Gerenciamento de bancos de dados
Inclui administração de banco de dados, arquitetura de dados (estrutura de tabela e modelo de
dados) e práticas de integração de dados:



Arquitetura de dados particionada e com poucos índices.
Gerenciamento cuidadoso da fragmentação do banco de dados com estratégias
adequadas de carregamento e ETL, além de manutenção periódica.
Requisitos previsíveis de aumento de dados. Os sistemas FTDW são preparados para
a uma capacidade totalmente equilibrada. A expansão do armazenamento requer
a migração de dados.
Etapa 2: Escolher uma arquitetura de referência do FTDW publicada
Um cliente pode ter um servidor em mente ao fazer uma avaliação simples com base no
orçamento ou na experiência. Como alternativa, o cliente já deve ter uma boa ideia da
capacidade de carga de trabalho ou de um sistema existente no qual basear a análise dos
requisitos de largura de banda. Seja qual for o caso, não execute uma avaliação completa
de plataforma em uma avaliação básica do FTDW. Em vez disso, selecione uma configuração
FTDW em conformidade que corresponda aos requisitos estimados do seu cliente.
Opção 2: Avaliação Completa
As arquiteturas de referência em conformidade com Fast Track fornecem configurações de
componentes de hardware de acordo com as cargas de trabalho definidas pelo cliente.
A metodologia a seguir permite uma abordagem otimizada para escolher uma arquitetura de
componentes de banco de dados que assegure o melhor equilíbrio imediato entre os requisitos
do caso de uso, o desempenho e a escalabilidade. Essa abordagem presume um alto grau de
experiência na arquitetura do sistema de banco de dados e na implantação do data warehouse.
Os parceiros de Fast Track e os recursos técnicos de vendas da Microsoft geralmente são
envolvidos nesse processo.
15
Visão geral do processo
O fluxo de processo a seguir resume o processo de seleção da Avaliação Completa do FTDW:
1. Avalie os atributos de carga de trabalho Fast Track em relação ao cenário de uso
de destino.
2. Identifique os requisitos de servidor e/ou largura de banda para o caso de uso do
cliente. É necessário escolher uma configuração de referência publicada do FTDW
antes de iniciar uma avaliação.
3. Identifique uma consulta que represente o requisito de carga de trabalho do cliente.
4. Calcule a BCR (taxa de consumo de avaliação de desempenho) do SQL Server para
a consulta.
5. Calcule a UDC (capacidade de dados de usuários) exigida.
6. Compare as classificações de BCR e UDC com as classificações de Capacidade
e MCR (taxa máxima de consumo de CPU) publicadas para arquiteturas de referência
Fast Track em conformidade.
A tabela a seguir descreve pontos individuais do fluxo de processo da Avaliação Completa
em detalhes.
Etapa 1: Avaliar o caso de uso do cliente
Avaliação da carga de trabalho
Esse processo é o mesmo da Opção 1: Avaliação Básica.
Selecionar o hardware de avaliação do FTDW
Antes de iniciar uma avaliação completa do sistema, você deve escolher e implantar uma
configuração de referência do FTDW publicada para teste. Você pode escolher entre vários
métodos para identificar uma configuração de referência apropriada. Veja a seguir as
abordagens comuns:




16
Orçamento. O cliente opta por comprar o sistema de capacidade mais alta e/ou
o sistema de mais alto desempenho para o orçamento disponível.
Desempenho. O cliente opta por comprar o sistema de mais alto desempenho
disponível.
Análise interna. A decisão é baseada na análise de carga de trabalho que o cliente
executou no hardware existente.
Análise ad hoc. A Ferramenta de dimensionamento do FTDW fornece uma abordagem
básica para calcular os requisitos de sistema para o FTDW com base nas suposições
básicas sobre a carga de trabalho do banco de dados de destino. Essa ferramenta de
planilha está disponível para download em
http://download.microsoft.com/download/D/F/A/DFAAD98F-0F1B-4F8B-988F22C3F94B08E0/Fast%20Track%20Core%20Calculator%20v1.2.xlsx.
Etapa 2: Estabelecer métricas de avaliação
As três métricas a seguir são importantes para uma avaliação completa do FTDW e abrangem
os principais critérios de decisão para avaliação de hardware:
 MCR (taxa máxima de consumo do núcleo de CPU)
 BCR (taxa de consumo de avaliação de desempenho)
 UDC (capacidade de dados de usuário) exigida
Para obter mais informações sobre como calcular essas métricas, consulte a seção Avaliação
de desempenho e validação deste documento.
MCR
Essa métrica mede a taxa máxima de processamento de dados do SQL Server para uma
consulta padrão e o conjunto de dados para uma combinação específica de servidor e CPU.
Ela é fornecida como uma taxa por núcleo, sendo medida como um exame baseado em
consulta no cache de memória. A MCR é o ponto de partida inicial para design do sistema Fast
Track. Ela representa uma largura de banda de E/S máxima estimada exigida para o servidor,
a CPU e a carga de trabalho. A MCR é útil como um guia de design inicial, pois requer apenas
um armazenamento local mínimo e um esquema de banco de dados para fazer uma estimativa
da taxa de transferência potencial para uma determinada CPU. É importante reforçar que
a MCR é usada como um ponto de partida para o design do sistema – não é uma medida de
desempenho do sistema.
BCR
A BCR é medida por um conjunto de consultas que são consideradas definitivas da carga de
trabalho do FTDW. Ela é calculada em termos de largura de banda total lida no disco e no
cache, e não somente no cache, como ocorre no cálculo da MCR. A BCR pode permitir
a personalização da infraestrutura para um determinado caso de uso do cliente, sendo medida
em um conjunto de consultas correspondente aos padrões de carga de trabalho do cliente.
Ou, no caso da FTRA validada pelo parceiro, é usado um conjunto de consultas de avaliação
de desempenho, garantindo que os sistemas sejam projetados para cargas de trabalho de alta
pressão. Em resumo, a BCR é uma medida real de processamento de dados que usa várias
consultas sujeitas à carga de trabalho simultânea em volumes de dados significativos.
Capacidade de dados de usuários
Essa é a capacidade de banco de dados prevista para o banco de dados do SQL Server.
A capacidade de dados de usuários Fast Track é responsável pela compactação do banco
de dados pós-carregamento e representa uma estimativa da quantidade de fluxos ou arquivos
de dados de usuários não compactados que podem ser carregados no sistema Fast Track.
A proporção de compactação padrão usada para o FTDW é 3,5:1.
Observe que qualquer expansão de armazenamento além da implantação inicial pode exigir
a migração de dados, que efetivamente dividiria os dados existentes pelos novos locais
do arquivo de banco de dados. Por esse motivo, é importante levar em consideração
o crescimento esperado do banco de dados e a expectativa de vida do sistema ao escolher
uma arquitetura de referência apropriada.
17
Etapa 3: Escolher uma arquitetura de referência do Fast Track Data Warehouse
Depois de calculada, a BCR pode ser comparada com uma MCR publicada e com
classificações de capacidade fornecidas pelos parceiros Fast Track para cada FTRA publicada.
Para obter mais informações sobre nossos parceiros, consulte Fast Track Data Warehouse
(http://www.microsoft.com/sqlserver/pt/br/solutions-technologies/data-warehousing/fast-track.aspx).
Você pode usar a métrica BCR como um ponto de referência comum para avaliar resultados
do sistema de teste/avaliação em relação às configurações publicadas. A partir dos dados de
BCR, seu cliente pode escolher a opção Fast Track que melhor se alinhe com os resultados
do teste.
Opção 3: Arquiteturas de referência definidas pelo usuário
Essa abordagem aproveita a metodologia FTDW para adaptar um sistema a uma carga de
trabalho ou um conjunto de hardwares específico. Essa abordagem requer uma compreensão
completa tanto do SQL Server quanto dos componentes de hardware nos quais ele é executado.
As etapas a seguir descrevem a abordagem geral para desenvolver uma arquitetura de
referência definida pelo usuário que esteja em conformidade com os princípios do FTDW.
Etapa 1: Definir a carga de trabalho
Compreender o caso de uso do banco de dados de destino é fundamental para as
configurações do FTDW, e isso se aplica igualmente a qualquer aplicação personalizada
das diretrizes fornecidas neste documento. As diretrizes para FTRAs, especificamente sobre
o tópico de cargas de trabalho, pode ser usada como um modelo de referência para incorporar
a avaliação da carga de trabalho no design da arquitetura de componentes.
Etapa 2: Estabelecer avaliações de desempenho da arquitetura de componentes
A estrutura a seguir fornece uma estrutura básica para desenvolver uma arquitetura de
referência para uma carga de trabalho predefinida:
1. Estabeleça a MCR (taxa máxima de consumo do núcleo de CPU) para o servidor
e a CPU escolhidos. Use o método descrito na seção Avaliação de desempenho
e validação deste documento para calcular a MCR. Você também pode usar as
classificações de MCR publicadas para configurações do FTDW. Em geral, as CPUs
da mesma família apresentam taxas semelhantes de consumo do núcleo de CPU para
o banco de dados do SQL Server.
2. Use o valor da MCR para fazer a estimativa dos requisitos de armazenamento e rede de
armazenamento e criar um design de sistema inicial.
3. Adquira um sistema de teste baseado no design de sistema inicial. De modo ideal, isso
será a configuração completa especificada.
4. Estabeleça uma BCR (taxa de consumo de avaliação de desempenho). Com base na
avaliação da carga de trabalho, identifique uma consulta ou, no caso ideal, um conjunto
de consultas representativas. Siga as práticas descritas na seção Medindo a BCR para
sua carga de trabalho deste documento.
5. Ajuste o design do sistema com base nos resultados.
6. Estabeleça a configuração final de servidor e armazenamento.
18
Etapa 3: Validação do sistema
A meta da avaliação de desempenho do sistema deve ser a validação da configuração e da
taxa de transferência da configuração de componentes de hardware identificada na Etapa 2.
Para obter mais informações sobre esse processo, consulte a seção Validando a FTRA
definida pelo usuário deste documento. Para validar seu sistema, siga estas etapas:
1. Avalie a taxa de transferência do componente em relação aos requisitos de
desempenho estabelecidos. Isso assegura que a taxa de transferência real do sistema
corresponda às expectativas.
2. Valide a taxa de transferência do sistema recriando a configuração final e executando
avaliações de desempenho finais. Como regra geral, a BCR final deve atingir 80% ou
ser superior à MCR do sistema.
Escolhendo um resumo de FTRA
A tabela a seguir resume as três opções de seleção de FTRA.
Opção
Avaliação básica
Avaliação completa
Arquitetura de referência
definida pelo usuário
Prós
 Aquisição e configuração
bem rápidas do sistema
(dias a semanas)
 Custo minimizado de
design e avaliação
 Menos requisitos de
habilidade de infraestrutura
 Arquitetura de referência
predefinida e personalizada
para a carga de trabalho
esperada
 Potencial para economia
em hardware
 Maior confiança na solução
 Potencial para reutilização
de hardware existente
 Potencial para incorporar
hardware mais recente
 Sistema altamente
personalizado para seu
caso de uso
Tabela 3: Comparação de diferentes opções de avaliação
19
Contras
 Possibilidade de
armazenamento além do
especificado ou de CPU
abaixo do especificado





A avaliação demanda
esforço e tempo (semanas
a meses)
Exige conhecimento
profundo da carga de
trabalho de destino
O processo leva vários
meses
Requer experiência
significativa em
infraestrutura
Requer experiência
significativa em SQL Server
Configuração padrão do FTDW
Arquitetura de componentes de hardware
As arquiteturas de referência atuais do FTDW são baseadas em configurações de armazenamento
dedicado. As opções atualmente publicadas incluem rede SAN alternada, rede SAN de
conexão direta, SAS de conexão direta, SAS-RBOD e iSCSI. A taxa de transferência de E/S de
disco é atingida pelo uso de processadores e compartimentos independentes de armazenamento
dedicado. Os detalhes e as configurações adicionais são publicados por cada fornecedor Fast
Track. A Figura 2 ilustra os blocos de construção no nível de componente que incluem uma
arquitetura de referência do FTDW com base no armazenamento de rede SAN.
Figura 2: Exemplo de configuração de armazenamento para um servidor de dois soquetes
e 12 núcleos
Requisitos e configuração do componente
Memória do servidor
RAM total: a alocação de RAM para FTRAs é baseada nos resultados da avaliação de
desempenho com a meta de equilibrar a taxa de transferência lógica máxima (total de páginas
lidas no disco e no buffer ao longo do tempo) com a utilização de CPU. A Tabela 4 lista
alocações de memória recomendadas para arquiteturas de referência do SQL Server 2012.
Os valores máximos de memória fornecidos não são limites de hardware, mas representam
a média de valores para sistemas validados bem-sucedidos.
20
Tamanho do servidor
Memória mínima
Memória máxima
1 soquete
64 GB
128 GB
2 soquetes
128 GB
256 GB
4 soquetes
256 GB
512 GB
8 soquetes
512 GB
768 GB
Tabela 4: Alocações de memória recomendadas para o SQL Server 2012
As considerações a seguir também são importantes na avaliação dos requisitos de memória
do sistema:




Consulta no cache: cargas de trabalho que atendem a uma grande porcentagem de
consultas no cache podem receber um benefício geral proveniente do aumento das
alocações de RAM à medida que a carga de trabalho aumenta.
Junções e classificações de hash: consultas que dependem de junções de hash
em grande escala ou executam operações de classificação em grande escala se
beneficiarão de grandes quantidades de memória física. Com uma memória menor,
essas operações são despejadas no disco e utilizam intensamente tempdb, o que
introduz um padrão de E/S aleatório nas unidades de dados no servidor.
Cargas: as inserções em massa também podem introduzir operações de classificação
que utilizam tempdb, caso não possam ser processadas na memória disponível.
Índice columnstore otimizado para a memória xVelocity: as cargas de trabalho que
favorecem intensamente os planos de consulta do índice columnstore são executadas
de modo mais eficiente com pools de memória no nível mais alto dos intervalos listados
na Tabela 4.
Rede SAN Fibre Channel
HBA – SAN: todos os componentes de rede HBA e SAN variam até certo ponto por fabricante
e modelo. Além disso, a taxa de transferência do compartimento de armazenamento pode
estar relacionada à configuração da rede SAN e aos recursos de barramento de PCIe.
Essa recomendação é uma diretriz geral e é consistente com o teste realizado durante
o desenvolvimento da configuração de referência de FTDW.
Se for usado o zoneamento, somente as portas em uso do Fast Track deverão existir na(s)
zona(s). A topologia e a configuração detalhadas da rede FC são documentadas no Guia de
Configuração Técnica fornecido por cada parceiro Fast Track e específicas a cada FTRA
publicada.
MPIO (Multipath I/O): o MPIO deve ser configurado. Cada volume hospedado em matrizes de
armazenamento dedicado deve ter pelo menos um caminho Ativo.
O rodízio com subconjunto é a política padrão usada para configurações Fast Track, mas
raramente é usado para arquiteturas de referência do parceiro, pois configurações mais
adequadas são identificadas pelas equipes de engenharia do parceiro de FTDW. DSMs e/ou
documentos específicos ao parceiro, muitas vezes, prescrevem diferentes configurações
e devem ser revisados antes da configuração.
21
Armazenamento
Disco local: uma matriz RAID1 de dois discos é a alocação mínima para a instalação do
Windows Server e do SQL Server. É necessário alocar espaço em disco suficiente para
requisitos de paginação e RAM virtual. Em geral, a RAM do sistema com mais de 250 GB ou
1,5 vez maior deve estar disponível no espaço livre em disco. A configuração de disco restante
depende do caso de uso e da preferência do cliente.
Sistema de arquivos lógico: a montagem de LUNs para caminhos de pasta do ponto de
montagem no Windows, em vez de letras de unidade, é preferível ao número de volumes em
muitos sistemas Fast Track.
Também pode ser útil entender qual atribuição de unidade do sistema operacional Windows
representa qual LUN (volume), grupo de discos RAID e ponto de montagem do Windows
Server nos compartimentos de armazenamento. Você pode adotar um esquema de
nomenclatura para os volumes e pontos de montagem ao montar LUNs para pastas do
Windows. Para obter mais informações sobre esquemas de nomenclatura de dispositivos,
consulte as diretrizes de configuração técnica do parceiro de Fast Track.
Você pode usar ferramentas específicas do fornecedor para obter o esquema de nomenclatura
de volume recomendado. Se não houver uma ferramenta adequada, você poderá disponibilizar
um disco para o Windows das matrizes de armazenamento enquanto atribui nomes de
unidades para garantir a topologia correta de físico para lógico.
Sistema de arquivos físico: para obter mais informações, incluindo instruções detalhadas,
consulte a seção Configuração de aplicativos deste documento.
Configuração do compartimento de armazenamento: todas as configurações de compartimento
permanecem em seus padrões, a menos que documentado em contrário pela documentação
técnica do parceiro Fast Track. As especificações de FTDW para configuração do sistema de
arquivos exigem compartimentos de armazenamento que permitem configuração específica
de agrupamentos RAID e atribuições de LUN. Isso deve ser levado em consideração para
qualquer configuração de referência do FTDW, substituições de hardware ou avaliações de
hardware personalizado.
Configuração de aplicativos
Windows Server 2008 R2
Salvo indicação em contrário, as configurações padrão devem ser usadas para o sistema
operacional Windows Server 2008 R2 Enterprise. Verifique se o service pack mais recente
e todas as atualizações críticas foram aplicadas. O recurso Multipath I/O é exigido para
diversas arquiteturas de referência. Para obter mais informações sobre a configuração
detalhada do MPIO, consulte o guia de instalação técnica do parceiro Fast Track para
a arquitetura de referência fornecida. Verifique se o Windows Server 2008 R2 está instalado
como uma função de Servidor de Aplicativos para garantir a instalação e os padrões
adequados do .NET Framework.
22
SQL Server 2012 Enterprise
Opções de inicialização
-E deve ser adicionado às opções de inicialização. Isso aumenta o número de extensões
contíguas em cada arquivo que são alocadas para uma tabela de banco de dados à medida
que ela cresce. Isso melhora o acesso sequencial ao disco. Para obter mais informações
sobre essa opção, consulte o artigo 329526 da Base de Dados de Conhecimento da Microsoft
(http://support.microsoft.com/kb/329526). É importante garantir que a opção -E tenha entrado
em vigor na inicialização do banco de dados. A opção diferencia letras maiúsculas de minúsculas
e o formato. O espaço em branco antes ou depois da opção pode impedir a inicialização.
-T1117 também deve ser adicionado às opções de inicialização. Esse sinalizador de
rastreamento garante o aumento de todos os arquivos em um grupo de arquivos no caso
de habilitação do aumento automático. A recomendação padrão do FTDW para aumento do
arquivo de banco de dados é a pré-alocação, em vez do aumento automático (com exceção
de tempdb). Para obter mais informações, consulte a seção Detalhes da configuração do
armazenamento deste documento.
Habilite a opção Bloquear Páginas na Memória. Para obter mais informações, consulte Como
habilitar a opção Bloquear Páginas na Memória (http://go.microsoft.com/fwlink/?LinkId=141863).
-T834 deve ser avaliado caso a caso. Esse sinalizador de rastreamento pode aumentar
as taxas de transferência para muitas cargas de trabalho do data warehouse. Ele permite
alocações de páginas grandes na memória para o pool de buffers do SQL Server. Para obter
mais informações sobre esse e outros sinalizadores de rastreamento, consulte o artigo 920093
da Base de Dados de Conhecimento da Microsoft (http://support.microsoft.com/kb/920093).
Observação: no momento, o SQL Server 2012 não oferece suporte ao uso de –T834 se os
índices columnstore estiverem em uso no banco de dados. Se você planeja usar índices
columnstore, não use esse sinalizador de rastreamento.
Memória máxima do SQL
No SQL Server 2012, não deve ser alocado para o SQL Server mais de 92% do total de RAM
do servidor. Se aplicativos adicionais forem compartilhar o servidor, a quantidade de RAM
restante disponível para o sistema operacional deverá ser ajustada adequadamente. Essa
configuração é controlada pela opção max server memory. Para obter mais informações
sobre as configurações de memória para arquiteturas de referência validadas, consulte
a documentação do parceiro FTDW.
Administrador de Recursos
As cargas de trabalho do data warehouse geralmente incluem consultas complexas que
operam em grandes volumes de dados. Essas consultas podem consumir grandes quantidades
de memória e podem ser despejadas no disco se a memória for restrita. Esse comportamento
tem implicações específicas em termos de gerenciamento de recursos. Você pode usar
a tecnologia Administrador de Recursos no SQL Server 2012 para gerenciar o uso de recursos.
23
Nas configurações padrão do SQL Server, o Administrador de Recursos fornece um máximo de
25% de recursos de memória do SQL Server para cada sessão. Isso significa que, na pior das
hipóteses, três consultas pesadas o suficiente para consumir pelo menos 25% da memória
disponível bloquearão qualquer outra consulta que consuma muita memória. Nesse estado,
todas as consultas adicionais que exigem uma grande quantidade de memória para serem
executadas serão enfileiradas até que os recursos se tornem disponíveis.
Você pode usar o Administrador de Recursos para reduzir a memória máxima consumida
por consulta. No entanto, como resultado, as consultas simultâneas que, de alguma forma,
consumiriam grandes quantidades de memória passam a usar tempdb, introduzindo mais E/S
aleatória, o que pode reduzir a taxa de transferência geral. Embora isso possa ser benéfico
para muitas cargas de trabalho de data warehouse para limitar a quantidade de recursos do
sistema disponíveis para uma sessão individual, é mais bem mensurado com a análise das
cargas de trabalho de consultas simultâneas. Para obter mais informações sobre como usar
o Administrador de Recursos, consulte Gerenciando cargas de trabalho do SQL Server com
o Administrador de Recursos (http://msdn.microsoft.com/pt-br/library/bb933866.aspx).
As diretrizes e práticas específicas ao fornecedor de soluções Fast Track também deve ser
examinadas. Especificamente, as soluções Fast Track com mais de 4 e 8 soquetes podem depender
das configurações específicas do Administrado de Recursos para atingir um bom desempenho.
Em resumo, há uma troca entre a redução de restrições que oferecem desempenho mais alto
para consultas individuais e restrições mais rigorosas que garantem o número de consultas que
podem ser executadas simultaneamente.
Para obter mais informações sobre as práticas recomendadas e os cenários comuns do
Administrador de Recursos, leia o white paper Usando o Administrador de Recursos
(http://msdn.microsoft.com/pt-br/library/ee151608.aspx).
Sistema de armazenamento
Gerenciar a fragmentação é essencial para o desempenho do sistema ao longo do tempo
para arquiteturas de referência do FTDW, que colocam o armazenamento de banco de dados
primário em HDDs (unidades de disco rígido). Por esse motivo, uma configuração detalhada do
sistema de arquivos e do armazenamento é especificada.
Componentes do sistema de armazenamento
A Figura 3 fornece uma exibição que combina as três camadas principais da configuração de
armazenamento para a pilha integrada de banco de dados. Isso deve ser considerado um caso
de referência, uma vez que a topologia específica varia muito de acordo com o parceiro Fast
Track. A pilha típica de banco de dados contém os seguintes elementos:



24
Matriz de disco físico: RAID 1+0 de 4 eixos é a abordagem padrão detalhada na Figura
3. Também são usados RAID 5 e RAID 6 em algumas arquiteturas de referência de
parceiros para o SQL Server 2008 R2 e SQL Server 2012.
Atribuição de volume do sistema operacional (LUN)
Bancos de dados: Usuário, Temp do Sistema, Log do Sistema
Figura 3: Exemplo de arquitetura de armazenamento abrangente para um sistema FTDW
baseado em três compartimentos de armazenamento com um LUN (volume) por grupo
de discos
Detalhes de configuração do armazenamento
Para cada compartimento de armazenamento, execute o procedimento a seguir.
1. Crie grupos contendo quatro discos cada um, usando RAID 1+0 (RAID 10). O número
exato de grupos de discos por compartimento de armazenamento pode variar por
fornecedor. Para obter mais informações, consulte a documentação específica ao
fornecedor. Em geral, o número é grupo de discos (2) RAID10 e (1) RAID1 para
compartimentos de fator forma grande (LFF) e grupos de discos (5) RAID10 para
compartimentos SFF (fator forma pequeno).
25
Os volumes totais usados como locais do grupo de arquivos para dados primários não
devem exceder 32. Se o número total de LUNs do sistema de armazenamento exceder
esse limite, poderão ser usados grupos de discos maiores para reduzir a contagem de
LUNs mantendo uma taxa de transferência de E/S semelhante. Por exemplo, use um
grupo de discos RAID 10 de 8 discos com 1 LUN, em vez de um grupo de discos
RAID 10 de 4 discos com 1 LUN. A taxa de transferência e a eficiência são um pouco
reduzidas com grupos de discos maiores. Isso varia de acordo com a tecnologia de
armazenamento.
2. Dedique tudo, menos um grupo de discos, aos dados de usuário primários (PRI).
Os locais dos dados de usuário primários são sinônimos dos locais de grupos de
arquivos do banco de dados do SQL Server.
Todas as FTRAs requerem um ou dois LUNs por grupo de discos PRI. Consulte
as diretrizes específicas ao fornecedor da arquitetura de referência de sua escolha.
Esses LUNs são usados para armazenar os arquivos de banco de dados do SQL
Server (arquivos .mdf e .ndf).
3. Verifique se a atribuição do processador de armazenamento primário para cada volume
de disco alocado aos dados primários em um compartimento de armazenamento
é uniformemente equilibrada. Por exemplo, um compartimento de armazenamento com
quatro volumes de disco alocados para dados primários terá dois volumes atribuídos ao
processador de armazenamento "A" e dois atribuídos ao processador de
armazenamento "B".
4. Crie um LUN no grupo de discos restante para hospedar os logs de transações do
banco de dados. Para algumas configurações Fast Track mais amplas, as alocações de
log são limitadas apenas aos primeiros compartimentos de armazenamento no sistema.
Nesse caso, os grupos de discos adicionais são usados para preparo não referente
a banco de dados ou não são populados para redução de custos.
Para cada banco de dados, siga este procedimento:
1. Crie pelo menos um grupo de arquivos que contenha um arquivo de dados por LUN
de PRI. Certifique-se de deixar todos os arquivos do mesmo tamanho. Se você planeja
usar vários grupos de arquivos dentro de um único banco de dados para separar
objetos (por exemplo, um banco de dados de preparo para oferecer suporte ao
carregamento), não se esqueça de incluir todos os LUNs de PRI como locais para cada
grupo de arquivos.
2. Ao criar os arquivos para cada grupo de arquivos, aloque-os previamente para os
respectivos tamanhos máximos previstos, com um tamanho suficiente para manter
os objetos previstos.
3. Desabilite a opção de aumento automático para arquivos de dados e aumente
manualmente todos os arquivos de dados quando o limite de tamanho atual estiver
se aproximando.
4. Para obter mais informações sobre recomendações para bancos de dados de usuários
e grupos de arquivos, consulte a seção Gerenciando a fragmentação de dados deste
documento.
26
Para tempdb, siga este procedimento:
1. Pré-aloque espaço e adicione um único arquivo de dados por LUN. Certifique-se de
deixar todos os arquivos do mesmo tamanho.
2. Atribua arquivos de log temporários em um dos LUNs dedicados aos arquivos de log.
3. Habilite o aumento automático; em geral, o uso de um grande incremento de
crescimento é apropriado para cargas de trabalho de data warehouse. Um valor
equivalente a 10% do tamanho do arquivo inicial é um ponto de partida razoável.
4. Siga as práticas recomendadas padrão do SQL Server para o banco de dados e as
considerações de dimensionamento de tempdb. A alocação de um espaço maior pode
ser necessária durante a fase de migração ou durante a carga de dados inicial do data
warehouse. Para obter mais informações, consulte Planejamento de capacidade para
tempdb (http://msdn.microsoft.com/pt-br/library/ms345368.aspx) nos Manuais Online do
SQL Server.
Para o log de transações, siga este procedimento:
1. Crie um único arquivo de log de transações por banco de dados em um dos LUNs
atribuídos ao espaço de log de transações. Distribua arquivos de log por diferentes
bancos de dados nos LUNs disponíveis ou use vários arquivos de log para o aumento
do log, conforme a necessidade.
2. Habilite a opção de aumento automático para arquivos de log.
3. Verifique se a capacidade de log se alinha aos requisitos fornecidos na Tabela 5.
Alguma variação é aceitável, dependendo das características específicas de design
do sistema.
RAM do sistema (GB)
Capacidade classificada
de FT (terabytes)
<= 96
<=10
<= 128
>10
<=40
Alocação mínima
de log recomendada
Espaço livre espelhado
em GB
Volume de 300 GB x 1
Volume de 300 GB x 2
ou
Volume de 600 GB x 1
Tabela 5: Recomendações de alocação de log
Consulte as práticas recomendadas existentes para alocação e gerenciamento do log de
transações do SQL Server.
Armazenamento de estado sólido
As arquiteturas de referência do FTDW que usam o armazenamento de estado sólido para PRI
(dados primários) têm muitas vantagens, incluindo gerenciamento simplificado, redução dos
custos operacionais e manutenção previsível.
27
Gerenciamento simplificado: o armazenamento de estado sólido não exige gerenciamento
de fragmentação. A opção de inicialização – E do SQL Server ainda deve ser usada, mas não
há necessidade de otimização ou gerenciamento de alocação de página. Essa simplificação
facilita consideravelmente o gerenciamento de longo prazo de ambientes FTDW. Além disso,
os grupos de discos maiores e as contagens mais baixas de volume/LUN podem ser usados
sem nenhuma implicação negativa de desempenho. Essa alteração simplifica a criação
e a manutenção do grupo de arquivos.
Resiliência de E/S: o armazenamento de estado sólido apresenta degradação mínima de
desempenho sob alta simultaneidade ou fragmentação de página. Além disso, a carga de
trabalho de leitura (busca) aleatória combinada não afeta negativamente os padrões de E/S
de uma solicitação (exame) grande.
Manutenção previsível: muitas opções de armazenamento de estado sólido fornecem
monitoramento da vida útil da gravação com menor frequência de falhas físicas que são
difíceis de prever.
Redução dos custos operacionais: embora mais caro no preço de lista, o armazenamento
de estado sólido oferece um equilíbrio mais eficiente entre a taxa de transferência de E/S
e a capacidade por unidade. As taxas efetivas de E/S de carga de trabalho do FTDW para HDD
SAS de 10k com 300 GB são em média de 50 MBs. O SSD MLC empresarial oferece entre
150 e 200 MBs em uma capacidade de 600 GB. Além disso, o armazenamento de estado
sólido consome significativamente menos energia, gera menos calor e, muitas vezes, oferece
suporte a soluções de densidade mais alta.
Configuração do armazenamento de estado sólido
Os ajustes a seguir podem ser feitos nas diretrizes de configuração do armazenamento padrão
do FTDW caso o armazenamento de estado sólido seja usado para volumes de PRI.




28
Se houver necessidade de espelhamento, você poderá usar RAID1+0 ou RAID5.
O RAID5 oferece a melhor capacidade sem impacto no desempenho para cargas de
trabalho do FTDW no estado sólido.
A contagem de LUNs e volumes pode ser reduzida para apenas um volume de PRI por
unidade de armazenamento. É útil, em alguns casos, que a contagem de volumes de
PRI seja um múltiplo da contagem de núcleos de CPU. A contagem mínima de volumes
de PRI é dois.
O log de transações também pode ser colocado em estado sólido, mas as cargas de
trabalho do FTDW geralmente não são associadas ao log. O custo pode ser reduzido
quando se coloca o log em um HDD tradicional. O mesmo vale para o armazenamento
local da instalação do Windows Server e do SQL Server.
As recomendações para o gerenciamento da fragmentação de páginas e o carregamento
paralelo de índices de cluster podem ser ignoradas, pois a fragmentação lógica do
banco de dados não afeta o desempenho de E/S de estado sólido.
Práticas recomendadas do SQL Server para FTDW
As práticas para cargas de trabalho Fast Track são validadas e documentadas em dois casos.
O primeiro ocorrerá se uma prática Fast Track for substancialmente diferente das práticas
recomendadas estabelecidas para o SQL Server. O segundo caso ocorrerá em cenários em
que práticas existentes estiverem ausentes ou não puderem ser acessadas com facilidade.
As práticas fornecidas aqui não pretendem ser abrangentes, pois existe uma extensa
documentação para a implantação de bancos de dados do SQL Server. A documentação
técnica e as práticas recomendadas existentes do SQL Server devem ser referenciadas em
vários tópicos relacionados a uma implantação do FTDW.
Importante: há vários links para a documentação escrita para o SQL Server 2008 R2 neste
guia. Acreditamos que a maioria dessas diretrizes ainda seja útil para o SQL Server 2012,
mas você deve procurar as versões constantemente atualizadas desses documentos.
As versões futuras deste guia de referência atualizarão os links à medida que eles se
tornarem disponíveis.
Arquitetura de dados
Estrutura de tabela
O tipo de tabela que é usado para armazenar dados no banco de dados tem um efeito
significativo no desempenho do acesso sequencial. É muito importante desenvolver o esquema
físico com isso em mente, para permitir que os planos de consulta induzam a E/S sequencial
na medida do possível.
A escolha de um tipo de tabela se reduz à maneira como os dados na tabela serão acessados
na maioria das vezes. As informações a seguir podem ser usadas para ajudar a determinar
que tipo de tabela deve ser considerado, com base nos detalhes dos dados que estão sendo
armazenados.
Tabelas heap
As tabelas heap fornecem E/S sequencial limpa para exames de tabela e, geralmente,
diminuem a sobrecarga relacionada à fragmentação da tabela. Intrinsecamente, elas não
permitem exames baseados em intervalos (acesso direto) otimizados, como acontece com
uma tabela de índice clusterizado. Em uma situação de exame de intervalo, uma tabela
heap examina a tabela inteira (ou a partição de intervalo apropriada, se o particionamento
for aplicado).
O exame de tabelas heap atinge a taxa de transferência máxima em 32 arquivos, de modo
que o uso de heaps para tabelas de fatos grandes em sistemas com números elevados de
LUNs (mais de 32) ou núcleos (mais de 16) pode exigir o uso do Administrador de Recursos,
restrições DOP ou alterações na alocação de arquivos do banco de dados Fast Track padrão.
29
É melhor usar as tabelas heap onde:



A maioria das consultas de alta prioridade na referência de tabela contém predicados
que referenciam uma série de colunas distintas ou não tem nenhum predicado de coluna.
As consultas geralmente executam exames grandes, ao contrário dos exames restritos
a intervalos, como as tabelas usadas exclusivamente para popular cubos do Analysis
Services. (Nesses casos, a tabela heap deve ser particionada com a mesma
granularidade que o cubo do Analysis Services que está sendo populado.)
Os requisitos de carga de trabalho de consulta são atendidos sem a sobrecarga
incremental do gerenciamento de índice ou o desempenho da carga – o carregamento
de tabelas heap é mais rápido.
Tabelas de índice clusterizado
No ambiente de data warehouse, um índice clusterizado é mais eficiente quando a chave
é uma coluna qualificada por intervalo (como a de data), que geralmente é usada em restrições
da carga de trabalho de consulta relevante. Nesse caso, o índice pode ser usado para restringir
e otimizar significativamente os dados a serem examinados.
É melhor usar tabelas de índice clusterizado quando:

A tabela contém colunas qualificadas por intervalo que são usadas em restrições de
consulta na maioria dos cenários de carga de trabalho de consulta de alta prioridade
da tabela. Para configurações do FTDW, a coluna de data particionada de um índice
clusterizado também deve ser a chave do índice clusterizado.
Observação: em alguns casos, pode ser vantajoso escolher uma chave de índice
clusterizado que não seja a coluna de partição de data para uma tabela de índice
clusterizado. No entanto, isso pode resultar em fragmentação, a menos que partições
completas sejam carregadas, pois os dados novos que sobrepõem intervalos de chave
de índice clusterizado existentes criam divisões de página.

As consultas na tabela normalmente fazem pesquisas granulares ou restritas ao
intervalo, e não exames grandes de vários intervalos ou da tabela inteira.
Particionamento de tabela
O particionamento de tabela pode ser uma importante ferramenta para gerenciar a fragmentação
em bancos de dados do FTDW. Por exemplo, é possível usar o particionamento para atualizar
ou excluir de uma tabela grandes blocos de dados de usuário baseados em intervalos, sem
afetar outras partes da tabela. Em contrapartida, excluir linha por linha de um índice de cluster
pode provocar uma fragmentação significativa da extensão. Um cenário comum é recriar
partições mais novas quando elas se tornam antigas e a frequência de operações DML para
o intervalo de dados diminui. Agora a partição está estável em relação às operações DML
e apresenta fragmentação mínima da extensão.
30
Além disso, as tabelas grandes que são usadas principalmente para popular cubos do
SQL Server Analysis Services podem ser criadas como tabelas heap particionadas, com
o particionamento de tabela alinhado ao particionamento do cubo. Quando acessadas,
somente as partições relevantes da tabela grande são examinadas. (As partições que oferecem
suporte ao modo ROLAP do Analysis Services podem ser mais bem estruturadas como índices
clusterizados.)
Para obter mais informações sobre particionamento de tabela, leia o white paper Estratégias de
tabelas e índices particionados usando o SQL Server 2008 (http://msdn.microsoft.com/ptbr/library/dd578580(v=SQL.100).aspx).
Indexação
Considere as seguintes diretrizes na criação de índices do FTDW:




Use um índice clusterizado para intervalos de datas ou restrições comuns.
Use um índice columnstore sempre que possível. A seção a seguir aborda as práticas
recomendadas para se trabalhar com índices columnstore em ambientes do FTDW.
Reserve a indexação não clusterizada para situações em que a pesquisa granular for
necessária e o particionamento de tabela não oferecer um desempenho suficiente.
Se possível, use um índice columnstore como uma alternativa ao índice não clusterizado.
Os índices de cobertura não clusterizados podem proporcionar valor a algumas cargas
de trabalho do data warehouse. Eles devem ser avaliados caso a caso e comparados
com o índice columnstore.
Índices columnstore na memória xVelocity
O SQL Server 2012 inclui um novo recurso de aceleração de consultas de data warehouse que
se baseia na tecnologia de colunas: índices columnstore. Esses novos índices, combinados
a recursos avançados de processamento de consultas, melhoram o desempenho das consultas
de data warehouse para uma grande variedade de consultas analíticas.
Os índices columnstore otimizados para a memória xVelocity são columnstores "puros"
(não híbridos), pois armazenam todos os dados para colunas incluídas em páginas separadas.
Os índices columnstore aumentam o desempenho do exame de E/S e as taxas de acertos do
buffer, além de estarem bem-alinhados à metodologia de design do FTDW.
Práticas recomendadas
Os objetos do índice columnstore residem junto às tabelas e são criados de forma semelhante
aos índices não clusterizados. Esses fatos implicam na capacidade incremental de
armazenamento, se necessário. Não é necessário criar índices columnstore em grupos
de arquivos separados, a menos que as alterações frequentes na tabela de destino do
índice sejam esperadas. Manter os índices columnstore em grupos de arquivos separados
pode ajudar a gerenciar a fragmentação de páginas ao longo do tempo em ambientes
altamente voláteis.
31
Criando índices columnstore para modelos de dados normalizados
Os modelos de dados normais (isto é, 3NF) muitas vezes disparam junções entre duas ou
mais tabelas grandes (de fatos). Atualmente, esses tipos de junções não são ideais para
o processamento de índices columnstore e podem exibir regressões de desempenho em
relação aos planos de consulta não relacionados a índices columnstore. As abordagens
a seguir podem ajudar a evitar esse problemas com modelos de dados normais:




Use dicas no nível de consulta para impedir que o processamento de índices
columnstore seja usado.
Use OPTION(IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX)
Reescreva as consultas. Para obter mais informações, consulte os recursos listados
na seção Práticas recomendadas gerais do índice columnstore deste documento.
Tente omitir chaves comuns de junção de uma tabela envolvida em junções SQL que
exibem regressões de desempenho de planos de consulta não relacionados a índices
columnstore. A omissão da chave de junção do índice columnstore em uma tabela pode
fazer com que o índice columnstore não seja usado para consultas que se unem na
coluna omitida. Essa abordagem poderá ser útil em ambientes onde não é possível
aplicar opções no nível de consulta. Lembre-se de que omitir uma coluna do índice
columnstore não é garantia de um plano de consulta melhor e pode afetar outras
consultas para as quais o índice columnstore forneceria benefícios de desempenho.
Se você optar por usar essa opção, a seleção de uma coluna da menor das tabelas
envolvidas pode reduzir o impacto de desempenho em outras consultas. Observe que
as chaves primárias declaradas (DDL) devem ser incluídas no índice columnstore,
o que pode limitar as colunas de junção disponíveis. Mesmo que você omita uma coluna
de chave primária da definição do índice columnstore, todas as colunas de chave primária
serão adicionadas automaticamente ao índice columnstore quando ele for criado.
Embora os modelos de dados normais não sejam perfeitamente otimizados para índices
columnstore na versão atual, é importante observar que a avaliação de desempenho do FTDW
é baseada em uma versão modificada do TPC-H, que é um modelo normalizado. Ganhos
significativos ainda foram medidos para cargas de trabalho simultâneas que combinaram
planos de consulta de índice columnstore e outros tipos de índices, incluindo a taxa de
transferência classificada pelo FTDW que quase dobrou o desempenho geral da carga de
trabalho em alguns casos.
Criando índices columnstore para modelos de dados dimensionais
Siga as práticas recomendadas de índice columnstore para modelos dimensionais,
como esquemas em estrela. Isso pode ser considerado um cenário do melhor caso para
o processamento de índices columnstore.
32
Gerenciamento de memória para índices columnstore
A FTRA validada para SQL Server 2012 geralmente tem mais RAM total de sistema do que
as configurações semelhantes para o SQL Server 2008 R2. O principal motivo para isso é que
as cargas de trabalho aprimoradas com índices columnstore são executadas de modo mais
eficiente com pools de memória maiores. O Administrador de Recursos sempre deve ser
usado para definir a quantidade máxima de memória por sessão para ambientes do FTDW
em que você pretende aproveitar os índices columnstore. As FTRAs validadas documentam as
configurações do Administrador de Recursos usadas para atingir um desempenho classificado
pelo FT, e esses valores podem ser considerados um ponto de partida para cargas de trabalho
do cliente. De modo ideal, a configuração será avaliada e ajustada especificamente para uma
carga de trabalho do cliente após a instalação do sistema.
O comando SQL a seguir configura o Administrador de Recursos do SQL Server para essas
recomendações. Nesse caso, a quantidade máxima de memória por sessão é definida como 19%.
ALTER RESOURCE GOVERNOR RECONFIGURE;
ALTER WORKLOAD GROUP [default] WITH(request_max_memory_grant_percent=19);
Práticas recomendadas gerais do índice columnstore otimizado para a memória
xVelocity
As diretrizes de referência do FTDW abrangem apenas as práticas exclusivas ao Fast Track.
Para obter mais informações sobre índices columnstore, consulte o Guia de Ajuste de CSI do
SQL Server 2012 (http://social.technet.microsoft.com/wiki/contents/articles/sql-server-columnstoreperformance-tuning.aspx) e as Perguntas frequentes sobre CSI do SQL Server 2012
(http://social.technet.microsoft.com/wiki/contents/articles/sql-server-columnstore-index-faq.aspx).
Estatísticas de banco de dados
Sua decisão de quando executar as estatísticas e com que frequência atualizá-las não
depende de nenhum fator único. A janela de manutenção disponível e a falta geral de
desempenho do sistema costumam ser as duas principais razões para os problemas de
estatísticas de banco de dados serem solucionados.
Para obter mais informações, consulte Estatísticas do SQL Server 2008
(http://msdn.microsoft.com/pt-br/library/dd535534.aspx).
Práticas recomendadas
Sugerimos as seguintes práticas recomendadas para estatísticas de banco de dados:


33
Use as opções AUTO CREATE e AUTO UPDATE (síncrona ou assíncrona) para
as estatísticas (o padrão do sistema no SQL Server). O uso dessa técnica minimiza
a necessidade de executar as estatísticas manualmente.
Se você precisa coletar estatísticas manualmente, o ideal é que elas sejam coletadas
em todas as colunas de uma tabela. Se não for possível executar estatísticas para
todas as colunas, você deve, ao menos, coletar estatísticas de todas as colunas usadas
em uma cláusula WHERE ou HAVING e em chaves de junção. A criação de índice gera
estatísticas sobre a chave de índice, portanto não é preciso fazer isso explicitamente.


As estatísticas compostas (várias colunas) são essenciais para muitos cenários de
junção. As junções de dimensão de fatos que envolvem chaves de junção compostas
podem resultar em planos de otimização de loop aninhado de qualidade inferior na
ausência de estatísticas compostas. As estatísticas automáticas não criam, atualizam
nem substituem estatísticas compostas.
As estatísticas que envolvem um valor de chave crescente (como uma data em
uma tabela de fatos) devem ser atualizadas manualmente após cada operação
de carregamento incremental. Em todos os outros casos, as estatísticas podem
ser atualizadas com menos frequência. Se você determinar que a opção
AUTO_UPDATE_STATISTICS não é suficiente, execute estatísticas agendadas.
Compactação
As configurações do FTDW foram desenvolvidas com a compactação de página habilitada.
É recomendável usar a compactação de página em todas as tabelas de fatos. A compactação
de tabelas de dimensões pequenas (isto é, aquelas com menos de um milhão de linhas)
é opcional. Já com as tabelas de dimensões maiores, frequentemente é vantajoso usar
a compactação de página. Em ambos os casos, a compactação das tabelas de dimensões
deverá ser avaliada de acordo com o caso de uso. A compactação de linhas é uma opção
adicional que fornece taxas razoáveis de compactação para determinados tipos de dados.
A compactação de página do SQL Server reduz os dados em tabelas, índices e partições.
Isso reduz a quantidade de espaço físico necessário para armazenar tabelas de usuário, o que
permite que mais dados caibam no pool de buffers do SQL Server (memória). Um benefício
disso está na redução do número de solicitações de E/S atendidas pelo armazenamento físico.
A quantidade da compactação real que pode ser percebida varia em relação aos dados que
estão sendo armazenados e à frequência de campos de dados duplicados nos dados. Se os
dados forem altamente aleatórios, os benefícios da compactação serão muito limitados. Até
mesmo nas melhores condições, o uso da compactação aumenta a demanda na CPU para
compactar e descompactar os dados, mas também reduz os requisitos de espaço do disco
físico e, na maioria das vezes, aumenta o tempo de resposta das consultas ao atender às
solicitações de E/S do buffer de memória. Geralmente, a compactação de página apresenta
uma proporção de compactação (tamanho original/tamanho compactado) entre 2 e 7:1, com
3:1 sendo uma estimativa conservadora típica. Os resultados podem variar de acordo com as
características de seus dados.
Gerenciando a fragmentação de dados
A fragmentação pode acontecer em vários níveis, que devem ser controlados para preservar
a E/S sequencial. Um objetivo importante de um FTDW é manter seus dados em sequência da
maneira mais ordenada possível, ao mesmo tempo em que limita a fragmentação subjacente.
Se for permitido que a fragmentação ocorra, o desempenho geral será prejudicado.
A desfragmentação periódica é necessária, mas as diretrizes a seguir podem ajudar
a minimizar o número de processos demorados de desfragmentação.
34
Fragmentação do sistema de arquivos
Os blocos de disco por arquivo de banco de dados devem ser mantidos contíguos na superfície
física dentro do sistema de arquivos NTFS. A fragmentação nesse nível pode ser evitada pela
pré-alocação de arquivos para o respectivo tamanho máximo esperado na criação.
As ferramentas de desfragmentação do sistema de arquivos NTFS devem ser evitadas.
Essas ferramentas foram desenvolvidas para funcionar no nível do sistema operacional e não
conhecem as estruturas internas do arquivo de dados do SQL Server.
Fragmentação da extensão
No SQL Server, todas as páginas em um arquivo, independentemente da associação de tabela,
podem ser intercaladas até o tamanho de extensão (2M) ou o nível de página (8K). Em geral,
isso ocorre devido a operações DML simultâneas, atualizações excessivas no nível de linha ou
exclusões excessivas no nível de linha.
Reescrever completamente as tabelas em questão é a única maneira de garantir uma alocação
de página adequada em um arquivo. Não há nenhum método alternativo para resolver esse
tipo de fragmentação de banco de dados. Por esse motivo, é importante seguir as diretrizes de
configuração e práticas recomendadas do SQL Server para carregar dados e gerenciar o DML.
A consulta a seguir fornece informações fundamentais para avaliação da fragmentação lógica
de uma tabela do FTDW. A métrica de prioridade mais alta é Tamanho Médio do Fragmento.
Esse valor fornece um inteiro que representa o número médio de páginas do SQL Server que
são clusterizadas em extensões contíguas.
SELECT db_name(ps.database_id) as database_name
,object_name(ps.object_id) as table_name
,ps.index_id
,i.name
,cast (ps.avg_fragmentation_in_percent as int) as [Logical Fragmentation]
,cast (ps.avg_page_space_used_in_percent as int) as [Avg Page Space Used]
,cast (ps.avg_fragment_size_in_pages as int) as [Avg Fragment Size In Pages]
,ps.fragment_count as [Fragment Count]
,ps.page_count
,(ps.page_count * 8)/1024/1024 as [Size in GB]
FROM sys.dm_db_index_physical_stats (DB_ID() --NULL = All Databases
, OBJECT_ID('$(TABLENAME)')
,1
, NULL
, 'SAMPLED') AS ps
35
--DETAILED, SAMPLED, NULL = LIMITED
INNER JOIN sys.indexes AS i
on (ps.object_id = i.object_id AND ps.index_id = i.index_id)
WHERE ps.database_id = db_id()
and ps.index_level = 0;
A tabela a seguir fornece diretrizes gerais para interpretar valores de tamanho médio do
fragmento.
Tamanho médio do
fragmento
>400
Status
Ação
Ideal
Esse é um valor ideal e pode ser difícil de
manter para algumas estruturas de dados.
300-399
Verde
A tabela fornecerá bom desempenho de E/S
e não requer manutenção da fragmentação
lógica.
150-299
Amarelo
A fragmentação lógica tem mais
probabilidade de afetar a eficiência de E/S.
A manutenção é recomendada para
melhorar a contagem de fragmentos.
10-149
Vermelho
Fragmentação lógica severa. Grandes
solicitações de E/S nessa estrutura
resultarão em movimento significativo
do cabeçote de disco e em redução da
eficiência de E/S geral do sistema.
<10
Vermelho
Esses valores baixos de tamanho médio
do fragmento geralmente significam que
a opção de inicialização –E do SQL Server
não foi definida ou não está sendo
reconhecida na inicialização.
Tabela 6: Valores de tamanho médio do fragmento
Por fim, é importante observar que os resultados de tamanho médio do fragmento não devem
ser avaliados para tabelas ou partições inferiores a 500 MB. As estruturas de dados pequenas
simplesmente não têm páginas suficientes no total para atingir quantidades de fragmentos
altamente eficazes. Além disso, essas estruturas de dados menores geralmente representam
solicitações de dados relativamente pequenas e têm impacto limitado na eficiência geral de E/S
do sistema. Muitas vezes, os melhores resultados são vistos no gerenciamento de tabelas
maiores, acessadas com mais frequência em um ambiente de data warehouse.
36
Fragmentação de índice
Um índice pode estar em ordens física (página) e lógica (índice) diferentes.
Não use o comando ALTER INDEX REORGANIZE para resolver esse tipo de fragmentação,
pois isso pode invalidar os benefícios de alocações grandes. Uma recompilação de índice ou
o uso de INSERT…SELECT para inserir dados em uma nova cópia do índice (o que evita uma
reordenação) podem resolver esse problema. Qualquer processo de ALTER INDEX REBUILD
deve especificar SORT_IN_TEMPDB=TRUE para evitar a fragmentação do grupo de arquivos
de destino. Um valor MAXDOP de 1 é ideal, mas pode resultar em taxas de carregamento
muito lentas. É possível definir valores MAXDOP de até 8 em alguns casos. Para obter mais
informações, consulte a seção Carregando dados deste documento.
Vários grupos de arquivos
Os grupos de arquivos separados podem ser criados para reduzir a fragmentação lógica de
casos de uso de dados voláteis, como:




Tabelas ou índices que frequentemente são removidos e recriados (deixando lacunas
no layout do armazenamento que são novamente preenchidas por outros objetos).
Índices para os quais não há nenhuma opção que não seja aceitá-los como altamente
fragmentados devido a divisões de página, como nos casos em que frequentemente
são carregados dados incrementais que, na maioria das vezes, sobrepõem o intervalo
de chaves de índice clusterizado existente.
Tabelas menores (como tabelas de dimensões) que são carregadas em incrementos
relativamente pequenos, que podem ser colocados em um grupo de arquivos volátil
para evitar que essas linhas se intercalem com grandes transações ou tabelas de fatos.
Bancos de dados de preparo dos quais os dados são inseridos na tabela de destino final.
Outras tabelas podem ser colocadas em um grupo de arquivos não volátil. Além disso, as tabelas
de fatos muito grandes também podem ser colocadas em grupos de arquivos separados.
Carregando dados
A arquitetura de componentes Fast Track é balanceada para maiores taxas médias de exame
obtidas com o acesso de disco sequencial. Para manter essas taxas de exame, é preciso tomar
cuidado para garantir um layout de dados contíguo no sistema de arquivos do SQL Server.
Esta seção é dividida em duas abordagens de alto nível: carregamento incremental e migração
de dados. Estas diretrizes são específicas, mas não exclusivas, ao data warehouse Fast Track.
Para obter mais informações sobre o carregamento em massa do SQL Server, consulte o Guia de
Desempenho de Carregamento de Dados (http://msdn.microsoft.com/pt-br/library/dd425070.aspx).
Outro recurso útil é o Guia de Práticas Recomendadas de Carregamento de Dados Fast Track
3.0. Essa apresentação do Microsoft PowerPoint pode ser encontrada no Portal do SQL Server
Fast Track DW (http://msdn.microsoft.com/pt-br/library/dd425070.aspx). Embora inicialmente
baseado no SQL Server 2008 R2, o documento ainda se aplica ao SQL Server 2012.
37
Cargas incrementais
Esta seção abrange os cenários comuns de carga diária de um ambiente de data warehouse.
Ela inclui cenários de carregamento com um ou mais dos seguintes atributos:



Tamanho pequeno em relação à memória de sistema disponível
As operações de classificação de carga se ajustam à memória disponível
Tamanho pequeno em relação ao total de linhas no objeto de carga de destino
As diretrizes a seguir devem ser consideradas durante o carregamento de tabelas heap e de
índice clusterizado.
Processo de carregamento de tabela heap
As inserções em massa de tabelas heap podem ser implementadas como processo serial ou
paralelo. Use as dicas a seguir:


Para executar a movimentação de dados na tabela heap de destino, use BULK INSERT
com a opção TABLOCK. Se a tabela permanente final for particionada, use a opção
BATCHSIZE, pois o carregamento em uma tabela particionada gera uma classificação
para que tempdb ocorra.
Para melhorar o desempenho do tempo de carga quando estiver importando grandes
conjuntos de dados, execute várias operações de inserção em massa ao mesmo tempo
para usar o paralelismo no processo em massa.
Processo de carregamento de índice clusterizado
Existem duas abordagens gerais para carregar tabelas de índice clusterizado com
fragmentação mínima da tabela.
Opção 1
Use BULK INSERT para carregar dados diretamente na tabela de destino. Para otimizar
o desempenho, o conjunto completo de dados que está sendo carregado deve caber em
uma classificação na memória. Todos os dados carregados devem ser tratados por uma única
operação de confirmação usando um valor BATCHSIZE de 0. Essa configuração impede que
os dados em vários lotes sejam intercalados e gerem divisões de página. Se você usar essa
opção, o carregamento deverá ocorrer em um único thread.
Opção 2
Crie uma tabela de preparo que corresponda à estrutura (incluindo o particionamento) da tabela
de destino:

38
Execute uma inserção em massa serial ou multithreaded na tabela de preparo vazia de
índice clusterizado usando valores de tamanho de lote moderados e diferentes de zero
para evitar que as classificações transbordem em tempdb. O melhor desempenho será
obtido com qualquer nível de paralelismo. O objetivo desta etapa é o desempenho;
portanto, as divisões de página e a fragmentação lógica induzidas por inserções
paralelas e/ou simultâneas não são um problema.

Faça uma inserção da tabela de preparo na tabela de índice clusterizado de destino
usando uma única instrução INSERT…SELECT com um valor MAXDOP de 1.
MAXDOP 1 garante fragmentação de extensão mínima, mas geralmente prejudica
o desempenho. As configurações de MAXDOP de até 8 podem ser usadas para
aumentar o desempenho da carga, mas mostrarão a crescente fragmentação de
extensão à medida que o paralelismo aumentar. O equilíbrio efetivo nessa troca é mais
bem avaliado caso a caso.
Opção 3
Esta opção requer o uso de dois grupos de arquivos e de duas ou mais tabelas. A abordagem
requer uma tabela de índice de cluster particionado e é mais adequada para as tabelas
que percebem altos níveis de fragmentação lógica nas partições mais atuais com pouca ou
nenhuma atividade nas partições mais antigas. A meta geral é colocar partições voláteis em
um grupo de arquivos dedicado e antigo ou "mover" essas partições para o grupo de arquivos
estático depois que elas pararem de receber novos registros ou alterações em registros
existentes:





39
Crie dois grupos de arquivos, seguindo as diretrizes do FTDW. Um será dedicado
a partições voláteis e o outro a partições estáticas. Uma partição volátil é aquela em que
mais de 10% de linhas são alteradas ao longo do tempo. Uma partição estática é aquela
que não é volátil.
Crie a tabela primária particionada de índices de cluster no grupo de arquivos estático.
Crie uma tabela consistente com uma das duas abordagens gerais a seguir:
o Uma única tabela heap com uma restrição que espelhe o esquema de partição
da tabela primária. Essa restrição deve representar o intervalo volátil do conjunto
de dados primário e pode incluir um ou mais intervalos de partição do esquema
da tabela primária. Isso será muito útil se o desempenho do carregamento inicial
for o principal critério de decisão, pois os carregamentos em um heap costumam
ser mais eficientes do que os carregamentos em um índice de cluster.
o Uma única tabela de índice de cluster com um esquema de partição que seja
consistente com a partição da tabela primária. Isso permite inserções diretas
com baixo DOP (grau de paralelismo) na tabela primária à medida que as
partições voláteis ficam antigas. Depois de envelhecerem por meio da inserção
na tabela primária, as partições são removidas e novos intervalos são adicionados.
Crie uma exibição que reúna ambas as tabelas. Essa exibição apresenta a combinação
das duas tabelas como um único objeto da perspectiva do usuário.
Depois que os intervalos de dados voláteis tornam-se estáticos de uma perspectiva de
alteração de dados, use um processo de antiguidade apropriado, como a alternância de
partição:
o Se for usada uma tabela heap com restrição, mova os dados por intervalo de
partição para o grupo de arquivos estático usando a inserção na tabela de
preparo. Use CREATE INDEX e a alternância de partição para mover os dados
para a tabela primária. Para obter mais informações sobre esse tipo de operação
para configurações do FTDW, consulte a seção Migração de dados deste
documento.
o
Se um índice clusterizado particionado for usado, use um DOP que seja menor
ou igual a 8. Em seguida, realize uma operação INSERT restrita por intervalo de
partição diretamente na tabela primária. Talvez você precise definir o DOP como
1 para evitar a fragmentação, dependendo da simultaneidade geral do sistema.
Migração de dados
Isso inclui cenários de grandes carregamentos ocasionais ou infrequentes em um ambiente
de data warehouse. Essas situações podem ocorrer durante a migração de plataforma ou
enquanto os dados de teste são carregados para a avaliação de desempenho do sistema.
Este tópico inclui os cenários de carregamento com um ou mais dos seguintes atributos:


Operações de carregamento que excedem a memória do sistema disponível
Operações de carregamento de alta simultaneidade e alto volume que criam pressão
sobre a memória disponível
Processo de carregamento de tabela heap
Siga as diretrizes fornecidas anteriormente para o processamento de carregamento
incremental.
Processo de carregamento de índice clusterizado
Existem várias abordagens gerais para carregar tabelas de índice clusterizado com
fragmentação mínima da tabela.
Opção 1
Use BULK INSERT para carregar dados diretamente em uma tabela de destino de índice
clusterizado. As operações de classificação e o tamanho completo da confirmação devem se
ajustar à memória para otimizar o desempenho. É preciso tomar cuidado para garantir que
esses lotes separados de dados que estão sendo carregados não tenham intervalos de chave
de índice que se sobreponham.
Opção 2
Execute uma inserção em massa serial ou multithreaded em uma tabela vazia de preparo de
índice clusterizado de estrutura idêntica. Use um tamanho de lote moderado e diferente de zero
para manter as classificações na memória. Em seguida, insira dados em uma tabela vazia de
índice clusterizado usando uma única instrução INSERT...SELECT com um valor MAXDOP de 1.
Opção 3
Use inserções em massa multithreaded em uma partição que esteja em conformidade com
a tabela de preparo heap, usando valores de tamanho de lote moderados e diferentes de zero
para manter as classificações na memória. Em seguida, use instruções INSERT…SELECT
seriais ou paralelas que incluam cada intervalo de partição para inserir dados na tabela de
índice clusterizado.
40
Opção 4
Use operações de alternância de partição em um processo de várias etapas, que geralmente
fornece os melhores resultados para grandes operações de carregamento. Essa abordagem
adiciona mais complexidade ao processo geral e foi criada para demonstrar que é ideal para
o desempenho de carregamento bruto. O principal objetivo dessa abordagem é habilitar
a atividade paralela de gravação em todas as fases da operação de inserção no índice de
cluster sem apresentar fragmentação lógica. Isso é obtido com a preparação da tabela por
vários grupos de arquivos antes de inserir os dados na tabela de destino final.
1. Identifique o esquema de partição para a tabela de índice de cluster de destino final.
2. Crie um grupo de arquivos de preparo.
3. Crie uma tabela de preparo "base" heap descompactada e não particionada no grupo
de arquivos de preparo.
4. Insira dados em massa usando WITH TABLOCK para a tabela de preparo base.
As operações múltiplas de cópia em massa paralela são a abordagem mais eficiente
quando vários arquivos de origem são uma opção. O número de operações paralelas
de carregamento para atingir uma taxa de transferência máxima depende dos recursos
do servidor (CPU e memória) e dos dados que estão sendo carregados.
5. Identifique o número de grupos de arquivos primários que devem ter suporte. Esse
número deve ser um múltiplo do número total de partições na tabela de destino.
O número também representa o número total de operações INSERT e CREATE INDEX
a serem executadas simultaneamente em etapas posteriores. Por exemplo, para uma
tabela com 24 partições e um servidor com oito núcleos, seria indicado um banco de
dados com oito grupos de arquivos primários. Essa configuração permite a execução
de oito inserções paralelas nas próximas etapas, uma para cada um dos oito grupos de
arquivos primários e núcleos de CPU. Cada grupo de arquivos, nesse caso, conteria
três intervalos de partição de dados.
6. Crie o número de grupos de arquivos primários conforme determinado anteriormente.
7. Crie uma tabela heap de preparo em cada grupo de arquivos primário para cada
intervalo de partição, sem compactação. Crie uma restrição na tabela de preparo
que seja compatível com o intervalo de partição correspondente na tabela de destino.
Usando o exemplo fornecido anteriormente, haveria três tabelas de preparo por grupo
de arquivos primário criado nesta etapa.
8. Crie a tabela de destino de índice de cluster particionado com compactação de página.
Essa tabela deve ser particionada por todos os grupos de arquivos primários.
As partições devem ser alinhadas aos intervalos de restrição da tabela de preparo heap.
9. Execute uma instrução INSERT ou SELECT da tabela de preparo base para as tabelas
de grupos de arquivos de preparo para cada grupo de arquivos primário. Isso deve
ser feito em paralelo. Verifique se o predicado da instrução INSERT ou SELECT
é compatível com os intervalos de partição correspondentes. Nunca execute mais de
uma instrução INSERT ou SELECT por grupo de arquivos simultaneamente.
41
10. Execute um comando CREATE CLUSTERED INDEX com a compactação de página
por grupo de arquivos para as tabelas de preparo recentemente populadas. Isso pode
ser feito em paralelo, mas nunca com o DOP mais alto que 8. Nunca execute mais de
uma criação de índice por grupo de arquivos simultaneamente. Certifique-se de usar
a opção SORT_IN_TEMPDB sempre que executar uma operação CREATE INDEX para
evitar a fragmentação dos grupos de arquivos primários. O número ideal de operações
simultâneas de criação de índice dependerá do tamanho do servidor, da memória e dos
dados em si. Em geral, tente a alta utilização de CPU em todos os núcleos sem
inscrição excessiva (de 85 a 90% da utilização total).
11. Execute as operações de alternância de partição serial das tabelas de preparo para
a tabela de destino. Isso também pode ser feito na conclusão de cada operação
CREATE INDEX de preparo.
Avaliação de desempenho e validação
Esta seção fornece uma descrição básica dos processos usados para criar e qualificar
arquiteturas de referência do FTDW do SQL Server. O objetivo de fornecer essas informações
é dar suporte às arquiteturas de referência personalizadas ou definidas pelo usuário com
base na metodologia do FTDW. Para avaliação de desempenho, solução de problemas ou
verificação de arquiteturas de referência do parceiro pré-validadas e publicadas, contate
o parceiro de publicação (H-P, Dell, EMC, IBM, Cisco, entre outros).
O processo para validação do FTDW pode ser dividido em duas categorias descritas aqui.
Validação de hardware de linha de base
O objetivo da validação de hardware é estabelecer métricas de desempenho reais, e não
classificadas, para os principais componentes de hardware da arquitetura de referência Fast
Track. Esse processo determina características reais de desempenho de linha de base dos
principais componentes de hardware na pilha do banco de dados.
Validação de banco de dados Fast Track
Estabelecer características de desempenho do SQL Server, com base em uma carga de
trabalho do FTDW, permite a comparação com as suposições de desempenho fornecidas
pelo processo de avaliação de hardware de linha de base. No geral, as métricas de taxa de
transferência de carga de trabalho do banco de dados devem refletir pelo menos 80% das
taxas de linha de base para arquiteturas de referência Fast Track validadas. As métricas de
desempenho calculadas nesse processo são a base para valores de desempenho do FTDW
publicados e se baseiam nas cargas de trabalho simultâneas de consulta SQL executadas com
a ferramenta de avaliação de desempenho Fast Track Reference Point.
42
O Ponto de Referência é uma ferramenta de software da Microsoft distribuída aos parceiros
de hardware Fast Track e é a única infraestrutura pela qual uma arquitetura de referência oficial
Fast Track pode ser validada e aprovada pela Microsoft. A ferramenta instancia um esquema
de banco de dados de referência e direciona várias cargas de trabalho simultâneas de consulta
projetadas para identificar afunilamentos e estabelecer importantes medidas de desempenho
do sistema.
Validação do Fast Track com índices columnstore otimizados para a memória xVelocity
O SQL Server 2012 implementa a tecnologia de índice columnstore como uma opção de
indexação não clusterizada para tabelas preexistentes. As consultas individuais podem ou não
usar planos de otimização do índice columnstore de acordo com a estrutura da consulta. Isso
significa que a combinação de planos de consulta de colunas novos e de linhas tradicionais
para um ambiente do FTDW em um momento determinado não pode ser prevista.
Por esses motivos, o FTDW para design e validação do sistema SQL Server 2012 se baseia
em avaliações de desempenho não relacionadas a índices columnstore. Os sistemas FTDW
foram desenvolvidos para serem executados com eficiência caso nenhuma otimização de
coluna seja obtida em nenhum período de tempo determinado. Muitas vezes, os ganhos de
desempenho significativos são obtidos quando os planos de consulta do índice columnstore
estão ativos, e esse desempenho pode ser exibido como incremental para o design básico
do sistema
As arquiteturas de referência Fast Track para SQL Server 2012 validadas por parceiros
publicam uma classificação de taxa de transferência lógica adicional para avaliações de
desempenho aprimoradas pelo índice columnstore, e esses números podem ser usados para
estimar o impacto positivo no desempenho de consultas que os clientes podem esperar sob
carga de trabalho de consultas simultâneas. Esses números são baseados nas mesmas avaliações
de desempenho do FTDW e no esquema usados para todas as validações do sistema.
Executando a validação do FTDW de linha de base
A validação de linha de base é feita no nível de sistema operacional com uma ferramenta como
o SQLIO. Os testes de aplicativos do SQL Server não são feitos nessa fase, e todos os testes
são cenários sintéticos do melhor caso. O objetivo é garantir que a configuração de hardware
e sistema operacional seja precisa e forneça os resultados esperados com base nas avaliações
de desempenho de design e desenvolvimento.
O Monitor de Desempenho e Confiabilidade do Windows Server (também conhecido
como perfmon) pode ser usado para rastrear, registrar e relatar o desempenho de E/S.
Uma ferramenta como o SQLIO pode ser usada para testar a largura da banda de E/S.
Para obter mais informações sobre o SQLIO, incluindo instruções e locais de download,
consulte o white paper do SQLCAT Práticas recomendadas de pré-implantação de E/S
(http://sqlcat.com/sqlcat/b/whitepapers/archive/2007/11/21/predeployment-i-o-best-practices.aspx).
Os componentes e processos de validação a seguir são usados para gerar avaliações de
desempenho de hardware de linha de base.
43
Testes de linha de base com o SQLIO
O uso do SQLIO é descrito de forma mais completa no artigo de práticas recomendadas.
Os testes de leitura geralmente recebem o formato:
sqlio –kR –fSequential -s30 -o120 -b512 d:\iobw.tst –t1
Nesse caso, R indica um teste de leitura, 30 é a duração do teste em segundos,
120 é o número de solicitações pendentes emitidas, 512 é o tamanho do bloco em quilobytes
de solicitações feitas, d:\iobw.tst é o local do arquivo de teste e 1 é o número de threads.
Para testar cenários de agregação de largura de banda, devem ser emitidos vários testes
do SQLIO em paralelo. Uma única instância do SQLIO deve ser usada para cada ponto de
montagem de dados primário (volume de disco). A paralelização das instâncias do SQLIO
pode ser obtida com o Windows PowerShell ou outros métodos de geração de script. Para
arquiteturas de referência FTDW validadas por parceiros, os scripts de validação de E/S de
linha de base podem ser disponibilizados pelo parceiro.
O artigo sobre práticas recomendadas de pré-implantação também aborda como rastrear
seus testes usando o Monitor de Desempenho e Confiabilidade do Windows Server. O registro
e o armazenamento dos resultados desses testes fornecerão uma linha de base para a análise
de desempenho e a resolução de problemas futuros.
Etapa 1 - Validar a largura de banda de E/S
A primeira etapa na validação de uma configuração do FTDW é determinar a taxa de transferência
de agregação máxima que pode ser percebida entre a rede de E/S de armazenamento
e o servidor. Isso envolve remover o disco como um afunilamento e concentrar-se em
componentes não relacionados ao disco (isto é, HBAs, infraestrutura de comutador
e controladores de matriz). Use as etapas a seguir para executar essa tarefa usando o SQLIO:
1. Gere um arquivo de dados pequeno em cada LUN a ser usado para arquivos de banco
de dados. Esses arquivos devem ser dimensionados de forma que todos os arquivos de
dados se ajustem ao cache de leitura nos controladores da matriz (por exemplo, 50 MB
por arquivo).
2. Use o SQLIO para emitir leituras sequenciais no arquivo que usa simultaneamente
tamanhos de blocos grandes de E/S (512 K) e, pelo menos, dois threads de leitura
por arquivo. Certifique-se de calcular leituras pendentes de agregação. Por exemplo,
2 threads de leitura com 50 solicitações pendentes seriam responsáveis por um total
de 100 solicitações pendentes para o LUN de destino.
3. Comece com um valor relativamente baixo de operações de E/S pendentes (-o) e repita
os testes aumentando esse valor até que não haja mais ganho na taxa de transferência
de agregação.
A meta desse teste é atingir uma taxa de transferência de agregação que seja razoável
em comparação com os limites teóricos dos componentes no caminho entre o servidor
e o armazenamento. Esse teste valida a largura da banda entre o servidor e os processadores
de armazenamento da rede SAN, isto é, os caminhos Fibre Channel de Múltiplos Caminhos.
44
Etapa 2 - Validar a largura de banda de LUN/volume
Esse teste é semelhante ao teste anterior. No entanto, é usado um arquivo maior para remover
os possíveis benefícios do cache de matriz do cache do controlador. Esses arquivos de teste
devem ser grandes o bastante para simular o tamanho do arquivo de banco de dados de
destino por volume, por exemplo, 25 GB por volume. Parâmetros semelhantes devem ser
usados para o SQLIO, conforme descrito na etapa 1.
As leituras sequenciais de bloco grande (512 KB) devem ser emitidas nos arquivos de teste
em cada volume. Recomendamos usar um único thread por arquivo com uma profundidade
de solicitação pendente em torno de 4 e 16 (comece com um número baixo e aumente até
que a taxa de transferência máxima seja atingida). Em primeiro lugar, teste cada volume
individualmente e, em seguida, teste os dois simultaneamente. A taxa de transferência do
grupo de discos varia por fornecedor de armazenamento e configuração, mas a comparação
sempre pode ser feita com taxas únicas de leituras de HDD. Um grupo de discos RAID1+0 de
4 discos, por exemplo, poderia atingir uma taxa de leitura de pico de aproximadamente quatro
vezes a taxa de leitura única de HDD para esse tipo de padrão de leitura básico. O desempenho
do RAID 1 ou 1+0 pode variar por produto de armazenamento, pois a tecnologia de alguns
fornecedores permite a "leitura espelhada", a qual permite que as operações de E/S sejam
atendidas em ambos os lados do par espelhado quando solicitações contíguas são recebidas.
Etapa 3 - Validar a largura de banda de agregação
Nesse teste, as leituras sequenciais devem ser executadas simultaneamente em todos os
volumes de dados disponíveis nos mesmos arquivos usados na etapa 2. O SQLIO deve ser
executado usando dois threads por arquivo de teste, com um tamanho de E/S de 512 K e um
número ideal de E/Ss pendentes, conforme determinado pelo teste anterior.
Os resultados desse teste ilustram a taxa de transferência máxima de agregação que pode ser
atingida na leitura de dados dos discos físicos.
Os dados são lidos no arquivo de dados grande, como no teste anterior, em cada volume
simultaneamente.
O desempenho de agregação do disco deve estar na faixa de 80 a 90% da largura de banda de
E/S de armazenamento agregado, para sistemas FTDW balanceados.
Classificações de componentes
O diagrama a seguir ilustra os resultados da avaliação de desempenho sintética que são
consistentes com os valores vistos em arquiteturas de referência Fast Track semelhantes.
45
Figura 4: Exemplo de largura de banda percebida pela avaliação de desempenho sintética
para um servidor de 12 núcleos e 2 soquetes com 3 placas HBA de 8 Gbps de porta dupla,
com 12 volumes de dados primários RAID1+0 de 4 discos.
Resumo
A avaliação de desempenho de hardware de linha de base valida a capacidade real da
largura de banda para os principais componentes de hardware da pilha de banco de dados.
Essa validação é feita com uma série de testes sintéticos do melhor caso executados com
uma ferramenta como o SQLIO.
Executando a avaliação de desempenho de banco de dados Fast Track
Essa fase de avaliação da FTRA mede o desempenho do SQL Server para a carga de
trabalho do FTDW em termos de duas métricas principais. A primeira, MCR (taxa máxima de
consumo de CPU), é uma medida da taxa de transferência de processamento máximo de E/S.
A segunda, BCR (taxa de consumo de CPU da avaliação de desempenho), é uma medida
da taxa de transferência real do processamento de E/S para uma consulta ou uma carga de
trabalho baseada em consulta.
46
O que é MCR?
O cálculo da MCR fornece um valor de taxa de transferência de E/S por núcleo em MB ou GB
por segundo. Para medir esse valor, é necessário executar uma consulta predefinida, somente
leitura e não otimizada no cache do buffer e medir o tempo de execução em relação à quantidade
de dados em MB ou GB. Como a MCR é executada no cache, ela representa a taxa máxima de
exame não otimizada que pode ser atingida com o SQL Server para o sistema que está sendo
avaliado. Por esse motivo, a MCR fornece uma taxa de pico de linha de base para fins de
design inicial. Ela não se destina a indicar os resultados médios ou esperados para uma carga
de trabalho real. As arquiteturas do FTDW validadas terão os resultados da taxa de transferência
de E/S de linha de base agregada que são, pelo menos, 100% da MCR classificada pelo
servidor. Outra maneira de explicar isso é declarar que a MCR representa a melhor taxa
possível de processamento do SQL Server para uma carga de trabalho razoável do pior caso.
A MCR também pode ser usada como um quadro de referência na comparação de outras
arquiteturas de referência do FTDW publicadas e validadas para o SQL Server 2012.
Em resumo:




A MCR não representa os resultados reais definitivos para uma carga de trabalho
do cliente.
A MCR fornece uma linha de base da taxa máxima de processamento de dados para
o SQL Server e uma única consulta associada à carga de trabalho Fast Track.
A MCR é específica a uma CPU e um servidor. Em geral, as taxas de uma determinada
CPU variam muito por servidor e arquitetura de placa-mãe, mas a MCR final deve ser
determinada pelos testes reais.
A classificação da taxa de transferência da MCR pode ser usada como um valor
comparativo em relação a arquiteturas de referência do FTDW existentes já publicadas.
Isso pode ajudar na escolha do hardware antes dos testes de componentes e aplicativos.
Calculando a MCR
Uma taxa de consumo de CPU de linha de base para o aplicativo SQL Server é estabelecida
por uma consulta SQL padrão definida para o programa FTDW. Essa consulta foi desenvolvida
para ser uma representação relativamente simples de uma consulta comum para o tipo de
carga de trabalho (nesse caso, o data warehouse) e é executada no cache de buffer. O valor
resultante é específico à CPU e ao servidor em que a consulta está sendo executada. Use este
método para calcular a MCR:
1. Crie um conjunto de dados de referência com base na tabela lineitem do TPC-H ou em
um conjunto de dados semelhante. A tabela deve ser de um tamanho que possa ser
totalmente armazenada em cache no pool de buffers do SQL Server, mas ainda manter
um tempo de execução mínimo de um segundo para a consulta fornecida aqui.
2. Para o FTDW, a seguinte consulta é usada: SELECT sum([integer field]) FROM [table]
WHERE [restrict to appropriate data volume] GROUP BY [col].
47
3. O ambiente deve:
o Garantir que as configurações do Administrador de Recursos tenham os
valores padrão.
o Garantir que a consulta esteja sendo executada no cache do buffer. A primeira
execução da consulta deve colocar as páginas no buffer e as execuções
subsequentes devem ler somente do buffer. Verifique se não há leituras físicas
na saída de estatísticas da consulta.
o Definir STATISTICS IO e STATISTICS TIME como ON para gerar os resultados.
4. Execute a consulta várias vezes, com MAXDOP = 4.
5. Registre o número de leituras lógicas e o tempo de CPU da saída de estatísticas para
cada execução da consulta.
6. Calcule a MCR em MB/s usando a fórmula:
( [Leituras lógicas] / [Tempo de CPU em segundos] ) * 8 KB / 1024
7. Um intervalo de valores consistente (+/- 5%) deve aparecer em um mínimo de cinco
execuções da consulta. As exceções significativas (+/- 20% ou mais) podem indicar
problemas de configuração. A média de pelo menos 5 resultados calculados é a MCR
do FTDW.
Com base no cálculo da MCR, é possível criar um diagrama da taxa de transferência da
arquitetura do componente. Para fins de avaliação da MCR do sistema, a taxa de transferência
do componente é baseada na largura de banda classificada pelo fornecedor. Este diagrama
pode ser útil para o design, a seleção e a análise de afunilamentos do sistema. A Figura 5
ilustra um exemplo disso.
48
Figura 5: Exemplo de MCR e largura de banda de componentes classificados para um servidor
de 12 núcleos e 2 soquetes baseado em CPUs Intel Westmere
Para obter mais informações sobre como medir a MCR, consulte Teste de carga de trabalho
no apêndice.
Calculando a BCR
Uma taxa de consumo de CPU da avaliação de desempenho do aplicativo do SQL Server
é estabelecida com a execução de um conjunto de linha de base das consultas SQL,
sob um nível adequado de simultaneidade, que são específicas à sua carga de trabalho
de data warehouse. O número de consultas e o nível de simultaneidade usados dependem
completamente do caso de uso esperado. A carga de trabalho da consulta deve ser atendida
no disco, não no pool de buffers do SQL Server, como acontece com a MCR. O valor
resultante é específico à CPU, ao servidor e à carga de trabalho em que a consulta está
sendo executada. A entrada do apêndice para Teste de carga de trabalho fornece um exemplo
mais detalhado de como criar uma avaliação de desempenho de carga de trabalho da BCR.
49
Use este método para calcular a BCR:
1. Crie um conjunto de dados de referência que contenha pelo menos uma tabela.
A tabela deve ter um tamanho suficiente significativo, que não seja inteiramente
armazenado em cache no pool de buffers do SQL Server nem no cache de matriz da
rede SAN. Na ausência de dados do cliente, um conjunto de dados sintético pode ser
usado. É importante tentar aproximar as características esperadas dos dados do caso
de uso pretendido.
2. A forma básica de consulta para o FTDW é a seguinte: SELECT sum([integer field])
FROM [table] WHERE [restrict to appropriate data volume] GROUP BY [col]. Ela poderá
ser usada como um ponto de partida para o design de carga de trabalho de consulta
se as consultas do cliente não estiverem prontamente disponíveis. TPC-H é outra
avaliação de desempenho de consulta usada frequentemente que pode ser tratada
como um conjunto de consultas de referência.
3. Para uma avaliação de desempenho de cliente do FTDW é sempre ideal escolher as
consultas que são representativas da carga de trabalho de destino. As consultas devem
ser agendadas em várias sessões simultâneas que representam a atividade máxima
histórica ou projetada para o ambiente do cliente. Os critérios a seguir podem ser
considerados na seleção da consulta:
 Represente os requisitos médios da carga de trabalho de destino. Isso pode
indicar o aumento ou a diminuição da complexidade da forma básica de
consulta, a adição de junções e/ou o descarte de mais ou menos dados por
meio de projeção e restrição.
 A consulta não deve causar gravações de dados em tempdb, a menos que
essa característica seja uma parte essencial da carga de trabalho de destino.
 A consulta deve retornar linhas mínimas. A opção SET ROWCOUNT pode ser
usada para gerenciar isso. Deve ser usado um valor ROWCOUNT maior que
100 (105 é o padrão para a avaliação de desempenho do Fast Track). Como
alternativa, a agregação pode ser usada para reduzir os registros retornados de
grandes exames irrestritos.
4. O ambiente deve:
 Garantir que as configurações do Administrador de Recursos sejam definidas
para os valores padrão.
 Garantir que os caches sejam limpos antes da execução da consulta, usando
DBCC dropcleanbuffers.
 Definir STATISTICS IO e STATISTICS TIME como ON para gerar os resultados.
5. Execute a consulta ou a carga de trabalho várias vezes, começando em MAXDOP 8.
Toda vez que você executar a consulta, aumente a configuração MAXDOP para
a consulta, limpado os caches entre cada execução.
 Registre o número de leituras lógicas e o tempo de CPU da saída de estatísticas.
 Calcule a BCR em MB/s usando a fórmula:
( [Leituras lógicas] / [Tempo de CPU em segundos] ) * 8 KB / 1024
 Isso fornece um intervalo para a BCR. Se várias consultas forem usadas, use
uma média ponderada para determinar a BCR.
50
Resultados da BCR
A Figura 6 ilustra os resultados da avaliação de desempenho baseada na carga de trabalho do
SQL Server que são consistentes com os valores vistos em arquiteturas de referência do Fast
Track Data Warehouse semelhantes.
Figura 6: Exemplo de largura de banda percebida pela avaliação de desempenho sintética
para um servidor de 12 núcleos e 2 soquetes com 3 placas HBA de 8 Gbps de porta dupla,
com 12 LUNs de dados primários RAID1+0 de 4 discos
51
Interpretando a BCR
Se sua CR para a consulta média for muito menor que a MCR padrão avaliada para a FTRA,
é provável que você esteja limitado à CPU. Em resposta, você pode pensar na diminuição
da taxa de transferência de armazenamento, por exemplo, reduzindo o número de matrizes,
introduzindo mais discos por matriz ou aumentando o tamanho dos discos – essas etapas
podem ajudar a reduzir os custos de infraestrutura de armazenamento a um nível balanceado.
Como alternativa, você pode pensar em como usar um servidor com um número de soquetes
mais alto ou CPUs de desempenho mais alto que possam aproveitar a taxa de transferência de
E/S de armazenamento excedente. Em ambos os casos, a meta é equilibrar a capacidade de
processamento do banco de dados com a taxa de transferência de E/S do armazenamento.
De modo correspondente, se sua BCR for mais alta que a MCR, talvez você precise de mais
taxa de transferência de E/S para processar uma carga de trabalho de consulta de maneira
equilibrada.
Arquiteturas de referência do FTDW publicadas
As especificações detalhadas da arquitetura de referência de hardware estão disponíveis
em cada parceiro participante do Fast Track Data Warehouse. Para obter mais informações,
incluindo links para cada parceiro, consulte Fast Track Data Warehouse
(http://www.microsoft.com/sqlserver/pt/br/solutions-technologies/data-warehousing/fast-track.aspx).
A capacidade do FTDW foi avaliada pela estimativa da quantidade de arquivos de dados de
usuário não compactados que podem ser carregados no banco de dados. Isso é chamado de
UDC (capacidade de dados do usuário). Esse cálculo supõe que a compactação de página
esteja habilitada para todas as tabelas e que os volumes de dados serão espelhados. É usado
um fator médio de compactação de 3,5:1. Além disso, uma alocação de até 30% de capacidade
não compactada é alocada para tempdb antes do cálculo da UDC. Observe que, para
configurações maiores com mais capacidade total, essa taxa é reduzida para até 20%.
Para obter informações sobre o dimensionamento de tempdb, consulte Planejamento de
capacidade para tempdb (http://msdn.microsoft.com/pt-br/library/ms345368.aspx).
Conclusão
O SQL Server Fast Track Data Warehouse oferece um modelo e ferramentas para desde
o design até a implantação de um data warehouse. Este documento descreve a metodologia,
as opções de configuração, as práticas recomendadas, as configurações de referência e as
técnicas de avaliação de desempenho e validação do Fast Track Data Warehouse.
52
Para obter mais informações:
Site do SQL Server
Site do SQL Server Fast Track
TechCenter do SQL Server
Recursos online do SQL Server
As 10 principais práticas recomendadas para criação de data warehouses relacionais em
grande escala (equipe do SQLCAT)
Como habilitar a opção Bloquear Páginas na Memória (Windows)
Opções de ajustes para o SQL Server 2005 e o SQL Server 2008 ao executar cargas de
trabalho de alto desempenho
Como configurar o SQL Server para usar o NUMA para software
Inicialização de arquivos de bancos de dados
Como exibir ou alterar o modelo de recuperação de um banco de dados (SQL Server
Management Studio)
Monitorando o uso da memória
Solucionando problemas de rede SAN
Instalando e configurando o MPIO
White paper de noções básicas sobre E/S do SQL Server 2000
Compactação de dados: estratégia, planejamento de capacidade e práticas recomendadas
Este documento ajudou você? Envie seus comentários. Em uma escala de 1 (ruim) a 5
(excelente), como você classificaria este white paper e por quê? Por exemplo:

Você está classificando-o como excelente devido aos bons exemplos, às excelentes
capturas de tela, à clareza do texto ou por outro motivo?
 Você está classificando-o como ruim devido aos exemplos insatisfatórios, às capturas
de tela confusas ou ao texto mal-escrito?
Estes comentários nos ajudam a melhorar a qualidade dos white papers que lançamos.
Enviar comentários.
53
Apêndice
Ferramenta de dimensionamento do sistema FTDW
A ferramenta FTDW System Sizing é uma calculadora em formato de planilha que ajuda você
a calcular um requisito de carga de trabalho do cliente em termos de taxa de transferência do
sistema FTDW. Você pode usar essa ferramenta na ausência de uma plataforma de teste ou
como ponto de partida ao avaliar requisitos do cliente. A ferramenta pode ser encontrada em
Fast Track Data Warehouse (http://www.microsoft.com/sqlserver/pt/br/solutions-technologies/
data-warehousing/fast-track.aspx). Além disso, alguns fornecedores parceiros criaram as
próprias ferramentas de dimensionamento em conformidade com o Fast Track. Elas podem
ser encontradas nos sites dos parceiros.
Validando uma FTRA definida pelo usuário
Testes sintéticos de E/S
O SQLIO é uma ferramenta disponível para download da Microsoft que permite testar
o subsistema de E/S independentemente do SQL Server.
Gerando arquivos de teste com o SQLIO
Quando você executa o SQLIO, ele cria um arquivo de teste apropriado, caso esse arquivo
não esteja presente. Para gerar um arquivo de um tamanho específico, use o parâmetro –F.
Por exemplo, use um arquivo de parâmetro (param.txt) que contenha o seguinte:
C:\stor\pri\1\iobw.tst 1 0x0 50
A execução de SQLIO com o parâmetro –F gera um arquivo de 50 MB na primeira execução:
Eq sqlio -kW -s60 -fsequential -o1 -b64 -LS -Fparam.txt
Esse processo pode ser demorado para arquivos grandes. Crie um arquivo em cada disco
de dados no qual você hospedará os dados do SQL Server e os arquivos tempdb. Isso pode
ser feito com a inclusão de mais linhas ao arquivo de parâmetro, que criará os arquivos
necessários, um a um. Para criar arquivos em paralelo, crie vários arquivos de parâmetro
e execute várias sessões do SQLIO simultaneamente.
Validar a largura de banda do armazenamento (no cache)
Usar um arquivo de teste pequeno com uma duração de leitura de vários minutos garante que
o arquivo resida por completo no cache da matriz. A Figura 7 mostra o contador Disco Lógico
> Bytes de Leitura/s nos discos em um exemplo do sistema Fast Track em vários números
de solicitações pendentes e tamanhos de bloco. Os testes devem ser executados, pelo menos,
durante alguns minutos para garantir um desempenho consistente. A figura mostra que o bom
desempenho exige uma fila de solicitações pendentes de, pelo menos, quatro solicitações por
arquivo. Cada disco individual deve contribuir para a largura de banda total.
54
Figura 7: Contador Disco Lógico > Bytes de Leitura/s
Validar largura de banda de LUN/volume (no disco)
Esses testes garantem que todos os volumes de disco apresentados pelas matrizes de disco
para o Windows sejam capazes de contribuir com a largura de banda agregada geral, lendo
cada volume individualmente. Você pode ver que alguns dos LUNs parecem ser um pouco
mais rápidos do que outros. Isso não é incomum, mas as diferenças superiores a 15% devem
ser examinadas.
Figura 8: Validando a largura de banda do par LUN/volume e RAID
55
Execute testes simultâneos em um ou mais volumes que compartilhem o mesmo grupo de
discos. A imagem a seguir mostra a saída dos testes em 8 grupos de discos.
Figura 9: Testando LUNs que compartilham grupos de disco
Validar largura de banda agregada (no disco)
O teste a seguir demonstra o efeito de aumentar a taxa de transferência de E/S adicionando
um volume extra ao teste em intervalos regulares. À medida que cada teste for executado para
um intervalo de conjuntos, você observará uma diminuição. Você deverá notar um padrão
semelhante. A largura de banda agregada máxima do disco deve se aproximar de 80% a 90%
da largura de banda demonstrada no cache na primeira etapa. O gráfico mostra o teste em
vários tamanhos de bloco, 512 K e 64 K.
Figura 10: Largura de banda agregada em vários tamanhos de bloco
56
Testes de carga de trabalho
Medindo a MCR para seu servidor (opcional)
A meta da MCR é calcular a taxa de transferência máxima de um único núcleo de CPU, que
executa o SQL Server, na ausência de problemas de afunilamento de E/S. A MCR é avaliada
por núcleo. Se você optou por esse cálculo para seu próprio servidor, aqui são fornecidos os
detalhes adicionais que descrevem a metodologia para calcular a MCR:
1. Crie um conjunto de dados de referência com base na tabela lineitem do TPC-H ou
em um conjunto de dados semelhante. A tabela deve ter um tamanho que permita
seu armazenamento completo em cache no pool de buffers do SQL Server, mas ainda
manter um tempo de execução mínimo de 2 segundos para a consulta fornecida aqui.
2. Para o FTDW, a seguinte consulta é usada: SELECT sum([integer field]) FROM [table]
WHERE [restrict to appropriate data volume] GROUP BY [col].
3. O ambiente deve:
 Garantir que o Administrador de Recursos seja definido para os valores padrão.
 Garantir que a consulta esteja sendo executada no cache do buffer. A primeira
execução da consulta deve colocar as páginas no buffer e as execuções
subsequentes devem ler somente do buffer. Verifique se não há leituras físicas
na saída de estatísticas da consulta.
 Definir STATISTICS IO e STATISTICS TIME como ON para gerar os resultados.
4. Execute a consulta várias vezes, com MAXDOP = 4.
 Registre o número de leituras lógicas e o tempo de CPU da saída de estatísticas
para cada execução da consulta.
 Calcule a MCR em MB/s usando a fórmula:
( [Leituras lógicas] / [Tempo de CPU em segundos] ) * 8 KB / 1024
 Um intervalo de valores consistente (+/- 5%) deve aparecer em um mínimo
de cinco execuções da consulta. As exceções significativas (+/- 20% ou mais)
podem indicar problemas de configuração. A média de pelo menos 5 resultados
calculados é a MCR do FTDW.
Medindo a BCR para sua carga de trabalho
A medida de BCR é semelhante à medida de MCR, exceto pelo fato de que os dados são
atendidos no disco, não no cache. A consulta e o conjunto de dados para a BCR representam
a carga de trabalho do seu data warehouse de destino.
Uma abordagem para a BCR é executar uma consulta simples, uma consulta média e uma
consulta complexa na carga de trabalho. As consultas complexas devem ser aquelas que
colocam mais demandas na CPU. A consulta simples deve ser análoga à MCR e deve realizar
um volume de trabalho semelhante, de modo que seja comparável à MCR.
Criando o banco de dados
Veja o exemplo de uma instrução CREATE DATABASE para um sistema FTDW de 8 núcleos,
com 16 LUNs de dados.
57
CREATE DATABASE FT_Demo ON
( NAME = N 'FT_Demo_.mdf' ,
FILEGROWTH = 0 ),
( NAME = N 'FT_Demo_v1.ndf'
FILEGROWTH = 0 ),
( NAME = N 'FT_Demo_v2.ndf'
FILEGROWTH = 0 ),
( NAME = N 'FT_Demo_v3.ndf'
FILEGROWTH = 0 ),
( NAME = N 'FT_Demo_v4.ndf'
FILEGROWTH = 0 ),
PRIMARY Filegroup FT_Demo
FILENAME = N'C:\FT\PRI\SE1-SP1-DG1-v1' , SIZE = 100MB ,
( NAME = N
FILEGROWTH
( NAME = N
FILEGROWTH
( NAME = N
FILEGROWTH
( NAME = N
FILEGROWTH
, FILENAME = N'C:\FT\PRI\SE2-SP1-DG6-v6' , SIZE = 417GB ,
(
,
(
,
(
,
(
,
'FT_Demo_v6.ndf'
= 0 ),
'FT_Demo_v7.ndf'
= 0 ),
'FT_Demo_v8.ndf'
= 0 ),
'FT_Demo_v9.ndf'
= 0 ),
, FILENAME = N'C:\FT\PRI\SE1-SP1-DG1-v1' , SIZE = 417GB ,
, FILENAME = N'C:\FT\PRI\SE1-SP1-DG2-v2' , SIZE = 417GB ,
, FILENAME = N'C:\FT\PRI\SE1-SP2-DG3-v3' , SIZE = 417GB ,
, FILENAME = N'C:\FT\PRI\SE1-SP2-DG4-v4' , SIZE = 417GB ,
, FILENAME = N'C:\FT\PRI\SE2-SP1-DG7-v7' , SIZE = 417GB ,
, FILENAME = N'C:\FT\PRI\SE2-SP2-DG8-v8' , SIZE = 417GB ,
NAME = N 'FT_Demo_v11.ndf'
FILEGROWTH = 0 ),
NAME = N 'FT_Demo_v12.ndf'
FILEGROWTH = 0 ),
NAME = N 'FT_Demo_v13.ndf'
FILEGROWTH = 0 ),
NAME = N 'FT_Demo_v14.ndf'
FILEGROWTH = 0 ),
, FILENAME = N'C:\FT\PRI\SE2-SP2-DG9-v9' , SIZE = 417GB ,
, FILENAME = N'C:\FT\PRI\SE3-SP1-DG11-v11' , SIZE = 417GB
, FILENAME = N'C:\FT\PRI\SE3-SP1-DG12-v12' , SIZE = 417GB
, FILENAME = N'C:\FT\PRI\SE3-SP2-DG13-v13' , SIZE = 417GB
, FILENAME = N'C:\FT\PRI\SE3-SP2-DG14-v14' , SIZE = 417GB
LOG ON
( NAME = N 'FT_LOG_v5.ldf' , FILENAME = N 'C:\FT\LOG\SE1-SP2-DG5-v5' , SIZE = 100GB ,
MAXSIZE = 500GB , FILEGROWTH = 50 )
GO
/*****************Configure recommended settings***********************/
ALTER DATABASE FT_Demo SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE
GO
FT_Demo
SET AUTO_UPDATE_STATISTICS ON
ALTER DATABASE
GO
FT_Demo
SET AUTO_UPDATE_STATISTICS_ASYNC ON
ALTER DATABASE
GO
FT_Demo
SET RECOVERY SIMPLE
sp_configure 'show advanced options', 1
go
reconfigure with override
go
/********Make sure all tables go on our filegroup and not the Primary filegroup****/
ALTER DATABASE FT_Demo
MODIFY FILEGROUP FT_Demo
DEFAULT
GO
58
Criando as tabelas de teste
Aqui está um exemplo da instrução CREATE TABLE.
CREATE TABLE lineitem
( l_orderkey
bigint not null,
l_partkey
integer not null,
l_suppkey
integer not null,
l_linenumber
integer not null,
l_quantity
float not null,
l_extendedprice float not null,
l_discount
float not null,
l_tax
float not null,
l_returnflag
char(1) not null,
l_linestatus
char(1) not null,
l_shipdate
datetime not null,
l_commitdate
datetime not null,
l_receiptdate
datetime not null,
l_shipinstruct char(25) not null,
l_shipmode
char(10) not null,
l_comment
varchar(132) not null
)
ON FT_Demo
GO
CREATE CLUSTERED INDEX cidx_lineitem
ON lineitem(l_shipdate ASC)
WITH( SORT_IN_TEMPDB = ON
, DATA_COMPRESSION = PAGE
)
ON FT_Demo
GO
Carregando dados para avaliação de BCR
Conforme descrito anteriormente neste documento, os sistemas Fast Track Data Warehouse
reconhecem a fragmentação dos arquivos de banco de dados. Use uma das técnicas que este
documento descreve para carregamento de dados. Durante os testes do FTDW, foi usado
o método de carregamento de índice clusterizado descrito como a opção 2. Usando a ferramenta
datagen do TPC-H, os dados da tabela lineitem foram gerados com um tamanho de 70 GB,
usando opções -s100, gerando o arquivo em 8 partes e usando as opções –S e –C.
O sinalizador de rastreamento 610 foi definido durante todas as operações de carregamento
para usar o registro em log mínimo sempre que possível.
Usando BULK INSERT, esses dados foram inseridos paralelamente em uma única tabela de
preparo de índice clusterizado, usando o log mínimo; escolhemos um tamanho de bloco que
não oprimisse a memória disponível e que reduzisse o derramamento no disco. Desabilitar
os bloqueios de página e o escalonamento de bloqueio na tabela de preparo melhorou
o desempenho durante essa fase.
Uma inserção final foi executada em uma tabela de destino idêntica, com MAXDOP 1 (usando
a dica TABLOCK) e evitando uma classificação.
59
Executando consultas para avaliação da BCR
Use a ferramenta SQL Server Profiler para registrar informações relevantes de avaliações de
desempenho de consulta. O SQL Server Profiler deve ser configurado para registrar leituras
lógicas, a CPU, a duração, o nome do banco de dados, o nome do esquema, a instrução SQL
e os planos de consulta reais. Como alternativa, podem ser usados os parâmetros de sessão
de estatísticas set statistics io on e set statistics time on.
Veja alguns exemplos de consultas (com base nas consultas da avaliação de desempenho
TPC-H) e a BCR atingida nos sistemas de referência. Observe que este exemplo não é uma
indicação de que o desempenho será obtido em qualquer sistema. Os números da BCR são
exclusivos do sistema, do tamanho do esquema, dos tipos de dados, da estrutura de consulta
e das estatísticas, apenas para citar algumas das muitas variáveis.
Complexidade da consulta
BCR por núcleo
(Página compactada ) em MAXDOP 4
Simples
201 MB/s
Média
83 MB/s
Complexa
56 MB/s
Tabela 7: Exemplos de avaliações de desempenho
Simples
SELECT
sum(l_extendedprice * l_discount) as revenue
FROM
lineitem
WHERE
l_discount between 0.04 - 0.01 and 0.04 + 0.01 and
l_quantity < 25
OPTION (maxdop 4)
Média
SELECT
l_returnflag,
l_linestatus,
sum(l_quantity) as sum_qty,
sum(l_extendedprice) as sum_base_price,
sum(l_extendedprice*(1-l_discount)) as sum_disc_price,
sum(l_extendedprice*(1-l_discount)*(1+l_tax)) as sum_charge,
avg(l_quantity) as avg_qty,
avg(l_extendedprice) as avg_price,
avg(l_discount) as avg_disc,
count_big(*) as count_order
FROM
lineitem
WHERE
l_shipdate <= dateadd(dd, -90, '1998-12-01')
60
GROUP BY
l_returnflag,
l_linestatus
ORDER BY
l_returnflag,
l_linestatus
OPTION (maxdop 4)
Complexa
SELECT
100.00 * sum(case
when p_type like 'PROMO%'
then l_extendedprice*(1-l_discount)
else 0
end) / sum(l_extendedprice * (1 - l_discount)) as
promo_revenue
FROM
lineitem,
part
WHERE
l_partkey = p_partkey
and l_shipdate >= '1995-09-01'
and l_shipdate < dateadd(mm, 1, '1995-09-01')
OPTION (maxdop 4)
Fatores que afetam a taxa de consumo da consulta
Nem todas as consultas atingirão a MCR (taxa máxima de consumo de CPU) e a BCR
(taxa de consumo de avaliação de desempenho). Há muitos fatores que podem afetar a taxa
de consumo de uma consulta. As consultas mais simples do que a carga de trabalho usada
para gerar a taxa de consumo terão taxas de consumo mais altas, e as cargas de trabalho
mais complexas terão taxas de consumo mais baixas. Muitos fatores podem afetar essa
complexidade e a taxa de consumo, por exemplo:



61
Complexidade da consulta: quanto mais a consulta consumir CPU, por exemplo,
em termos de cálculos e número de agregações, mais baixa será a taxa de consumo.
Complexidade da classificação: as classificações de ordem explícita por operações
ou grupo por operações gerarão mais carga de trabalho de CPU e diminuirão a taxa de
consumo. As gravações adicionais em tempdb causadas pelo derramamento de tais
consultas no disco afetam negativamente a taxa de consumo.
Complexidade do plano de consulta: quanto mais complexo for um plano de consulta,
e quanto mais etapas e operadores ele tiver, mais baixa será a taxa de consumo da
CPU, pois cada unidade de dados é processada por meio de um pipeline mais longo
de operações.


62
Compactação: a compactação diminuirá a taxa de consumo de dados em termos reais,
pois a taxa de consumo, por definição, é medida para consultas que são associadas
à CPU, e a descompactação consumirá ciclos de CPU. Entretanto, os maiores
benefícios em taxa de transferência geralmente superam a sobrecarga de CPU
adicional envolvida na compactação, a menos que a carga de trabalho consuma CPU
em alto grau. Ao comparar taxas de consumo de dados compactados e descompactados,
leve em consideração o fator de compactação. Outra maneira de ver isso é pensar na
taxa de consumo em termos de linhas por segundo.
Utilização de dados: descartar dados durante os exames (por exemplo, por meio da
projeção e seleção de consultas) é um processo bastante eficiente. As consultas que
usam todos os dados em uma tabela têm taxas de consumo menores, pois mais dados
são processados por taxa de transferência de dados da unidade.