# Snowflake Table Low-Benefit Search Optimization

> Flags Snowflake tables spending search optimization credits in 30 days with under 10 point lookups, priced at the edition credit rate.

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

---

## An access path you pay to build and keep current

The [search optimization service](https://docs.snowflake.com/en/user-guide/search-optimization-service), an Enterprise Edition
feature, speeds up selective point lookups that return one or a few rows, and several other query
shapes. According to Snowflake's [cost estimation page](https://docs.snowflake.com/en/user-guide/search-optimization/cost-estimation),
it costs in two ways: a search access path that typically takes about a quarter of the table's
size in storage, and compute to build it and then maintain it as data changes, billed per second.
High-churn tables cost the most to maintain.

If almost nobody runs the lookups it was added for, both costs continue with nothing to show for
them.

## Credits per table, and a caution on column names

[SEARCH_OPTIMIZATION_HISTORY](https://docs.snowflake.com/en/sql-reference/account-usage/search_optimization_history) records
maintenance credits:

```sql
SELECT database_name, schema_name, table_name,
       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, 2, 3
HAVING SUM(credits_used) > 0
ORDER BY sos_credits_30d DESC;
```

Snowflake is replacing `TABLE_NAME` in this view with `BASE_TABLE_NAME` and `INDEX_NAME`; if your
account has the new columns, group by `base_table_name` instead. `SHOW TABLES` reports
`search_optimization` (ON or OFF) and `search_optimization_bytes` for each table.

## Credits spent, fewer than 10 point lookups

ZopNight sums the table's search optimization credits over 30 days and needs a total above zero,
then sums the point-lookup queries against the table in the same window and fires when there were
fewer than 10. Other query shapes the service can help, such as substring or geospatial searches,
are not counted, which is the main source of false positives.

## Tables the check passes over

Missing or empty credit or lookup series mean nothing is raised. Ten or more point lookups in 30
days clears the table. And because the finding is priced, it is not shown when ZopNight has no
credit rate for the account.

## The saving covers compute only

The saving is the table's search optimization credits, valued at the per-credit rate for the
account's edition, or the Enterprise rate when the edition is unknown:

```text
saving per month = search optimization credits over 30 days x credit rate for the edition
```

The storage used by the search access path is not in that figure, so dropping the service
recovers somewhat more than shown.

## Dropping search optimization

1. Confirm which queries used it. If they are substring, text or geospatial searches rather than
   point lookups, the finding may not apply.
2. Remove it from the whole table: `ALTER TABLE crm.customers DROP SEARCH OPTIMIZATION;` or from
   specific columns with the ON clause.

**Warning**
Adding search optimization back later costs the same as adding it the first time, since
the access path is rebuilt from scratch. Drop it only when the lookups are genuinely gone.