During this post we will discuss Java Script Procedure to resolve a business use case. In recent times developers noticed query execution is getting slower on the database. Normal queries would often be long running, lasting from several seconds to a couple of minutes. Since these queries were running smoothly last week but as of sudden there is downfall in execution. After the analysis we have found there are some adhoc queries running on the database. These long runner queries consider as resource intensive eating up the Warehouse resources. Due to this, Normal queries to get exceptions in case of resource crunch on WH.
As part of workaround, we have identified the query id of these long runner and killed them manually. But identifying the long runners and killed them periodically requires manual intervention. Business wants if there is any automated process to be in place which identifies these culprit queries on it own. Once the query id is available, process should kill them automatically. A procedure has been written in Javascript language. This procedure checks if there is any long runner query, Currently threshold is 45 mins. So if any query is running more than 45 minutes, consider it as long runner. Procedure extracts the query id for such resource intensive queries and use the System function to kill them.
Currently there are three queries running in system for past 50 minutes.
We have developed the following procedure.
Executed the procedure:
This way we can leverage this procedure and can schedule it with Task to run at specified interval.
Also based on the business requirement we can change the threshold value in procedure to identify long runners.