Contents
What should be included in a SQL plan guide?
In the plan guide, you specify the Transact-SQL statement that you want optimized and either an OPTION clause that contains the query hints you want to use or a specific query plan you want to use to optimize the query.
How to view LINQ to SQL generated SQL queries?
Now, let’s run the Application and run the trace in SQL Server Profiler. Click on Get Data button, the data will be populated on the page. Go to SQL Server Profiler and stop the trace, where you will see T-SQL statement of our stored procedure, as shown below. Notice that GetEmployees stored procedure is executed.
When to use a fixed query plan in SQL?
You can apply a fixed query plan to a plan guide of type OBJECT or SQL. Plan guides that apply a fixed query plan are useful when you know about an existing execution plan that performs better than the one selected by the optimizer for a particular query. The following example creates a plan guide for a simple ad hoc SQL statement.
How to create plan guide from query plan?
The @xml_showplan variable is then passed to the sp_create_plan_guide statement in the @hints parameter. Or, you can create a plan guide from a query plan in the plan cache by using the sp_create_plan_guide_from_handle stored procedure.
Can a plan guide be matched to a query?
Only plan guides in DB1 are eligible to be matched to the query because the query is executing in the context of DB1. For SQL- or TEMPLATE-based plan guides, SQL Server matches the values for the @module_or_batch and @params arguments to a query by comparing the two values character by character.
How does SQL Server match params in plan guide?
For SQL- or TEMPLATE-based plan guides, SQL Server matches the values for the @module_or_batch and @params arguments to a query by comparing the two values character by character. This means you must provide the text exactly as SQL Server receives it in the actual batch.
What does creating a plan guide of type do?
Creating a plan guide on a module removes the query plan for that module from the plan cache. Creating a plan guide of type OBJECT or SQL on a batch removes the query plan for a batch that has the same hash value. Creating a plan guide of type TEMPLATE removes all single-statement batches from the plan cache within that database.