I initially had lots of trouble creating my first plan guide. There are some strict rules to getting them to work, but when they do, it’s magical.
Plan guides are unique to a database – you need to make sure you are in the right database when you create it. If a query runs in master but accesses other databases, you need to create your guide in master. Likewise, if the query is local to one database, create it in that database.
To look at existing plan guides…
select * from sys.plan_guides
Once you have determined the reason for creating a plan guide (vendor product, middleware code from entity framework etc), you’ll need to start preparing the script. Plan guides can only be created in T-SQL. The command is:
EXEC sys.sp_create_plan_guide @name = '' , @stmt = '' , @type = '' , @module_or_batch = '' , @params = '' , @hints = ''
@name – Is the name you will give the plan guide.
@stmt – Is the SQL statement. This is the catch, you have to get this from SQL Profiler, because it has to be exact. Using the textdata field in the trace to identify your query, right click on the row and select Extract Event Data. Also, be sure to run a server side trace i.e. SQL Trace writing to a file – don’t use the Profiler GUI as there is too much risk of it affecting the SQL Server. Then you can find your particular query using the Trace Functions to query the active trace file. To query the trace file, use this:
SELECT * FROM ::fn_trace_gettable(‘<path to the trace file>’, DEFAULT)
Make sure you have the start time in your trace, so you can identify the row easily when you open profiler to extract the row. When you Extract Event Data, save to a location as a .sql file and then open in SSMS.
Copy the T-SQL from the Event Data unchanged into the @stmt value. in a parameterised query, do not include the parameters, only the SQL.
@type – is one of three options and mostly will be SQL
@module_or_batch – Make this NULL
@params – This is the section of the query taken from profiler that has the parameters. Only pull the Parameters and not the values e.g.
N’@p0 int,@p1 int,@p2 nvarchar(4000)’
@hints – This is the whole reason for creating the plan guide – enter your hint here following Query hint rules e.g
OPTION (MAXDOP 1)
Once you have successfully created a plan guide, matching query plans in the cache are removed. If you have lots of single use plans and you’ve created a plan guide to force parameterisation, you will see al these plan disappear.
Next step is testing the plan guide. The best way to do this is by querying the plan cache and looking at the execution plan. To query the plan cache, use the followings, inserting a snippet of the query to find the object in the cache:
SELECT * FROM sys.dm_exec_cached_plans AS cp
INNER JOIN sys.dm_exec_query_stats AS qs ON qs.plan_handle = cp.plan_handle
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) as qp
WHERE st.text like ‘% enter your snippet here %’
Once you have found your query, open the Query Plan and in the Graphical Plan Window, right click and select Show Execution Plan XML. Inside the XML, Ctrl-F to find “PlanGuide”. If this exists, this will confirm that the plan guide has been applied.
As mentioned earlier, to view a Plan Guide’s existence, query sys.plan_guides in the correct database.
To drop a plan guide, execute:
EXEC sp_control_plan_guide @operation = ‘drop’, @name = <name of the plan guide>.
If at any time any sort of upgrade occurs of either the appplication or SQL, you need to re-evaluate the use of the plan guide.