Isso pode se enquadrar na categoria de opinião, mas estou curioso para saber se as pessoas estão usando o sinalizador de rastreamento 4199 como um parâmetro de inicialização para o SQL Server. Para aqueles que o usaram, em que circunstâncias você experimentou regressão de consulta?
Certamente parece um benefício de desempenho potencial em geral, estou pensando em habilitá-lo globalmente em nosso ambiente de não produção e deixá-lo descansar por alguns meses para descobrir quaisquer problemas.
As correções em 4199 são lançadas no otimizador por padrão em 2014 (ou 2016)? Embora eu entenda o caso de não introduzir alterações inesperadas no plano, parece estranho manter todas essas correções ocultas entre as versões.
Estamos usando 2008, 2008R2 e principalmente 2012.
Pessoalmente, sempre que construo um novo servidor para um novo projeto, sempre habilito o TF4199 globalmente. O mesmo se aplica quando eu atualizo instâncias existentes para versões mais recentes.
O TF permite novas correções que afetariam o comportamento do aplicativo, mas para novos projetos o risco de regressão não é um problema. Para instâncias atualizadas de versões anteriores, as diferenças entre a versão antiga e a nova são uma preocupação por conta própria e ter que lidar com a regressão do plano é esperada de qualquer maneira, então prefiro lutar com ela com o TF4199 ativado.
No que diz respeito aos bancos de dados existentes, só há uma maneira de saber: testá-lo. Você pode capturar uma carga de trabalho na configuração existente e reproduzi-la depois de habilitar o sinalizador. O RML Utilities pode ajudá-lo a automatizar o processo, conforme descrito nesta resposta .
Obviamente, o sinalizador afeta toda a instância, então você terá que testar todos os bancos de dados que estão ali.
Minha pesquisa sobre o assunto me trouxe até aqui, então gostaria apenas de compartilhar minha experiência recente sobre o assunto.
Eu estava executando o SQL 2014, então imaginei que estaria seguro de ter que me preocupar com 4199 por um tempo... mas simplesmente não era verdade...
Como Diagnosticar se você precisa do 4199
Se sua consulta parecer mal executada , especialmente quando você acha que não deveria, tente adicionar o seguinte ao final dela para ver se corrige todos os seus problemas, pois você pode precisar de 4199 ("Habilitar todas as correções do Otimizador de Consultas." )
Na minha situação, eu tinha uma cláusula top 10 explodindo uma consulta que funcionava bem sem, o que me fez pensar que algo suspeito estava acontecendo e que 4199 poderia ajudar.
Cerca de 4199
Quaisquer correções de bug/desempenho do SQL Server Query Optimizer criadas após o lançamento da nova versão principal, na verdade, ficam ocultas e bloqueadas. Isso é para o caso de eles poderem realmente prejudicar algum outro programa teoricamente perfeitamente otimizado. Portanto, instale as atualizações como você pode, as alterações reais do otimizador de consulta não são habilitadas por padrão. Portanto, uma vez que uma única correção ou aprimoramento foi feito, o 4199 se torna uma necessidade se você quiser aproveitá-lo. À medida que muitas correções aparecem, você acabará ativando isso quando uma delas o afetar. Essas correções geralmente estão vinculadas a seus próprios sinalizadores de rastreamento, mas 4199 é usado como o mestre "Ativar todas as correções".
Se você souber quais correções precisa, poderá habilitá-las aos poucos em vez de usar 4199. Se quiser habilitar todas as correções, use 4199.
Ok, então você quer 4199 globalmente...
Basta criar um trabalho do SQL Agent que é executado todas as manhãs com a seguinte linha para habilitar o sinalizador de rastreamento globalmente. Isso garante que, se alguém os desligou ou etc, eles serão ligados novamente. Esta etapa do trabalho tem um sql bastante simples:
Onde -1 especifica a parte global em DBCC TRACEON. Para mais informações consulte:
https://msdn.microsoft.com/en-us/library/ms187329.aspx?f=255&MSPPError=-2147217396
Planos de consulta "recompilando"
Na minha tentativa mais recente, tive que habilitar 4199 globalmente e também remover os planos de consulta em cache existentes :
https://msdn.microsoft.com/en-us/library/ms181647.aspx?f=255&MSPPError=-2147217396
Onde o procedimento armazenado de recompilação localiza quaisquer planos de consulta relacionados ao objeto de banco de dados (como uma tabela) e exclui esses planos de consulta, exigindo a próxima tentativa de executar uma consulta semelhante para compilá-los.
Portanto, no meu caso, o 4199 evitou que os planos de consulta inválidos fossem criados, mas também tive que remover aqueles que ainda estavam armazenados em cache via sp_recompile. Escolha qualquer tabela da consulta conhecida afetada e você deve tentar essa consulta novamente, supondo que agora tenha ativado 4199 globalmente e limpou o plano de consulta em cache ofensivo.
Em conclusão em 4199
À medida que você utiliza índices, uma otimização de plano de consulta inteligente torna-se importante para realmente usar esses índices de maneira inteligente e, supondo que, com o tempo, alguma correção para o processo de otimização de consulta seja lançada, geralmente você está em águas seguras para executar apenas com 4199 ativado globalmente, contanto que você perceba que alguma nova correção pode não funcionar tão bem com um banco de dados altamente otimizado que foi tão otimizado no ambiente anterior antes dessa correção. Mas o que 4199 faz? Apenas permite todas as correções.
Eu queria compartilhar minha experiência com o sinalizador de rastreamento 4199.
Acabei de diagnosticar um problema de desempenho em um sistema de cliente executando o SQL Server 2012 SP3. O cliente estava transferindo um banco de dados de relatórios de seu servidor OLTP de produção para um novo servidor. O objetivo do cliente era eliminar a competição por recursos com as consultas OLTP. Infelizmente, o cliente disse que o novo servidor de relatórios estava muito lento.
Uma consulta de amostra executada no sistema OLTP foi concluída em 1,6 segundos. O plano de consulta fez uma busca de índice em uma tabela de aproximadamente 200 milhões de linhas que fazia parte de uma exibição.
No novo servidor, a mesma consulta foi concluída em 10 minutos e 44 segundos. Ele executou uma varredura de índice na mesma tabela de aproximadamente 200 milhões de linhas.
Os dados do servidor de relatórios eram uma cópia dos dados OLTP, portanto, não parecia haver diferença nos dados.
Fiquei perplexo até lembrar que nosso software (que executa o sistema OLTP) habilitou alguns sinalizadores de rastreamento na inicialização. Um deles, 4199, lembrei-me, era uma correção do otimizador de consulta.
Testei a ativação do sinalizador de rastreamento 4199 no novo servidor de relatórios do cliente e a consulta de relatórios foi concluída em 0,6 segundos. (Uau!) Desativei o sinalizador de rastreamento e a consulta voltou a ser concluída em 10 minutos e 44 segundos. Ativado o sinalizador: volta para 0,6 segundos. Aparentemente, ativar o sinalizador de rastreamento permitiu que o otimizador usasse uma busca de índice na visualização da tabela de 200 milhões de linhas.
Nesse caso, as otimizações de consulta habilitadas pelo sinalizador de rastreamento 4199 fizeram uma enorme diferença. Suas experiências podem variar. No entanto, com base nessa experiência, definitivamente vale a pena habilitar para mim.