Skip to main content
Last updated on

Identify unused vector search endpoints

This page describes how to use the audit log system table to find vector search endpoints that have indexes but receive no query traffic. Unused endpoints consume resources and incur costs without delivering value. Identifying unused endpoints helps you clean up unused resources and reduce costs.

Requirements​

  • A Unity Catalog-enabled workspace.
  • Access to the system.access.audit table. By default, only account admins have access. To grant access to other users, see Audit log system table reference.
  • A SQL warehouse or serverless compute to run queries.

How it works​

The system.access.audit table logs every vector search API call as an event with service_name = 'vectorSearch'. This includes index creation, deletion, queries, upserts, and scans.

A single endpoint can serve multiple indexes. To find unused endpoints, compare the set of indexes that exist (created but not deleted) against the set of indexes that received query traffic in a given time window, then aggregate to the endpoint level. An endpoint is unused only when none of its indexes received queries.

The audit log retains 365 days of data, so you can look back up to one year.

note

The audit log records queries at the index level, not the endpoint level. The queries in this guide map indexes back to their endpoints using the endpoint_name recorded at index creation time.

Find unused endpoints​

The following query identifies endpoints where no index received any queries in the last 30 days. It uses audit log events to determine which indexes were created, which were deleted, and which received query traffic, then aggregates to the endpoint level.

important

This query determines active indexes by comparing createVectorIndex and deleteVectorIndex events in the audit log. If an index was created more than 365 days ago (before the audit log retention window), it doesn't appear in the results. For a complete picture, cross-reference these results with the output of the vector search list_indexes SDK method.

SQL
WITH created_indexes AS (
  -- All indexes created in the last year
  SELECT DISTINCT
    request_params['name'] AS index_name,
    request_params['endpoint_name'] AS endpoint_name
  FROM system.access.audit
  WHERE service_name = 'vectorSearch'
    AND action_name = 'createVectorIndex'
    AND event_date >= current_date() - INTERVAL 365 DAYS
),

deleted_indexes AS (
  -- Indexes that have been deleted
  SELECT DISTINCT
    request_params['name'] AS index_name
  FROM system.access.audit
  WHERE service_name = 'vectorSearch'
    AND action_name = 'deleteVectorIndex'
    AND event_date >= current_date() - INTERVAL 365 DAYS
),

active_indexes AS (
  -- Indexes that exist (created but not deleted)
  SELECT ci.index_name, ci.endpoint_name
  FROM created_indexes ci
  LEFT JOIN deleted_indexes di ON ci.index_name = di.index_name
  WHERE di.index_name IS NULL
),

queried_indexes AS (
  -- Indexes that received queries in the last 30 days
  SELECT DISTINCT
    request_params['name'] AS index_name
  FROM system.access.audit
  WHERE service_name = 'vectorSearch'
    AND action_name IN (
      'queryVectorIndex',
      'queryVectorIndexNextPage',
      'scanVectorIndex'
    )
    AND event_date >= current_date() - INTERVAL 30 DAYS
),

index_status AS (
  SELECT
    ai.endpoint_name,
    ai.index_name,
    CASE WHEN qi.index_name IS NOT NULL THEN 1 ELSE 0 END AS is_queried
  FROM active_indexes ai
  LEFT JOIN queried_indexes qi ON ai.index_name = qi.index_name
)

SELECT
  endpoint_name,
  COUNT(*) AS total_indexes,
  SUM(is_queried) AS queried_indexes,
  COUNT(*) - SUM(is_queried) AS unqueried_indexes
FROM index_status
GROUP BY endpoint_name
HAVING SUM(is_queried) = 0  -- Only fully unused endpoints
ORDER BY total_indexes DESC

Find unused indexes within active endpoints​

Even if an endpoint is actively serving queries for some indexes, it may have other indexes that receive no traffic. These unused indexes still consume resources on the endpoint. Removing unused indexes from an active endpoint can reduce its memory footprint and prevent it from scaling up unnecessarily.

The following query identifies individual unused indexes, including those on endpoints that have other active indexes.

SQL
WITH created_indexes AS (
  SELECT DISTINCT
    request_params['name'] AS index_name,
    request_params['endpoint_name'] AS endpoint_name
  FROM system.access.audit
  WHERE service_name = 'vectorSearch'
    AND action_name = 'createVectorIndex'
    AND event_date >= current_date() - INTERVAL 365 DAYS
),

deleted_indexes AS (
  SELECT DISTINCT
    request_params['name'] AS index_name
  FROM system.access.audit
  WHERE service_name = 'vectorSearch'
    AND action_name = 'deleteVectorIndex'
    AND event_date >= current_date() - INTERVAL 365 DAYS
),

active_indexes AS (
  SELECT ci.index_name, ci.endpoint_name
  FROM created_indexes ci
  LEFT JOIN deleted_indexes di ON ci.index_name = di.index_name
  WHERE di.index_name IS NULL
),

queried_indexes AS (
  SELECT DISTINCT
    request_params['name'] AS index_name
  FROM system.access.audit
  WHERE service_name = 'vectorSearch'
    AND action_name IN (
      'queryVectorIndex',
      'queryVectorIndexNextPage',
      'scanVectorIndex'
    )
    AND event_date >= current_date() - INTERVAL 30 DAYS
)

SELECT
  ai.endpoint_name,
  ai.index_name
FROM active_indexes ai
LEFT JOIN queried_indexes qi ON ai.index_name = qi.index_name
WHERE qi.index_name IS NULL
ORDER BY ai.endpoint_name, ai.index_name

Get query activity details per index​

To understand query patterns across all your indexes, not just the unused ones, use the following query. This query finds the last query time and query volume for each index, helping to identify indexes that are queried but see very low traffic and might also be candidates for cleanup.

SQL
SELECT
  request_params['name'] AS index_name,
  MAX(event_time) AS last_query_time,
  DATEDIFF(current_date(), DATE(MAX(event_time))) AS days_since_last_query,
  COUNT(*) AS query_count_30d,
  COUNT(DISTINCT DATE(event_time)) AS active_days_30d
FROM system.access.audit
WHERE service_name = 'vectorSearch'
  AND action_name IN (
    'queryVectorIndex',
    'queryVectorIndexNextPage',
    'scanVectorIndex'
  )
  AND event_date >= current_date() - INTERVAL 30 DAYS
GROUP BY 1
ORDER BY last_query_time ASC

Identify who created unused endpoints​

To find who created endpoints that are serving unused indexes, use the following query. This can help you reach out to the right team for cleanup.

SQL
SELECT
  request_params['name'] AS endpoint_name,
  user_identity.email AS created_by,
  event_time AS created_at
FROM system.access.audit
WHERE service_name = 'vectorSearch'
  AND action_name = 'createEndpoint'
  AND event_date >= current_date() - INTERVAL 365 DAYS
ORDER BY event_time DESC

Customize the lookback window​

The examples above use a 30-day window to define "unused." Adjust the INTERVAL value based on your use case:

  • 30 days: Good default for most workloads.
  • 7 days: Use for workloads that should be queried daily.
  • 90 days: Use for batch or seasonal workloads that run less frequently.

Replace INTERVAL 30 DAYS in the queried_indexes CTE with your preferred window.

Next steps​