7.7.0.18 - takes over 10 MINUTES to refresh Activity pane

PDinCA

Tried to use the UI on the prior released 7.6 version. It was so slow, and never returned any data - spinner only - that I decided to check for an update. Having found 7.7 and installed UI and server components, I had my fingers crossed for increased performance.

There are only 12 databases on the active-passive cluster.

6 databases have 5-minutely log backups.

SQL Monitor barfs up a "Long Running Query" Alert for the Activity Pane's refresh activity when the UI is first opened, expand the databases, click the one of interest, and go for LUNCH!

Low	Long-running query | 4325661
	
Raised on:	cluster.xxx.xxxx.int\(local)
Time raised:	4 Aug 2014 1:18 PM (UTC-04)
Details
Process ID:	176
Process name:	SQL Backup
Database:	master
Host:	XXX-DB1
User:	XXX\user
Process login time:	4 Aug 2014 1:08 PM
Query start time:	4 Aug 2014 1:08 PM
Query duration:	600.777 sec
SQL process fragment
sqbdata

15 minutes and NO DATA = unacceptable.

And it's not like the cluster is overworked, either. There are only 40 jobs, 12 or so of which are SQL Backups and many that only run for a few seconds or minutes every 15 or hourly.

This is a SQL2012SP1CU6 instance on a dual-hex-core Dell R720 with 256GB of RAM, so it's not lacking resources. CPU currently at 16%, 5 waiting tasks, 7MB/sec I/O. Can't see any good reason for this issue other than, perhaps, with msdb at 35GB. there may be a woefully lacking index on one of the core job history tables...

Seriously UNUSABLE interface.

Any ideas, chaps?

PDinCA

Red Gate Support commented, "...the UI uses an internal SQL compact database to cache the history. It is the syncing process between the system tables and the cached database where the performance issue is seen. We do have an existing request logged to improve the UI performance in these types of situations. The number for your reference is SB-4400. I will add you to the list of requestors. In the meantime, the workaround that has worked for other customers is to reduce the number of rows in the table. Sorry for the inconvenience."

I dropped the SQL Backup history to 10 days and left the UI open - the message on the Server Options dialog says that cleanup only takes place when the UI is open. I see that as a DEFECT because I only ever go to the UI when adding or updating a scheduled backup, or to get the full log message for a rare failure. Cleanup should be something SQL Backup takes care of on its own - the essence of an unattended process - especially because the service knows to write to the history, so it surely can do cleanup, too.....

At least I received a response in about 45 seconds. Much better, but still very slow... SELECT TOP(N) FROM, perhaps, with paging...?

Pity there's no "Export History" right-click option, either, so at least I could do some average run time, file size, time-of-day impact analysis on a set of data I can see in the UI, rather than run (and generate an "Unable to generate report" error via the UI "Query error: There was an error parsing the query. [Token line number=1, Token line offset=2, Token in error]" Not exactly helpful with no means of copying that error to the clipboard...) Ah well...