4 added [query-performance] to 2412 questions - Shog9 (Id=1924)
| link
3 added [performance-tuning] to 516 questions - Shog9 (Id=1924)
| link
    Tweeted twitter.com/StackDBAs/status/1037807951845748736
2 Copy edited.
source | link

warning Warning about memory Excessive Grant"Excessive Grant" in the query plan - how to find out what is causing it?

There are too many tables and indexes used, including a complex view, and therefore it is difficult to add all of the definitions here.

I am trying to find what might me causing thiethe Excessive Grant. can Can it be conversions?

Looking at the execution plan I can see the folowingfollowing:

                                        <ScalarOperator 

  ScalarString="CONVERT(date,[apia_repl_sub].[dbo].[repl_Aupair].[ArrivalDate] as [repl].[ArrivalDate],0)">
                                              <Convert DataType="date" Style="0" Implicit="false">
                                                <ScalarOperator>
                                                  <Identifier>
                                                    <ColumnReference Database="[apia_repl_sub]" Schema="[dbo]" Table="[repl_Aupair]" Alias="[repl]" Column="ArrivalDate" />
                                                  </Identifier>
                                                </ScalarOperator>
                                              </Convert>
                                            </ScalarOperator>

andAnd this one:

<ScalarOperator ScalarString="CONVERT(date,[JUNOCORE].[dbo].[applicationPlacementInfo].[arrivalDate] as [pi].[arrivalDate],0)">
                                                                                              <Convert DataType="date" Style="0" Implicit="false">
                                                                                                <ScalarOperator>
                                                                                                  <Identifier>
                                                                                                    <ColumnReference Database="[JUNOCORE]" Schema="[dbo]" Table="[applicationPlacementInfo]" Alias="[pi]" Column="arrivalDate" />
                                                                                                  </Identifier>
                                                                                                </ScalarOperator>
                                                                                              </Convert>
                                                                                            </ScalarOperator>

hereHere is the query, although you can see the query with the execution plan here too:

DECLARE @arrivalDate DATEtime = '2018-08-20'

SELECT      app.applicantID,
            app.applicationID,
            a.preferredName,
            u.firstname,
            u.lastname,
            u.loginId                        AS emailAddress,
            s.status                        AS statusDescription,
            CAST(repl.arrivalDate AS DATE)  AS arrivalDate,
            app.moodleCourseComplete,
            app.moodleCourseCompleteUpdated,
            u.loginId,
            c.countryName
FROM        app.application                    AS app
JOIN        app.applicant                    AS a    ON a.applicantId = app.applicantId
JOIN        usr.[user]                        AS u    ON u.userId = a.userId 
JOIN        app.ref_applicationStatus        AS s    ON s.statusCode = app.status
JOIN        APIA_Repl_Sub.dbo.repl_Aupair    AS repl    ON repl.JunoCore_applicationID = app.applicationID

JOIN        app.Country                        AS c    ON c.countryCode = a.nationalityCode
WHERE        repl.arrivalDate = @arrivalDate

UNION ALL

(
    SELECT        app.applicantID,
                app.applicationID,
                app.preferredName,
                app.firstname,
                app.lastname,
                app.emailAddress,
                ap.status,
                CAST(app.arrivalDate AS DATE)    AS arrivalDate,
                app.moodleCourseComplete,
                app.moodleCourseCompleteUpdated,
                app.emailAddress                 AS loginId,
                c.countryName
    FROM        JUNOCore.dbo.vw_SelectApplication    AS app    
                INNER JOIN JUNOCore.dbo.country c ON c.countryCode = app.nationalityCode
                INNER JOIN JUNOCore.dbo.application as ap ON ap.applicationID = app.applicationID
    WHERE        arrivalDate    = @arrivalDate AND
                app.applicationID NOT IN (SELECT p4.applicationId FROM APCore.app.application p4)
)

enter image description hereEnter image description here

question:

How todo I tackle this warning?

warning about memory Excessive Grant in the query plan - how to find out what is causing it?

There are too many tables and indexes used, including a complex view, therefore it is difficult to add all of the definitions here.

I am trying to find what might me causing thie Excessive Grant. can it be conversions?

Looking at the execution plan I can see the folowing:

                                        <ScalarOperator 

ScalarString="CONVERT(date,[apia_repl_sub].[dbo].[repl_Aupair].[ArrivalDate] as [repl].[ArrivalDate],0)">
                                              <Convert DataType="date" Style="0" Implicit="false">
                                                <ScalarOperator>
                                                  <Identifier>
                                                    <ColumnReference Database="[apia_repl_sub]" Schema="[dbo]" Table="[repl_Aupair]" Alias="[repl]" Column="ArrivalDate" />
                                                  </Identifier>
                                                </ScalarOperator>
                                              </Convert>
                                            </ScalarOperator>

and this one:

<ScalarOperator ScalarString="CONVERT(date,[JUNOCORE].[dbo].[applicationPlacementInfo].[arrivalDate] as [pi].[arrivalDate],0)">
                                                                                              <Convert DataType="date" Style="0" Implicit="false">
                                                                                                <ScalarOperator>
                                                                                                  <Identifier>
                                                                                                    <ColumnReference Database="[JUNOCORE]" Schema="[dbo]" Table="[applicationPlacementInfo]" Alias="[pi]" Column="arrivalDate" />
                                                                                                  </Identifier>
                                                                                                </ScalarOperator>
                                                                                              </Convert>
                                                                                            </ScalarOperator>

here is the query, although you can see the query with the execution plan here too

DECLARE @arrivalDate DATEtime = '2018-08-20'

SELECT      app.applicantID,
            app.applicationID,
            a.preferredName,
            u.firstname,
            u.lastname,
            u.loginId                        AS emailAddress,
            s.status                        AS statusDescription,
            CAST(repl.arrivalDate AS DATE)  AS arrivalDate,
            app.moodleCourseComplete,
            app.moodleCourseCompleteUpdated,
            u.loginId,
            c.countryName
FROM        app.application                    AS app
JOIN        app.applicant                    AS a    ON a.applicantId = app.applicantId
JOIN        usr.[user]                        AS u    ON u.userId = a.userId 
JOIN        app.ref_applicationStatus        AS s    ON s.statusCode = app.status
JOIN        APIA_Repl_Sub.dbo.repl_Aupair    AS repl    ON repl.JunoCore_applicationID = app.applicationID

JOIN        app.Country                        AS c    ON c.countryCode = a.nationalityCode
WHERE        repl.arrivalDate = @arrivalDate

UNION ALL

(
    SELECT        app.applicantID,
                app.applicationID,
                app.preferredName,
                app.firstname,
                app.lastname,
                app.emailAddress,
                ap.status,
                CAST(app.arrivalDate AS DATE)    AS arrivalDate,
                app.moodleCourseComplete,
                app.moodleCourseCompleteUpdated,
                app.emailAddress                AS loginId,
                c.countryName
    FROM        JUNOCore.dbo.vw_SelectApplication    AS app    
                INNER JOIN JUNOCore.dbo.country c ON c.countryCode = app.nationalityCode
                INNER JOIN JUNOCore.dbo.application as ap ON ap.applicationID = app.applicationID
    WHERE        arrivalDate    = @arrivalDate AND
                app.applicationID NOT IN (SELECT p4.applicationId FROM APCore.app.application p4)
)

enter image description here

question:

How to tackle this warning?

Warning about memory "Excessive Grant" in the query plan - how to find out what is causing it?

There are too many tables and indexes used, including a complex view, and therefore it is difficult to add all of the definitions here.

I am trying to find what might me causing the Excessive Grant. Can it be conversions?

Looking at the execution plan I can see the following:

<ScalarOperator
  ScalarString="CONVERT(date,[apia_repl_sub].[dbo].[repl_Aupair].[ArrivalDate] as [repl].[ArrivalDate],0)">
  <Convert DataType="date" Style="0" Implicit="false">
    <ScalarOperator>
      <Identifier>
        <ColumnReference Database="[apia_repl_sub]" Schema="[dbo]" Table="[repl_Aupair]" Alias="[repl]" Column="ArrivalDate" />
      </Identifier>
    </ScalarOperator>
  </Convert>
</ScalarOperator>

And this one:

<ScalarOperator ScalarString="CONVERT(date,[JUNOCORE].[dbo].[applicationPlacementInfo].[arrivalDate] as [pi].[arrivalDate],0)">
  <Convert DataType="date" Style="0" Implicit="false">
    <ScalarOperator>
      <Identifier>
        <ColumnReference Database="[JUNOCORE]" Schema="[dbo]" Table="[applicationPlacementInfo]" Alias="[pi]" Column="arrivalDate" />
      </Identifier>
    </ScalarOperator>
  </Convert>
</ScalarOperator>

Here is the query, although you can see the query with the execution plan here too:

DECLARE @arrivalDate DATEtime = '2018-08-20'

SELECT      app.applicantID,
            app.applicationID,
            a.preferredName,
            u.firstname,
            u.lastname,
            u.loginId                       AS emailAddress,
            s.status                        AS statusDescription,
            CAST(repl.arrivalDate AS DATE)  AS arrivalDate,
            app.moodleCourseComplete,
            app.moodleCourseCompleteUpdated,
            u.loginId,
            c.countryName
FROM        app.application                  AS app
JOIN        app.applicant                    AS a    ON a.applicantId = app.applicantId
JOIN        usr.[user]                       AS u    ON u.userId = a.userId
JOIN        app.ref_applicationStatus        AS s    ON s.statusCode = app.status
JOIN        APIA_Repl_Sub.dbo.repl_Aupair    AS repl ON repl.JunoCore_applicationID = app.applicationID

JOIN        app.Country                      AS c    ON c.countryCode = a.nationalityCode
WHERE       repl.arrivalDate = @arrivalDate

UNION ALL

(
    SELECT      app.applicantID,
                app.applicationID,
                app.preferredName,
                app.firstname,
                app.lastname,
                app.emailAddress,
                ap.status,
                CAST(app.arrivalDate AS DATE)    AS arrivalDate,
                app.moodleCourseComplete,
                app.moodleCourseCompleteUpdated,
                app.emailAddress                 AS loginId,
                c.countryName
    FROM        JUNOCore.dbo.vw_SelectApplication    AS app
                INNER JOIN JUNOCore.dbo.country c ON c.countryCode = app.nationalityCode
                INNER JOIN JUNOCore.dbo.application as ap ON ap.applicationID = app.applicationID
    WHERE        arrivalDate    = @arrivalDate AND
                app.applicationID NOT IN (SELECT p4.applicationId FROM APCore.app.application p4)
)

Enter image description here

How do I tackle this warning?

1
source | link