v5 Horrendously Slow UI

PDinCAPDinCA Posts: 642 Silver 1
edited January 12, 2016 11:39AM in SQL Monitor Previous Versions
Traversing just about anywhere in the revamped UI is so slow! Average of FIFTEEN seconds between page loads, and frequently longer when drilling down just one level from the Overview page.

This was reported for v4 over 16 months ago, but there's no posted cause or solution.

It's running on a Standard_A4 Azure instance - 8 cores, 14GB RAM, ONLY App running. Cores aren't spiking. Disks are averaging under 600KB/s.

Attempting to monitor FIFTEEN instances using this UI is indescribably hopeless, even when RDP'd onto the Azure box itself.

What on earth can we tune to get something like usable performance out of this thing? Seriously...
Jesus Christ: Lunatic, liar or Lord?
Decide wisely...
«1

Comments

  • On top of this (I experience the same); the new UI is so "big" that I have to zoom out to see things correctly (especially the analysis page) even using a screen res of 1920x1080!
  • PDinCA and jchorlton,

    Could you both let me know how many databases and Instances you are monitoring? Could you also let me know the current size of the data repository database?

    If you could also let me know how much memory the RedGate.Response.Base.Service is using.

    jchorlton did you also see a slow performance in SQL Monitor 4?
    Kind regards,
    Dan Bainbridge
    Product Support Engineer | Redgate Software
  • PDinCAPDinCA Posts: 642 Silver 1
    Eighteen instances. Typically 2 user databases per.

    Memory usage: 818,108 KB, peak: 1,277,084 KB

    CPU usage: typically 20 to 45%

    UI.Server.Service: 22,396 KB, same peak.

    UI is near-constantly "Loading" - it may take a 2-second break, but it's then back to another TWENTY-FIVE seconds of Loading!

    It takes over TWENTY SECONDS just to bring up the Alerts page.

    Perhaps the fact that we are retaining up to 30 days of history is contributing to this poor performance? The data file is over 120GB, with just 3GB free - another performance inhibitor, perhaps?

    I completely agree with @jchortlton - the UI is (IMO, stupidly) too big! I used to fit 25 rows of Alerts comfortable on a page. Now, with an even bigger monitor that has 1440 vertical pixels, I now see 23 rows before I scroll, and my "choices" (laughable) are still 10,25,50 and 100. Has nobody heard of a custom number per page? If this redesign was aimed at the visually impaired, or some other section of the population experiencing some usability impairment, that is laudable, and understandable, but please, give us options, like even Google does with Compact, Cozy, etc., so those who have less screen real estate and need to see the "meat" can adjust to that. All that whitespace at the top of the page: really? Why is the big, fat, "Filter" button-drop on its own, taking up 3/4 of an inch of my page? Stick it with the line above, for goodness sake... Your Monitored Servers tree clearly, readably, shows SIXTEEN servers in the space that 9.5 Alert entries consume. That's a ridiculous wastage of space on the Alerts pane. Use the same spacing, at least as an option.

    In summary, re the UI. RETROGRADE STEP
    Jesus Christ: Lunatic, liar or Lord?
    Decide wisely...
  • PDinCA

    There is nothing overly worrying there.

    Could you confirm if the Base monitor, the data repository, and the web server are all installed on the same machine?

    Can you also confirm the data repository responds reasonably well to other SQL calls outside of SQL monitor?

    Another useful bit of information would be the number of alerts you have in SQL monitor?
    Kind regards,
    Dan Bainbridge
    Product Support Engineer | Redgate Software
  • PDinCAPDinCA Posts: 642 Silver 1
    All 3 are definitely on the same machine.

    2 seconds to count 1087 alert.Alert rows. Uncleared alerts: 757 High, 63 Medium, 80 Low, 869 Unread, 67 in last 24 hrs.

    37 seconds to run sp_blitz

    Under 2 seconds to get row and KB counts for the 234 tables in the RG DB.

    Thus, I'd say the DB is decently responsive, albeit the SSMS connection took a long time to come up - over 10 seconds, but with an A4 Azure box, who can tell what's normal firing up a beast like VS.

    If you want the sp_blitz output and the row/KB counts, I have two tab-delimited files...


    I'm a little disturbed by your opening comment, "There is nothing overly worrying there." - respectfully, I would strongly dispute that assertion. An essentially unusable product costing US thousands of dollars should be of concern to YOU!
    Jesus Christ: Lunatic, liar or Lord?
    Decide wisely...
  • Hi PDinCA,

    Firstly I would like to apologise for my poor wording giving you the wrong impression. What I meant to express was nothing in your post seemed to be a major cause for the slowness issues you are having. The fact you are having problems is a huge concern to us and something we would like to resolve as soon as possible.

    Can you confirm which browser you are using to access SQL Monitor in? Have you tried an alternative browser? If so was there any noticeable loading differences.

    Could you also confirm if you are hosting SQL monitor using IIS or the SQL Monitor Web Service?

    Regarding the UI, there are plans for further work on this in future minor versions. Currently, I have no concrete news on any versions containing any updates at present.
    Kind regards,
    Dan Bainbridge
    Product Support Engineer | Redgate Software
  • I am in full agreement with PDinCA and jchorlton.

    I am monitoring nearly 40 Servers, have over 6 months of select data, 60 days of other select data and 4weeks of specific data we are retaining.

    Presently we have under 1000 alerts only because I have spent two days trying to get this "upgrade" to stop eating 98% of the CPU all the time so that I can clear them without the 30-45 second load times.

    The website and monitor are all on the same box and the SQL Server is on a separate server.

    We are using IIS.

    Red Gate any guidance on how to stop your bloated UI from eating all the CPU processing (now that we have thrown 12 at it and its still chewing up nearly 90%) would be helpful. We spent a ridiculous amount of money to license 40 servers and we are on the verge of dumping this software since the "downgrade" to something else.
  • Hi dbasfcspencer,

    For PDinCA and yourself - Is it specifically the UI process (in your instance the IIS process, for XSP the RedGate.Response.UI.Server.Service.exe process) or is it the Base Monitor process (RedGate.Response.Engine.Alerting.Base.Service.exe) that is taking up the CPU?

    The UI itself shouldn't be the cause as it only speaks to the Base Monitor, which queries the Data Repository so the speed issues, in theory, should be related to that portion of the process. As you can see in the thread we are looking at the size of the repository for performance issues. I understand that the sentiment in this thread is "the UI is too large" and we've passed that feedback to the team.

    Also, are you hosting your SQL Monitor in Azure as well like PDinCA and jchorlton? Hosting and monitoring in Azure and other cloud services isn't officially supported though it can work (and it is on the roadmap for official support) and we are trying to determine if the performance issues are specific to SQL Monitor or to the environment. I believe jchorlton was previously not using v4 in Azure so the comparison may not be 1-1 with regards to his previous performance experience; I'm not sure what PDinCA's circumstances are in this regard - please let me know if you were previously hosting v4 in Azure and experiencing better performance.

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • PDinCAPDinCA Posts: 642 Silver 1
    v4 was in Azure, on an A2, I believe, which was in itself very slow. Upgraded to v5, and the swiftly published v5 hotfix. Bumped to A4 - no change. Bumped to A7, still slow as molasses.

    8 vCPUs, 40% activity. Base Service @ 25%, UI Service flat-zero.

    Latency not an issue. Q-depth not an issue.

    Just poked the UI to get an Alerts list. Base jumped to 50%+. 1,053 Alerts only.

    Maybe the base service is so busy pulling, shredding, and posting acquired data that it doesn't treat UI requests with any degree of reasonable priority...?
    Jesus Christ: Lunatic, liar or Lord?
    Decide wisely...
  • PDinCAPDinCA Posts: 642 Silver 1
    Thanks for moving this entire thread to the current SQL Monitor 5 forum.
    Jesus Christ: Lunatic, liar or Lord?
    Decide wisely...
  • PDinCAPDinCA Posts: 642 Silver 1
    Drastic measure taken to test UI responsiveness:
      SUSPEND ALL MONITORING!

    Network traffic flat-lined.

    DB activity below 10ms latency.

    "Give me the Global Overview" - Twenty-five SECONDS later - methinks you have a UI/DB-code code problem, chaps, 'cos the services were collecting nothing whatsoever!

    So, we rebooted the server.

    FORTY SECONDS after login, Global Overview, and we are monitoring nothing whatsoever.

    What is going on?
    Jesus Christ: Lunatic, liar or Lord?
    Decide wisely...
  • Hi PDinCA,

    Not a problem for getting this in the right place and sorry I missed the request in the first instance!

    With your last test it seems even more likely that the issue is with Azure itself; the avenue we were investigating for an issue in Monitor was related to slowness getting info from the database to display. My colleague Dan sent you one article on why this may be the case (which I will put below for other users) and has been doing further research into this and should be sending you some further information, which he will post here as well. I will also reiterate that hosting on Azure and monitoring Azure machines is not supported, though it is on the roadmap - I will point out that there are distinctions between support for hosting in Azure and support for monitoring a machine in Azure, the latter of which will likely come first. Are you able to install this on a local machine and/or VM (see this for use on a VM) to see how the UI behaves for your setup there?

    We admit the v5 UI is a bit slower than the v4 UI and we are aware of this (I'm still trying to get actual values for this), but it shouldn't be to the degree you are seeing. We have quite a few customers using v5, some with large numbers of monitored servers, and they are not reporting slowness, much less to the degree you are seeing.

    http://weblog.west-wind.com/posts/2015/ ... nce-Battle

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • Hi PDinCA,

    Whilst researching these issues I have found several articles referencing poor performance on Azure machines especially when running SQL Server applications.

    This article discusses the best Azure machine for running SQL server but also specifies that even the top end D specced machines could still have some performance issues.

    http://sqlperformance.com/2014/12/io-su ... 4-on-azure

    I believe I have already sent this article regarding a separate instance of a user being unable to set up an effective Azure install vs his own physical server.

    http://weblog.west-wind.com/posts/2015/ ... nce-Battle

    This is quite a thorough 2 part guide for running SQL Server on azure machines and goes along with the third link which shows best practices by Microsoft for trying to do so.

    https://www.azuremsp.com/blog/SQL%20Ser ... 20Part%201
    https://www.azuremsp.com/blog/SQL%20Ser ... 20Part%202

    https://azure.microsoft.com/en-gb/docum ... practices/

    There is a lot of information here on tuning your azure environment which should help improve your experience with SQL Monitor 5. I hope you find this useful. If you could let me know your thoughts on the above and if you see any improvements implementing anything that is contained here that would be great.
    Kind regards,
    Dan Bainbridge
    Product Support Engineer | Redgate Software
  • DonFergusonDonFerguson San Diego, CAPosts: 132 Silver 2
    We admit the v5 UI is a bit slower than the v4 UI and we are aware of this (I'm still trying to get actual values for this), but it shouldn't be to the degree you are seeing. We have quite a few customers using v5, some with large numbers of monitored servers, and they are not reporting slowness, much less to the degree you are seeing.

    As a customer that monitors a large number of servers, I do want to report that performance with v5 is significantly worse.
  • Since upgrading from V4.2.0.243 to V5.0.2 yesterday SQL Monitor has become totally unusable due to the response time. The UI takes a minimum of 10 minutes to respond to anything before I get a refreshed screen.

    Despite doubling the cpu in the server with the services on there has been no change in performance.

    I'm going to have to take the drastic step of uninstalling and reverting back to V4.2 unless there is a quick fix
  • DonFergusonDonFerguson San Diego, CAPosts: 132 Silver 2
    Ditto, on one of my monitors. Totally unusable I will need to rollback. Red-Gate Support, please tell us why the v5 version is sucking up so much more CPU load on the base monitor than the 4.x versions? What activity is accounting for the additional load? Is there a way to configure the settings to be less aggressive?
  • DonFergusonDonFerguson San Diego, CAPosts: 132 Silver 2
    I went ahead and rolled two of my base monitor/ webserver pairs back to version 4.3 and CPU utilization and interface responsiveness immediately returned to normal on both systems. I have opened a case with Red-Gate support, hopping to find resolution to this issue.
  • I have a support call open now too.

    I've 4 Cpu on the server where the web server and base monitor are located and its steady between 30 and 50% usage and there's plenty of free memory (6GB free)
    This is also the case with the server where the SQL db is located.

    Never had any response issues on any previous versions - this is clearly a UI issue as the app itself is still working behind the scenes and firing out email alerts etc.

    Groups are also not working. Even on the config screen it only shows 1 server for each group and you cant expand the group.
  • Hi all,

    Sorry to everyone who's experiencing this. Just to confirm, we are currently looking into this performance regression - all signs point towards an issue with caching in SQL Monitor 5.0.2, so rolling back to v5.0.1 or v5.0.0 will likely improve things. We hope to have a fix for you early next week.

    Thanks

    Daniel Rothig
    Product Manager for SQL Monitor
    Redgate
  • Hi there, where can we download 5.0.1 and are there instructions to roll back?

    Thanks,
    Anthony
  • Hi there, are there instructions for downgrading and where can we get previous versions? I cannot find a recent version on http://www.red-gate.com/products/old-ve ... ef=account

    Thanks,
    Anthony
  • PDinCAPDinCA Posts: 642 Silver 1
    Downgrading within 5.x is OK, but for those wanting to peel back an entire major release, that's predicated on your having a full DB backup for when running 4.x

    The 5.x upgrade warning says it's irreversible in regards to a schema rollback, which is a pity for those who endured long enough for their retained backups to have cycled through so that only 5.x backups exist (oops!).

    Rule of thumb: always take at least a copy_only full backup before ANY SQL Monitor update, within or across versions, so you can go back.

    Hope you have a backup if you need one...
    Jesus Christ: Lunatic, liar or Lord?
    Decide wisely...
  • I took a decision to delete my existing Monitor db (135GB) and create a brand new one rather than revert to the old v4.

    The response times now are a few seconds rather than several minutes.

    My new db has 3GB of data in after just a couple of days so obviously the UI has hardly anything to process.

    I'd be interested to know the Monitor db sizes of other slow response sufferers.

    Incidentally, my old db had its purging levels at 1 month on most settings and 1 week on a couple of the high volume data.
  • PDinCAPDinCA Posts: 642 Silver 1
    We're at 90GB monitoring 18 instances with nothing older than two weeks and the high volume data down to just one week. Regular defrag in place. Nothing we do improves response times.
    Jesus Christ: Lunatic, liar or Lord?
    Decide wisely...
  • We are monitoring 17 instances and have been for at least 12 months, yet our Monitor db has grown much faster and larger than expected despite our purging settings.

    if you are still struggling with response times but don't want to rollback to V4 it might be worth starting with a new db like I have done but you'll have to go through changing all your settings again though which may take a while.
    Not having the groups working at all is still a major pain too.
  • Hi All,

    I want to point out that Daniels comment above is specific to version 5.0.2 while the beginning of this thread is from earlier versions of 5.0 when installed on Azure. SQL Monitor version 5.0.3 has been released (see this sticky thread) and fixes Groups and has some changes that we believe will improve performance (at least to earlier 5.0 version level). I have given it to one customer already which in their case did reduce the CPU usage considerably and make the UI responsive (according to them).

    We have a more detailed roadmap out now so I would like to bring it to attention here; the support for Azure, Amazon EC2 (Iaas) is coming in 5.1 along with performance improvements. As it says in the link this is planned for end of this year / beginning of 2016.

    In version 5 of SQL Monitor more data is being collected due to the additional support for Availability Groups (this data wasn't being collected before and now is, hence more data) which may or may not be the exact cause of the increased space consumption, but just thought I would throw it out there.

    For Anthony:
    5.0.0.1264: ftp://support.red-gate.com/patches/SqlM ... 0.1264.exe
    5.0.1.1302: ftp://support.red-gate.com/patches/SqlM ... torWeb.exe

    As PDinCA suggests take a full backup of your database before version changes where there is a schema change (as is the case in 5.0.3 as well so everyone knows) - this way you can roll back by uninstalling SQM, restoring the DB, then installing the earlier version associated with that schema in your backup.

    I tried to get to all the different points but if I missed one please let me know! Time to go home now :-)

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • FYI: The upgrade to 5.0.3.1858 appears to have solved the performance issue introduced by 5.0.2.1644. CPU utilization is back to where it was when we were on 5.0.1.1302, and the response time of the UI is back to normal as well. Not sure that this will fix the earlier complaints about the UI from the beginning of this thread, but it did fix the issues introduced in version 5.0.2 for us.
  • DonFergusonDonFerguson San Diego, CAPosts: 132 Silver 2
    While 5.0.3 is a definite improvement over 5.0.2, it still has a "Horrendously Slow UI". Using a system with over 62 instances on it made managing groups takes multiple minutes between screen refreshes. While a realize that this is a lot of instances, this was not an issue with version 4.3. I ended up rolling v5.0.3 back to v4.3 and now performance is once again snappy and fast. I have a case open with support and they are working on a fix, but until the fix is released, I warn anyone with lots of instances (and availability groups) to hold off on upgrade or proceed with extreme caution until this issue is fixed.
  • Hi All,

    For people experiencing slowness v5 that was not present in V4 - we have found an issue with having a large number of groups (>~20) defined in Configuration > Groups causes performance issues (slowness in the UI page loads). This is issue is being investigated and has internal reference SRP-10215.

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • PDinCAPDinCA Posts: 642 Silver 1
    edited January 12, 2016 4:48PM
    16 Groups - 15 with 1 server, 1 with 2
    Jesus Christ: Lunatic, liar or Lord?
    Decide wisely...
Sign In or Register to comment.