SFMC Tips #1 : How to find SQL query activities that take more than 15 minutes processing time on Automation Studio
Are you effectively utilizing the new data views, _AutomationInstance and _AutomationActivityInstance, released for Automation Studio in “Winter ‘23” in October 2022?
It seems that you can now address some of the Automation Studio-related issues that you used to contact Salesforce Support about, thanks to these data views. First, let’s summarize some of the unique specifications of these data views.
Data View: Automation Instance
Gain an overview of all automations within your tenant, verify the health of automations, and improve automation efficiency and success rates.
What can you learn from this data view?
- Duration of automations
- Whether automations succeeded, were skipped, or failed
- Reasons for automation failures
- File names used to initiate file drop-type automations
Usage notes:
- Only automations executed within the past 31 days are applicable.
- The data view is scoped to a single business unit
- The time zone for date data is UTC
Update: October 7, 2024
This article has been updated following the Winter ’25 release (between October 4 and October 25, 2024). Previously, the data retrieval range was limited to “1 day to 31 days ago”, but now it is possible to retrieve “data for the current day.” The article has been adjusted accordingly.
Data View: Automation Activity Instance
Verify the health of activities within Automation Studio to improve automation efficiency and success rates. Identify activities that frequently fail or run for extended periods to prevent failures.
What can you learn from this data view?
- Times at which activities were executed
- Duration of activity execution
- Whether activities succeeded or failed
- Reasons for activity failures
Usage notes:
- Only automations executed within the past 31 days are applicable.
- The data view is scoped to a single business unit
- The time zone for date data is UTC
Update: October 7, 2024
This article has been updated following the Winter ’25 release (between October 4 and October 25, 2024). Previously, the data retrieval range was limited to “1 day to 31 days ago”, but now it is possible to retrieve “data for the current day.” The article has been adjusted accordingly.
These usage notes are the same for both data views, but they have some unique aspects compared to other data views (Sent, Open, Click…), so it’s important to have a solid understanding of them.
By the way, are you experiencing timeout errors in Automation Studio due to high-load SQL query activities you’ve created?
Let’s work on improving this by identifying high-load SQL query activities in advance using the following SQL, and then optimizing them to prevent a single SQL query activity from timing out after 30 minutes.
SELECT b.AutomationName as [Automation_Name]
, b.ActivityInstanceStep as [Activity_Step]
, b.ActivityName as [Activity_Name]
, b.ActivityInstanceStartTime_UTC as [Start_Time]
, b.ActivityInstanceEndTime_UTC as [End_Time]
, b.Duration as [Duration]
FROM (SELECT a.AutomationName
, a.ActivityInstanceStep
, a.ActivityName
, a.ActivityInstanceStartTime_UTC
, a.ActivityInstanceEndTime_UTC
, DateDiff(Minute,a.ActivityInstanceStartTime_UTC,a.ActivityInstanceEndTime_UTC) as Duration
FROM _AutomationActivityInstance a
WHERE a.ActivityType = 300) b
WHERE b.Duration >= 15* This SQL is available in Query Studio.
* This SQL uses only the fields in the data view, so the same SQL query can be used in any instance.
* This SQL finds SQL query activity that has exceeded 15 minutes within the last 31 days.
* The time is displayed in the UTC timezone, so adjust it according to your timezone using dateadd.
* ActivityType = 300 in the code refers to “query activity”, so there is no need to change it.
* Duration of 15 in the WHERE at the bottom indicates query activity that took more than 15 minutes. Please adjust this in the range of 0 to 30 minutes.
In conclusion
You can use these SQL to proactively identify high-load SQL Query Activities and improve them to avoid them timing out within 30 minutes. As the number of records in the file gradually increases due to additions and updates, leaving it unattended may lead to timeout errors. Therefore, it’s important to periodically check and maintain your automation.
Thank you for reading.
Nobuyuki Watanabe
https://www.linkedin.com/in/nobuyuki-watanabe/ (+Follow me)
