January 27, 2020 at 7:47 pm
I have not been able to get any Processes to show in the Activity Monitor with my user (sysadmin) or even sa. I have to be able to get it to work as this is the only way I can fix our application's licensing issue getting stuck (kill process). Since I can't delete from sys.sysprocesses, I need to kill the process.
Nothing under Activity Monitor shows anything (Processes, Resource, Recent etc). Graphs are all blank too.
January 27, 2020 at 8:06 pm
Review this item: https://feedback.azure.com/forums/908035-sql-server/suggestions/37050118-ssms-17-9-1-activity-monitor-pauses-when-open-proc
If that is the issue - you can use this script to get the same data as you would from the processes tab:
With profiled_sessions
As (
Select Distinct
session_id profiled_session_id
From sys.dm_exec_query_profiles
)
Select [Session ID] = s.session_id
, [Login] = s.login_name
, [Database] = iif(p.dbid = 0, N'', 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] = iif(r2.session_id Is Not Null And (r.blocking_session_id = 0 Or r.session_id Is Null), N'1', N'')
, [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'')
, ps.profiled_session_id
From sys.dm_exec_sessions s
Left Join sys.dm_exec_connections c On s.session_id = c.session_id
Left Join sys.dm_exec_requests r On s.session_id = r.session_id
Left Join sys.dm_os_tasks t On r.session_id = t.session_id
And r.request_id = t.request_id
--==== 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 OUTER APPLY and TOP 1 to select the longest wait for each thread,
--==== and use it as representative of the other wait relationships this thread is involved in.
Outer Apply (Select Top 1
*
From sys.dm_os_waiting_tasks wt
Where wt.waiting_task_address = t.task_address
Order By
wt.wait_duration_ms desc
) w
Left Join sys.dm_exec_requests r2 On s.session_id = r2.blocking_session_id
Left Join sys.dm_resource_governor_workload_groups g On g.group_id = s.group_id
Left Join sys.sysprocesses p On s.session_id = p.spid
Left Join profiled_sessions ps On ps.profiled_session_id = s.session_id
Where s.is_user_process = 1
And r.command Is Not Null -- only show processes with active commands
And s.session_id <> @@spid -- don't need to show this session
Order By
s.login_name --[Host Name] --r.command desc
, s.session_id;
Comment out the line:
And r.command Is Not Null -- only show processes with active commands
to show all user processes and change the order by as needed.
Another alternative is to use sp_who2 - or download and install sp_WhoIsActive: https://www.brentozar.com/archive/2010/09/sql-server-dba-scripts-how-to-find-slow-sql-server-queries/
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
January 27, 2020 at 8:07 pm
It sounds like you have two different issues. The first one with blocking, killing a process - you don't need activity monitor to kill a process. I don't use activity monitor and have killed processes, checked for blocked processes. You can use sp_who2 to find blocking. An alternative I prefer is using sp_WhoIsActive. You can find more information and the download link at: http://whoisactive.com/
You kill a process executing Kill - that's all activity monitor is doing when you kill a process
In terms of Activity Monitor, have you tried another version of SSMS to see if that makes a difference?
Sue
January 27, 2020 at 8:10 pm
i'm running SSMS 18.4 so not sure if that 17.9 article applies. Anyway, when I open it the processes is not paused it's just empty. All of them are. Eventually it does change to paused.
I ran the query and it's showing only a single process running even though there are many. My issue isn't with how to show the processes but how to KILL one has I can't delete from the sys tables.
January 27, 2020 at 9:21 pm
The issue with Activity Monitor is not actually related to the version of SSMS - it is an issue with the version of SQL Server. This issue only shows up on systems (as of SQL Server 2014 when the new version of the code was added) where there are a high number of objects in the system.
Activity Monitor will appear without any data until the query times out - which is 30 seconds. Once the query times out then Activity Monitor changes status to paused.
As for only seeing a single item in the query I provider - did you comment out the line to filters to only those rows where the command is not null? That line is included so I can view currently active processes and not all processes...
Note: on systems prior to 2014 the query that is executed for activity monitor is quite different because 2012 does not have access to at least one of the DMV's used in this query - specifically the DMV sys.dm_exec_query_profiles.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
January 28, 2020 at 5:11 pm
if you have the pleasure of dealing with Amazon RDS - activity monitor will never work, good job i'm used to good old fashioned commands like
sp_who2
sp_lock
select * from master.dbo.sysprocesses
MVDBA
January 28, 2020 at 5:24 pm
Can I run a where statement on sp_who2 then a delete to "kill" that process? I get the same issues with the master.dbo.sysprocesses. How do I remove the record I need?
Ad hoc updates to system catalogs are not allowed.
no - you can't delete from a proc or a system table
just get the SPID value that you want to kill and then run the command
KILL 57
(I made up the 57 bit use the SPID number) - it may take a bit of time, but if you look at sp_who2 you will see that the SPID is in Rollback mode
MVDBA
January 28, 2020 at 7:44 pm
The whole idea behind the script I posted is that it will return the same information as Activity Monitor. In fact - it is the exact same query that is executed except I changed the section that is using ROW_NUMBER() to an OUTER APPLY (fixes the problem with timing out).
It can be filtered by changing the where clause to whatever is needed - in my case I use it to see what is currently executing at that point in time which is why it is filtering for r.Command Is Not Null.
For example, to identify the head blocker you would comment out the line with r.Command Is Not Null - and modify the Order By to:
Order By [Head Blocker] desc, [Blocked By]
If you want to find only those processes for login 'xyz'
Where s.is_user_process = 1
And s.login_name = 'xyz'
And s.session_id <> @@spid -- don't need to show this session
The advantage over sp_who2 is that you control what is included and how it is sorted. Often times I need to find all processes connecting from a specific machine - with this I can easily include that in the where clause.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
January 29, 2020 at 10:56 am
the only problem I've found with scripting this stuff out is that locks are quite transient - you spot them and then they disappear (this is a good thing , locks protect you... but only good if it's for a short transaction)
Grant has been nagging me about switching from profiler to Extended events, and I can see where an XE trace could capture these locks for future analysis … grab all of the locks, all of the processes during the lock period.
there is a lot of stuff grant has put up at scarydba.com that might help capture lock info..... just don't tell him I said it was good, we are still arguing over the use of profiler
MVDBA
January 29, 2020 at 4:19 pm
Just to throw my 2 cents in this, I have a quick and dirty little script template that I did up ages back to help with sp_who2:
DECLARE @who2 TABLE
(
[SPID]VARCHAR(255)
, [Status]VARCHAR(255)
, [Login]VARCHAR(255)
, [HostName]VARCHAR(255)
, [BlkBy]VARCHAR(255)
, [DBName]VARCHAR(255)
, [Command]VARCHAR(255)
, [CPUTIME]BIGINT
, [DISKIO]BIGINT
, [LastBatch]VARCHAR(255)
, [ProgramName] VARCHAR(1024)
, [SPID2]VARCHAR(255)
, [REQUESTID]VARCHAR(255)
);
INSERT INTO @who2
EXEC [sys].[sp_who2];
Not 100% certain those are the same data types that sp_who2 spits back, but it works and is simple and is quick. I expect CPUTIME and DISKIO are more likely to be INTs rather than BIGINTs, but for something quick and light like this, I'd rather have some extra room. Re-reading that, I am pretty sure some of those datatypes are wrong. SPID and SPID2 for example are very unlikely to be VARCHAR's... and even if they ARE VARCHARs, they likely are not going to reach 255 characters. I have tested the above on SQL Server 2008 R2 through 2016 (all of the instances I am responsible for) and it works on them. May need tweaking if Microsoft ever adds new columns to sp_who2.
Using this, you can easily sort it or show whatever information you actually want to see. For example, if you want to see see all the blocking queries:
SELECT * FROM @who2
WHERE BlkBy NOT LIKE ' .'
I do agree with both Mike Vessey and Grant about using extended events. Failing that, profiler is good too. Profiler is easier to use and has a feature that XE lacks (or at least it lacked last time I looked at it) and that is the ability to replay. That being said, I have not needed to use that feature in a long time. Profiler also has a nice and easy GUI to work with whereas extended events feel more like an advanced feature as I have yet to find a good GUI for it. That being said, there is something to say for knowing how to script the commands. If your GUI ever quits on you or fails to load, knowing the commands is much nicer. OR if you want to deploy or configure something on multiple servers, using a GUI is very time consuming. Benefits to Extended events are that you can get a LOT more data and you don't get any "default" data (that may or may not be discarded depending on your settings).
For monitoring something like locks and blocking, if it is very short lived, you may not notice and/or care. Or your company might be quite strict about how long a block can last and you may need to troubleshoot more of them. At my workplace, if the block is short, we don't care. We even have some systems where blocking is common - 1-2 minute blocks are not uncommon. These are usually for 3rd party tools and the blocking is all on automated tasks and so far no end user has complained. But if they are long blocks, you may get phone calls. If you don't have a monitoring solution in place, there are quite a few out there and I would highly recommend getting one in place.
Last piece of advice I have is that if you are having trouble with SSMS when working with the GUI side on a specific SQL instance, try switching to a different SSMS version. After working with SSIS and learning the hard way that the SSMS version must match or your package will fail to run (imports successfully, but will fail to execute), I tend to match my SSMS to my SQL instance version when things misbehave on me. Not sure if activity monitor has that problem too, but I never user activity monitor.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
January 29, 2020 at 4:35 pm
Just to throw my 2 cents in this, I have a quick and dirty little script template that I did up ages back to help with sp_who2:
DECLARE @who2 TABLE
(
[SPID]VARCHAR(255)
, [Status]VARCHAR(255)
, [Login]VARCHAR(255)
, [HostName]VARCHAR(255)
, [BlkBy]VARCHAR(255)
, [DBName]VARCHAR(255)
, [Command]VARCHAR(255)
, [CPUTIME]BIGINT
, [DISKIO]BIGINT
, [LastBatch]VARCHAR(255)
, [ProgramName] VARCHAR(1024)
, [SPID2]VARCHAR(255)
, [REQUESTID]VARCHAR(255)
);
INSERT INTO @who2
EXEC [sys].[sp_who2];Not 100% certain those are the same data types that sp_who2 spits back, but it works and is simple and is quick. I expect CPUTIME and DISKIO are more likely to be INTs rather than BIGINTs, but for something quick and light like this, I'd rather have some extra room. Re-reading that, I am pretty sure some of those datatypes are wrong. SPID and SPID2 for example are very unlikely to be VARCHAR's... and even if they ARE VARCHARs, they likely are not going to reach 255 characters. I have tested the above on SQL Server 2008 R2 through 2016 (all of the instances I am responsible for) and it works on them. May need tweaking if Microsoft ever adds new columns to sp_who2.
Using this, you can easily sort it or show whatever information you actually want to see. For example, if you want to see see all the blocking queries:
SELECT * FROM @who2
WHERE BlkBy NOT LIKE ' .'I do agree with both Mike Vessey and Grant about using extended events. Failing that, profiler is good too. Profiler is easier to use and has a feature that XE lacks (or at least it lacked last time I looked at it) and that is the ability to replay. That being said, I have not needed to use that feature in a long time. Profiler also has a nice and easy GUI to work with whereas extended events feel more like an advanced feature as I have yet to find a good GUI for it.
where is the Heart emoji when you need it? - finally someone who gave me the killer blow in my banter with grant over profiler - "REPLAY!!!!"
saying that, whether you use XE or profiler it is eventually what makes you more productive and your systems faster.. for me (i'm old) I can script up a trace and have a library of trace files that can track a single user while they are experiencing issues in less than a minute - when I switch to XE I become less productive as an individual.
MVDBA
January 29, 2020 at 5:00 pm
The push to use extended events on SQL Server 2008 is what turned me away from being all in on them. And I had even downloaded and used the snap-in Jonathan Kehayias wrote but they were just to obnoxious to work with. I use extended events some now but still use profiler as well. I think if there wasn't all that screaming to use XE when it was a half baked product, it would have resulted in more adoption when they became more usable. I don't think having to shred XML helps much either. In general, if you have to scream for people to adopt something that is better (which extended events are better than profiler) then often it is something wrong with that product or feature. But you can't bring that up, people have all kinds of posts of how you can do this, that the other thing, how it's the same number of clicks or whatever. I followed along with some of those posts and never had the same experience as the authors and never went "Oh this is so much better than profiler". Too much focus on the people not adopting it, almost like a shaming thing, when it's the usability of the feature that's seems to be the issue for most. I rarely have ever ranted up here so I'll stop now. But it is one of my "rants" kind of thing.
January 29, 2020 at 5:12 pm
I rarely have ever ranted up here so I'll stop now. But it is one of my "rants" kind of thing.
It's not a rant - it's an opinion, and opinions are good - I know the article about the same number of clicks... I read it and maybe it softened my opinion a tiny bit.
I know I'll be using XE for my server upgrade (side by side comparison) but as bad as perfmon and profiler have been, they are my quick and dirty fast food tools
MVDBA
January 29, 2020 at 6:17 pm
Just to clarify - I didn't mean to point to anyone's blog in particular - KendraLittle and ScaryDBA are a couple of the best SQL server blogs out there. They do promote using XEs, followed their articles but I've always seen both come from a point of view of how to help others convert to XEs and not ones I ever meant "shaming" people about using Profiler. That's more related to some of the curt replies by others on forums and in general when someone mentions using Profiler.
My point was more that it should be more about the feature itself limiting the adoption rather than about the people not adopting it. I think it ended up differently in regards to XE. I still reach for profiler first and not because the articles I've read were poor or not encouraging. Very much the opposite. It's related to my experience when using the different tools.
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply