The Query Store feature in SQL Server serves as a valuable tool for troubleshooting performance issues by allowing users to quickly identify performance degradation caused by changes to query plans. In the given example, we can observe a SQL query (ID 23058) that has two query plans. The yellow dot corresponds to a query plan that exhibits a relatively stable performance, whereas the blue dots indicate a more fluctuating performance plan. To enhance the stability of this SQL's performance, we can designate the yellow dot's query plan as the default plan by using the "Force Plan" function in Query Store. Query Store is a powerful feature provided by SQL Server that enables users to Force a specific query plan for a SQL in Query Store. However, Query Store has limitations, as it does not allow users to create a new query plan that has not been generated before. Its use is reactive, meaning it only allows for the recovery of degraded SQL performance without providing a means for users to improve SQL statements that better plans were not generated before. How to manually tune a SQL with Query Store? If you want to manually improve the performance of a SQL query stored in Query Store, the process can be quite complex. Here are some general steps to follow as a guideline: 1. Extract the SQL text you want to tune from the system tables sys.query_store_query and sys.query_store_query_text. 2. Tune the SQL by injecting various hints and identifying the best hint application to improve query performance. 3. Create a plan guide for the SQL text, keeping the original SQL text format and incorporating the hints identified in step 2. 4. Execute the SQL with the newly created plan guide to generate a new query plan in Query Store. 5. Use SQL Server Management Studio to force the new query plan with the SQL. 6. Finally, drop the plan guide. By following these steps, users can manually tune a SQL query in Query Store and achieve improved performance. However, it is important to note that this process can be complex and time-consuming, and should only be undertaken by experienced database administrators with a deep understanding of SQL performance optimization. A new product designed to optimize SQL statements for Query Store Tosska DB Ace for SQL Server marks a significant leap forward in this domain since it surpasses the reactive recovery capabilities of Query Store and introduces proactive SQL performance enhancement. This pioneering technology allows users to extract SQL from the Query Store and optimize it by creating new and improved query plans within the Query Store. With Tosska DB Ace, users can implement these new plans to their SQL without requiring any modifications to the program source code or extensive testing. Tosska DB Ace Enterprise for SQL Server - Tosska Technologies Limited DBAS Tune SQL QS - YouTube
