November 17, 2011 at 7:36 am
I am administrator and I don't see activity monior option in sql 2005
What am I missing , i am on service pack 1
November 17, 2011 at 7:51 am
If you are a sysadmin you dont need any extra permissions but if you are not you need View Server State permission.
November 17, 2011 at 7:59 am
If it's still real urgent and you still can't see it you can use this query (it is what activity monitor runs).
--Borrowed from the activity monitor
SELECT
[Session ID] = s.session_id,
[User Process] = CONVERT(CHAR(1), s.is_user_process),
[Login] = s.login_name,
[Database] = ISNULL(db_name(p.dbid), N''),
[Task State] = ISNULL(t.task_state, N''),
[Command] = ISNULL(r.command, N''),
[Application] = ISNULL(s.program_name, N''),
[Wait Time (ms)] = ISNULL(w.wait_duration_ms, 0),
[Wait Type] = ISNULL(w.wait_type, N''),
[Wait Resource] = ISNULL(w.resource_description, N''),
[Blocked By] = ISNULL(CONVERT (varchar, w.blocking_session_id), ''),
[Head Blocker] =
CASE
-- session has an active request, is blocked, but is blocking others or session is idle but has an open tran and is blocking others
WHEN r2.session_id IS NOT NULL AND (r.blocking_session_id = 0 OR r.session_id IS NULL) THEN '1'
-- session is either not blocking someone, or is blocking someone but is blocked by another party
ELSE ''
END,
[Total CPU (ms)] = s.cpu_time,
[Total Physical I/O (MB)] = (s.reads + s.writes) * 8 / 1024,
[Memory Use (KB)] = s.memory_usage * 8192 / 1024,
[Open Transactions] = ISNULL(r.open_transaction_count,0),
[Login Time] = s.login_time,
[Last Request Start Time] = s.last_request_start_time,
[Host Name] = ISNULL(s.host_name, N''),
[Net Address] = ISNULL(c.client_net_address, N''),
[Execution Context ID] = ISNULL(t.exec_context_id, 0),
[Request ID] = ISNULL(r.request_id, 0)
-- [Workload Group] = ISNULL(g.name, N'')
FROM sys.dm_exec_sessions s LEFT OUTER JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id)
LEFT OUTER JOIN sys.dm_exec_requests r ON (s.session_id = r.session_id)
LEFT OUTER JOIN sys.dm_os_tasks t ON (r.session_id = t.session_id AND r.request_id = t.request_id)
LEFT OUTER JOIN
(
-- In some cases (e.g. parallel queries, also waiting for a worker), one thread can be flagged as
-- waiting for several different threads. This will cause that thread to show up in multiple rows
-- in our grid, which we don't want. Use ROW_NUMBER to select the longest wait for each thread,
-- and use it as representative of the other wait relationships this thread is involved in.
SELECT *, ROW_NUMBER() OVER (PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC) AS row_num
FROM sys.dm_os_waiting_tasks
) w ON (t.task_address = w.waiting_task_address) AND w.row_num = 1
LEFT OUTER JOIN sys.dm_exec_requests r2 ON (s.session_id = r2.blocking_session_id)
--LEFT OUTER JOIN sys.dm_resource_governor_workload_groups g ON (g.group_id = s.group_id)--TAKE THIS dmv OUT TO WORK IN 2005
LEFT OUTER JOIN sys.sysprocesses p ON (s.session_id = p.spid)
ORDER BY s.session_id;
November 17, 2011 at 8:02 am
Ninja's_RGR'us (11/17/2011)
If it's still real urgent and you still can't see it you can use this query (it is what activity monitor runs).
--Borrowed from the activity monitorSELECT
[Session ID] = s.session_id,
[User Process] = CONVERT(CHAR(1), s.is_user_process),
[Login] = s.login_name,
[Database] = ISNULL(db_name(p.dbid), N''),
[Task State] = ISNULL(t.task_state, N''),
[Command] = ISNULL(r.command, N''),
[Application] = ISNULL(s.program_name, N''),
[Wait Time (ms)] = ISNULL(w.wait_duration_ms, 0),
[Wait Type] = ISNULL(w.wait_type, N''),
[Wait Resource] = ISNULL(w.resource_description, N''),
[Blocked By] = ISNULL(CONVERT (varchar, w.blocking_session_id), ''),
[Head Blocker] =
CASE
-- session has an active request, is blocked, but is blocking others or session is idle but has an open tran and is blocking others
WHEN r2.session_id IS NOT NULL AND (r.blocking_session_id = 0 OR r.session_id IS NULL) THEN '1'
-- session is either not blocking someone, or is blocking someone but is blocked by another party
ELSE ''
END,
[Total CPU (ms)] = s.cpu_time,
[Total Physical I/O (MB)] = (s.reads + s.writes) * 8 / 1024,
[Memory Use (KB)] = s.memory_usage * 8192 / 1024,
[Open Transactions] = ISNULL(r.open_transaction_count,0),
[Login Time] = s.login_time,
[Last Request Start Time] = s.last_request_start_time,
[Host Name] = ISNULL(s.host_name, N''),
[Net Address] = ISNULL(c.client_net_address, N''),
[Execution Context ID] = ISNULL(t.exec_context_id, 0),
[Request ID] = ISNULL(r.request_id, 0)
-- [Workload Group] = ISNULL(g.name, N'')
FROM sys.dm_exec_sessions s LEFT OUTER JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id)
LEFT OUTER JOIN sys.dm_exec_requests r ON (s.session_id = r.session_id)
LEFT OUTER JOIN sys.dm_os_tasks t ON (r.session_id = t.session_id AND r.request_id = t.request_id)
LEFT OUTER JOIN
(
-- In some cases (e.g. parallel queries, also waiting for a worker), one thread can be flagged as
-- waiting for several different threads. This will cause that thread to show up in multiple rows
-- in our grid, which we don't want. Use ROW_NUMBER to select the longest wait for each thread,
-- and use it as representative of the other wait relationships this thread is involved in.
SELECT *, ROW_NUMBER() OVER (PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC) AS row_num
FROM sys.dm_os_waiting_tasks
) w ON (t.task_address = w.waiting_task_address) AND w.row_num = 1
LEFT OUTER JOIN sys.dm_exec_requests r2 ON (s.session_id = r2.blocking_session_id)
--LEFT OUTER JOIN sys.dm_resource_governor_workload_groups g ON (g.group_id = s.group_id)--TAKE THIS dmv OUT TO WORK IN 2005
LEFT OUTER JOIN sys.sysprocesses p ON (s.session_id = p.spid)
ORDER BY s.session_id;
Keep in mind that it requires view server state permissions...
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
November 17, 2011 at 8:03 am
mandavli (11/17/2011)
I am administrator and I don't see activity monior option in sql 2005What am I missing , i am on service pack 1
Are you right clicking the instance in SSMS to check for Activity Monitor?
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply