EnsAlertRequest Summary Queries timing out /
Off the back of the Interface Monitoring post I had created a class that queries the Ens.AlertRequest global and returns the entries between 6pm the night before and 6am in the morning.
I tested this build in our T&D environments and the build worked very well.
However in our production environment the query is being truncated, by what I believe to be a timeout and I get a partial query output.
In the System>SQL pages my 12 hour query times out.
I compared the Global size by running a SELECT MAX(ID) query and got a return of 60,244,962 records. This may go some way to explain why the query is taking longer and possibly timing out.
In our T&D environments (which we don't purge), we have only 1 million and 2.5 million records.
For messages we in production we have a 90 day purge limit.
I wonder if the Ens.AlertRequest global isn't being purged and if so why it isn't as this global is considered to be a message in my mind.
Any advice please.
Regards
Stuart
Please find my method below which is used to drive a Business Operation. It's activated by a schedule on a daily basis at 6am:
MAX(ID) isn't necessarily the record count. Try a "select count(*) from Ens.AlertRequest" query and see what you get. Compare that to "'select count(*) from Ens.MessageHeader where MessageBodyClassName = 'Ens.AlertRequest'"
If the numbers are in line with the MAX(ID), then my suspicion is that you either don't have "BodiesToo" checked or do have "KeepIntegrity" checked in your purge process configuration. Either of those may be keeping old Ens.AlertRequest bodies around.
Hi Jeffrey,
SELECT COUNT(ID) & SELECT COUNT (*) are timing out via the System>SQL pages.
I had a look at our purge settings and we have "BodiesToo" unchecked and "KeepIntegrity" checked.
The next way forward would be to investigate the effect of purging with the "BodiesToo" and see if this reduces the EnsAlertRequest global size.
If you have access to Caché terminal, you can run run queries that won't time out:
(the sample below assumes your namespace is "PROD"; just substitute whatever your production's namespace is for that).
USER> zn "PROD"
PROD> d $system.SQL.Shell()
SQL Command Line Shell
----------------------------------------------------
The command prefix is currently set to: <<nothing>>.
Enter q to quit, ? for help.
PROD>>SELECT COUNT(*) AS AlertCount FROM Ens.MessageHeader WHERE MessageBodyClassName = 'Ens.AlertRequest'
AlertCount
2205
PROD>>Q
PROD>
So ... if you don't have BodiesToo checked, you most likely have lots of orphaned message bodies taking up database space. And KeepIntegrity is probably retaining a lot of message headers (and associated bodies) that you don't care about anymore. There are reasons you would not want to turn KeepIntegrity off in earlier versions of Caché/Ensemble, like pre-2015 releases. If you're on a release more modern than that and you don't need to worry about messages with parent/child relationships (certain batch types, for example), you can probably turn that off.
There are a couple of articles regarding the management of orphaned bodies here on DC. Might be worthwhile to peruse them :)
Thanks Jeffrey.
I've spoken to our Caché ODBA and he concurs there are orphaned records in the Ens.Message* globals.
We're on 2017.1 currently, but have been live since Caché 2012, which may explain todays issues.
Looking at the purge tasks we have records with no timestamp, which would escape the purging process.
Our Caché ODBA is going to open a WRC to confirm the best course of action for a one off purge of these Ens.Message* globals.
Thank you so much for your advice as it has helped immensely identifying this issue.