How can I determine how much RAM and Processer each RDP session is using on a terminal server

Question

I have a table called measurement_stat that takes the following form:

id     sensor_name              timestamp           value_cal 
1   measurement_status    2020-07-28 16:00:00    start_measurement
2   measurement_status    2020-07-28 17:00:00    stop_measurement
3   measurement_status    2020-07-28 18:00:00    start_measurement
4   measurement_status    2020-07-28 19:00:00    stop_measurement
5   measurement_status    2020-07-28 20:00:00    start_measurement
...          

I want to create a VIEW that returns this:

id      start_measurement      stop_measurement
1      2020-07-28 16:00:00    2020-07-28 17:00:00
2      2020-07-28 18:00:00    2020-07-28 19:00:00
...

How do I do this? I have tried doing this:

CREATE VIEW start_times AS 
   SELECT timestamp AS start_measurements FROM measurement_stat
      WHERE sensor_name = 'measurement_stat' AND value_cal = 'start_measurement'

Then I do the same for stop_times. Finally I perform a LEFT OUTER JOIN on the two VIEWs that I just created.

This works but, it has a problem. Occasionally while taking data if something goes wrong, we have to stop the measurement and sometimes 'stop_measurement' doesn't log. So there is a start_measurement with no associated stop_measurement. This issue throws my VIEW off and the columns become out of sync. If this starts happening often, then problem just compounds and gets even worse.

Is there a way of handling this in SQL? It would be great if I could throw in NULLs in place of 'stop_measurement' if there are 2 consecutive 'starts' without a 'stop' in between

Solution

You'll do better to approach this holistically - monitor all of the servers, including active connected RDP session count. Then divide the total performance numbers at any given time, by the number of users at the same time, to get your per-user resource usage.

/Edit - d'oh. Of course that doesn't count resource usage of the OS, which is what the performance numbers look like when session count is 0.

You should also be tracking these metrics with something like Perfmon, or Nagios, or Solarwinds - not by logging in and looking at Task Manager from time to time.

Doing an artificial test like you described won't be a true reflection of

1. How many resources that actual user uses or
2. What the average user session looks like.

Comments

[Zoredache]

Since you have 4 servers in a production setup, I would be strongly tempted to disable one, or maybe two and see what impact is has. Looking at a single user just doesn't make a lot of sense. I am assuming you have already planned for some redundancy, so test it a bit.

[Mike Organek]

What do you want if there are two consecutive stop_measurement records?

[pmoh]

This is an unlikely situation because we can only stop a measurement if it was started in the first place. However, if this were to happen then, I want to fill in a NULL for start_measurement in between the 2 stop_measurement records.

[Mike]

I am not sure if I completely understand the method. so I just logged into "Remote Desktop Service Manager" of the server. I can see there is 32 active sessions. Then when looking as "Windows Task Manager" I see that 8GB of RAM is being used and 11% of processor. does this mean that on average each user is using 256kb of RAM? this just sounds small.

[mfinni]

Edited my answer. And check your math - 8 GB / 32 users is about 256 MB, not KB.

[Mike]

yes that is a typo :) thanks for the correction and for your help.

[pmoh]

Thank you so much!! This worked after I filtered out a couple of other sensor names that I hadn't mentioned.