RedGate.SqlMonitor.Service.Web.exe Consuming 99% RAM in Version 13.0.41

Erick

Since I've upgraded SQL Monitor to version 13.0.41, the server keeps running out of available memory. Our resource monitoring tool says it's the "RedGate.SqlMonitor.Service.Web.exe" process consuming all of it. A server reboot is the only thing that fixes it. What's going on with this version? Is it related to the new Indexes tool? 

This new Indexes tool is also creating deadlocks in a few of our SQL instances too. It looks like there's no way to turn this preview feature off.

TheRealAndy

We upgraded from 13.0.37 to 13.0.39 and started having the same issue.  We upgraded to 13.0.40 and the issue persists.  If we're lucky, we can log in to the server and reboot it.  We've had a couple times where we've had to have our distributed systems folks kick the VM as the login dialog won't open.  There are also gaps in monitoring, sometimes a few minutes, sometimes a few hours.  Indexing was introduced in 13.0.38.  I opened a ticket yesterday. 

Erick

TheRealAndy said:

We upgraded from 13.0.37 to 13.0.39 and started having the same issue.  We upgraded to 13.0.40 and the issue persists.  If we're lucky, we can log in to the server and reboot it.  We've had a couple times where we've had to have our distributed systems folks kick the VM as the login dialog won't open.  There are also gaps in monitoring, sometimes a few minutes, sometimes a few hours.  I opened a ticket yesterday. 

Good to hear something else is experiencing resource issues. Let me know what you find out.

DonFerguson

I have also noticed stability issues with the product.  Though I can't pinpoint when the exactly problem started, it seems that it was sometime after version 13.0.35.   I will be starting a new thread with more detail. 

Erick

DonFerguson said:

I have also noticed stability issues with the product.  Though I can't pinpoint when the exactly problem started, it seems that it was sometime after version 13.0.35.   I will be starting a new thread with more detail. 

We upgraded to version 13.0.42 today because in the release notes, they state they are excluding indexes under system and internal tables from sampling. So perhaps changing that part of the Index feature makes SQL Monitor more stable? We will see.

DonFerguson

I upgraded to 13.0.42 yesterday and still had issues with the memory and loading problem today. 

Erick

DonFerguson said:

I upgraded to 13.0.42 yesterday and still had issues with the memory and loading problem today. 

Boo. Ok, in the meantime, I've scheduled a task to restart the Web Service service daily.

Hyperlance

We upgraded to 13.0.42 and got 100% CPU and we have had to disable it.

TheRealAndy

Erick said:

Since I've upgraded SQL Monitor to version 13.0.41, the server keeps running out of available memory. Our resource monitoring tool says it's the "RedGate.SqlMonitor.Service.Web.exe" process consuming all of it. A server reboot is the only thing that fixes it. What's going on with this version? Is it related to the new Indexes tool? 

This new Indexes tool is also creating deadlocks in a few of our SQL instances too. It looks like there's no way to turn this preview feature off.

I've turned off the indexes feature flag and SQL Monitor has been more stable.  SQLMONITOR_Indexes = 0 system environment variable:

DonFerguson

TheRealAndy said:

I've turned off the indexes feature flag and SQL Monitor has been more stable.  SQLMONITOR_Indexes = 0 system environment variable:

Interesting.  This is the new indexes tab feature mentioned in the release notes for 13.0.38 that you are referring to, right? I believe the new indexes tab feature, was added with version 3.0.38, which checks with the timeframe that I started having stability issues.  So perhaps disabling that feature until Red Gate works the kinks out will make it more stable.

I tried looking though the documentation for information on the environment variable  SQLMONITOR_Indexes and could not find any.  But I guess the answer would just be to set it as a system environment variable as you described above and see if it helps.  One question though that comes to mind since I run a multi-base monitor setup is would this need to be set on the Web host, the Base host or both?  Having to recycle the systems daily now is getting old and if it's due to this new feature, I am happy to disable it.

TheRealAndy

Correct.  I got SQLMONITOR_Indexes info from support.  Our environment is set up with the Base Monitor Service and Web Service on the same server.  I set the variable, rebooted and the Indexes tab is no longer available in SQL Moniter and it's been stable since (as opposed to needing to be rebooted daily).

chrisqtran

Hi all,

I can confirm that setting an environment variable, SQLMONITOR_Indexes to 0, then restarting the Base Monitor is a supported option to this issue. I will post here as soon as there's another update.

DonFerguson

I can also confirm that adding the SQLMONITOR_Indexes system environment variable with a value of 0 to the base monitors and restarting them does bring back the stability.

I have also received notification of the release of 13.0.43 this morning; however, it appears that release notes have not been published yet.  Does 13.0.43 address the stability issue introduced by the Indexes feature?

DonFerguson

So it looks like the 13.0.43 release notes, once published,  sort of answered my question.  In that 13.0.43 turns off the indexing feature by default.  Instead it needs to be manually enabled if you want the feature and don't have stability problems with the product using it.  Which for now I am fine with.

Redgate team, once you identify the cause of the stability issue with the indexes feature and address it, please let us know so we can confirm.

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?

Erick

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?

I've removed the environment variable on our server and rebooted. I'll let you know how things behave over the next few days.

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?

Tom_C,
I'm glad to hear that this may be addressed but I need some clarification on your instructions. 

Per the documentation at https://documentation.red-gate.com/sm13/indexes-225607686.html it says the following: 

In order to enable the indexes tab and its associated sampler, you need to enable the Indexes feature flag.

For SQL Monitor versions from 13.0.38 to 13.0.42 this is enabled by default. From 13.0.43 this is disabled by default due to high memory usage in some situations.

You can use a different value than the default by setting the environment variable <i><b>SQLMONITOR_Indexes</b></i> to <b><i>on</i></b> or <b><i>off</i></b>. This needs to be done on the host machines for both the Website and any Base Monitors you wish to sample indexes on.

In order for the change to make its effect, you will need to restart your SQL Monitor Base Monitor and the SQL Monitor Web services.

My question is: Has the default behavior reverted back to SQLMONITOR_indexes being enabled by default?

So what it seems you are implying is that we need to just remove the environment variable and it will default to indexes being enabled.   So if we still want it disabled we need to keep the environment variable, which conflicts with what the documentation is stating. 

Also one other point is that previous instructions said to set the value to 1 or 0 as opposed to on or off.  Does it matter?      

DonFerguson

I believe I was able to answer my own questions via testing.

The default behavior for 13.0.44 is to leave the indexes option disabled.  As per documentation, one has to explicitly set the system environment to reenable the indexing feature. 

I went ahead and tested using on and off since that's what the documentation says to use.  Though I suspect that 1 or 0 would work as well.  Just note that I did not test with 1 or 0 for this release given that on and off work.  Though I can confirm that a 0 value worked perfectly fine to disable the feature with version 13.0.42.

And now for the stability question.  Unfortunately it's still an issue.

After setting the SQLMONITOR_indexes system environment variable on all hosts (both web and base) to a value of on and restarting the service for all hosts, I almost imediatly had the issue with the Overview page stuck on loading again.  So I set the value for SQLMONITOR_indexes back to off and restarted once again.

I don't want to discourage other users from testing the feature, which does need to be explicitly set, but my experience unfortunately was negative.

Erick

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?

I've removed the environment variable on our server and rebooted. I'll let you know how things behave over the next few days.

Erick

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?

I've removed the environment variable on our server and rebooted. I'll let you know how things behave over the next few days.

TheRealAndy

Thanks for the info on your testing @DonFerguson .  I was curious if 13.0.44 included performance improvements to the indexing feature or just disabled the indexing feature by default.  Based on your testing sounds like more the latter than the former.

Tom_C

Apologies for the delayed response on this forum post, our development team included several fixes in 13.0.45 aimed at addressing this issue which looks to have worked for the majority of those reporting this feature exhausting memory.

There are still a few who are still seeing higher memory usage but these each look to be slightly different and so we're continuing to investigate these individually, I would encourage anyone else who experiences an issue with memory maxing out after enabling this feature (post 13.0.45) to contact/raise a ticket support directly so we can arrange to look at logs and dump files for diagnosis.

Erick

Tom_C said:

Apologies for the delayed response on this forum post, our development team included several fixes in 13.0.45 aimed at addressing this issue which looks to have worked for the majority of those reporting this feature exhausting memory.

There are still a few who are still seeing higher memory usage but these each look to be slightly different and so we're continuing to investigate these individually, I would encourage anyone else who experiences an issue with memory maxing out after enabling this feature (post 13.0.45) to contact/raise a ticket support directly so we can arrange to look at logs and dump files for diagnosis.

Thanks for posting on this thread. We no longer have the memory issue after upgraded to 13.0.45+. Thank you for resolving the issue. It's nice to have a stable app again :-)