page103
85
Long Table Scan
Long table scans manifest as a high percentage value for the
TableScan
operator
within a query profile. This may become a trend-based metric where progressive or
sudden adverse changes in performance of either a single query or several queries
may be observed. Monitoring long table scans may be a leading indicator of future
performance issues.
What Is Long Table Scan?
A long table scan occurs where most of the processing time is spent servicing remote
disk I/O, an expensive operation. I discuss local and remote disk I/O in detail within the
next chapter. You will also experience long table scans where there is little or no partition
pruning, identified from the query profile summary.
You might reasonably expect to see long table scans as part of your day-to-day
operational processes when creating denormalized table content; therefore, local system
knowledge is required when interpreting results. Alternatively, you might expect to see
long table scans when exploring data sets as part of an investigation or in preparation for
further system development.
Identifying Long Table Scans
In a production system you might not immediately identify queries suffering from long
table scans as the metric is recorded as steps within each query profile. The presenting
symptoms may be hidden within the overall runtime of a process or with backup files
awaiting processing. Conversely, for queries identified as having long table scans, you need
to identify which data pipeline or process they belong to before remediation can occur.
In this section you are looking to identify long table scans from all of the queries that
have been executed.
Let’s first create a table to hold all candidate query IDs for later investigation. I leave
it to you to add timestamps, etc.
CREATE OR REPLACE TABLE long_table_scans
(
query_id STRING,
warehouse_name STRING,
warehouse_size STRING,
ChapTer 3
The Query profiler