Before I hit Idera Support with an annoying ticket of "but it's slow" I thought I'd run this past everyone.
My long running install of v10.0 was annoyingly slow when my team would navigate around the tree on the left. Notably as we'd have to scroll to the bottom of our 350 servers everything would just pause while the tree refreshed. Like pause long enough to be a legit annoyance.
This week I finally upgraded to 10.2.3 and this issue is persisting.
Do other people encounter this? If so--did anyone figure out how to fix it? Are there any real world recommendation for workstation specifications for monitoring this many servers?
I only have 40 Servers (but 194 mio rows in the DatabaseStatistics Table), and had a slow console after upgrading to Version 10 in August 2017, too. I then decided to first have a look at it myself.
I could identify 2 Stored Procedures in the repository creating massive load and had a look at them. Modifying them by adding "WITH RECOMPILE", made instantly a huge difference. Possibly because I have all kinds of servers, from tiny to massive and therefore different data that require different execution plans they need to be compiled on every execution. I also tweaked a few indexes and after that the console was responsive as before.
I know, this is not supported, but it works and was quicker than going through the "send me the logs" standard support procedure ;-)
Thanks for the tip.
I'll start looking through the stored procedures and see what might be happening on that front. What 2 did you identify?
"quicker than going through the "send me the logs" standard support procedure" - so true, I even don't bother logging cases because of this.
I too find it so slow. had thought of logging a call too. Glad to hear it's not just me but sorry to hear the application does appear to have a fault.
In my case it was [p_GetDatabaseCounters] and [p_GetServerSummary].
I'm told that this was a known issue and it's fixed on 10.3.1 I have it on my test machine but that environment never had the slowness issue. Fingers crossed it fixes this when I get to prod
Just upgraded from 10.1.4 to 10.3.1. Console is slow. So slow, sometimes it is unable to fetch the data at all :-(
Will have a look at it tomorrow. Guess its the indexing again ;-)
This is what I did, to get back to acceptable speed:
Added ...WITH RECOMPILE AS ... to the following two procedures: p_GetDatabaseCounters, p_GetServerSummary
Changed one index:
(Update: as the management service restores the index in its inexpedient form, just disable it and create the expedient one with a different name)
ALTER INDEX [IX_DatabaseStatisticsComposite] ON [dbo].[DatabaseStatistics] DISABLE;CREATE NONCLUSTERED INDEX [IX_DatabaseStatisticsComposite2]
ON [dbo].[DatabaseStatistics] ([DatabaseID] ASC, [UTCCollectionDateTime] ASC)
INCLUDE ([DatabaseStatus], [Transactions], [LogFlushWaits], [LogFlushes],
[LogKilobytesFlushed], [LogCacheReads], [LogCacheHitRatio],
[TimeDeltaInSeconds], [NumberReads], [NumberWrites], [BytesRead], [BytesWritten],
[IoStallMS], [LastBackupDateTime], [DatabaseSizeTime])
WITH (DROP_EXISTING = OFF, SORT_IN_TEMPDB = OFF, PAD_INDEX = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF);
This helped, because in my repository, the table DatabaseStatistics has currently aprox. 213 Mio Rows and is 32 GB large. The "...Composite" index was about 20 GB and is now 30 GB, too. It took about 25 Minutes to recreate the index with this statement (drop existing = on).I thought about swapping the clustered index, but that's not as trivial, because the current clustered index is the PK.
Note: This is no advice. Changes to the database must only be done when instructed to do so by Idera support ;-)
We had slowness in 10.2.2 but actually had more pressing issues (missing snapshots for monitored SQL Servers) that we were told was addressed in v10.3. When we were ready to upgrade I saw that v10.3 had a few known issues that were addressed in 10.3.1 so we went there. Its now been 19days since my team has been able to use the desktop client. Support has been no help whatsoever other than providing a new executable 10.3.5 that addressed one of the known issues but we're still unable to open the tool without it freezing 30 seconds in.
We have 200 monitored servers and 480million in DatabaseFileStatistics 90million in DatabaseStatistics.
There are a few known issues tied to many clients, one involved getting a new executable for the Management Service v10.3.1.5.
Another involved scripts to cleanup large DatabaseStatistics and DatabaseFileStatistics tables. Even after the cleanup job our DatabaseFileStatistics table still had 480 million records though, I'm spending today looking at why there are still entries in the table from 2016 and early 2017 even though its run with 365 day as input parameter.
Had the grooming issue as well in mid 2017. I added an index for pain relief:
CREATE NONCLUSTERED INDEX [GroomingHelper01] ON [dbo].[DatabaseFileStatistics] ([UTCCollectionDateTime] ASC,[FileID] ASC);
Thanks, we added the below last week to get past the timeouts in the Data Aggregation SQL Agent Job
CREATE NONCLUSTERED INDEX [usaa_idx_DatabaseFileStatistics_FileID_UTCCollectionDateTime] ON [dbo].[DatabaseFileStatistics] ([FileID],[UTCCollectionDateTime]) INCLUDE ([MaxSize],[InitialSize],[UsedSpace],[AvailableSpace],[FreeDiskSpace],[DriveName])
Seems you found basically the same index keys to improve performance.
When I had this issue last year, I emailed Idera my detailed analysis of the issue including the solution with the Index recommendation and the query plan prove. As well as a bug I found in the grooming procedure. It is not fixed in 10.3.1.
Maybe they listen to this forum and hopefully prioritize these issues in their release planning.
Updated the index modification based on my experience after a management service restart.
I'm still shocked after having a look at the p_GetDatabaseStatistics procedure used for the "Database Statistics" report after having a timeout. It changed since V10.1. As far as I can see, it tries to pump the DatabaseStatistics rows from ALL servers and ALL databases in a given interval into a temptable first, before filtering them for a given server and a given database in a second step. So I guess, that's the reason why it times out.
Also the new ...Aggregation Tables are heaps and have no indexes at all ?
I wonder if this is just an oversight?
Guess I have to open a case *sigh*