Forum Discussion
Could not find stored procedure 'OPTIMIZE' in Synapse
Hi Fellow Azure people!
I'm trying to use the OPTIMIZE procedure on a delta table in Synapse but I'm getting a "Could not find stored procedure 'OPTIMIZE'" error.
Delta's docs do mention that this feature is only available in Delta Lake 1.2.0 and above, I've double checked and we are running Delta 1.2
Below is an example of what I'm doing:
OPTIMIZE '/path/to/delta/table' -- Optimizes the path-based Delta Lake table
Does anyone know what this could be happening?
I did notice that there was no reference to OPTIMIZE in the Synapse docs but it did exist in the Databricks docs. Perhaps the procedure hasn't been implemented in Synapse yet?
2 Replies
Hi Kam , yes, your suspicion is basically right, but the error also gives a clue about where the command is being run.
Could not find stored procedure 'OPTIMIZE' normally means Synapse is treating this as a T-SQL command, so you are likely running it from a Synapse SQL script, serverless SQL pool, or dedicated SQL pool. OPTIMIZE is a Delta/Spark table maintenance command, not a SQL stored procedure in Synapse SQL.
Try running it from a Spark notebook attached to a Synapse Spark pool, for example:
%%sql
OPTIMIZE delta.`/path/to/delta/table`
Or, if the Delta table is registered in the Spark catalog:
%%sql
OPTIMIZE database_name.table_name
Also note the syntax difference: use delta.\path`for a path-based Delta table, not justOPTIMIZE '/path/to/delta/table'`
The OPTIMIZE command is not supported in Azure Synapse serverless SQL pools. It is a Databricks Delta Lake feature, and while Microsoft Fabric and Databricks both support OPTIMIZE for Delta tables, Synapse does not currently implement this stored procedure. In Synapse, you must use alternative compaction strategies (e.g., partitioning, auto compaction, or optimize write) rather than calling OPTIMIZE.
