SQL Server – How to Find Cause of Excessive Grant Memory Warning in Query Plan

execution-planoptimizationperformanceperformance-tuningquery-performancesql server

I am running a query that is giving the warning about a memory Excessive Grant.

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

This is how the warning looks like:

The execution plan is here.

How do I tackle this warning?

As I said before I was looking at conversions. Is there anything I can look for in the execution plan that would indicate the possible causes of this excessive grant?

Obs. I said there are too many objects involved, however, I can add here whatever is required upon request if it might help to tackle this issue. No problem.

Best Answer

The most common two types of operators that consume memory are:

Sorts
Hashes

If a plan is parallel, memory requirements will go up some amount to compensate for exchanges for threads to pass rows through. Parallel plans do not require the entire serial memory grant * DOP (though there may be a relationship between serial required memory and DOP). The full grant is split and (hopefully) used evenly across all threads in the plan.

In some cases, Nested Loops Join may ask for memory as well.

Memory grants are calculated by the optimizer based on the number of rows, and the size of the data that will pass through memory consuming operators. As above, if the plan is parallel, it may ask for more. Another factor is that memory consuming operators can share memory.

For instance, some memory consumed by a sort can be passed to upstream operators after data is sorted, and hashes may pass memory upstream after the initial build phase is complete, and probed rows start moving upstream. This can be observed via the Memory Fraction information.

In your plan, you have three Sort operators.

Which, combined, the optimizer thinks it'll need 2 MB of memory to run without spilling to disk. It ends up only needing 24 KB, hence the warning.

Memory grant mis-estimates can come from many places. In your query, you have a single variable: @arrivalDate.

It's not clear if this parameter is within a stored procedure, or if you're calling it locally. In either case, you might try a recompile hint to see if that removes the warning by getting a different cardinality estimate.

If that doesn't work, you may want to try adjusting indexes so that separate sort operations aren't necessary, but that may be more complicated than is worthwhile for such a small amount of memory.

For reference:

Why You’re Tuning Stored Procedures Wrong (the Problem with Local Variables)
Troubleshooting Parameter Sniffing Issues the Right Way: Part 1 (links to other parts are provided in the first post)
Yet Another Post About Local Variables

Related Solutions

SQL Server Execution Plan – SHOWPLAN vs ‘Include Execution Plan’ Warnings for the Same Query

This:

SET SHOWPLAN_XML ON;
GO
SELECT * FROM sys.objects;
GO

Is equivalent to pressing Display Estimated Execution Plan on the toolbar (or hitting Ctrl + L). You'll notice that no rows are returned from the query, like there is when you use Include Actual Execution Plan (Ctrl + M).

The spill warning is only a runtime warning. There is no way that SQL Server can know, when displaying the estimated plan, that a spill will happen at runtime. This is because a spill is caused by factors that might only be present during certain invocations of the query (for example, when there is memory pressure). The estimated plan knows roughly how much memory it's going to ask for, but it can't know until execution that it isn't going to get it.

As an aside, may I recommend* our free tool, SQL Sentry Plan Explorer? I think it provides much more obvious information than Management Studio. I recently wrote a lengthy blog post that can act as a tutorial, and Jonathan Kehayias has a great PluralSight course on it as well.

_{* Disclaimer: I work for SQL Sentry.}

SQL Server – How to Read an Execution Plan

Why don't your input parameters match the type for the table? Why would you want to keep the wrong types there and perform any casts or conversions at all (whether implicit or explicit)? Why are you converting anything to FLOAT, of all things? To address specific questions:

My query says [low] is being converted from int to numeric but that doesn't seem to be what the cluster index seek details is showing, is it?

The convert of low is happening in the output, not in the seek predicate (the predicate is what is used to find matching rows and/or eliminate non-matching rows).

Is there a way to tell in this specific example how much "Better" it would be if I did the conversions Explicitly? I would think I could just do a "cast" in the query and insert a few numbers where the variables are, is that correct?

There's no way to make the execution plan show you how much better a different plan would be, except to generate that different plan and compare. You can use this comparison to document how much better it would be if the interface were correct (and two other ways would be to keep the interface but (a) perform explicit converts in the query - not of the column, but of the variables or (b) use local variables of the right type and assign them the values of the parameters). So you could show them 3 different ways to solve the problem, and show evidence that all 3 are better than the current version.

My recommendation is to fix the procedure the right way. First let's look at the actual types you care about:

USE master;
GO
SELECT t.name, c.max_length/CASE 
  WHEN t.name LIKE N'n[cvt]%' THEN 2 ELSE 1 END
FROM sys.all_columns AS c 
INNER JOIN sys.types AS t
ON c.system_type_id = t.system_type_id
AND c.system_type_id = t.user_type_id
WHERE EXISTS
(
  SELECT 1 FROM sys.all_objects AS o
    INNER JOIN sys.schemas AS s
    ON o.[schema_id] = s.[schema_id]
    WHERE o.[object_id] = c.[object_id]
    AND o.name = N'spt_values'
    AND s.name = N'dbo'
)
AND c.name IN (N'number',N'type');

Results:

number    int     4
type      nchar   3

So the interface to your stored procedure should be:

USE yourdb;
GO
ALTER PROCEDURE dbo.some_name
  @1 INT,
  @2 NCHAR(3),
  @3 NUMERIC(4, 0)
AS
BEGIN
  SET NOCOUNT ON;

  SELECT CONVERT([float], [low] / @3, 0) -- don't think you want float here
    FROM [master].[dbo].[spt_values]
    WHERE [number] = @1
    AND [type] = @2;
END
GO

Implicit conversions between varchar and nvarchar can be particularly bad (especially in the opposite scenario as yours - parameter is nvarchar and column is varchar), but there really is no reason to allow for a 8000-character parameter of any type when the longest string possible in the table is 3 characters...

Best Answer

Related Solutions

SQL Server Execution Plan – SHOWPLAN vs ‘Include Execution Plan’ Warnings for the Same Query

SQL Server – How to Read an Execution Plan

Related Question