SQL Server – Warning: Join Order Enforced Due to Local Join Hint

sql serversql-server-2012

I am running the below (homemade) analysis query, looking at wait states, memory usage, and other info for queries running on the current server.

Select GetDate() as RunDate,
    spid,
    blocked,
    waittype,
    lastwaittype,
    waittime,
    sp.dbid,
    uid,
    status,
    hostname,
    program_name,
    cmd,
    nt_domain,
    nt_username,
    loginame,
    sp.sql_handle,
    stmt_start,
    stmt_end,
    session_id,
    dop,
    request_time,
    grant_time,
    requested_memory_kb,
    granted_memory_kb,
    required_memory_kb,
    used_memory_kb,
    max_used_memory_kb,
    query_cost,
    ideal_memory_kb
    ,SUBSTRING (qt.text, stmt_start/2+1, (
        CASE 
            WHEN stmt_end = -1 
                THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 + 1
            ELSE stmt_end 
        END - stmt_start)/2) AS [Individual Query]
    ,[Parent Query] = qt.text

FROM sys.dm_exec_query_memory_grants mg
INNER JOIN sys.sysprocesses sp
    ON mg.session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) as qt
WHERE sp.spid != @@SPID
ORDER BY requested_memory_kb

When I run this and there are no currently running queries, I receive the warning message:
Warning: The join order has been enforced because a local join hint is used
As you can see, there are no join hints in the above code. This is only annoying in my context, but is this an annoying SQL Server bug, is this a known thing, or is it something else?

SQL version: Microsoft SQL Server 2012 (SP4) (KB4018073) – 11.0.7001.0 (X64)
Running on VMWare. Running in SSMS 2016 (Version 13.0.16106.4)

Best Answer

You can get the definition of that DMV by running the following T-SQL:

EXEC sp_helptext 'sys.dm_exec_query_memory_grants'

Which shows that a join hint is being used in the underlying view definition:

CREATE VIEW sys.dm_exec_query_memory_grants AS  
 SELECT A.session_id, A.request_id, A.scheduler_id, A.dop, A.request_time, A.grant_time,  
 A.requested_memory_kb, A.granted_memory_kb, A.required_memory_kb, A.used_memory_kb, A.max_used_memory_kb,  
 A.query_cost, A.timeout_sec,  
 convert(smallint, A.is_small) as resource_semaphore_id,   
 B.queue_id, B.wait_order, B.is_next_candidate, B.wait_time_ms,  
 A.plan_handle, A.sql_handle,  
 A.group_id, A.pool_id, A.is_small, A.ideal_memory_kb  
 FROM OpenRowset(TABLE DM_EXEC_QE_GRANTSINFO) A   
 LEFT OUTER LOOP JOIN OpenRowset(TABLE DM_EXEC_QE_GRANTWAITERS) B  
 ON A.session_id=B.session_id and A.request_id=B.request_id and A.sql_handle=B.sql_handle and A.plan_handle=B.plan_handle

Specifically it's the "LOOP JOIN" hint that is triggering the join order warning. Check out these Microsoft docs on join hints:

If a join hint is specified for any two tables, the query optimizer automatically enforces the join order for all joined tables in the query...

So even though the view just has a hint to enforce a particular join strategy (loop), a join order hint is automatically applied.

Related Question