page195
281
SELECT query_id,
warehouse_name,
warehouse_size,
bytes_spilled_to_local_storage,
bytes_spilled_to_remote_storage,
bytes_sent_over_the_network
FROM snowflake.account_usage.query_history
WHERE warehouse_name = '<YOUR_WAREHOUSE_HERE>'
AND bytes_spilled_to_remote_storage > 0;
The next check is for concurrency; you should investigate the number of concurrent
processes running at the same time your clients were reporting issues. If you observe
either queueing or blocking, then you should consider scaling out your ingest warehouse
by adding clusters. You could also implement the Query Acceleration Service as
discussed in Chapter 6.
The following query repeated from Chapter 6 identifies overlapping subset of records
using the same named warehouse and specific
date_time
. Replace variables with your
chosen values:
CREATE OR REPLACE VIEW v_warehouse_workload_by_hour COPY GRANTS
AS
SELECT warehouse_name,
start_time,
end_time,
query_id,
query_text,
total_elapsed_time / 1000 AS total_elapsed_time_in_secs,
queued_overload_time ,
transaction_blocked_time,
DATE_PART ( 'YYYY', start_time )||
LPAD ( DATE_PART ( 'MM', start_time ), 2, '0' )||
LPAD ( DATE_PART ( 'DD', start_time ), 2, '0' )||'_'||
LPAD ( DATE_PART ( 'HOUR', start_time ), 2, '0' )
AS date_time
FROM snowflake.account_usage.query_history
WHERE execution_time <> 0
Chapter 8
parallelization