In this series on how Dynamics 365 Finance & Operations personalization affects performance, today I want to look into alerts.

In D365F&O you can create alerts to be notified when data events you want to be informed about occur in most pages that have the Options menu group. When an event that you have created an alert for occurs the user will get a notification in the Action center and possibly by email. A very useful feature but also a problem if not managed correctly and all very well up to here.

However alerts are not intended for tracking high volumes of changes. They downgrade all set-based operations to record-based operations, and changed data must be written to the EVENTCUD table where a batch job picks it up and delivers it to the intended users. 

Last but not least, it's quite normal that users create alerts of all kinds of alerts and then just leave them there indefinitely, resulting in the amount of processing required for this growing and growing, so only create alerts you really need and only for as long as needed. This should be the responsibility of the users, but good luck.

While this is often brought up in the context of storage (data is also created in the tables EVENTINBOX and EVENTINBOXDATA) consumption (it costs money), from a performance perspective capturing the events and creating the notifications also consumes resources that are then not available for other processes. 

As a system administrator you can open the the form Notification list (this form is technically available in the application, but not linked to any menu item) by altering the URL parameters to mi=EventAlertInbox to get an idea of the state of things.

Data in the tables naturally grows over time and while users can delete the notifications from the action center, the data is not deleted from the event tables and will therefore grow indefinitely, so you need to use the standard cleanup functionality in D365F&O to ensure you manage the size and growth of these tables.

Go to System administration > Periodic tasks > Notification clean up to run it regularly, or even better set it up as a recurring batch job. Check with your business how long you should retain alerts and use that to set the Alert created date and time. Generally challenge anything with more than a 30 day recurrence pattern. If someone has not read a notification after 30 days of receiving it, what value does it have and how likely is it they ever will?

Unfortunately there is no cleanup procedure out-of-the-box for the EVENTCUD table so it tends to get really out of hand, and you need to implement a procedure/customization or your own to clean it up. Alternatively you can create a support ticket and ask support to do it for you.

If you want you can also connect to the SQL server on a Sandbox with a recent copy of your production database and use the following query to see what you have in there.

;with cte as (  

  SELECT  

  t.name as TableName,  

  SUM (s.used_page_count) as used_pages_count,  

  SUM (CASE  

              WHEN (i.index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)  

              ELSE lob_used_page_count + row_overflow_used_page_count  

          END) as pages  

  FROM sys.dm_db_partition_stats  AS s   

  JOIN sys.tables AS t ON s.object_id = t.object_id  

  JOIN sys.indexes AS i ON i.[object_id] = t.[object_id] AND s.index_id = i.index_id  

  -- Comment this out to get it for all tables.

  WHERE t.name = 'EVENTCUD' OR t.name = 'EventInbox' OR t.name = 'EventInboxData'

  GROUP BY t.name  

  )  

  ,cte2 as(select  

      cte.TableName,   

      (cte.pages * 8.) as TableSizeInKB,   

      ((CASE WHEN cte.used_pages_count > cte.pages   

                  THEN cte.used_pages_count - cte.pages  

                  ELSE 0   

            END) * 8.) as IndexSizeInKB  

  from cte  

 )  

 select TableName,TableSizeInKB,IndexSizeInKB,  

 case when (TableSizeInKB+IndexSizeInKB)>1024*1024   

 then cast((TableSizeInKB+IndexSizeInKB)/1024*1024 as varchar)+' GB'  

 when (TableSizeInKB+IndexSizeInKB)>1024   

 then cast((TableSizeInKB+IndexSizeInKB)/1024 as varchar)+' MB'  

 else cast((TableSizeInKB+IndexSizeInKB) as varchar)+' KB' end [TableSizeIn+IndexSizeIn]  

 from cte2  

 order by 2 desc  

And this one to see how old the alerts are.

SELECT   DATEPART(YEAR, CREATEDDATETIME) AS [Year],

   DATEPART(MONTH,CREATEDDATETIME) AS [Month],

   COUNT (CUDTABLEID) AS [Count]

FROM EVENTCUD (NOLOCK)

GROUP BY DATEPART(YEAR, CREATEDDATETIME), DATEPART(MONTH,CREATEDDATETIME)

ORDER BY 1 DESC, 2 DESC