The sp_auto_tuning_* Procedures in SQL Server 2025
You open a SQL Server 2025 system-object diff and see ten new procedures that start with sp_auto_tuning_. The prefix is notable because Azure SQL Database supports automatic index creation and removal. The box product already had automatic plan correction. The question is whether SQL Server 2025 now ships the Azure automatic index management machinery too.
This is part 5 in the SQL Server 2025 section of the undocumented system-object series. The wider method is in the first post on SQL Server 2025. The full series starts with the first post in this series. SQL Server 2025 has new internal objects that read and write create-index recommendations. I found no public documentation for the new sp_auto_tuning_* procedures, and my test instance had no create-index recommendations when I queried it.
What Microsoft documents
Microsoft Learn says automatic tuning in SQL Server identifies and fixes query execution plan choice regressions. The same page describes automatic index management as identifying indexes to add and indexes to remove. It says that feature applies to Azure SQL Database and SQL database in Microsoft Fabric.[1]
The SQL Server ALTER DATABASE syntax on Learn matches that split. For SQL Server, the AUTOMATIC_TUNING syntax exposes only FORCE_LAST_GOOD_PLAN = DEFAULT | ON | OFF. On the same page, the Azure and Fabric syntax also exposes CREATE_INDEX and DROP_INDEX.[2]
The documented dynamic management view (DMV), sys.dm_db_tuning_recommendations, also still describes plan-correction recommendations. A DMV is a system view that returns runtime or internal state. The Learn page says its type column contains an option such as FORCE_LAST_GOOD_PLAN, and its examples parse planForceDetails from JSON.[3]
The new SQL Server 2025 objects appear in addition to the documented plan-correction objects.
What changed in the 2025 catalogue
Against SQL Server 2022 CU25, my SQL Server 2025 CU9 inventory found 15 new objects with auto_tuning or automatic_tuning in the name. The same 15 objects are also present in the 2025 RTM inventory, so this part is not a CU9-only change.
| Object group | Count | What I found |
|---|---|---|
sp_auto_tuning_* procedures |
10 | 7 T-SQL procedures with definitions, and 3 extended stored procedures without T-SQL definitions |
| Internal recommendation views | 5 | Create-index recommendations, recommendation metrics, per-query impact metrics, workflows and an internal version view |
SQL Server 2019 and 2022 already had the documented automatic tuning views: sys.database_automatic_tuning_mode, sys.database_automatic_tuning_options, sys.dm_db_tuning_recommendations, and the extended stored procedure sys.sp_configure_automatic_tuning. SQL Server 2022 added sys.database_automatic_tuning_configurations. SQL Server 2025 adds the create-index recommendation group.
I searched the SQL Server, Azure and Fabric documentation source repositories for every new sp_auto_tuning_* procedure and every new sys.dm_db_internal_auto_tuning_* view. I used whole-word fixed-string git grep. I found zero matches for those new names. The same search found older documented objects in this naming family, including sys.database_automatic_tuning_options and sys.dm_db_tuning_recommendations.[4]
The procedure bodies point at create-index recommendations
The seven T-SQL procedures update recommendation and workflow state. I did not execute any of them. I read the definitions captured over the Dedicated Admin Connection (DAC), which is SQL Server’s administrator-only connection for diagnostics.
The name and parameters for sys.sp_auto_tuning_publish_index_recommendation point to index recommendations. Its parameters include @schema, @table, @index_columns, @included_columns, @index_name, @estimated_space_change, @impact_queries, @recommendation_id OUTPUT, and @is_new OUTPUT. Inside the body, it updates older active recommendations to Expired. It inserts into sys.ats_recommendations, inserts the index details into sys.ats_index_recommendations, links the recommendation to a workflow, and records per-query metrics.
The definition includes the comment -- Insert a new recommendation, followed by an insert into sys.ats_recommendations. It then inserts into sys.ats_index_recommendations with the schema, table, index columns, included columns and index name.
sys.sp_auto_tuning_index_recommendation_verification_report records query impact data after an action runs. Its parameters include reported changes for CPU, logical reads, logical writes, improved queries and regressed queries. It parses @observed_query_level_impacts_json with OPENJSON, then merges values into sys.ats_recommended_impact_values and sys.ats_index_recommendation_impact_queries.
The view definitions align with those writes. sys.dm_db_internal_auto_tuning_create_index_recommendations selects from internal rowsets named DM_DB_INTERNAL_ATS_RECOMMENDATIONS, DM_DB_INTERNAL_ATS_INDEX_RECOMMENDATIONS, and DM_DB_INTERNAL_ATS_DIM_STATE_NAMES. sys.dm_db_internal_auto_tuning_workflows selects from DM_DB_INTERNAL_ATS_WORKFLOW_FSM and DM_DB_INTERNAL_ATS_WORKFLOW_RECOMMENDATION_RELATION.
The definitions show catalogue objects for create-index recommendation storage, workflow state, impact metrics and clean-up. I would not call that proof that SQL Server 2025 automatically creates or drops indexes. The documented SQL Server syntax still exposes only FORCE_LAST_GOOD_PLAN, so I treat the index objects as internal evidence rather than a supported feature switch.
What the local instance returned
I ran read-only checks against the local 2025 instance. First, I checked the procedures and their parameters:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SELECT [object_name] = SCHEMA_NAME(o.schema_id) + N'.' + o.name , [object_type] = o.type_desc FROM sys.system_objects AS o WHERE o.name LIKE N'sp_auto_tuning%' ORDER BY o.name; SELECT [object_name] = SCHEMA_NAME(o.schema_id) + N'.' + o.name , p.parameter_id , [parameter_name] = p.name , [type_name] = TYPE_NAME(p.user_type_id) , p.max_length , p.is_output FROM sys.system_objects AS o INNER JOIN sys.all_parameters AS p ON p.object_id = o.object_id WHERE o.name LIKE N'sp_auto_tuning%' ORDER BY o.name, p.parameter_id; |
The first query returned 10 procedures. Seven are SQL_STORED_PROCEDURE, and three are EXTENDED_STORED_PROCEDURE: sp_auto_tuning_agent_notify_deactivate, sp_auto_tuning_try_build_internal_tables, and sp_auto_tuning_validate_executable. sys.all_parameters returned 89 parameters for the seven T-SQL procedures. It returned no parameters for the three extended stored procedures.
Then I checked the new internal create-index recommendation DMV and the two documented tuning views.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT COUNT_BIG(*) AS [internal_create_index_recommendation_rows] FROM sys.dm_db_internal_auto_tuning_create_index_recommendations; SELECT [name] , [desired_state_desc] , [actual_state_desc] , [reason_desc] FROM sys.database_automatic_tuning_options ORDER BY [name]; SELECT COUNT_BIG(*) AS [dm_db_tuning_recommendations_rows] FROM sys.dm_db_tuning_recommendations; |
In the user database on my local 2025 instance, the internal create-index recommendation view returned zero rows. sys.dm_db_tuning_recommendations also returned zero rows. sys.database_automatic_tuning_options is scoped to the current database. It returned no rows in master, and one row in msdb, model and a user database: FORCE_LAST_GOOD_PLAN, with desired state DEFAULT, actual state OFF, and reason AUTO_CONFIGURED.
Zero rows is not proof that SQL Server 2025 cannot produce a create-index recommendation. It only proves that this idle local instance had none when I queried it. New internal create-index objects exist. The public SQL Server documentation still describes automatic index management as Azure SQL Database and Fabric behaviour.
Where this leaves me
SQL Server 2025 ships more automatic tuning objects than SQL Server 2022. Those definitions identify recommendation, workflow and impact data. They store create-index recommendations, link them to workflows, record estimated and observed impact, and clean up archived recommendation rows.
I still wouldn’t turn that into a support statement. I found no public documentation for the new procedures or internal views, and I found no documented SQL Server switch for CREATE_INDEX or DROP_INDEX. Future builds may expose additional behaviour through these objects, but the current evidence does not establish that.
Next in the series: System procedures whose code changed in SQL Server 2025.
If you have seen these procedures called on a SQL Server 2025 test instance, tell me on Bluesky or LinkedIn. I’ll update the notes.
References
- Automatic tuning - SQL Server - Microsoft Learn. States that SQL Server automatic tuning fixes query execution plan choice regressions, while automatic index management applies to Azure SQL Database and SQL database in Microsoft Fabric. ↩
- ALTER DATABASE SET Options (Transact-SQL) - Microsoft Learn. Shows the SQL Server
AUTOMATIC_TUNINGsyntax and the Azure/FabricCREATE_INDEX,DROP_INDEX, andFORCE_LAST_GOOD_PLANoptions. ↩ - sys.dm_db_tuning_recommendations (Transact-SQL) - Microsoft Learn. Documents the recommendation columns and examples for plan-correction recommendations. ↩
- MicrosoftDocs/sql-docs, MicrosoftDocs/azure-docs and MicrosoftDocs/fabric-docs - GitHub. The Markdown source of Microsoft Learn for SQL Server, Azure and Fabric, searched with whole-word, fixed-string
git grepon 2026-09-26. The search results are my own. ↩