Análise da workload do banco de dados em uma instância de banco de dados do Amazon RDS para SQL Server com o Orientador de Otimização do Mecanismo de Banco de Dados

O Orientador de Otimização do Mecanismo de Banco de Dados é uma aplicação de cliente fornecida pela Microsoft que analisa a workload do banco de dados e recomenda um conjunto ideal de índices aos bancos de dados do Microsoft SQL Server, com base nos tipos de consultas que você executa. Assim como no SQL Server Management Studio, você executa o Orientador de Otimização em um computador cliente conectado à instância de banco de dados do Amazon RDS que está executando o SQL Server. O computador cliente pode ser um computador local que você executa nas instalações dentro de sua própria rede, ou pode ser uma instância do Amazon EC2 para Windows que está sendo executada na mesma região que a sua instância de banco de dados do Amazon RDS.

Esta seção mostra como capturar uma workload para análise do Orientador de Otimização. Este é o processo ideal para capturar uma workload, pois o Amazon RDS restringe o acesso do host à instância do SQL Server. Para obter mais informações, consulte Database Engine Tuning Advisor (Orientador de Otimização do Mecanismo de Banco de dados) na documentação da Microsoft.

Para usar Orientador de Otimização, é necessário fornecer uma workload ao aplicativo. Uma workload é um conjunto de instruções Transact-SQL executadas em um banco de dados ou nos bancos de dados que você deseja ajustar. Orientador de Otimização do Mecanismo de Banco de Dados usa arquivos de rastreamento, tabelas de rastreamento, scripts Transact-SQL ou arquivos XML como entrada de workload ao ajustar bancos de dados. Ao trabalhar com o Amazon RDS, uma workload pode ser um arquivo em um computador cliente ou uma tabela de banco de dados em um banco de dados do Amazon RDS para SQL Server que pode ser acessada pelo computador cliente. O arquivo ou a tabela deve conter consultas feitas nos bancos de dados que você deseja ajustar e em um formato adequado para repetição.

Para que o Orientador de Otimização seja mais eficiente, uma workload deve ser o mais realista possível. É possível gerar um arquivo ou uma tabela de workload executando rastreamento na instância de banco de dados. Durante a execução do rastreamento, é possível simular uma carga na instância de banco de dados ou executar os aplicativos com uma carga normal.

Há dois tipos de rastreamento: no lado do cliente e no lado do servidor. A configuração de um rastreamento no lado do cliente é mais fácil e permite acompanhar a captura dos eventos de rastreamento em tempo real no SQL Server Profiler. A configuração de um rastreamento no lado do servidor é mais complexa e requer o uso de scripts Transact-SQL. Além disso, como o rastreamento é gravado em um arquivo na instância de banco de dados do Amazon RDS, o espaço de armazenamento é consumido pelo rastreamento. É importante controlar o espaço de armazenamento usado pelo rastreamento em execução no lado do servidor, pois a instância de banco de dados não ficará mais disponível se não houver espaço de armazenamento.

No caso de um rastreamento no lado do cliente, quando uma quantidade suficiente de dados de rastreamento é capturada no SQL Server Profiler, é possível gerar o arquivo de workload. Para isso, basta salvar o rastreamento em um arquivo no computador local ou em uma tabela de banco de dados em uma instância de banco de dados disponível para o seu computador cliente. A principal desvantagem de usar um rastreamento no lado do cliente é que talvez nem todas as consultas sejam capturadas quando sob cargas pesadas. Isso pode diminuir a eficácia da análise realizada pelo Orientador de Otimização do Mecanismo de Banco de Dados. Se você precisar executar um rastreamento sob cargas pesadas e desejar garantir que sejam capturadas todas as consultas durante uma sessão de rastreamento, será necessário usar um rastreamento no lado do servidor.

Para realizar um rastreamento do lado do servidor, é necessário que os arquivos de rastreamento na instância de banco de dados estejam em um arquivo de workload adequado. Também é possível salvar o rastreamento em uma tabela na instância de banco de dados após a conclusão do rastreamento. Você pode usar SQL Server Profiler para salvar o rastreamento em um arquivo no computador local ou usar Orientador de Otimização, de modo que ele faça a leitura na tabela de rastreamento na instância de banco de dados.

Execução de um rastreamento no lado do cliente em uma instância de banco de dados SQL Server

Para executar um rastreamento no lado do cliente em uma instância de banco de dados SQL Server

1. Inicie SQL Server Profiler. Ele é instalado na pasta Performance Tools da instância do SQL Server. É necessário carregar ou especificar um modelo de definição de rastreamento para iniciar um rastreamento no lado do cliente.

2. No menu Arquivo do SQL Server Profiler, escolha New Trace (Novo rastreamento). Na caixa de diálogo Connect to Server (Conectar-se ao servidor), insira o endpoint da instância de banco de dados, a porta, o nome de usuário principal e a senha do banco de dados no qual deseja executar um rastreamento.

3. Na caixa de diálogo Trace Properties (Propriedades de rastreamento), insira um nome de rastreamento e escolha um modelo de definição de rastreamento. O aplicativo vem com o modelo padrão TSQL_Replay. É possível editar este modelo para definir o rastreamento. Edite eventos e as informações sobre eles na guia Events Selection (Seleção de eventos) da caixa de diálogo Trace Properties (Propriedades de rastreamento).

   Para obter mais informações sobre os modelos de definição de rastreamento e sobre o uso do SQL Server Profiler para especificar um rastreamento no lado do cliente, consulte Database Engine Tuning Advisor (Orientador de Otimização do Mecanismo de Banco de dados) na documentação da Microsoft.

4. Inicie o rastreamento no lado do cliente e acompanhe as consultas do SQL em tempo real enquanto elas são executadas na instância de banco de dados.

5. Selecione Stop Trace (Parar rastreamento) no menu File (Arquivo) quando você tiver concluído o rastreamento. Salve os resultados como um arquivo ou como uma tabela de rastreamento na instância de banco de dados.

Execução de rastreamentos no lado do servidor em uma instância de banco de dados SQL Server

A criação de scripts para fazer um rastreamento no lado do servidor pode ser uma tarefa complexa e está além do escopo deste documento. Esta seção contém amostras de scripts que é possível usar como exemplos. Assim como em um rastreamento no lado do cliente, o objetivo é criar um arquivo de workload ou uma tabela de rastreamento que você pode abrir usando o Orientador de Otimização do Mecanismo de Banco de Dados.

Veja a seguir um script de exemplo resumido iniciado no lado do servidor e que captura detalhes de rastreamento para um arquivo de workload. Inicialmente, o rastreamento é salvo no arquivo, que fica no diretório D:\RDSDBDATA\Log RDSTrace.trc, e é renovado a cada 100 MB. Assim, os arquivos de rastreamento subsequentes são chamados RDSTrace_1.trc, RDSTrace_2.trc etc.

DECLARE @file_name NVARCHAR(245) = 'D:\RDSDBDATA\Log\RDSTrace';
DECLARE @max_file_size BIGINT = 100;
DECLARE @on BIT = 1
DECLARE @rc INT
DECLARE @traceid INT

EXEC @rc = sp_trace_create @traceid OUTPUT, 2, @file_name, @max_file_size
IF (@rc = 0) BEGIN
   EXEC sp_trace_setevent @traceid, 10, 1, @on
   EXEC sp_trace_setevent @traceid, 10, 2, @on
   EXEC sp_trace_setevent @traceid, 10, 3, @on
 . . .
   EXEC sp_trace_setfilter @traceid, 10, 0, 7, N'SQL Profiler'
   EXEC sp_trace_setstatus @traceid, 1
   END

O exemplo a seguir é um script que interrompe um rastreamento. Observe que um rastreamento criado pelo script anterior continuará a ser executado até que seja explicitamente interrompido ou até que o processo fique sem espaço em disco.

DECLARE @traceid INT
SELECT @traceid = traceid FROM ::fn_trace_getinfo(default) 
WHERE property = 5 AND value = 1 AND traceid <> 1 

IF @traceid IS NOT NULL BEGIN
   EXEC sp_trace_setstatus @traceid, 0
   EXEC sp_trace_setstatus @traceid, 2
END 

É possível salvar os resultados do rastreamento no lado do servidor em uma tabela de banco de dados e usar essa como a workload do Orientador de Otimização com a função fn_trace_gettable. Os comandos a seguir carregam os resultados de todos os arquivos chamados RDSTrace.trc no diretório D:\rdsdbdata\Log RDSTrace.trc, inclusive todos os arquivos subsequentes como RDSTrace_1.trc, em uma tabela chamada RDSTrace no banco de dados atual.

SELECT * INTO RDSTrace
FROM fn_trace_gettable('D:\rdsdbdata\Log\RDSTrace.trc', default); 

Para salvar um arquivo subsequente específico em uma tabela (por exemplo o RDSTrace_1.trc), especifique o nome do arquivo e substitua default pelo número 1 como o último parâmetro para fn_trace_gettable.

SELECT * INTO RDSTrace_1
FROM fn_trace_gettable('D:\rdsdbdata\Log\RDSTrace_1.trc', 1); 

Execução do Orientador de Otimização com um rastreamento

Após a criação de um rastreamento, seja como um arquivo local ou uma tabela de banco de dados, é possível executar Orientador de Otimização na instância de banco de dados. Usar o Orientador de Otimização com o Amazon RDS é como trabalhar com uma instância autônoma e remota do SQL Server. É possível usar a interface de usuário do Orientador de Otimização na máquina cliente ou o utilitário dta.exe na linha de comando. Em ambos os casos, ao usar o Orientador de Otimização, você deve se conectar à instância de banco de dados do Amazon RDS usando o endpoint para a instância de banco de dados e fornecer seu nome de usuário mestre e senha de usuário mestre.

O exemplo de código a seguir demonstra como usar o utilitário de linha de comando dta.exe em uma instância de banco de dados do Amazon RDS com um endpoint de dta.cnazcmklsdei.us-east-1.rds.amazonaws.com. O exemplo inclui o nome de usuário mestre admin e a senha do usuário mestre test. O banco de dados de exemplo a ser ajustado é chamado de máquina C:\RDSTrace.trc. O código de linha de comando do exemplo também especifica uma sessão de rastreamento chamada RDSTrace1 e especifica os arquivos de saída para a máquina local chamados RDSTrace.sql para o script de saída do SQL, RDSTrace.txt para um arquivo de resultado e RDSTrace.xml para um arquivo XML da análise. Há também uma tabela de erros especificada no banco de dados do RDSDTA chamada RDSTraceErrors.

dta -S dta.cnazcmklsdei.us-east-1.rds.amazonaws.com -U admin -P test -D RDSDTA -if C:\RDSTrace.trc -s RDSTrace1 -of C:\ RDSTrace.sql -or C:\ RDSTrace.txt -ox C:\ RDSTrace.xml -e RDSDTA.dbo.RDSTraceErrors 

Veja o mesmo exemplo de código de linha de comando, exceto que a workload de entrada é uma tabela na instância remota do Amazon RDS denominada RDSTrace que está no banco de dados RDSDTA.

dta -S dta.cnazcmklsdei.us-east-1.rds.amazonaws.com -U admin -P test -D RDSDTA -it RDSDTA.dbo.RDSTrace -s RDSTrace1 -of C:\ RDSTrace.sql -or C:\ RDSTrace.txt -ox C:\ RDSTrace.xml -e RDSDTA.dbo.RDSTraceErrors

Para obter uma lista completa dos parâmetros da linha de comando do utilitário dta, consulte dta Utility (Utilitário dta) na documentação da Microsoft.