After upgrade to 2.1 - MOLASSES

PDinCAPDinCA Posts: 642 Silver 1
edited December 23, 2010 1:12PM in SQL Monitor Previous Versions
Just upgraded from 2.0 to 2.1.

Pulling up an Alert now takes 15 seconds and over 50% of the CPU for all that time.

To clear an Alert takes 10 seconds.

IIS7 on 64-bit Windows Server 2008 SP2. Dual Xeon 2.27GHz CPUs w/ 16GB RAM.

Performance is a real issue (or the lack thereof, should I say).

Assistance, please.............
Jesus Christ: Lunatic, liar or Lord?
Decide wisely...

Comments

  • Hi PDinCA

    We might have to ask for an ANTS Performance Profiler trace of your installation, but first, the following questions would help narrow down the problem.

    Where do you have the Base Monitor and Data Repository installed? Are they also on your IIS machine? What is the CPU utilization of RedGate.Response.Engine.Alerting.Base.Service.exe and the repository SQL Server - do they show the same spike when you use the UI?

    Is the rest of the UI slow or is it just the alert pages?

    Did you change any part of your configuration (other than the 2.0 to 2.1 upgrade) before these performance problems occurred?

    Thanks
    Ben
  • PDinCAPDinCA Posts: 642 Silver 1
    Hi Ben,

    No configuration changes were made at all, simply ran the installer from the download and extract.

    Base Monitor and Data Repository are both on the same machine as the IIS7 website.

    As Murphy's law would have it, today it is running like lightening! Must have enjoyed the rest overnight.

    Service spikes to 53% when the UI is asked to display an Alert's detail.

    There's actually nothing but the Red-Gate Monitor and Idera's Defrag Manager currently on the machine in question and the latter showed near flat-line CPU usage throughout the problem time and is still showing 0%.

    And while I looked up the sqlservr.exe CPU usage, having requested a "Long Running Query" Alert detail - 15 seconds elapsed, the UI spiked to 74% and SQL spiked to 44% to begin with, then back to its typical 0 to 3%. Murphy is active today!

    Tried a selection of pages besides Alerts and the Overviews came up fast, as did the drilldown on the cluster. The Analysis page is also molasses - selected just the "Avg. CPU queue length" for the default 10 minutes and, three minutes later, I'm still waiting... The hourglass is shown over the graph area and the progress bar is solid blue; the status bar says it is downloading the header.png background, for what it's worth.

    Happy to have you diagnose with ANTS. Need help with the install as I'm no UI developer and have no experience with ANTS though I've heard of it and prior site used it to good effect. Let me know.

    FYI: Likely to be "gone" from 14:00 Pacific time on Dec 23rd until the New Year.

    Happy Christmas.

    Regards, Stephen
    Jesus Christ: Lunatic, liar or Lord?
    Decide wisely...
  • Hi Stephen

    Good to hear that it has improved.

    If opening the Long Running Query alert is much faster the second time round, it sounds like the second page load is benefiting from SQL Server's cache. The first page load should probably be faster than that, but if you were loading data for a historical alert it had probably dropped out of the SQL cache. The SQL process data is quite hard to cache as the rows are quite wide (lots of strings).

    I am also going away until the New Year. Could you send an email to support@red-gate.com and mention my name (Ben Challenor) please? Then I or someone else on the team can continue the investigation.

    Thanks, and happy Christmas
    Ben
  • PDinCAPDinCA Posts: 642 Silver 1
    "Improved" is relative... Still takes 15 seconds to pull up a log-running query alert from today, and it is the most recent alert I have, comprising somewhat little text content.
    Error: 102, Severity: 15, State: 1 Error: 102, Severity: 15, State: 1 -- network protocol: TCP/IP
    set quoted_identifier on
    set arithabort off
    set numeric_roundabort off
    set ansi_warnings on
    set ansi_padding on
    set ansi_nulls on
    set concat_null_yields_null on
    set cursor_close_on_commit off
    set implicit_transactions off
    set language us_english
    set dateformat mdy
    set datefirst 7
    set transaction isolation level read committed
    Error: 102, Severity: 15, State: 1 set quoted_identifier off select @@microsoftversion select convert(sysname, serverproperty(N'servername')) SELECT ISNULL(SUSER_SNAME(), SUSER_NAME()) SET TEXTSIZE 1024 EXEC dbo.spr_UpdateMeterUsage
    
    Not sure that emailing your support colleagues will speed anything along as both you and I will be otherwise occupied until Jan 3. If you feel it would be productive, you could always forward your additional thoughts to them internally, perhaps?

    Enjoy the Christmas break.
    Jesus Christ: Lunatic, liar or Lord?
    Decide wisely...
Sign In or Register to comment.