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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply