Index JobStreams Table
Out of the box the JobStreams table does not have an index for JobID and TenantID. The result is that Get-SMAJobOutput and the web portal can get quite easily bogged down. Even with job history set to the standard 30 days and process/debug logging off, this table can get quite large on a busy environment. Simply indexing this table drastically improves the overall performance of SMA. I had a few runbooks with process monitoring turned on and the table reached over 17 million rows (at which point I couldn't get any output from SMA)
In my environment, I created the following index:
CREATE NONCLUSTERED INDEX [NC_JobIDTenantID]
ON [Stream].[JobStreams] ([TenantId],[JobId])
Currently, I understand that by creating this index I am taking the risk that future updates may break this tweak so it would be nice if this type of optimization were an option that can be enabled within the product itself. I also understand that process monitoring is intended as a temporary tool and currently have it off for all my runbooks :-)
Thanks for sharing this feedback!