• Artigo

P+R SQLConfiguração da memória, criação de perfil de desempenho, configuração do fator de preenchimento e muito mais

Edited by Nancy Michell

Configuração da memória

Dica: criação de perfil mais simples

Você sabia que agora pode correlacionar o PerfMon ao SQL Server 2005 Profiler?

Talvez você tenha notado picos de uso de CPU, consumo excessivo de memória ou desempenho geral lento no Monitor de Desempenho da sua máquina com SQL Server e queira saber o que causou essas anomalias de desempenho. Antes do SQL Server 2005, você tinha de usar o Profiler para capturar um rastreamento, examinar processos do sistema no Enterprise Manager e, por fim, capturar os logs do seu Monitor de Desempenho — o que, é claro, exigia a execução do PerfMon. E depois de todo esse trabalho usando todas essas ferramentas, você ainda precisava reconciliar manualmente os eventos entre as ferramentas para descobrir qual era o problema de desempenho. E isso significava trabalhar arduamente e repassar os logs um por um. Não era divertido, mas você tinha de fazer isso se quisesse chegar ao fundo dos seus problemas de desempenho.

Com o SQL Server 2005, você ainda precisa capturar um rastreamento e examinar os logs do seu PerfMon, mas o Profiler agora permite que você os anexe. Você pode rolar pelas suas instruções T-SQL e o Profiler mostrará automaticamente e de forma gráfica o que aconteceu. Se você clicar na interface do usuário do Monitor de Desempenho no Profiler, pulará para a instrução relacionada ao carimbo de data/hora. Isso poupa muito tempo na solução de problemas do seu ambiente SQL Server.

Vamos ver como anexar logs do PerfMon ao Profiler:

  1. Inicie o Monitor de Desempenho e comece a capturar informações do servidor de banco de dados.
  2. Crie um novo log de contador nos Alertas e Logs de Desempenho e digite um nome para ele.
  3. Adicione novos contadores, como % Tempo de Processador. Você também vai querer definir o início da captura dos logs manualmente ou agendá-la usando a guia Agenda.
  4. Clique em OK e, se a opção manual tiver sido a escolhida, não se esqueça de iniciar a captura de logs.
  5. Configure um rastreamento em seu SQL Server por meio do Profiler. Faça isso clicando em New Trace (Novo Rastreamento) no menu File (Arquivo). Não se esqueça de incluir a hora de início e hora de término em seu rastreamento, dê um nome a ele e defina-o para ser salvo em um arquivo. Por fim, você deve utilizar ao máximo o seu servidor para simular alguma atividade de transação, e depois interrompa a captura de dados do Monitor de Desempenho e do Profiler.
  6. No Profiler, selecione Import Performance Data (Importar Dados de Desempenho) no menu File (Arquivo). Em seguida, escolha o local onde você armazenou o seu log do PerfMon e selecione File | Open | Trace (Arquivo | Abrir | Rastreamento). Por fim, selecione o local onde o seu rastreamento do Profiler foi armazenado.

Depois de terminar, você verá como é muito mais fácil descobrir que efeito em particular as instruções SQL tiveram no tempo de processamento.

P Estou tentando descobrir a melhor configuração de memória para os meus servidores SQL Server™. O administrador antes de mim definiu o Boot.ini para cada máquina com 12 GB de RAM em um cluster de dois nós do SQL Server 2000 como a seguir: Sim /PAE NO /3GB (Nenhum AWE definido para o SQL Server). Com 12 GB de RAM disponíveis, eu deveria remover a opção /3GB do Boot.ini, ativar o AWE e oferecer cerca de 10 GB dos 12 GB ao SQL Server? Não existe nada mais nas máquinas que executam o SQL Server e, portanto, nenhum outro aplicativo precisa da memória.

R Sim, você deve ativar o AWE (address windowing extensions) e pré-alocar um limite superior de RAM para o SQL Server — 10 GB parece ser um bom número para um servidor SQL Server dedicado com 12 GB (observe que a pré-alocação só será válida para o SQL Server 2000. A partir do SQL Server 2005, a utilização do AWE não é mais estática e pode ser alterada de forma dinâmica). Sempre houve um grande debate sobre a utilização ou não das opções /3GB e /PAE ou somente da /PAE obrigatória. Embora na verdade só seja preciso definir /PAE e habilitar o AWE, sugiro que você utilize ambas as opções; no entanto, existem alguns fatores a serem considerados.

A utilização da opção /3GB se resume a ser necessária em sua situação em particular. Estão acabando as áreas MemToLeave da memória que deveriam estar nos 2 ou 3 GB do espaço de endereço virtual? Se você habilitar a opção, estará privando o sistema operacional de memória? (Consulte support.microsoft.com/kb/316739 para obter mais informações). Se você estiver trabalhando em um cluster, poderia definir /3GB em um nó e não no outro. Dessa forma, se você estiver fazendo testes com /3GB e estiver tendo problemas, poderá fazer o failover da instância para o outro nó de forma muito rápida. Tenha em mente que se você tiver mais de 16 GB de RAM, a opção /3GB não terá suporte.

Ao usar /3GB, você estará aumentando o VAS (espaço de endereço virtual) em 50%, para que os aplicativos que colocam pressão de memória no VAS e não somente no cache de dados tenham grandes vantagens. Felizmente, os servidores de 64 bits, tanto o IA64 como o x64, eliminam esse fator incompreendido. Preocupações quanto a privar o sistema operacional não se aplicarão caso a máquina seja dedicada ao SQL Server. Deixar 2 GB para o sistema operacional também é um pouco de exagero; se esse servidor é dedicado ao SQL Server com somente os serviços mínimos e padrão do sistema operacional em execução, você verá que sobrará cerca de 1,3 GB de memória livre no servidor e, portanto, você também poderá deixar que o SQL Server utilize o 1 GB extra. Comece com 10 GB, use o PerfMon para monitorar a memória disponível em um período longo para ver o quanto permanecerá ocioso e faça os ajustes adequados. Lembre-se de que você terá swap se comprometer excessivamente o SQL Server 2000, já que o AWE não é tão dinâmico como no SQL Server 2005. A chave para a decisão de usar ou não a opção /3GB é testá-la em seu ambiente específico.

Nomes de instância para replicação

P Agora posso usar o endereço IP do meu servidor na Replicação do SQL Server 2005 para indicar a instância a ser replicada? No SQL Server 2000, de acordo com o texto "Como: Replicar entre computadores que estejam executando o SQL Server em domínios não-confiáveis ou pela internet" (support.microsoft.com/kb/321822), fazer isso causará erros, mas eu não sei se isso ainda acontece.

R Durante a especificação de instâncias de servidor para a replicação, você deve fornecer o nome de instância registrado do SQL Server. Por exemplo, você deve usar o nome da instância do SQL Server quando especifica os parâmetros Editor ou Assinante para procedimentos armazenados da replicação ou para as configurações de conexão do agente de replicação na linha de comando. Se o nome de rede da instância do SQL Server for diferente do nome de instância registrado, as conexões de replicação por agentes falharão.

Se o nome de rede da instância e o nome da instância do SQL Server forem diferentes, considere a adição do nome de instância do SQL Server como um nome de rede válido. Uma forma de definir um nome de rede alternativo é adicioná-lo ao arquivo hosts local. Por padrão, o arquivo hosts local fica na pasta WINDOWS\system32\drivers\etc ou WINNT\system32\drivers\etc. Por exemplo, se o nome do computador for comp1, se o seu endereço IP for 10.193.17.129 e se o nome da instância for inst1/instname, adicione a entrada a seguir ao arquivo hosts:

10.193.17.129 inst1

SQL Server Integration Services

P Estou instalando um cluster ativo/ativo para o SQL Server 2005 (Enterprise Edition de 64 bits com dois servidores) e terei um total de quatro instâncias do SQL Server 2005. O SSIS (SQL Server Integration Services) será necessário para todas as instâncias. O que você pode me dizer sobre o SSIS em cluster e o efeito disso sobre os planos de manutenção?

R Embora você possa usar o serviço SSIS em cluster, não é necessário fazer isso e isso poderia causar uma variedade de problemas, incluindo a falta de suporte a delegação (consulte msdn2.microsoft.com/aa337083); e isso não inclui várias instâncias — você só pode ter uma instância em execução em um nó por vez.

Anteriormente, o SSIS tinha de ser instalado — não precisava ser executado, só instalado — para o Maintenance Plan Wizard (Assistente de Plano de Manutenção) poder ser executado. No entanto, isso não acontece mais no SQL Server 2005 SP1. Se o SSIS não estiver em execução, os planos de manutenção poderão ser executados pelo SQL Server Agent.

Em vez de usar o SSIS em cluster, você poderia pensar em mantê-lo em execução como um serviço autônomo e editar o MsDtsSrvr.ini.xml para apontar para todas as instâncias em execução. Isso permite que você gerencie os seus pacotes a partir de qualquer nó e ofereça a alta disponibilidade que a maioria dos clientes está procurando sem os problemas associados ao serviço em cluster.

Para obter mais informações sobre a criação de um plano de manutenção com falha, consulte o artigo da Base de Dados de Conhecimento em support.microsoft.com/kb/909036.

Tempos de execução estranhos

P Durante um teste de carga em meu servidor SQL Server 2005 SP1, o SQL Server Profiler registrou diversos valores negativos de tempo de execução de procedimentos armazenados (SP) e, em alguns casos, o tempo de execução de SP não foi igual ao resultado da subtração da hora de início da hora de término.

R Existem muitos aspectos que podem afetar a criação de relatórios de tempo de execução de SP e outros tempos de desempenho no SQL Server Profiler. Lembre-se, o SQL Server 2005 usa milissegundos para contar o tempo de execução e, se você estiver usando uma tecnologia que altere a unidade de medida, obterá relatórios inconsistentes e tempos de execução que não são somados.

Por exemplo, se você estiver usando outros esquemas de energia, nível de CPU ou a tecnologia Cool 'n Quiet da AMD, estará alterando freqüências de CPU, fazendo com que elas não sejam o que o SQL Server Profiler espera quando calcula o tempo de execução.

Existe um artigo da Base de Dados de Conhecimento em support.microsoft.com/kb/931279 que explica os sintomas, a variedade de motivos e algumas soluções.

Dica: verifique o seu fator de preenchimento

Digamos que você tenha um copo totalmente cheio de água e tente colocar ainda mais água. O que acontece? O copo transborda.

Isso também acontece com o SQL Server. Sempre que uma nova linha é adicionada a uma página de índice cheia, o SQL Server move cerca de metade das linhas para uma nova página e assim dar espaço para a nova. Isso é conhecido como divisão da página. As divisões das páginas abrem espaço para novos registros, mas também levam tempo e utilizam recursos com muita intensidade. E causam a fragmentação, o que pode afetar negativamente as operações de E/S. Dessa forma, como você pode evitá-las?

Para evitar essas situações, você precisa determinar, de forma proativa, o valor do fator de preenchimento. Quando um índice é criado ou refeito, o valor do fator de preenchimento determina a porcentagem de espaço em cada página de nível de folha a ser preenchida com os dados, reservando o resto para um crescimento futuro. Por exemplo, a configuração de um valor de fator de preenchimento 60 significa que 40% de cada página de nível de folha ficarão vazios para possibilitar a expansão do índice à medida que os dados são adicionados à tabela subjacente.

O valor de fator de preenchimento padrão sempre é 0, o que é adequado à maioria das situações. Basicamente, um fator de preenchimento 0 significa que o nível de folha é preenchido em quase toda a sua capacidade, mas algum espaço é deixado pelo menos para uma linha de índice adicional (observe que os fatores de preenchimento 0 e 100 são similares).

Você pode definir o valor de fator de preenchimento de índices individuais durante uma instrução CREATE INDEX ou ALTER INDEX ou pode configurar esse valor diretamente no servidor, para que qualquer novo índice criado utilize o padrão.

O exemplo a seguir define o valor de fator de preenchimento no servidor como 70%, o que significa que você deixará 30% de espaço livre para expansão futura. É claro que você deve testar com cuidado essa opção antes de implementá-la em um ambiente de produção.

USE Master; GO SP_Configure 'show advanced options',1; GO SP_Configure 'Fill Factor', 70; GO -- Você deve reiniciar o SQL Server Engine para que as alterações entrem em vigor.

E se você quiser configurar o fator de preenchimento no nível de índice individual? Suponha que você esteja criando a tabela a seguir e queira criar um índice exclusivo na coluna chamada Col_A com um valor de fator de preenchimento 70. O comando seria assim:

-- Criar uma tabela Item USE Item_DB; GO CREATE TABLE ITEM (Col_A Varchar(100),Col_b Varchar(200)); GO;

-- Criar um índice exclusivo na coluna Col_A da tabela Item com valor de fator de preenchimento 70 CREATE UNIQUE INDEX AK_Index ON Item (Col_A) WITH (FillFactor = 70); GO

Como você identifica o fator de preenchimento para cada índice? Você pode consultar sys.Indexes para obter o valor de fator de preenchimento para todos os índices de um banco de dados, desta forma:

USE Item_DB; GO SELECT Fill_Factor FROM Sys.Indexes WHERE Object_id=object_id('item') AND name IS NOT NULL; GO

© 2008 Microsoft Corporation e CMP Media, LLC. Todos os direitos reservados. A reprodução parcial ou completa sem autorização é proibida..