Mixing
“Warning: The join order has been enforced because a local join hint is used†returned with no Join hints
Clash Royale CLAN TAG#URR8PPP
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;
up vote
3
down vote
favorite
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)
sql-server sql-server-2012
asked Aug 21 at 18:56
Laughing Vergil
574110
add a comment |Â
up vote
3
down vote
favorite
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)
sql-server sql-server-2012
asked Aug 21 at 18:56
Laughing Vergil
574110
1
Three suggestions: (1) stop using the deprecatedsys.sysprocesses(2) use the ANSI standard<>instead of!=(3) stop reinventing the wheel and writing these homemade queries from scratch and having to re-solve the same issues others before you have already solved - go grab Adam Machanic's sp_whoisactive, Brent Ozar Unlimited's First Responder Kit, or Glenn Berry's DMV queries. They're all great but when you're up for big kid monitoring tools, let me know. :-)
– Aaron Bertrand♦
Aug 22 at 0:09
@AaronBertrand - I appreciate your comments, but (1) I can't install Brent Ozar's First Responder Kit here due to company requirements [sigh], (2) I can't install sp_whoisactive on the machine I am analyzing due to app owner requirements [sigh x 2], and (3) I can and am using Glenn Berry's DMV queries, but they don't answer the specific questions I am trying to answer. They are good, though. ;-)
– Laughing Vergil
Aug 22 at 17:54
So your company wants you wasting your valuable time solving issues that have already been solved by scripts that are freely available? Okay. You might want whoever is responsible for that policy to visit this page and re-think.
– Aaron Bertrand♦
Aug 22 at 18:05
add a comment |Â
up vote
3
down vote
favorite
up vote
3
down vote
favorite
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)
sql-server sql-server-2012
asked Aug 21 at 18:56
Laughing Vergil
574110
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)
sql-server sql-server-2012
asked Aug 21 at 18:56
Laughing Vergil
574110
asked Aug 21 at 18:56
Laughing Vergil
574110
asked Aug 21 at 18:56
Laughing Vergil
574110
asked Aug 21 at 18:56
Laughing Vergil
574110
574110
1
Three suggestions: (1) stop using the deprecatedsys.sysprocesses(2) use the ANSI standard<>instead of!=(3) stop reinventing the wheel and writing these homemade queries from scratch and having to re-solve the same issues others before you have already solved - go grab Adam Machanic's sp_whoisactive, Brent Ozar Unlimited's First Responder Kit, or Glenn Berry's DMV queries. They're all great but when you're up for big kid monitoring tools, let me know. :-)
– Aaron Bertrand♦
Aug 22 at 0:09
@AaronBertrand - I appreciate your comments, but (1) I can't install Brent Ozar's First Responder Kit here due to company requirements [sigh], (2) I can't install sp_whoisactive on the machine I am analyzing due to app owner requirements [sigh x 2], and (3) I can and am using Glenn Berry's DMV queries, but they don't answer the specific questions I am trying to answer. They are good, though. ;-)
– Laughing Vergil
Aug 22 at 17:54
So your company wants you wasting your valuable time solving issues that have already been solved by scripts that are freely available? Okay. You might want whoever is responsible for that policy to visit this page and re-think.
– Aaron Bertrand♦
Aug 22 at 18:05
add a comment |Â
1
Three suggestions: (1) stop using the deprecatedsys.sysprocesses(2) use the ANSI standard<>instead of!=(3) stop reinventing the wheel and writing these homemade queries from scratch and having to re-solve the same issues others before you have already solved - go grab Adam Machanic's sp_whoisactive, Brent Ozar Unlimited's First Responder Kit, or Glenn Berry's DMV queries. They're all great but when you're up for big kid monitoring tools, let me know. :-)
– Aaron Bertrand♦
Aug 22 at 0:09
@AaronBertrand - I appreciate your comments, but (1) I can't install Brent Ozar's First Responder Kit here due to company requirements [sigh], (2) I can't install sp_whoisactive on the machine I am analyzing due to app owner requirements [sigh x 2], and (3) I can and am using Glenn Berry's DMV queries, but they don't answer the specific questions I am trying to answer. They are good, though. ;-)
– Laughing Vergil
Aug 22 at 17:54
So your company wants you wasting your valuable time solving issues that have already been solved by scripts that are freely available? Okay. You might want whoever is responsible for that policy to visit this page and re-think.
– Aaron Bertrand♦
Aug 22 at 18:05
1
1
Three suggestions: (1) stop using the deprecated
sys.sysprocesses (2) use the ANSI standard <> instead of != (3) stop reinventing the wheel and writing these homemade queries from scratch and having to re-solve the same issues others before you have already solved - go grab Adam Machanic's sp_whoisactive, Brent Ozar Unlimited's First Responder Kit, or Glenn Berry's DMV queries. They're all great but when you're up for big kid monitoring tools, let me know. :-)– Aaron Bertrand♦
Aug 22 at 0:09
Three suggestions: (1) stop using the deprecated
sys.sysprocesses (2) use the ANSI standard <> instead of != (3) stop reinventing the wheel and writing these homemade queries from scratch and having to re-solve the same issues others before you have already solved - go grab Adam Machanic's sp_whoisactive, Brent Ozar Unlimited's First Responder Kit, or Glenn Berry's DMV queries. They're all great but when you're up for big kid monitoring tools, let me know. :-)– Aaron Bertrand♦
Aug 22 at 0:09
@AaronBertrand - I appreciate your comments, but (1) I can't install Brent Ozar's First Responder Kit here due to company requirements [sigh], (2) I can't install sp_whoisactive on the machine I am analyzing due to app owner requirements [sigh x 2], and (3) I can and am using Glenn Berry's DMV queries, but they don't answer the specific questions I am trying to answer. They are good, though. ;-)
– Laughing Vergil
Aug 22 at 17:54
@AaronBertrand - I appreciate your comments, but (1) I can't install Brent Ozar's First Responder Kit here due to company requirements [sigh], (2) I can't install sp_whoisactive on the machine I am analyzing due to app owner requirements [sigh x 2], and (3) I can and am using Glenn Berry's DMV queries, but they don't answer the specific questions I am trying to answer. They are good, though. ;-)
– Laughing Vergil
Aug 22 at 17:54
So your company wants you wasting your valuable time solving issues that have already been solved by scripts that are freely available? Okay. You might want whoever is responsible for that policy to visit this page and re-think.
– Aaron Bertrand♦
Aug 22 at 18:05
So your company wants you wasting your valuable time solving issues that have already been solved by scripts that are freely available? Okay. You might want whoever is responsible for that policy to visit this page and re-think.
– Aaron Bertrand♦
Aug 22 at 18:05
add a comment |Â
1 Answer
1
active
oldest
votes
up vote
10
down vote
accepted
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.
answered Aug 21 at 20:32
jadarnel27
1,6111125
add a comment |Â
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
10
down vote
accepted
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.
answered Aug 21 at 20:32
jadarnel27
1,6111125
add a comment |Â
up vote
10
down vote
accepted
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.
answered Aug 21 at 20:32
jadarnel27
1,6111125
add a comment |Â
up vote
10
down vote
accepted
up vote
10
down vote
accepted
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.
answered Aug 21 at 20:32
jadarnel27
1,6111125
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.
answered Aug 21 at 20:32
jadarnel27
1,6111125
answered Aug 21 at 20:32
jadarnel27
1,6111125
answered Aug 21 at 20:32
jadarnel27
1,6111125
answered Aug 21 at 20:32
jadarnel27
1,6111125
1,6111125
add a comment |Â
add a comment |Â
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f215517%2fwarning-the-join-order-has-been-enforced-because-a-local-join-hint-is-used-re%23new-answer', 'question_page');
);
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
1
Three suggestions: (1) stop using the deprecated
sys.sysprocesses(2) use the ANSI standard<>instead of!=(3) stop reinventing the wheel and writing these homemade queries from scratch and having to re-solve the same issues others before you have already solved - go grab Adam Machanic's sp_whoisactive, Brent Ozar Unlimited's First Responder Kit, or Glenn Berry's DMV queries. They're all great but when you're up for big kid monitoring tools, let me know. :-)– Aaron Bertrand♦
Aug 22 at 0:09
@AaronBertrand - I appreciate your comments, but (1) I can't install Brent Ozar's First Responder Kit here due to company requirements [sigh], (2) I can't install sp_whoisactive on the machine I am analyzing due to app owner requirements [sigh x 2], and (3) I can and am using Glenn Berry's DMV queries, but they don't answer the specific questions I am trying to answer. They are good, though. ;-)
– Laughing Vergil
Aug 22 at 17:54
So your company wants you wasting your valuable time solving issues that have already been solved by scripts that are freely available? Okay. You might want whoever is responsible for that policy to visit this page and re-think.
– Aaron Bertrand♦
Aug 22 at 18:05