SQL Server 2025 Has Automatic Index Management Parts, but I Could Not Turn CREATE_INDEX On
Finding new system objects with unclear support status makes me slow down. SQL Server 2025 includes ten sp_auto_tuning_* procedures and five internal automatic tuning views that line up with create-index recommendations.
This post is part of the Undocumented Changes in SQL Server 2022 and 2025 series. The contents page lists every part in order.
The earlier post on the sp_auto_tuning_* procedures in SQL Server 2025 mapped the new names at a high level. For this post, I read every related definition in the 2025 inventory, mapped what each object reads and writes, and tested the documented CREATE_INDEX automatic tuning option on a scratch database.
The current Microsoft Learn pages describe different scopes for SQL Server, Azure SQL Database and Fabric. The SQL Server automatic tuning article says SQL Server automatic tuning fixes query execution plan choice regressions. The same article says automatic index management identifies indexes that should be added or removed, and applies to Azure SQL Database and SQL database in Microsoft Fabric.[1] The Azure SQL automatic tuning overview describes CREATE INDEX, DROP INDEX, and FORCE LAST GOOD PLAN automatic tuning options for Azure SQL Database, with CREATE INDEX supported for single and pooled databases but not instance databases.[2]
The Transact-SQL syntax page matches that split. For SQL Server, ALTER DATABASE SET AUTOMATIC_TUNING shows only FORCE_LAST_GOOD_PLAN = DEFAULT | ON | OFF. The Azure SQL Database and Fabric syntax on the same page includes CREATE_INDEX and DROP_INDEX.[3]
What ships in the box
Against SQL Server 2022 CU25, my SQL Server 2025 CU9 inventory has ten new sp_auto_tuning_* procedures and five new internal views. Seven of the procedures have T-SQL definitions. Three are extended stored procedures, so the inventory has their names but not a T-SQL body.
I also searched the SQL Server, Azure and Fabric documentation source repositories for each new procedure and internal view name. I used whole-word, fixed-string git grep, then repeated the check case-insensitively. I found no matches for the new names in my local checkouts of those three documentation repositories on 2026-09-26.[4]
The internal views are wrappers over OPENROWSET(TABLE DM_DB_INTERNAL_ATS_...) rowsets. The T-SQL procedures use sys.ats_* tables. I could not find public documentation for the ATS name, so I am not expanding it.
| Object | Reads | Writes |
|---|---|---|
sys.dm_db_internal_auto_tuning_create_index_recommendations |
DM_DB_INTERNAL_ATS_RECOMMENDATIONS, DM_DB_INTERNAL_ATS_INDEX_RECOMMENDATIONS, DM_DB_INTERNAL_ATS_DIM_STATE_NAMES |
None |
sys.dm_db_internal_auto_tuning_recommendation_impact_query_metrics |
DM_DB_INTERNAL_ATS_INDEX_RECOMMENDATION_IMPACT_QUERIES |
None |
sys.dm_db_internal_auto_tuning_recommendation_metrics |
DM_DB_INTERNAL_ATS_RECOMMENDED_IMPACT_VALUES |
None |
sys.dm_db_internal_auto_tuning_workflows |
DM_DB_INTERNAL_ATS_WORKFLOW_FSM, DM_DB_INTERNAL_ATS_WORKFLOW_RECOMMENDATION_RELATION |
None |
sys.dm_db_internal_automatic_tuning_version |
DM_DB_INTERNAL_ATS_PROPERTIES |
None |
sys.sp_auto_tuning_publish_index_recommendation |
sys.ats_recommendations, sys.ats_index_recommendations |
Inserts, updates and deletes recommendation, index detail, workflow relation, query impact and impact value rows |
sys.sp_auto_tuning_create_workflow |
sys.ats_recommendations |
Inserts workflow and workflow-to-recommendation relation rows |
sys.sp_auto_tuning_update_workflow |
sys.ats_workflow_fsm |
Updates workflow state, retry count and properties |
sys.sp_auto_tuning_update_index_recommendation |
sys.ats_recommendations |
Updates recommendation state, archive flag, extended properties and cached index metrics |
sys.sp_auto_tuning_update_create_index_workflow |
Calls the workflow and recommendation update procedures | Writes through those procedures |
sys.sp_auto_tuning_index_recommendation_verification_report |
sys.ats_workflow_fsm, sys.ats_recommendations |
Merges observed impact values and per-query metrics, then updates cached reported metrics |
sys.sp_auto_tuning_cleanup_index_recommendations |
Archived terminal-state recommendation rows older than a cutoff | Deletes impact, workflow, relation, index recommendation and recommendation rows |
The definition that most directly shows create-index recommendation storage is sys.sp_auto_tuning_publish_index_recommendation. Its parameters include schema name, table name, index columns, included columns, index name, estimated space change, impact query hashes, a recommendation ID output parameter and an is_new output parameter. Its body inserts a row with type_id = 0, commented as for create index, into sys.ats_recommendations. Then it inserts the index details into sys.ats_index_recommendations.
sys.sp_auto_tuning_index_recommendation_verification_report appears to support post-action reporting. It accepts reported CPU, logical read, logical write, improved-query and regressed-query values. It can parse @observed_query_level_impacts_json with OPENJSON, then merge per-query rows into sys.ats_index_recommendation_impact_queries.
These definitions show objects that appear to store, track and report create-index recommendations. They do not show that the boxed product supports automatic index creation on SQL Server.
The test I ran
I did not execute any sp_auto_tuning_* procedure. I created a scratch database, ran the documented automatic tuning syntax that Azure SQL Database and Fabric expose, captured the error, and then read the database automatic tuning option state.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
SET NOCOUNT ON; IF DB_ID(N'undoc_spinoff_autotune') IS NULL BEGIN CREATE DATABASE [undoc_spinoff_autotune]; END; GO BEGIN TRY EXEC(N'ALTER DATABASE [undoc_spinoff_autotune] SET AUTOMATIC_TUNING ( CREATE_INDEX = ON );'); SELECT [result] = N'ALTER succeeded'; END TRY BEGIN CATCH SELECT [error_number] = ERROR_NUMBER() , [error_severity] = ERROR_SEVERITY() , [error_state] = ERROR_STATE() , [error_message] = ERROR_MESSAGE(); END CATCH; GO USE [undoc_spinoff_autotune]; GO SELECT [name] , [desired_state_desc] , [actual_state_desc] , [reason_desc] FROM sys.database_automatic_tuning_options ORDER BY [name]; |
On my local SQL Server 2025 CU9 test instance, the ALTER DATABASE statement did not succeed.
|
1 2 3 4 5 6 7 |
error_number error_severity error_state error_message ------------ -------------- ----------- ------------------------------- 102 15 124 Incorrect syntax near 'CREATE_INDEX'. name desired_state_desc actual_state_desc reason_desc -------------------- ------------------ ----------------- ------------- FORCE_LAST_GOOD_PLAN DEFAULT OFF AUTO_CONFIGURED |
In this SQL Server 2025 CU9 boxed-product test, CREATE_INDEX = ON was not accepted by the parser, and sys.database_automatic_tuning_options showed only FORCE_LAST_GOOD_PLAN for the scratch database.
The sys.database_automatic_tuning_options Learn page says the view returns automatic tuning options for the current database, and points readers to the ALTER DATABASE SET AUTOMATIC_TUNING syntax for available options.[5] My result matches the SQL Server syntax page rather than the Azure SQL Database and Fabric syntax.
Support status in boxed SQL Server
The definitions include tables, procedures, workflows and reporting paths for recommendations. The procedures maintain recommendation rows, index recommendation rows, workflow state, workflow-to-recommendation links, estimated impact values and observed query-level impact values. The internal views read those same internal rowsets back out.
I treat the objects as unsupported internals on boxed SQL Server unless Microsoft documents otherwise. In the current SQL Server documentation, the SQL Server syntax lists only FORCE_LAST_GOOD_PLAN. The public switch for CREATE_INDEX exists in the Azure SQL Database and Fabric syntax, but that syntax was not accepted on my SQL Server 2025 CU9 test database.
If you see different behaviour on another SQL Server 2025 build, or you find documentation I missed, tell me on Bluesky or LinkedIn. I will update the notes.
References
- Automatic tuning - SQL Server - Microsoft Learn. States that SQL Server automatic tuning fixes query execution plan choice regressions, and that automatic index management applies to Azure SQL Database and SQL database in Microsoft Fabric. ↩
- Automatic Tuning Overview - Azure SQL and SQL database in Fabric - Microsoft Learn. Describes the
CREATE INDEX,DROP INDEX, andFORCE LAST GOOD PLANautomatic tuning options for Azure SQL Database. ↩ - ALTER DATABASE SET Options (Transact-SQL) - Microsoft Learn. Shows the SQL Server
AUTOMATIC_TUNINGsyntax and the separate Azure SQL Database and Fabric syntax that includesCREATE_INDEXandDROP_INDEX. ↩ - MicrosoftDocs/sql-docs, MicrosoftDocs/azure-docs and MicrosoftDocs/fabric-docs - GitHub. The Microsoft Learn source repositories searched in local checkouts with whole-word fixed-string
git grep, including case-insensitive follow-up, on 2026-09-26. Checked commits: sql-docs2e21fb0, azure-docsbb03aa73, fabric-docs2975a77. ↩ - sys.database_automatic_tuning_options (Transact-SQL) - Microsoft Learn. Documents the view as returning automatic tuning options for the current database and points to
ALTER DATABASE SET AUTOMATIC_TUNINGfor available options. ↩