Leveraging Plan Guides in MS SQL Server can prove beneficial for optimizing the performance of a particular query within database objects like stored procedures and trigger applications. This technique allows you to enhance query efficiency without requiring modifications to the application's source code. Here are the steps to use Plan Guides to tune third-party applications SQL in MS SQL Server without changing the source code: 1. Identify the SQL that are causing performance issues in the database object. 2. Create a Plan Guide that offers an optimized execution plan for the specified query by incorporating query hints to influence the decision-making process of the optimizer. 3. Test the Plan Guide to ensure that it provides the desired performance improvements and does not cause any unintended side effects. 4. Deploy the Plan Guide to production and monitor the performance of the application to ensure that the Plan Guide is being used and is providing the desired performance improvements. Before optimizing an database object’s SQL statement executed by an application program without modifying the source code, it is crucial to understand how the SQL statement matches the one specified in the Plan Guide, which includes whitespace and comments. Additionally, it is important to match the database object that execute the SQL statement. Below is an example that demonstrates how to optimize a SQL statement from a database object called getRD2. The SQL statement is selected and highlighted by the user. Creating a plan guide may initially seem complex, but it is a valuable method for improving SQL performance without modifying the source code or lacking the necessary permissions. The most time-consuming part involves finding the best query hint for the SQL statement using the @hints = N'OPTION(query_hint [ ,...n ]) parameter in the sp_create_plan_guide system procedure. If you don't have extensive knowledge of SQL tuning techniques or enough time for experimentation, a solution is available that simplifies the process. It captures SQL, identifies the SQL source type, automatically optimizes query hints, and facilitates easy deployment of plan guides. The following product automatically identifies a Plan Guide, as depicted in the accompanying screenshot, which can enhance SQL performance by 75.81%. After identifying the optimal Plan Guide, we can proceed to deploy it along with the stored procedure to the SQL Server database. This deployment will result in improved performance for the stored procedure named getRD2, all without requiring any modifications to the source code of the stored procedure. You also have the option to manually create the Plan Guide using the system procedure sp_create_plan_guide once you have obtained a suitable hint solution for the SQL statement. For detailed information, kindly visit our website and take a look at our demo video. Tosska DB Ace Enterprise for SQL Server - Tosska Technologies Limited DBAS Tune SQL PG Object - YouTube
