Snowflake tables paying for search optimization with fewer than 10 point lookups

An access path you pay to build and keep current

The 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, 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 records maintenance credits:

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:

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.