Como gerar um script de estatísticas para criar um banco de dados somente estatísticas no SQL Server
Neste artigo, você aprenderá a gerar um script de estatísticas usando metadados de banco de dados para criar um banco de dados somente estatísticas em SQL Server.
Versão original do produto: SQL Server 2014, SQL Server 2012, SQL Server 2008
Número de KB original: 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 você não puder usar DBCC CLONEDATABASE.
O otimizador de consulta no Microsoft SQL Server usa os seguintes tipos de informações para determinar um plano de consulta ideal:
- metadados de 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.
Os Serviços de Suporte ao Cliente da Microsoft podem pedir que você gere um script dos metadados de banco de dados para investigar um problema de 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 dentro destes 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.
Script de todo o banco de dados
Quando você gera um banco de dados clone somente estatísticas, pode ser mais fácil e confiável fazer scripts de todo o banco de dados em vez de criar scripts de objetos individuais. Ao escrever o script de todo o banco de dados, você receberá os seguintes benefícios:
- Você evita problemas com objetos dependentes ausentes que são necessários para reproduzir o problema.
- Você requer 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 contiver milhares de objetos, o processo de script consumirá recursos significativos da CPU. É recomendável que você gere o script durante o horário de pico ou use a segunda opção Script Individual Objects para gerar o script para objetos individuais.
Para script 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 rotear.
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 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 seguintes configurações do valor padrão para o valor listado na tabela a seguir.
Opção de script Valor a ser selecionado Preenchimento ansi Verdadeiro Continuar scripting no erro Verdadeiro Gerar script para objetos dependentes Verdadeiro Incluir nomes de restrição do sistema Verdadeiro Ordenação de Script Verdadeiro Logons de script Verdadeiro Permissões de nível de objeto script Verdadeiro Estatísticas de Script Estatísticas de script e histogramas Índices de script Verdadeiro Gatilhos de script Verdadeiro Observação
Observe que a opção Logons de Script e a opção Script Object Level Permissions podem não ser necessárias, a menos que o esquema contenha objetos que pertencem a logons diferentes do dbo.
Selecione OK para salvar as alterações e feche a página Opções avançadas de script .
Selecione Salvar no Arquivo e selecione a opção Arquivo único .
Examine suas seleções e selecione Avançar.
Selecione Concluir.
Script de objetos individuais
Você só pode script os objetos individuais que são referenciados por uma consulta específica em vez de script do 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 roteia um objeto dependente.
O processo de script pode rotear objetos na ordem incorreta. Para executar o script com êxito, você deve editar manualmente o script gerado.
Portanto, não é recomendável que você faça script de objetos individuais, a menos que o banco de dados tenha muitos objetos e scripts de outra forma 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 rotear.
Clique com o botão direito do mouse no banco de dados, aponte para Script Database As e, em seguida, aponte para CREATE To e selecione Arquivo.
Insira um nome de arquivo e selecione Salvar.
O contêiner de banco de dados principal será roteado. Esse contêiner inclui arquivos, grupos de arquivos, 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 que a consulta problemática referencia.
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.
Quando você tiver selecionado 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 seguintes configurações do valor padrão para o valor listado na tabela a seguir na página Opções avançadas de script .
Opção de script Valor a ser selecionado Ansi Padding Verdadeiro Continuar scripting no erro Verdadeiro Incluir nomes de restrição do sistema Verdadeiro Gerar script para objetos dependentes Verdadeiro Ordenação de Script Verdadeiro Logons de script Verdadeiro Permissões de nível de objeto script Verdadeiro Estatísticas de Script Estatísticas de script e histogramas 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 do 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 que você inseriu na etapa 3.
Selecione Concluir para iniciar o script.
Quando o script for concluído, envie o arquivo de script para o engenheiro 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 que existe.
Opções SET que estavam ativas quando você executou a consulta.
Observe que você pode já 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.
Metadata
| Opção | Explicação |
|---|---|
| Restrições | O otimizador de consulta frequentemente 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 uma CHECK (col < 5) restrição existir na tabela subjacente, o otimizador de consulta saberá que nenhuma linha corresponderá. O otimizador de consulta faz tipos semelhantes de deduções sobre a nulidade. Por exemplo, a WHERE col IS NULL cláusula é conhecida por ser 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 para 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 líder da chave de índice e estatística. Dependendo da natureza do predicado, o otimizador de consulta pode usar densidade, 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 para estimar o 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 de cardinalidade final é 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 para estimar o 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 que estão 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 pesquise correlações entre colunas. Essa configuração afeta a cardinalidade e a estimativa de custos. |
Ambiente
| Opção | Explicação |
|---|---|
| Opções set de sessão | A ANSI_NULLS configuração afeta se a NULL = NULL expressão é avaliada como true. 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 ser alteradas. Por exemplo, a col = NULL expressão é avaliada de forma diferente com base na configuração. No entanto, a col IS NULL expressão sempre é avaliada da mesma maneira. |
| Recursos de hardware | O custo para operadores de classificação e hash depende da quantidade relativa de memória disponível para SQL Server. Por exemplo, se o tamanho dos dados for maior que o cache, o otimizador de consulta saberá que os dados devem ser sempre carregados em disco. No entanto, se o tamanho dos dados for muito menor que o cache, é provável que a operação seja feita na memória. SQL Server também considerará 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 o grau máximo de opção de configuração de paralelismo. |
Confira também
Comentários
Em breve: Ao longo de 2024, eliminaremos os problemas do GitHub como o mecanismo de comentários para conteúdo e o substituiremos por um novo sistema de comentários. Para obter mais informações, consulte https://aka.ms/ContentUserFeedback.
Enviar e exibir comentários de