Investigate a query

Read a query's SQL, warehouse context and execution profile, and find queries with the same shape.

Questions you can ask your agent:

  • "Why is query 01b2c3d4-0000-1111-2222-333344445555 so slow?"
  • "Was the warehouse busy when nightly_revenue_job's insert ran last night?"
  • "Have we run anything shaped like this aggregate over analytics.orders_fact before?"

A typical chain: read the query with query_detail, read its profile with query_profile_detail, then look for the same shape elsewhere with search_similar_queries.

A query's processing_time is the day it belongs to. Pass it back when you have it; the tools look it up otherwise.

All sample responses use synthetic data and are shortened.

query_detail

Returns a query's text, cost, signature, warehouse, concurrency and column usage.

When to use: to read the SQL of a query id, or to see which columns it filters and joins on.

Parameters
ParameterTypeRequiredDefaultMeaning
query_idstringYesThe query id.
processing_timestringNolooked upThe query's day, as this tool returns it.
query_offsetintegerNo0For a query longer than 40,000 characters: where to start reading.

Returns:

  • query: the text, at most 40,000 characters per call. A longer query also returns query_chars, query_offset and query_next_offset; call again with query_offset = query_next_offset until it is null.
  • cost, query_signature, default_database, job_id ("<dag> &&& <task>", or null).
  • warehouse_name, warehouse_size, max_concurrency and avg_concurrency (peak and average parallel queries on the warehouse while it ran). Null on Spark.
  • execution_status: the engine's own value. A failed or cancelled query is costed like any other, but its profile is partial; don't size a saving on it.
  • column_usage: one entry per table, column and usage type, from the query profile: SELECT, GROUPBY, ORDERBY, AGGREGATE, WHERE (with operator, value and wrapping functions) and joins (INNER_JOIN, LEFT_JOIN, FULL_JOIN, JOIN_UNSPECIFIED, with the join column). Empty for a query without a profile.
  • next: when the query has a job and the server serves a job health card, the call that reads it.
Example

"Show me query 01b2c3d4-0000-1111-2222-333344445555. Was the warehouse busy?"

{
  "query": "INSERT INTO analytics.orders_daily SELECT order_date, region, SUM(amount) FROM analytics.orders_fact GROUP BY 1, 2",
  "cost": 18.42,
  "query_signature": "nightly_revenue &&& build_orders_daily",
  "job_id": "nightly_revenue &&& build_orders_daily",
  "warehouse_name": "ETL_WH",
  "warehouse_size": "Medium",
  "max_concurrency": 12,
  "avg_concurrency": 7.4,
  "column_usage": [{"table_fqn": "ANALYTICS_DB.ANALYTICS.ORDERS_FACT", "column": "ORDER_DATE", "usage_type": "GROUPBY"}]
}

query_profile_detail

Snowflake only. Returns a query's execution profile as a compact text tree.

When to use: "why is this query slow?" Read the operators that carry the cost.

Parameters
ParameterTypeRequiredDefaultMeaning
query_idstringYesThe query id.
processing_timestringNosearches the last 30 daysThe query's day, as query_detail returns it.

Returns plain text, not JSON. The first line is a totals header (scan, spill, and the share of the query's cost the profile accounts for). Then one operator per line, depth-first:

{step}.{node}:{type} c={cost%} i={input_rows} o={output_rows} p={parent} partitions=S/T sc=<bytes> spl=<bytes> spr=<bytes> fcols=<cols> {attrs}
  • c: the operator's own cost as a percent of the query's cost. Missing in a preparatory step: unknown, not zero.
  • p: parents, comma-joined when several; missing for the root.
  • partitions: scanned/total on scans; total ? when unknown.
  • sc, spl, spr: bytes scanned, spilled locally and spilled remotely.
  • fcols: the predicate columns on a scan.

Subtrees under 1% of the cost collapse into one [...N nodes, tables: X, Y] line; scans, joins and filters over scans still print. The text is at most 50,000 characters. No profile returns {"error": ...}: the id may be unknown, a query inside a script, or on another day.

Example

"Why is query 01b2c3d4... slow?"

# totals (whole query, incl. collapsed nodes): scan=1.2TB spill=0B (local=0B remote=0B) attributed=97%
1.0:Insert c=2% o=720
1.1:Aggregate c=31% i=1.4B o=720 p=1.0
1.2:TableScan c=64% o=1.4B p=1.1 partitions=3650/3650 sc=1.2TB fcols=ORDER_DATE table=ANALYTICS.ORDERS_FACT

search_similar_queries

Snowflake only. Finds historical query fragments similar to one input fragment.

When to use: you have a new or slow query and want to know whether its shape is a one-off or recurs, for example while reviewing new SQL.

Parameters
ParameterTypeRequiredDefaultMeaning
sql_textstringYesOne fragment to search for, best as an indented operator tree (see below). Can't be empty.
limitintegerNo10Results to return. At least 1.
start_timestring (ISO-8601)Nono lower boundInclusive lower bound on the query day.
end_timestring (ISO-8601)Nono upper boundExclusive upper bound on the query day. Must be after start_time.
exclude_job_idslist of stringsNononeJobs whose fragments to leave out.

The index is built from plan subtrees, not raw SQL. The best input is an indented operator tree:

Filter attrs={"filter_condition": "order_date >= ?"}
  TableScan attrs={"table_name": "analytics.orders_fact", "columns": ["order_date", "amount"]}

For new SQL without a profile, approximate this from the SQL: keep table names, columns, filters, joins, aggregations, windows and nesting. Raw SQL usually scores worse. Split a large query into fragments yourself and search one per call.

Returns at most one result per query signature (its closest fragment), best first: query_id, fragment_id, query_signature, similarity_score, processing_time and fragment_plan_text_preview. An empty list means no stored fragment cleared the noise floor.

This tool is present only where similarity search is enabled. Where it isn't, the tool is missing from the list or returns {"error": "similarity search not enabled"}.

Example

"Have we run anything like this aggregate over orders_fact before?"

[
  {
    "query_id": "01b2c3d4-...8888",
    "fragment_id": "f2e3d4c5-2222-3333-4444-555566667777",
    "query_signature": "weekly_finance &&& orders_rollup",
    "similarity_score": 0.94,
    "processing_time": "2026-05-07T00:00:00Z",
    "fragment_plan_text_preview": "Aggregate attrs={\"functions\": \"sum(amount)\"}\n  Filter ..."
  }
]

Did this page help you?