Instrução SELECT com * na lista de colunas é mais rápida que a mesma instrução sem *

Situação

Ao consultar um banco de dados com uma instrução SELECT com um conjunto definido de colunas, os resultados são recebidos em cerca de 21 segundos.

Se houver um asterisco adicional ( , *) no final da lista de conjunto de colunas definido, a consulta retornará os resultados em 2 segundos.

Planos de Execução de Consultas

Os planos de execução diferem significativamente.

Você pode encontrar o plano de execução de consulta real bom e o plano de execução de consulta real ruim com os links do PasteThePlan.

Instrução contendo , * na lista de colunas (no final)

            SELECT    -- DISTINCT -- 27.04.2020
                'SchuelerKlasse' AS EcoQuery,
                VX_PERSON.PER_MAN_ID, VX_PERSON.PER_ID, VX_PERSON.PER_NAME, VX_PERSON.PER_VORNAME, VX_PERSON.PER_LB_PER_ID, 
                VX_PERSON.PER_GESCHLECHT, VX_PERSON.PER_GEBURTSDATUM, VX_PERSON.PER_TELP, VX_PERSON.PER_MAILP, VX_PERSON.PER_NATP, VX_PERSON.PER_VERSICHERTENNUMMER, VX_PERSON.PER_LAND,
                VX_ADRESSE.ADR_STRASSE, VX_ADRESSE.ADR_PLZ, VX_ADRESSE.ADR_ORT,
                VX_KLASSE.KL_CODE, VX_KLASSE.KL_BEZEICHNUNG,
                VX_KLASSEABSCHNITTSCHUELER.KAS_ANMELDE_STATUS, 
                VX_KLASSEABSCHNITTSCHUELER.KAS_ANMELDETYP, VX_KLASSEABSCHNITTSCHUELER.KAS_ABSCHNITTSNR,
                VX_KLASSE_ZEITRAUM.KLZ_IS_ABSCHLUSSKLASSE, VX_KLASSE_ZEITRAUM.KLZ_ZR_NR,
                VX_ZEITRAUM.ZR_BEGINN, VX_ZEITRAUM.ZR_ENDE
                ,'' AS FA_CODE
                ,'' AS FA_BEZ_STP, '' AS FA_BEZ_STP_LANG
                , '' AS EcoOrig_FA_CODE, '' AS EcoOrig_FA_BEZ_STP, '' AS EcoOrig_FA_BEZ_STP_LANG
                , VX_ANGEBOT.ANG_BEGINN
            
 ,* 

            FROM 
                ECOLST.VX_KLASSE_ZEITRAUM, 
                ECOLST.VX_PERSON, 
                ECOLST.VX_KLASSE, 
                ECOLST.VX_KLASSEABSCHNITTSCHUELER, 
                ECOLST.VX_ZEITRAUM, 
                ECOLST.VX_ADRESSE 
                , ECOSYS.T_KLASSE
                , ECOLST.VX_ANGEBOT

            WHERE  
                    VX_KLASSE_ZEITRAUM.klz_kl_id = VX_KLASSE.kl_id 
                AND VX_KLASSE_ZEITRAUM.klz_zr_id = VX_ZEITRAUM.zr_id 
                AND VX_KLASSEABSCHNITTSCHUELER.kas_ang_id = VX_KLASSE.kl_ang_id 
                AND VX_KLASSEABSCHNITTSCHUELER.kas_zr_id = VX_ZEITRAUM.zr_id 
                AND VX_KLASSEABSCHNITTSCHUELER.kas_per_id = VX_PERSON.per_id 
                AND VX_KLASSEABSCHNITTSCHUELER.kas_kl_id = VX_KLASSE.kl_id 
                AND VX_KLASSEABSCHNITTSCHUELER.KAS_ANMELDE_STATUS LIKE 'De%'  -- LIKE 'Definitiv%'
                AND VX_PERSON.per_id = VX_ADRESSE.adr_per_id 
                AND VX_PERSON.per_man_id = VX_KLASSE.kl_man_id
                AND VX_KLASSE.KL_ANG_ID = VX_ANGEBOT.ANG_ID
                AND VX_KLASSE.KL_MAN_ID = 15 
                AND VX_KLASSE.KL_ID = T_KLASSE.KL_ID
                AND T_KLASSE.KL_STATUS_ID = 491   -- d.h. TS_CODE.CODE_UP_BEZEICHNUNG = 'AKTIV'
            

                AND VX_KLASSE.KL_KLASSENTYP_ID IN (742,743,1235,1926,2075,2076,2078,2079,2080,2081,2086,2103,2118,2119,2122,2152,2252,2308,2416)
        

                AND VX_PERSON.PER_NP = 1   -- Natürliche Person
                AND LEN(LTRIM(RTRIM(VX_PERSON.PER_VORNAME))) > 0        -- TRIM() kann erst ab SQL Server 2017 verwendet werden
                AND LEN(LTRIM(RTRIM(VX_PERSON.PER_NAME))) > 0           -- TRIM() kann erst ab SQL Server 2017 verwendet werden
        
 AND VX_ZEITRAUM.zr_beginn <= CONVERT(DATETIME, '20.05.2023', 104) 
 AND VX_ZEITRAUM.zr_ende   >= CONVERT(DATETIME, '14.02.2023', 104) 
 AND VX_PERSON.per_man_id IN ( 15 ) 

                --AND VX_Person.PER_ID IN  (233777,233779)
        

Questões

A recomendação geral é não usar *ao definir a lista de colunas, mas, no meu caso, adicionar o , *à lista de colunas no final acelera significativamente a consulta. (de 21s até 2s)

Não há recomendações de índices ausentes nos planos de execução reais.

Presumo que tenha a ver com colunas específicas retornadas ao usar o , *in the statement , que possivelmente estão incluídas em índices considerados úteis pelo otimizador de consulta, mas não tenho certeza de como identificar essas colunas.

1. Quais índices eu teria que criar para persuadir o SQL Server a executar a instrução de desempenho ruim que não contém nenhuma , *lista de colunas, para usar um plano semelhante à instrução de desempenho, * que contém o adicional na lista de colunas?

2. Teria que analisar todos os índices usados ​​no bom plano de execução e criar índices reduzidos (omitindo certas colunas) para que o otimizador de consulta considerasse usar um plano de boa execução semelhante para a instrução sem o adicional , *?

Soluções experimentadas de acordo com as sugestões

1. OPTION (MIN_GRANT_PERCENT = 10, MAX_GRANT_PERCENT = 15)

   A aplicação da solução acima forneceu apenas um aumento temporário no desempenho por cerca de 1 hora. Depois disso, a consulta voltou ao plano de execução ruim. Eu não tenho ideia do porquê...

2. Nível de compatibilidade do banco de dados

   Alterar o nível de compatibilidade do banco de dados para 110 (SQL Server 2012) com o seguinte comando resultou em um aumento constante de desempenho para a consulta mencionada sem a adição de ,*na lista de colunas.

   USE [master]
   GO
   ALTER DATABASE [ECOWEBBSP] SET COMPATIBILITY_LEVEL = 110
   GO
   

   O plano de execução de consulta com nível de compatibilidade em 110 mostra que o otimizador de consulta escolheu uma abordagem totalmente diferente ao recuperar os dados e não teve nenhum problema em atribuir a quantidade correta de memória (110 MB).

Questão a seguir

Definir o nível de compatibilidade para 110 é minha única opção?

Comentários Adicionais

A Ominous Function fi_kla_is_abschlussklassemencionada na resposta de Erik é acionada pela coluna VX_KLASSE_ZEITRAUM.KLZ_IS_ABSCHLUSSKLASSEna ECOLST.VX_KLASSE_ZEITRAUMexibição. A tabela subjacente chama a função de valor escalar ao recuperar dados. A própria função retorna 0 ou 1, dependendo se o aluno está em uma turma de graduação (1) ou não (0).

No entanto, a função não parece ter tanto impacto na duração da consulta ao executar com o nível de compatibilidade definido como 110 (SQL Server 2012). Consulte o plano de execução da consulta com nível de compatibilidade em 110 para obter detalhes.