SELECT Statement with , * in column list is faster than same statement without *

Question

Situation

When querying a database with a SELECT statement with a defined set of columns, the results are received in around 21 seconds.

If there is an additional asterisk (, *) at the end of the list of defined set of columns, then the query returns the results in 2 seconds.

Query Execution Plans

The execution plans differ significantly.

You can find the good actual query execution plan and the bad actual query execution plan with the links from PasteThePlan.

Statement containing , * in the column list (at the end)

            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)
        

Questions

The general recommendation is to not use * when defining the column list, but in my case, adding the , * to the column list at the end, speeds up the query significantly. (from 21s down to 2s)

There are no Missing Index recommendations in the Actual Execution Plans.

I assume is has to do with specific columns being returned when using the , * in the statement, that are possibly included in indexes that are considered useful by the query optimizer, but I'm unsure how to pin-point these columns.

1. What indexes would I have to create in order to persuade SQL Server to run the badly performing statement containing no , * column list, to use a similar plan as the performant statement which contains the additional , * in the column list?

2. Would I have to analyse all the indexes used in the good execution plan and create reduced indexes (omitting certain columns) so that the query optimizer would consider using a similar good execution plan for the statement without the additional , *?

---

Tried Solutions as Per The Suggestions

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

   Applying the above solution provided only a temporary increase in performance for about 1 hour. After that the query reverted back to the bad execution plan. I have no idea why...

2. Database Compatibility Level

   Changing the database's compatibility level down to 110 (SQL Server 2012) with the following command, resulted in a constant increase of performance for the mentioned query without the addition of ,* in the column list.

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

   The query execution plan with compatibility level at 110 shows that the query optimizer chose a totally different approach while retrieving the data and did not have any issues with assigning the correct amount of memory (110 MB).

Follow Up Question

Is setting the compatibility level to 110 my only option?

Additional Feedback

The Ominous Function fi_kla_is_abschlussklasse which was mentioned in Erik's answer is triggered by the column VX_KLASSE_ZEITRAUM.KLZ_IS_ABSCHLUSSKLASSE in the ECOLST.VX_KLASSE_ZEITRAUM view. The underlying table calls the scalar valued function when retrieving data. The function itself returns either a 0 or 1 depending on whether the student is in a graduating class (1) or not (0).

However, the function does not seem have as much impact on the duration of the query when running with the compatibility level set to 110 (SQL Server 2012). See the query execution plan with compatibility level at 110 for details.

Answer 1

While there are many differences in the two query plans, the problem you run into in the slower plan occurs mainly in a spill operation because of an undersized memory grant:

There is at least one other spill, but it's of less consequence.

The reason the faster plan doesn't spill is because it gets a higher memory grant, which SQL Server estimates based on the number of rows and estimated size of each row that passes through memory consuming operators, like Sorts and Hashes.

Here is the memory grant information:

It is difficult to get accurate cardinality estimation in queries of this complexity, but you could try:

• Updating statistics
• Using the legacy CE: OPTION(QUERYTRACEON 9481)
• Breaking the query up with #temp tables
• Using the MIN_GRANT_PERCENT hint to get a higher memory grant

Potentially worth noting is that you have a scalar UDF inhibiting parallelism for this query, and a couple non-SARGable predicates that could be contributing to throwing estimates off.

Function:

Query anti-pattern:

AND LEN(LTRIM(RTRIM(VX_PERSON.PER_VORNAME))) > 0
AND LEN(LTRIM(RTRIM(VX_PERSON.PER_NAME))) > 0

Can be simplified to look for a single character wildcard:

AND VX_PERSON.PER_VORNAME LIKE '_%'
AND VX_PERSON.PER_NAME LIKE '_%'

Or just looking for non-empty strings:

AND VX_PERSON.PER_VORNAME <> ''
AND VX_PERSON.PER_NAME <> ''