Activity Monitor Cpu

0 views

Skip to first unread message

Alysa Guillama

unread,

Aug 3, 2024, 4:59:13 PM8/3/24

to proflearthecit

This application allows Zwift users to monitor their moving average power and FTP in real-time. It also provides valuable metrics to the rider such as average and normalized power (NP), intensity factor (IF), and training stress score (TSS). For the...

I am up for security updates, but PLEASE ZWIFT, work with the great community projects that are out there BEFORE you roll such things out to ensure it can continue to be operational - or at least look to incorporate the functionality in game.

@Rich You can see the features in the video titled: ZAM Pro New Features on the EnJoy Fitness YouTube page. It is also posted on the Zwift Activity Monitor Users Group. I have been able to PR the Uber Pretzel by putting in goal splits for every 2 miles. I was able to manage my effort for 4 hours and PR knowing exactly how I was pacing. I also dropped to a C and used ZAM during an iTT to get my 20 minutes at 4.4 so I could move back up to a B cat.

To expand on this a bit, sp_whoisactive works very well as part of an automated monitoring solution. Capture wait stats, active processes, get a look back in time to see what was slowing things down last night or last week.

one of our developers switched it on in search of his bad performing queries we found in the top of a top50 most resource consuming queries.
now he left AM running apparently and after 500 excecutions it settled itself in the top50 at place 2 aiming for better

I went looking to see if this count can be trusted. Is it condensing queries with the same form but different constraint values (string GUIDs for example)? Or is it trying to tell me that 33k of the exact displayed query executed with the exact same values? Again, hard to believe.

Any help or insights would be appreciated. I'm not a fan of switching back and forth between two management studios if I can help it. (I uses 17 so I can have context menus when right clicking on items in SSMS which wont work on 2016 servers in older versions of the studio).

I setup a basic SQL login and found that activity monitor was permanently paused for this login. Then I granted this login the "View server state" permission and activity monitor now works. To do this, open up the Security and Logins folders for the relevant server instance, right click a login and choose properties. Choose Securables and you should see all Permissions listed in the bottom pane. Put a tick in the grant column next to "View server state".

However I did have access via the sa user to our dev-environment. Using the sa user did the trick in dev, and the same applied for our production environment, leading me to assume that this is connected with privileges/roles on my user.

In the environments that I manage, that only happens when I am using SSMS in a different computer, other than the server where actually the SQL Server ENGINE is installed. That is: SSMS client on a PC, and SQL Server engine/instance in a server. For me, 99% of the time this means: I am in Florida running a local PC Virtual Machine on my personal iMacPro, running SSMS, accessing a SQL Server server in Chicago via VPN.

EDIT: Also, I notice that it's when I expand the processes panel that shortly thereafter it pauses. If I leave the processes panel collapsed it does not happen, or at least not as promptly. AND, interestingly, if I open Activity Monitor, then I do NOT immediately open the process panel and let the graphs run for a while, say, two minutes, and THEN I open the process panel it does not pause anymore.

It seems to be that the initial population of the graphs AND the initial population of the process panel at the same time that cause the problem. At least that's the case for me across the SQL Servers I manage.

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.

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:

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.

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.

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.

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.

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)

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.

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.