# snowflake_table_search_optimization_low_benefit

> Tables accruing any Search Optimization maintenance credits over 30 days while serving fewer than 10 point-lookup queries pay more to maintain the search access path than the workload gives back. ZopNight counts a lookup as a query scanning under 0.1% of the table's partitions, measured from QUERY_HISTORY joined to ACCESS_HISTORY.

Source: https://zop.dev/integrations/snowflake/recommendations/snowflake-table-search-optimization-low-benefit
Updated: 2026-08-19

---

## The maintenance meter behind the feature

Search Optimization keeps a persistent access path alongside your table, and Snowflake
maintains it with serverless compute every time the underlying data changes. Those
maintenance credits accrue whether or not any query benefits. The feature pays for
itself on tables where a meaningful share of queries are highly selective lookups; on a
table scanned in bulk by ELT and dashboards, the maintenance line is a recurring charge
with no offsetting speedup.

## How the no-benefit verdict is reached

Two 30-day sums, both from ACCOUNT_USAGE. First, `snowflake.table.sos_credits` must be
greater than 0, meaning the table is actually spending maintenance credits, sourced from
SEARCH_OPTIMIZATION_HISTORY. Second, `snowflake.table.point_lookup_queries` must be
below 10. A point lookup is defined mechanically: a query whose
`partitions_scanned / partitions_total` ratio came in under 0.001, found by joining
QUERY_HISTORY to ACCESS_HISTORY on `query_id` and unnesting the base objects each query
touched. Fewer than 10 such queries in a month means the access path is essentially
never exercised.

## Audit the spend per table

```sql
SELECT database_name || '.' || schema_name || '.' || table_name AS table_uid,
       SUM(credits_used) AS sos_credits_30d
FROM SNOWFLAKE.ACCOUNT_USAGE.SEARCH_OPTIMIZATION_HISTORY
WHERE start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP())
GROUP BY 1
ORDER BY 2 DESC;
```

Any table appearing here that you cannot name a lookup workload for is a candidate.

## The heuristic, stated honestly

Partition-scan ratio is a proxy, not ground truth. Search Optimization also accelerates
substring and geospatial predicates whose queries may scan more than 0.1% of partitions
and still benefit, so a table serving those could be flagged despite genuine value. The
join also depends on ACCESS_HISTORY, an Enterprise-edition view that lags up to 3 hours,
and the maintenance history lags similarly, so verify against a full recent month before
acting.

## Removing the property

```sql
ALTER TABLE my_db.app.events DROP SEARCH OPTIMIZATION;
```

Maintenance charges stop immediately. Be deliberate: re-enabling later rebuilds the
entire access path from scratch, which itself costs credits proportional to table size,
so confirm with the table's consumers that no selective-lookup workload is planned. If
only one column ever serves lookups, a narrower middle path is dropping the table-wide
property and re-adding Search Optimization scoped to that single column with an ON
clause. Maintenance then tracks one access path instead of every eligible column, and
the monthly credit line shrinks accordingly.