29 de dezembro de 2015

Trace Flags mais comuns do SQL Server

Olá pessoal, tudo certo?

No post de hoje vou abordar um pouco sobre as trace flags mais comuns do SQL Server, o Trace Flag é uma configuração que nos permite customizar o que iremos rastrear na nossa instância.

A maioria dos trace flags não estão documentados pela Microsoft, então não temos suporte e devemos ter muito cuidado para usa-las. Então, por que alguém iria utiliza-las? Porque eles nos dá algumas possibilidades para fazer o ajustes de desempenho e diagnóstico.

Segue uma tabela com os principais trace flags, maiores informações podem ser obtidas no Technet.

Trace Flag	Descrição
260	Imprime informações de versão sobre DLLs (bibliotecas de vínculo dinâmico) de procedimento armazenado estendido. Para obter mais informações sobre __GetXpVersion(), consulte Criando procedimentos armazenados estendidos. Escopo: global ou sessão
1204	Retorna os recursos e tipos de bloqueios que participam de um deadlock e também o comando atual afetado. Escopo: somente global
1211	Desabilita o escalonamento de bloqueios com base na pressão de memória ou no número de bloqueios. A Engine de Banco de Dados do SQL Server não escalona bloqueios de linha ou de página para bloqueios de tabela. O uso desse sinalizador de rastreamento pode gerar um número excessivo de bloqueios. Isso pode reduzir o desempenho do Mecanismo de Banco de Dados ou causar erros 1204 (não é possível alocar recurso de bloqueio) por causa de memória insuficiente. Se os sinalizadores de rastreamento 1211 e 1224 forem definidos, o 1211 terá precedência sobre o 1224. Entretanto, como o sinalizador de rastreamento 1211 evita o escalonamento em todos os casos, mesmo sob pressão de memória, recomendamos usar 1224. Isso ajuda a evitar erros de "falta de bloqueios" quando muitos bloqueios são usados. Escopo: global ou sessão
1222	Retorna os recursos e os tipos de bloqueios que participam de um deadlock e também o comando atual afetado, em um formato XML que não obedece a nenhum esquema XSD. Escopo: somente global
1224*	Desabilita o escalonamento de bloqueios baseado no número de bloqueios. Entretanto, a pressão de memória ainda pode ativar o escalonamento de bloqueios. O Mecanismo de Banco de Dados escalona bloqueios de linha ou de página para bloqueios de tabela (ou partição) se a quantidade de memória utilizada pelos objetos de bloqueio exceder uma das seguintes condições: Quarenta por cento da memória usada pelo Mecanismo de Banco de Dados. Isso é aplicável somente quando o parâmetro locks de sp_configure é definido como 0. Quarenta por cento da memória de bloqueio que é configurada usando o parâmetro locks de sp_configure. Para obter mais informações, consulte Opções de configuração de servidor. Se os sinalizadores de rastreamento 1211 e 1224 forem definidos, o 1211 terá precedência sobre o 1224. Entretanto, como o sinalizador de rastreamento 1211 evita o escalonamento em todos os casos, mesmo sob pressão de memória, recomendamos usar 1224. Isso ajuda a evitar erros de "falta de bloqueios" quando muitos bloqueios são usados. Escopo: global ou sessão
1448	Permite que o leitor de log de replicação continue mesmo se os secundários assíncronos não reconhecerem a recepção de uma alteração. Mesmo com este sinalizador de rastreamento habilitado, o leitor de log sempre espera pelos secundários de sincronização. O leitor de log não passará do reconhecimento mínimo dos secundários de sincronização. Este sinalizador de rastreamento se aplica à instância do SQL Server, não apenas a um grupo de disponibilidade, um banco de dados de disponibilidade ou uma instância do leitor de log. Entra em vigor imediatamente, sem reinicialização. Este sinalizador de rastreamento pode ser ativado antecipadamente ou quando um secundário assíncrono falha.
2528	Desabilita a verificação paralela de objetos por DBCC CHECKDB, DBCC CHECKFILEGROUP e DBCC CHECKTABLE. Por padrão, o grau de paralelismo é automaticamente determinado pelo processador de consultas. O grau de máximo de paralelismo é configurado da mesma forma que as consultas paralelas. Para obter mais informações, consulte Configurar a opção de configuração de servidor max degree of parallelism. DBCC paralelo, em geral, deve permanecer habilitado. Para DBCC CHECKDB, o processador de consultas reavalia e automaticamente ajusta o paralelismo em cada tabela ou lote de tabelas verificadas. Às vezes, a verificação pode iniciar quando o servidor está quase ocioso. Um administrador que sabe que a carga aumentará antes que a verificação seja concluída poderá manualmente diminuir ou desabilitar o paralelismo. Desabilitar a verificação paralela do DBCC pode fazer com que o DBCC leve mais tempo para ser concluído e se o DBCC for executado com o recurso TABLOCK habilitado e o paralelismo definido como off, as tabelas poderão ser bloqueadas por longo tempo. Escopo: global ou sessão
3042	Ignora o algoritmo padrão de pré-alocação de compactação de backup para permitir que o arquivo de backup cresça somente quando necessário para alcançar seu tamanho final. Este sinalizador de rastreamento será útil se você precisar salvar em espaço alocando somente o tamanho real necessário para o backup compactado. Usar este sinalizador de rastreamento pode causar uma pequena penalidade de desempenho (um possível aumento na duração da operação de backup). Para obter mais informações sobre o algoritmo de pré-alocação, consulte Compactação de backup (SQL Server).
3205	Por padrão, se uma unidade de fita oferecer suporte à compactação de hardware, a instrução DUMP ou BACKUP a usará. Com esse sinalizador de rastreamento, é possível desabilitar a compactação de hardware para drivers de fita. Isso é útil quando se deseja trocar as fitas por outros locais ou unidades de fita que não oferecem suporte à compactação. Escopo: global ou sessão
3226	Por padrão, toda operação de backup bem-sucedida acrescenta uma entrada ao log de erros do SQL Server e ao log de eventos do sistema. Se você criar backups de log com frequência, essas mensagens se acumularão rapidamente, resultando em enormes logs de erros nos quais será difícil localizar outras mensagens. Com este sinalizador de rastreamento, você pode suprimir estas entradas de log. Isso é útil se você estiver executando backups de log frequentes e se nenhum dos seus scripts depender dessas entradas.
3608	Impede que o SQL Server seja iniciado automaticamente e recupere qualquer banco de dados, exceto o banco de dados mestre. Se as atividades que requerem tempdb forem iniciadas, o modelo será recuperado e tempdb será criado. Os bancos de dados do usuário serão iniciados e recuperados quando acessados. Alguns recursos, como isolamento de instantâneo e instantâneo de leitura confirmada, talvez não funcionem. Use para Mover bancos de dados do sistema e Mover bancos de dados de usuário. Não use durante operação normal.
3625	Limita a quantidade de informações retornadas aos usuários que não são membros da função de servidor fixa sysadmin, mascarando os parâmetros de algumas mensagens de erro usando '******'. Isso pode ajudar a evitar a divulgação de informações confidenciais. Escopo: somente global
4199	Controla várias alterações do otimizador de consulta feitas anteriormente em vários sinalizadores de rastreamento. Para obter mais informações, consulte este artigo do Suporte da Microsoft Escopo: global ou sessão
4616	Torna os metadados em nível de servidor visíveis para funções de aplicativo. No SQL Server, uma função de aplicativo não pode acessar metadados fora de seu próprio banco de dados porque as funções de aplicativo não são associadas a um principal em nível de servidor. É uma alteração de comportamento de versões anteriores do SQL Server. Definir esse sinalizador global desabilita as novas restrições e permite que funções de aplicativo acessem metadados em nível de servidor. Escopo: somente global
6527	Desabilita a geração de um despejo de memória na primeira ocorrência de uma exceção de memória insuficiente na integração de CLR. Por padrão, o SQL Server gera um despejo de memória pequeno na primeira ocorrência de uma exceção de memória insuficiente no CLR. O comportamento do sinalizador de rastreamento é: Se isso for usado como um sinalizador de rastreamento de inicialização, um despejo de memória nunca será gerado. No entanto, um despejo de memória poderá ser gerado se forem usados outros sinalizadores de rastreamento. Se esse sinalizador de rastreamento estiver habilitado em um servidor em execução, um despejo de memória não será gerado automaticamente a partir desse ponto. No entanto, se um despejo de memória já tiver sido gerado devido a uma exceção de memória insuficiente no CLR, este sinalizador de rastreamento não terá nenhum efeito. Escopo: somente global
7806	Habilita uma conexão de administrador dedicada (DAC) no SQL Server Express. Por padrão, nenhum recurso DAC é reservado no SQL Server Express. Para obter mais informações, consulte Conexão de diagnóstico para administradores de banco de dados. Escopo: somente global
8032*	Reverte os parâmetros de limite de cache para a configuração do SQL Server 2005 RTM, que, em geral, permite que os caches sejam maiores. Use esta configuração quando entradas de cache reutilizadas com frequência não se ajustarem no cache e quando o Opção de configuração de servidor optimize for ad hoc workloads não tiver resolvido o problema com o cache do plano.
8207	Habilita atualizações singleton para replicação transacional. As atualizações nos assinantes podem ser replicadas como um par de DELETE e INSERT. Isso pode não atender a regras de negócio, como acionar um gatilho UPDATE. Com o sinalizador de rastreamento 8207, uma atualização em uma coluna exclusiva que afeta apenas uma linha (uma atualização singleton) é replicada como um UPDATE e não como um par DELETE ou INSERT. Se a atualização afetar uma coluna com uma restrição exclusiva ou se a atualização afetar várias linhas, a atualização ainda será replicada como um par DELETE ou INSERT.
9485	Desabilita a permissão SELECT para DBCC SHOW_STATISTICS.

* Trace flag 1224 – O escalonamento de bloqueios para a granularidade no nível de tabela ou HoBT também pode ser controlado usando a opção LOCK_ESCALATION da instrução ALTER TABLE.
* Trace flag 8032 – O sinalizador de rastreamento 8032 pode levar a um baixo desempenho se caches grandes disponibilizam menos memória para outros consumidores de memória, como o pool de buffers.

No SQL Server, há dois tipos de trace flags: sessão e global. Os trace flags de sessão são ativos para uma conexão. Enquanto os trace flags globais são definidos no nível de instância e são visíveis em todas as conexões no servidor.

As seguintes regras se aplicam:

O trace flag global deve ser habilitado na inicialização da instância, usando a opção de linha de comando -T.

O trace flag de sessão, ele poderá ser ativado na sessão aberta, um trace flag habilitado no nível de sessão nunca afeta outra sessão, e seu efeito se perde quando a SPID que abriu a sessão faz logoff.

Habilitando um Trace Flag

Você pode habilitar um trace flag utilizando o comando DBCC TRACEON ou então adiciona-lo como parâmetro de inicialização utilizando o parâmetro -T.

Ativando o Trace Flag com o comando DBCC TRACEON

Para habilitar um trace flag em nível de sessão (por exemplo trace flag 2528) você digita:

DBCC TRACEON (2528)

Se você em vez disso quiser habilitar um trace flag em nível de instância, você deve adicionar um -1 como um parâmetro.

DBCC TRACEON (2528, -1)

Usando parâmetros de inicialização

Como já foi dito alguns trace flags funcionam apenas a nível de sessão e outros apenas no nível de instância. Alguns devem ser definidos como parâmetros de inicialização do SQL Server. Isso pode ser feito usando o SQL Server Configuration Manager. Clique duas vezes no serviço do SQL Server (ou clique com o botão direito e escolha Propriedades)