In SQL Server 2008 when we open activity monitor we can see SPID and information related for that SPID . But what if we want to get detail information for that SPID.
In SQL Server 2008 MS has provided a easy way for this , when we right click on this process , menu has one more option available "Trace Process in SQL Server Profiler".
So it will directly open profiler ,filter it for that SPID and start to trace this SPID. This can be handy many times for trouble shooting or monitoring activity for selected SPID
Cheers
In SQL Server 2008 MS has provided a easy way for this , when we right click on this process , menu has one more option available "Trace Process in SQL Server Profiler".
So it will directly open profiler ,filter it for that SPID and start to trace this SPID. This can be handy many times for trouble shooting or monitoring activity for selected SPID
Cheers
1 comment:
There are number of times when you can't even bring up Activity Monitor.
In that case
I would suggest quering the sys views. something simiar to
sys.dm_exec_sessions s LEFT JOIN sys.dm_exec_connections c ON s.session_id = c.session_id LEFT JOIN sys.dm_db_task_space_usage tsu ON tsu.session_id = s.session_id LEFT JOIN sys.dm_os_tasks t ON t.session_id = tsu.session_id AND t.request_id = tsu.request_id LEFT JOIN sys.dm_exec_requests r ON r.session_id = tsu.session_id AND r.request_id = tsu.request_id OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) TSQL This way you can get a TotalPagesAllocated which can help you fiure out the spid that is taking all the server resources. There has lot of times when i cant even bring up activity monitor and use these sys views to see whats going on.
I would recommend you reading the following article.
http://tsqltips.blogspot.com/2012/06/monitor-current-sql-server-processes.html
Post a Comment