1

Is there a way to identify top database users who are actually consuming high resources with their executed queries ?

4

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.

Grab sp_WhoIsActive

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!

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.