Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ozSQLServer
Starting Member
32 Posts |
 Posted - 2011-09-28 : 20:33:50
|
| 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 2008Cheers,ozSQL |
|
|
Cindyaz
Yak Posting Veteran
73 Posts |
Posted - 2011-09-29 : 09:14:27
|
| dm_exec_query_stats stores the statictics of plans which are in memory. It doesnt have session information as one particular plan can be reused by multiple sessions. You can try joining sql_handle to most_recent_sql_handle in dm_exec_connections and from there you can join session_id to dm_exec_sessions. However, this wont give you correct data. If one session has executed an sp (sp1) and has not executed any other sql, you may get session details. However, if that session has executed a new sp (say sp2), you wont get session details abt sp2 using this method. |
 |
|
|
paultech
Yak Posting Veteran
79 Posts |
Posted - 2011-09-29 : 10:23:15
|
| hi about sys.dm_exec_sessions is a server-scope view that shows information about all active user connections and internal tasks.all about sys.dm_exec_sessions and how to exceute it in the following link :http://msdn.microsoft.com/en-us/library/ms176013.aspxpaul Tech |
|
|
|
|
|
|
what to link dm_exec_query_stats to