Observação
O acesso a essa página exige autorização. Você pode tentar entrar ou alterar diretórios.
O acesso a essa página exige autorização. Você pode tentar alterar os diretórios.
Neste artigo, você aprenderá a gerar um script de estatísticas usando metadados de banco de dados para criar um banco de dados somente de estatísticas no SQL Server.
Versão original do produto: SQL Server
Número original do KB: 914288
Introdução
O DBCC CLONEDATABASE é o método preferencial para gerar um clone somente de esquema de um banco de dados para investigar problemas de desempenho. Use o procedimento neste artigo somente quando não for possível usar DBCC CLONEDATABASEo .
O otimizador de consulta no Microsoft SQL Server usa os seguintes tipos de informações para determinar um plano de consulta ideal:
- metadados do banco de dados
- Ambiente de hardware
- estado da sessão do banco de dados
Normalmente, você deve simular todos esses mesmos tipos de informações para reproduzir o comportamento do otimizador de consulta em um sistema de teste.
O Atendimento ao Cliente da Microsoft pode solicitar que você gere um script dos metadados do banco de dados para investigar um problema do otimizador de consulta. Este artigo descreve as etapas para gerar o script de estatísticas e também descreve como o otimizador de consulta usa as informações.
Observação
As chaves salvas nesses dados podem conter informações de PII. Por exemplo, se sua tabela contiver uma coluna Número de telefone com uma estatística, o valor de chave alta de cada etapa estará no script de estatísticas gerado.
Crie um script de todo o banco de dados
Quando você gera um banco de dados clone somente de estatísticas, pode ser mais fácil e confiável criar scripts de todo o banco de dados em vez de criar scripts de objetos individuais. Ao criar o script de todo o banco de dados, você recebe os seguintes benefícios:
- Você evita problemas com objetos dependentes ausentes que são necessários para reproduzir o problema.
- Você precisa de menos etapas para selecionar os objetos necessários.
Observe que, se você gerar um script para um banco de dados e os metadados do banco de dados contiverem milhares de objetos, o processo de script consumirá recursos significativos da CPU. É recomendável que você gere o script fora do horário de pico ou use a segunda opção Script de Objetos Individuais para gerar o script para objetos individuais.
Para criar scripts de cada banco de dados referenciado por sua consulta, siga estas etapas:
Abra o SQL Server Management Studio.
No Pesquisador de Objetos, expanda Bancos de Dados e localize o banco de dados que você deseja criar script.
Clique com o botão direito do mouse no banco de dados, aponte para Tarefas e selecione Gerar Scripts.
No assistente de script, verifique se o banco de dados correto está selecionado. Clique para selecionar o banco de dados inteiro do Script e todos os objetos de banco de dados e selecione Avançar.
Na caixa de diálogo Escolher Opções de Script, selecione o botão Avançado para alterar as configurações a seguir do valor padrão para o valor listado na tabela a seguir.
Opção de script Valor a ser selecionado Preenchimento Ansi Verdadeiro Continuar o script em caso de erro Verdadeiro Gerar script para objetos dependentes Verdadeiro Incluir nomes de restrição do sistema Verdadeiro Agrupamento de script Verdadeiro Logons de script Verdadeiro Permissões de nível de objeto de script Verdadeiro Estatísticas do script Estatísticas e histogramas de script Índices de script Verdadeiro Gatilhos de script Verdadeiro Observação
Observe que a opção Scripts de Logons e a opção Script Object Level Permissions podem não ser necessárias, a menos que o esquema contenha objetos que pertencem a logins diferentes de dbo.
Selecione OK para salvar as alterações e feche a página Opções avançadas de script.
Selecione Salvar em arquivo e selecione a opção Arquivo único.
Revise suas seleções e selecione Avançar.
Selecione Concluir.
Script de objetos individuais
Você só pode criar scripts para os objetos individuais que são referenciados por uma consulta específica, em vez de criar scripts para o banco de dados completo. No entanto, a menos que todos os objetos de banco de dados tenham sido criados usando a WITH SCHEMABINDING cláusula, as informações de dependência na tabela do sys.depends sistema podem nem sempre ser precisas. Essa imprecisão pode causar um dos seguintes problemas:
O processo de script não cria script de um objeto dependente.
O processo de script pode criar scripts de objetos na ordem incorreta. Para executar o script com êxito, você deve editar manualmente o script gerado.
Portanto, não é recomendável criar scripts de objetos individuais, a menos que o banco de dados tenha muitos objetos e, caso contrário, o script levaria muito tempo. Se você precisar usar objetos individuais de script, siga estas etapas:
No SQL Server Management Studio, expanda Bancos de Dados e localize o banco de dados que você deseja criar script.
Clique com o botão direito do mouse no banco de dados, aponte para Script de Banco de Dados Como, aponte para CREATE Para e selecione Arquivo.
Insira um nome de arquivo e selecione Salvar.
O contêiner do banco de dados principal será roteirizado. Esse contêiner inclui arquivos, grupos de arquivos, o banco de dados e propriedades.
Clique com o botão direito do mouse no banco de dados, aponte para Tarefas e selecione Gerar Scripts.
Verifique se o banco de dados correto está selecionado e selecione Avançar.
Na caixa de diálogo Escolher tipos de objeto, escolha Selecionar objetos de banco de dados específicos e selecione todos os tipos de objeto de banco de dados aos quais a consulta problemática faz referência.
Por exemplo, se a consulta fizer referência apenas a tabelas, selecione Tabelas. Se a consulta fizer referência a uma exibição, selecione Exibições e Tabelas. Se a consulta problemática usar uma função definida pelo usuário, selecione Funções.
Depois de selecionar todos os tipos de objeto referenciados pela consulta, selecione Avançar.
Na caixa de diálogo Definir Opções de Script, selecione o botão Avançado e altere as configurações a seguir do valor padrão para o valor listado na tabela a seguir na página Opções de Script Avançadas.
Opção de script Valor a ser selecionado Preenchimento Ansi Verdadeiro Continuar o script em caso de erro Verdadeiro Incluir nomes de restrição do sistema Verdadeiro Gerar script para objetos dependentes Verdadeiro Agrupamento de script Verdadeiro Acessos de script Verdadeiro Permissões de nível de objeto de script Verdadeiro Estatísticas do script Estatísticas e histogramas de script Script USE DATABASE Verdadeiro Índices de script Verdadeiro Gatilhos de script Verdadeiro Observação
Observe que as opções Logons de script e Permissões de nível de objeto de script podem não ser necessárias, a menos que o esquema contenha objetos que pertencem a logons diferentes de dbo.
Selecione OK para salvar e fechar a página Opções avançadas de script.
Uma caixa de diálogo é exibida para cada tipo de objeto de banco de dados selecionado na etapa 7.
Em cada caixa de diálogo, selecione as tabelas, exibições, funções ou outros objetos de banco de dados específicos e selecione Avançar.
Selecione a opção Script para arquivo e especifique o mesmo nome de arquivo inserido na etapa 3.
Selecione Concluir para iniciar o script.
Quando o script for concluído, envie o arquivo de script para o Engenheiro de Suporte da Microsoft. O Engenheiro de Suporte da Microsoft também pode solicitar as seguintes informações:
Configuração de hardware, incluindo o número de processadores e a quantidade de memória física existente.
SET que estavam ativas quando você executou a consulta.
Observe que você já pode ter fornecido essas informações enviando um relatório SQLDiag ou um rastreamento do SQL Profiler. Você também pode ter usado outro método para fornecer essas informações.
Como as informações são usadas
As tabelas a seguir ajudam a explicar como o otimizador de consulta usa essas informações para selecionar um plano de consulta.
Metadados
| Opção | Explicação |
|---|---|
| Restrições | O otimizador de consulta freqüentemente usa restrições para detectar contradições entre a consulta e o esquema subjacente. Por exemplo, se a consulta contiver a WHERE col = 5 cláusula e existir uma CHECK (col < 5) restrição na tabela subjacente, o otimizador de consulta saberá que nenhuma linha corresponderá. O otimizador de consulta faz tipos semelhantes de deduções sobre nulidade. Por exemplo, a WHERE col IS NULL cláusula é conhecida como verdadeira ou falsa, dependendo da nulidade da coluna e se a coluna é da tabela externa de uma junção externa. A presença de restrições FOREIGN KEY é útil para determinar a cardinalidade e a ordem de junção apropriada. O otimizador de consulta pode usar informações de restrição para eliminar junções ou simplificar predicados. Essas alterações podem remover o requisito de acessar as tabelas base. |
| Estatísticas | As informações de estatísticas contêm densidade e um histograma que mostra a distribuição da coluna inicial do índice e da chave de estatísticas. Dependendo da natureza do predicado, o otimizador de consulta pode usar a densidade, o histograma ou ambos para estimar a cardinalidade de um predicado. Estatísticas atualizadas são necessárias para estimativas precisas de cardinalidade. As estimativas de cardinalidade são usadas como uma entrada na estimativa do custo de um operador. Portanto, você deve ter boas estimativas de cardinalidade para obter planos de consulta ideais. |
| Tamanho da tabela (número de linhas e páginas) | O otimizador de consulta usa os histogramas e a densidade para calcular a probabilidade de que um determinado predicado seja verdadeiro ou falso. A estimativa final de cardinalidade é calculada multiplicando a probabilidade pelo número de linhas que o operador filho retorna. O número de páginas na tabela ou no índice é um fator na estimativa do custo de E/S. O tamanho da tabela é usado para calcular o custo de uma verificação e é útil quando você estima o número de páginas que serão acessadas durante uma busca de índice. |
| Opções de banco de dados | Várias opções de banco de dados podem afetar a otimização. As AUTO_CREATE_STATISTICS opções e AUTO_UPDATE_STATISTICS afetam se o otimizador de consulta criará novas estatísticas ou atualizará estatísticas desatualizadas. O nível de parametrização afeta como a consulta de entrada é parametrizada antes que a consulta de entrada seja entregue ao otimizador de consulta. A parametrização pode afetar a estimativa de cardinalidade e também pode impedir a correspondência com exibições indexadas e outros tipos de otimizações. A DATE_CORRELATION_OPTIMIZATION configuração faz com que o otimizador procure correlações entre colunas. Essa configuração afeta a cardinalidade e a estimativa de custo. |
Ambiente
| Opção | Explicação |
|---|---|
| Opções de SET de sessão | A ANSI_NULLS configuração afeta se a expressão é avaliada NULL = NULL como verdadeira. A estimativa de cardinalidade para junções externas pode mudar dependendo da configuração atual. Além disso, expressões ambíguas também podem mudar. Por exemplo, a col = NULL expressão é avaliada de forma diferente com base na configuração. No entanto, a expressão sempre é avaliada col IS NULL da mesma maneira. |
| Recursos de hardware | O custo dos operadores de classificação e hash depende da quantidade relativa de memória disponível para o SQL Server. Por exemplo, se o tamanho dos dados for maior que o cache, o otimizador de consulta saberá que os dados sempre devem ser colocados em spool no disco. No entanto, se o tamanho dos dados for muito menor que o cache, a operação provavelmente será feita na memória. O SQL Server também considera otimizações diferentes se o servidor tiver mais de um processador e se o paralelismo não tiver sido desabilitado usando uma MAXDOP dica ou a opção de configuração de grau máximo de paralelismo. |