September 28, 2011 at 6:33 pm
Hi,
dm_exec_query_stats shows statistics about the performance and resources consumed for every query in the query cache.
How can I obtain the hostname and login name for each row in dm_exec_query_stats ?
I thought of linking it to sys.dm_exec_sessions but I couldn't know how.
SQL Server 2008
September 28, 2011 at 9:11 pm
You can get to sys.dm_exec_sessions via sys.dm_exec_requests. Use sql_handle and/or plan_handle from sys.dm_exec_query_stats to join to sys.dm_exec_requests. Then use session_id to get to sys.dm_exec_sessions.
This is all assuming that the plan you are looking at is currently connected and/or cached.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 29, 2011 at 3:16 am
ahmed_b72 (9/28/2011)
How can I obtain the hostname and login name for each row in dm_exec_query_stats ?
Simple. You don't.
Query stats is just the list of queries that have run in the past, with their performance stats and links to their execution plans. It's not a security audit log (The plan cache doesn't care where the connection that ran the query came from)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 29, 2011 at 4:53 am
sys.dm_exec_query_stats and sys.dm_exec_procedure_stats are aggregates showing all the times that the query has been called. No way to link it to an individual call, as Gail says.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply