How do I create a SQL Server Plan Guide?

How do I create a SQL Server Plan Guide?

OBJECT plan guide

1. CREATE PROCEDURE Sales.
2. sp_create_plan_guide @name = N’Guide1′, @stmt = N’SELECT *FROM Sales.
3. SELECT TOP 1 * FROM Sales.
4. sp_create_plan_guide @name = N’Guide2′, @stmt = N’SELECT TOP 1 * FROM Sales.
5. exec sp_executesql N’SELECT * FROM Sales.

How do I enable show plan in SQL?

Use SQL Server Profiler

1. Start SQL Server Profiler.
2. In the File menu, select New Trace.
3. In the Events Section tab, check Show all events.
4. Expand the Performance node.
5. Select Showplan XML.
6. Execute the query you want to see the query plan for.
7. Stop the trace.
8. Select the query plan in the grid.

What is a SQL plan guide?

SQL plan guides match queries that execute in the context of stand-alone Transact-SQL statements and batches that are not part of a database object. SQL-based plan guides can also be used to match queries that parameterize to a specified form.

How are plan guides used in SQL Server?

SQL-based plan guides can also be used to match queries that parameterize to a specified form. TEMPLATE plan guides match stand-alone queries that parameterize to a specified form. These plan guides are used to override the current PARAMETERIZATION database SET option of a database for a class of queries.

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.

When to use SQL profiler to create plan guide?

Use SQL Server Profiler to Create and Test Plan Guides. When you are creating a plan guide, you can use SQL Server Profiler to capture the exact query text for use in the statement_text argument of the sp_create_plan_guide stored procedure. This helps make sure that the plan guide will be matched to the query at compile time.

How to use SQL Server plan Guide to tune queries?

Re-running the queries, the plan guide in place forces the SQL statement to recompile generating the most optimal query plan for a passed in ProductID. This comes without having to change a stitch of code. Examining the execution plans, we now see that a table scan is used for ProductID = 870 but an index seek is used on ProductID = 897.