Snippet Name: Index_Recommendations_Troubleshooting

Tags:

Created Date: 2025-06-16 13:12:40
Last Modified Date: 2025-05-31 10:31:34

Dependencies:

DBA Notes:

Sample Output:

Title: Index Recommendations Troubleshooting
Author: user
in SLQ server, when i query  select * from  sys.dm_db_tuning_recommendations tr, i only see "FORCE_LAST_GOOD_PLAN" recommendations,a nd not index recommendations

Author: ChatGPT
In SQL Server, the `sys.dm_db_tuning_recommendations` dynamic management view (DMV) is used to retrieve tuning recommendations provided by the SQL Server Automatic Tuning feature. The types of recommendations typically include:

1. **FORCE_LAST_GOOD_PLAN**: This recommendation is made when the server detects that a query performance regression has occurred and it suggests using the last known good execution plan.
2. **CREATE_INDEX**: This recommendation is made when the server identifies missing indexes that could improve query performance.

If you are only seeing `FORCE_LAST_GOOD_PLAN` recommendations and not `CREATE_INDEX` recommendations, there could be several reasons for this:

1. **Query Workload**: The queries being run on the server might not benefit significantly from new indexes, or the automatic tuning feature has not detected any missing indexes that would improve performance.
2. **Index Usage**: The existing indexes might be sufficient for the current workload, and no additional indexes are deemed necessary by the tuning advisor.
3. **Statistics and Maintenance**: Ensure that statistics are up-to-date and that regular index maintenance (such as rebuilding or reorganizing indexes) is performed. Outdated statistics and fragmented indexes can affect the recommendations.
4. **Automatic Tuning Configuration**: The automatic tuning feature might not be fully configured to provide index recommendations. Check the automatic tuning settings using the following query:

   ```sql
   SELECT * 
   FROM sys.database_automatic_tuning_options
   WHERE option_name = 'create_index'
   ```

   Ensure that the `desired_state` is set to `ON` for index creation recommendations.

5. **Permissions**: Ensure that the user querying `sys.dm_db_tuning_recommendations` has the necessary permissions to see all recommendations. 

6. **Feature Limitations**: Be aware that some editions or configurations of SQL Server might have limitations on the automatic tuning feature's capabilities.

To investigate further, you can:

1. **Check Automatic Tuning Settings**:
   ```sql
   SELECT * 
   FROM sys.database_automatic_tuning_options
   WHERE option_name = 'create_index'
   ```

2. **Review Query Store Data**: Examine the query store data to see if there are any insights into query performance and potential missing indexes.
   ```sql
   SELECT * 
   FROM sys.query_store_recommendations
   ```

3. **Manual Index Analysis**: Use the Database Engine Tuning Advisor (DTA) or manually analyze the workload to identify potential missing indexes.

4. **Extended Events**: Enable and monitor extended events related to automatic tuning to gain more insights into its operation and recommendations.

By following these steps, you can better understand why you are not seeing `CREATE_INDEX` recommendations and take appropriate actions to ensure your SQL Server is optimized for performance.

Author: user
how can i find any tuning recommendations for plans that were actually implemented 

Author: ChatGPT
To find tuning recommendations that were actually implemented by SQL Server, you can use the `sys.dm_db_tuning_recommendations` DMV in combination with other system views to see the history of implemented recommendations. Here???s how you can do it:

### Checking Implemented Tuning Recommendations

1. **Query the `sys.dm_db_tuning_recommendations` DMV**:
   This DMV shows both active and historical tuning recommendations. The `implementation_status` column will help you determine if a recommendation was implemented.

   ```sql
   SELECT 
       reason,
       score,
       state,
       details,
       start_time,
       last_modified,
       implementation_details
   FROM 
       sys.dm_db_tuning_recommendations
   WHERE 
       state = 'APPLIED'
   ```

   The `state` column will have values such as `PENDING`, `EXECUTING`, `SUCCESS`, `FAILED`, and `APPLIED`. For recommendations that have been implemented, look for `APPLIED`.

2. **Check Query Store for Implemented Plans**:
   The Query Store captures query performance data, including plans that have been forced or recommended by automatic tuning.

   ```sql
   SELECT 
       qsrs.plan_id,
       qsrs.query_id,
       qsrs.recommendation_type,
       qsrs.script,
       qsp.plan_forcing_type_desc,
       qsp.force_failure_count,
       qsqt.query_sql_text
   FROM 
       sys.query_store_recommendations qsrs
       JOIN sys.query_store_plan qsp ON qsrs.plan_id = qsp.plan_id
       JOIN sys.query_store_query qsq ON qsrs.query_id = qsq.query_id
       JOIN sys.query_store_query_text qsqt ON qsq.query_text_id = qsqt.query_text_id
   ```

   This query provides details on plans that were recommended and potentially implemented by the Query Store, along with their current status.

3. **Review Index Recommendations**:
   If you're specifically looking for index recommendations that were implemented, you can look into the `sys.indexes` view along with the `sys.dm_db_tuning_recommendations` DMV.

   ```sql
   SELECT 
       i.name AS IndexName,
       o.name AS TableName,
       i.index_id,
       i.type_desc,
       i.is_hypothetical,
       i.is_disabled,
       tr.reason,
       tr.score,
       tr.state,
       tr.details,
       tr.start_time,
       tr.last_modified
   FROM 
       sys.indexes i
       JOIN sys.objects o ON i.object_id = o.object_id
       JOIN sys.dm_db_tuning_recommendations tr ON i.name = tr.details.value('(/CreateIndexAction/Index/@Name)[1]', 'NVARCHAR(MAX)')
   WHERE 
       tr.state = 'APPLIED'
   ```

   This query correlates indexes with tuning recommendations, helping to identify which recommendations have been applied and resulted in index creation.

4. **Extended Events**:
   If you have extended events set up to track automatic tuning actions, you can query the extended event logs to see a history of applied tuning recommendations.

   ```sql
   -- This is an example of querying an extended events session log.
   -- Ensure you have an appropriate session set up to capture automatic tuning events.
   SELECT 
       event_data.value('(event/@name)[1]', 'NVARCHAR(50)') AS event_name,
       event_data.value('(event/data[@name="reason"]/value)[1]', 'NVARCHAR(MAX)') AS reason,
       event_data.value('(event/data[@name="recommendation"]/value)[1]', 'NVARCHAR(MAX)') AS recommendation,
       event_data.value('(event/data[@name="state"]/value)[1]', 'NVARCHAR(MAX)') AS state,
       event_data.value('(event/data[@name="implementation_status"]/value)[1]', 'NVARCHAR(MAX)') AS implementation_status,
       event_data.value('(event/@timestamp)[1]', 'DATETIME') AS timestamp
   FROM 
       sys.fn_xe_file_target_read_file('your_xel_path_here*.xel', NULL, NULL, NULL)
       CROSS APPLY 
       (SELECT CAST(event_data AS XML)) AS events(event_data)
   ```

By following these steps and utilizing the appropriate system views and DMVs, you can find detailed information about tuning recommendations that have been implemented in your SQL Server environment.