November 16, 2023 at 3:34 pm
Can we capture memory usage continously ?
November 16, 2023 at 7:12 pm
Are you meaning per query or overall? and what do you mean by "continuously"? Like you want a realtime constant monitoring of overall memory usage? If so, then the easiest way to do that is with WMI or perfmon. If it is per-query, that's a hard thing to capture "continuously" as queries tend to start and stop and once they complete, some data remains in memory, but that specific session may not be active. If you mean overall, task manager will provide that information to you. Once SQL gets memory, it is not going to give it up unless something forces it to.
In general, the queries you run against SQL Server are all for point in time information. If you need to continuously monitor something, you would need to use 3rd party tools. SSMS does have an "activity monitor" but that thing is a performance hog and if you watch it long term, it can get sluggish and sometimes even stop refreshing the data.
The question I'd have for you though is why? Why do you need this data to be captured continuously? What will that information help you with?
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.
November 21, 2023 at 4:40 am
one of our servers : memory usage is now 98%, which is above the error threshold. We keep getting this message frequently.
November 21, 2023 at 8:21 am
have a look at these https://www.webfx.com/blog/web-design/10-free-server-network-monitoring-tools-that-kick-ass/
my company, for some of their servers is now using https://www.zabbix.com/
November 21, 2023 at 2:21 pm
It sounds like you have a tool that monitors memory usage on the server. If you want a per-process app that'll do that, perfmon or task manager will monitor your memory usage and are pre-installed on the computer. Something to note though - running any tools ON the server will use up some resources (CPU, memory), so I would recommend using a perfmon and running it on a secondary machine to capture metrics on the server. Free tool that comes with Windows and as long as the proper ports are open and proper permissions are applied to the user account, you can pull all sorts of metrics out of it.
Since it sounds like the alert is at the OS level, not the application level (do correct me if I am wrong on this), it MAY or MAY NOT be SQL that is using all the memory. If it is SQL, I would recommend looking at the max memory value you have assigned and if it is default (2 PB), you may want to look at decreasing that value to something reasonable.
To add to the above though, having "98%" used memory may not be a bad thing. If you have 256 GB of memory in the system, 2% is still 5 GB of memory (approximately) that is unused. Now if you have 64 GB of memory in the system, that's only 1.2 GB of free memory and I'd be concerned that the OS may not have enough free memory. My preference when setting up alerts is to set them up at a GB level, not percent; similar to how I set up autogrow. I like my OS to have 4 GB free so I can start a new session (ie RDP in) if needed (which is rare) and for the OS to use, and then the rest is allocated to SQL and other applications. When I am provisioning a new system, I get the bare OS running, then check how much memory is in use. It varies depending on what is installed on it by IT post OS, so that gives me a baseline of how much the OS needs. Then I add 4 GB so that I have enough for an RDP session, and the remaining goes off to SQL Server. I do work with IT on it as well though as I may have missed some scheduled processes such as an antivirus and I don't want their tools to crash or cause high CPU usage due to limited memory.
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply