Início / dba / Perguntas / 187342
Accepted
Erik Darling
Asked: 2017-10-01 08:40:04 +0800 CST

Existe uma maneira de impedir que UDFs escalares em colunas computadas inibam o paralelismo?

  • 772

Muito foi escrito sobre os perigos das UDFs escalares no SQL Server. Uma pesquisa casual retornará muitos resultados.

Existem alguns lugares onde uma UDF escalar é a única opção.

Como exemplo: ao lidar com XML: XQuery não pode ser usado como definição de coluna computada. Uma opção documentada pela Microsoft é usar uma UDF escalar para encapsular sua XQuery em uma UDF escalar e, em seguida, usá-la em uma coluna computada.

Isso tem vários efeitos e algumas soluções alternativas.

  • Executa linha por linha quando a tabela é consultada
  • Força todas as consultas na tabela a serem executadas em série

Você pode contornar a execução linha por linha vinculando a função ao esquema e persistindo a coluna computada ou indexando-a. Nenhum desses métodos pode impedir a serialização forçada de consultas que atingem a tabela, mesmo quando a UDF escalar não é referenciada.

Existe uma maneira conhecida de fazer isso?

sql-server functions

2 respostas

  1. Best Answer

    Sim se você:

    • estão executando o SQL Server 2014 ou posterior; e
    • são capazes de executar a consulta com o sinalizador de rastreamento 176 ativo; e
    • a coluna calculada éPERSISTED

    Especificamente, são necessárias pelo menos as seguintes versões :

    • Atualização cumulativa 2 para SQL Server 2016 SP1
    • Atualização cumulativa 4 para SQL Server 2016 RTM
    • Atualização cumulativa 6 para SQL Server 2014 SP2

    MAS para evitar um bug (ref para 2014 e para 2016 e 2017 ) introduzido nessas correções, aplique:

    O sinalizador de rastreamento é eficaz como uma opção de inicialização –T, no escopo global e de sessão usando DBCC TRACEONe por consulta com OPTION (QUERYTRACEON)ou um guia de plano.

    O sinalizador de rastreamento 176 impede a expansão de coluna computada persistente.

    A carga inicial de metadados realizada ao compilar uma consulta traz todas as colunas, não apenas aquelas diretamente referenciadas. Isso torna todas as definições de coluna computadas disponíveis para correspondência, o que geralmente é uma coisa boa.

    Como um efeito colateral infeliz, se uma das colunas carregadas (computadas) usa uma função escalar definida pelo usuário, sua presença desabilita o paralelismo para toda a consulta, mesmo quando a coluna computada não é realmente usada .

    O sinalizador de rastreamento 176 ajuda nisso, se a coluna persistir, não carregando a definição (já que a expansão é ignorada). Dessa forma, uma função escalar definida pelo usuário nunca está presente na árvore de consulta de compilação, portanto, o paralelismo não é desabilitado.

    A principal desvantagem do sinalizador de rastreamento 176 (além de ser apenas levemente documentado) é que ele também impede que a expressão de consulta corresponda a colunas computadas persistentes: Se a consulta contiver uma expressão que corresponda a uma coluna computada persistente, o sinalizador de rastreamento 176 impedirá que a expressão seja substituída por uma referência à coluna calculada.

    Para obter mais detalhes, consulte meu artigo SQLPerformance.com, Colunas computadas adequadamente persistentes .

    Como a pergunta menciona XML, como uma alternativa para promover valores usando uma coluna computada e uma função escalar, você também pode usar um Índice XML seletivo, como escreveu em Índices XML seletivos: nada mal .

    • 35

  2. Além do excelente Yes #1 de @Paul, na verdade existe um Yes #2 que:

    • funciona desde o SQL Server 2005,
    • não requer a configuração de um sinalizador de rastreamento,
    • não requer que a coluna computada seja ,PERSISTED e
    • (devido a não exigir o sinalizador de rastreamento 176), não impede que a expressão de consulta corresponda às colunas computadas persistentes

    As únicas desvantagens (até onde eu sei) são:

    • não funciona no Banco de Dados SQL do Azure (pelo menos ainda não, embora funcione no Amazon RDS SQL Server e no SQL Server no Linux) e
    • está um pouco fora da zona de conforto de muitos DBAs

    E esta opção é: SQLCLR

    Isso mesmo. Um aspecto interessante das UDFs escalares SQLCLR é que, se elas não fazem nenhum acesso a dados (nem usuário nem sistema), elas não proíbem o paralelismo. E isso não é apenas teoria ou marketing. Embora eu não tenha tempo (no momento) para fazer a redação totalmente detalhada, testei e comprovei isso.

    Eu usei a configuração inicial da seguinte postagem no blog (espero que o OP não considere isso uma fonte não confiável ?):

    Bad Idea Jeans: várias dicas de índice

    E realizou os seguintes testes:

    1. Executou a consulta inicial como está ─⇾ Paralelismo (como esperado)
    2. Adicionada uma coluna computada não persistente definida como ([c2] * [c3])─⇾ Paralelismo (como esperado)
    3. Removida essa coluna computada e adicionada uma coluna computada não persistente que fazia referência a uma UDF escalar T-SQL (criada com SCHEMABINDING) definida como RETURN (@First * @Second);─⇾ NO Parallelism (conforme esperado)
    4. Removida a coluna computada T-SQL UDF e adicionada uma coluna computada não persistente que fazia referência a uma UDF escalar SQLCLR (tentada com IsDeterministic = truee = false) definida como return SqlInt32.Multiply(First, Second);─⇾ Paralelismo (woo hoo!!)

    Portanto, embora o SQLCLR não funcione para todos, certamente tem suas vantagens para aquelas pessoas/situações/ambientes que se encaixam bem. E, no que se refere a essa questão específica - o exemplo dado diz respeito ao uso de XQuery - certamente funcionaria para isso (e, dependendo do que especificamente está sendo feito, pode até ser um pouco mais rápido ? ).

    • 12

relate perguntas