Snowflake stored procedure queries not showing up in Query Profiler

Question

I have a Snowflake stored procedure which is running for 8 hrs. Upon checking the query profiler to see which query is running long I just see a single entry for the call stored procedure statement.

However, Through the logs I know that an insert statement is running which is like:

insert into Snowflake_table 
    select * 
    from External_table(over S3 bucket)

I want to check and find out why reading from external table is taking lot of time but the insert query is not showing up in the Query profiler. I have tried querying the information_schema.Query_history but its not showing another query running apart from the call stored procedure statement

SELECT * 
FROM Table(information_schema.Query_history_by_warehouse('ANALYTICS_WH_PROD')) 
WHERE execution_status = 'RUNNING'
ORDER BY start_time desc;

Please suggest how to find the bottleneck here

"through the logs I know..." - is the insert statement logging something to a certain table and you checked there or how do you know? — Marcel
@Marcel - yes , the procedure logs all the queries into a log table before firing them — Shanil

Marcel Marcel · Accepted Answer · 2021-04-27T11:56:44

Docs is stating that Queries on INFORMATION_SCHEMA views do not guarantee consistency with respect to concurrent DDL: https://docs.snowflake.com/en/sql-reference/info-schema.html

This means that it is possible that your insert-statement is running but is not shown as a result of your query. It could be included but it's not a must.

You could now change the filter to execution_status IN 'success', 'failed' and check again after the procedure finished.

Snowflake stored procedure queries not showing up in Query Profiler

1 Answers