Is there a way to identify top database users who are actually consuming high resources with their executed queries ?
There's nothing built in to SQL Server that will do this. If you don't feel like using sp_who3 and partying like it's 2002, here's another method.
If you just want to see what's happening now, run it like this:
EXEC dbo.sp_WhoIsActive @get_plans = 1, @get_locks = 1
If you want to use it long term to find out what users are up to, follow the instructions here to set it up to log results to a table. I'd probably skip collecting query plans here on a busy server, that could really bloat things.
How does this help? It returns wait info, tempdb use, blocking, cpu, reads, writes, locking, memory use, along with login name, host name, and program name so you can (if you choose to log it to a table) group by connection info to aggregate resource usage over time. The aggregations won't be perfect, but it might get you close enough to what you need (for free).
If you want to spent money on it, buy a monitoring tool. Sentry One Performance Advisor and Quest Spotlight are both pretty nifty.
Hope this helps!
Sp_who3 would provide you with useful info from users who are executing queries in real time.
SELECT des.session_id , des.status , des.login_name , des.[HOST_NAME] , der.blocking_session_id , DB_NAME(der.database_id) AS database_name , der.command , des.cpu_time , des.reads , des.writes , dec.last_write , des.[program_name] , der.wait_type , der.wait_time , der.last_wait_type , der.wait_resource , CASE des.transaction_isolation_level WHEN 0 THEN 'Unspecified' WHEN 1 THEN 'ReadUncommitted' WHEN 2 THEN 'ReadCommitted' WHEN 3 THEN 'Repeatable' WHEN 4 THEN 'Serializable' WHEN 5 THEN 'Snapshot' END AS transaction_isolation_level , OBJECT_NAME(dest.objectid, der.database_id) AS OBJECT_NAME , SUBSTRING(dest.text, der.statement_start_offset / 2, ( CASE WHEN der.statement_end_offset = -1 THEN DATALENGTH(dest.text) ELSE der.statement_end_offset END - der.statement_start_offset ) / 2) AS [executing statement] , deqp.query_plan FROM sys.dm_exec_sessions des LEFT JOIN sys.dm_exec_requests der ON des.session_id = der.session_id LEFT JOIN sys.dm_exec_connections dec ON des.session_id = dec.session_id CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) dest CROSS APPLY sys.dm_exec_query_plan(der.plan_handle) deqp
You can also use extended events and capture high resource consuming queries, with included user names