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)
sys.sysprocesses(2) use the ANSI standard
!=(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. :-)