Creating a plan guide on a query submitted by using an API cursor request
Definition Description
https://msdn.microsoft.com/en-us/library/ms179880(v=sql.90).aspx Plan guides can match queries that are submitted from API server cursor routines. These routines include sp_cursorprepare, sp_cursorprepexec, and sp_cursoropen. Applications that use the ADO, OLE DB, and ODBC APIs frequently interact with SQL Server by using API server cursors. For more information, see API Server Cursors. You can see the invocation of API server cursor routines in SQL Server Profiler traces by viewing the RPC:Starting profiler trace event. Suppose the following data appears in an RPC:Starting profiler trace event for a query you want to tune with a plan guide: DECLARE @p1 int; SET @p1=-1; DECLARE @p2 int; SET @p2=0; DECLARE @p5 int; SET @p5=4104; DECLARE @p6 int; SET @p6=8193; DECLARE @p7 int; SET @p7=0; EXEC sp_cursorprepexec @p1 output,@p2 output,N'@P1 varchar(255),@P2 varchar(255)',N'SELECT * FROM Sales.SalesOrderHeader h INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d ON h.SalesOrderID = d.SalesOrderID WHERE h.OrderDate BETWEEN @P1 AND @P2',@p5 OUTPUT,@p6 OUTPUT,@p7 OUTPUT,'20040101','20050101' SELECT @p1, @p2, @p5, @p6, @p7; You notice that the plan for the SELECT query in the call to sp_cursorprepexec is using a merge join, but you want to use a hash join. The query submitted by using sp_cursorprepexec is parameterized, including both a query string and a parameter string. You can create the following plan guide to change the choice of plan by using the query and parameter strings exactly as they appear, character for character, in the call to sp_cursorprepexec. EXEC sp_create_plan_guide @name = N'APICursorGuide', @stmt = N'SELECT * FROM Sales.SalesOrderHeader AS h INNER JOIN Sales.SalesOrderDetail AS d ON h.SalesOrderID = d.SalesOrderID WHERE h.OrderDate BETWEEN @P1 AND @P2', @type = N'SQL', @module_or_batch = NULL, @params = N'@P1 varchar(255),@P2 varchar(255)', @hints = N'OPTION(HASH JOIN)'; Subsequent executions of this query by the application will be affected by this plan guide, and a hash join will be used to process the query.
