We may have a memory leak in the SQL Base Monitor after 13.0.38

DonFerguson

(Updated title and content from 13.0.35 to 13.0.38 for better accuracy)

I suspect that a memory leak may have been introduced into the base monitor product around the release of 13.0.38. The symptom I am experiencing is that if a base monitor gets jammed up, it will cause the Overview page to get stuck on loading, nothing updates, it just spins on loading.  Recycling all the base monitors and all the web monitors generally resolves the problem.  This seems to happen every couple of days or so and to multiple systems in the multibase configuration around the same general timeframe.  When this happens we also get alerts indicating that all the free memory is being used, again symptomatic of a memory leak.  

TheRealAndy

I have a ticket open for this right now.  In our case, we are unable to recycle the services and have to reboot the server.  We've also had to kick the VM as we've been unable to even get logged into it.  I believe it started after 13.0.37 (we didn't have issue at 13.0.37).

IanKeogh

I have to the same issue and for me it started after 13.0.39 I believe. I have opened a ticket for this. 

chrisqtran

Hi all,

Thank you for bringing this to our attention.

Can we get y'all to attempt to add an environment variable for this?
Please set an environment variable SQLMONITOR_Indexes to 0, then restart the Base Monitor.

DonFerguson

Thank you chrisqtran for confirming that the SQLMONITOR_Indexes system environment variable setting needs to be applied to the Base Monitor.   I have now implemented that work around and will let you know if that resolves the stability issue for me.  Let me give it a couple of days and if I don't need to restart SQLMonitor we will likely know.

AlexRounds

I am having the same issue and have version 13.0.42 installed. I am not quite sure how and where to set an environment variable to potentially fix this issue. 

jstanlick

I am so happy I found this thread! Our base monitor has been chewing up memory and requiring service restarts daily since we upgraded from 13.0.34 to 13.0.40 last Thursday. Other things were patched on the server last week as part of our monthly patch cycle and I've been digging through them all trying to figure out what caused the memory leak. I'm adding the environment variable and we'll see what happens.

DonFerguson

On your base monitor host navigate to "Advanced system settings", select the advanced tab and click Environment Variables.  



Then add the new system variable (under system variables not user variables)
SQLMonitor_indexes and set it with a value of 0



After that restart SQL Monitor Base Monitor 

in services.msc

Do that for all hosts that you are running SQL Monitor Base Monitor on.

DonFerguson

Also see similar thread:

https://forum.red-gate.com/discussion/90254/redgate-sqlmonitor-service-web-exe-consuming-99-ram-in-version-13-0-41

jstanlick

AlexRounds said:

I am having the same issue and have version 13.0.42 installed. I am not quite sure how and where to set an environment variable to potentially fix this issue. 

1. On the Windows taskbar, right-click the Windows icon and select System.
2. In the Settings window, under Related Settings, click Advanced system settings.
3. On the Advanced tab, click Environment Variables.
4. Click New to create a new environment variable. Click Edit to modify an existing environment variable.
5. After creating or modifying the environment variable, click Apply and then OK to have the change take effect.

** Assuming you have the appropriate permissions to change the environment variables on the machine. **

Tom_C

Hi all,

Many thanks for your help and patience on this.
 
Yesterday our development team released 13.0.44 aimed at addressing the high memory issues (from index sampling) and this can be downloaded here.
 
Appreciate you may not be able to update immediately but once you have could I please ask you to remove the previously added environment variable, restart services and let us know if you believe this is looking stable again?

DonFerguson

Tom_C said:

Hi all,

Many thanks for your help and patience on this.
 
Yesterday our development team released 13.0.44 aimed at addressing the high memory issues (from index sampling) and this can be downloaded here.
 
Appreciate you may not be able to update immediately but once you have could I please ask you to remove the previously added environment variable, restart services and let us know if you believe this is looking stable again?

Unfortunately it's still an issue as I posted in the other thread.

Jon_Kirkwood

Sorry to hear that DonFerguson,

The indexing performance concern has been front of scope for our developers.
V13.0.45 includes some more memory usage fixes for indexes

Can I ask that you patch at your earliest convenience and confirm if this is an ongoing concern for you

Download link: 
https://download.red-gate.com/checkforupdates/SQLMonitorWeb/SQLMonitorWeb_13.0.45.30059.exe

Patch notes:
https://documentation.red-gate.com/sm/release-notes-and-other-versions/sql-monitor-13-0-release-notes

Fixes

• Properties filter on Alert Inbox now also works when only some properties are selected.
• Speculative fix for worker threads being consumed by extended events by allowing multiple events to be lost under heavy load.
• Last used property on the indexes table now correctly shows the last used date after a SQL Server restart.
• Fix SQL Server Licensing Estate page chart headers.
• Improved memory usage for indexes sampling for indexes with partitioning.

TheRealAndy

I installed V13.0.45 yesterday and it has been stable, so far.  We'll see what the next few days bring, but certainly an improvement.   

DonFerguson

I did install 13.0.45 Wednesday evening and turned enabled the SQLMONITOR_indexes setting.  When I came in the next day, one of the base monitors had crashed.  I also observed that some base monitors were maxed out at close to 100% CPU and memory utilization.  I also observed very sluggish behavior when refreshing the SQL Monitor overview dashboard.  In that all tiles would be hung up on loading for about 1 minute, which was too long for me, but I will acknowledge that it was an improvement over what it was before when the "Loading..." condition would not correct with previous versions. Still I felt compelled to disable the SQLMONITOR_indexes setting once again as I feel that the poor performance is not worth the feature.

One observation and a suggestion is that in a multi-base configuration, if just one base monitor is acting up it brings everything down without providing any indication about which base monitor is the problem.  So I would suggest that instead of hanging the whole multi-base dashboard up on "Loading...", it might help if it could move on and at least indicate which base monitor is causing the issue. 

Tom_C

Hi @DonFerguson,

I'm really sorry the issue with high CPU has persisted for you despite updating. Have sent you a message from an internal support ticket to arrange gathering some additional information for our development team to investigate why the issue looks to persist for you.

TheRealAndy

We've been stable since installing V13.0.45 (5 days ago).

IanKeogh

Base Monitor for 13.0.45 is consuming about 10GB of memory when the index feature is enabled vs 1GB when the index feature is disabled. for us we have disable the feature as it is not worth the excessive memory footprint.

Tom_C

Hi @IanKeogh,
Apologies for the delayed response, have just opened an internal ticket and will be in contact shortly to investigate this with you.