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

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_KLAS

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 <> ''

StackExchange Database Administrators Q#323625, answer score: 13