what to link dm_exec_query_stats to

Question

Post reply

what to link dm_exec_query_stats to

ahmed_b72

Ten Centuries

Points: 1251
More actions
September 28, 2011 at 6:33 pm

#390505

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281324 More actions · Answer 1

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 2

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 3

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