Where to graph precise tsql for a query run from php

Question

I'd like to pin a plan for a query by adding a table hint. I've read the docs that says the query text pasted into @stmt must be the same as came from the app.

I used the Extended Events 'sql_statement_completed' and 'rpc_completed' to get precise sql query text from the app. But.

If I take sql text from sql_statement_completed I cannot get to params declaration and following complains about missing params declaration.

DECLARE @stmt nvarchar(max) = 'SELECT * FROM myTable WHERE item = @P1'

EXEC sp_create_plan_guide
    @name = N'Use my plan',
    @stmt = @sql,
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = NULL,
    @hints = N'OPTION (TABLE HINT(DG_DOCU, INDEX(myIndex)))';

If I use rpc_completed, it looks like

declare @p1 int
set @p1=10600
exec sp_prepexec @p1 output,N'@P1 int,N''SELECT * FROM myTable WHERE item = @P1',5
select @p1

But I cannot use whole content from rpc_completed because it contains multiple statements and execution of sp_create_plan_guide fails. And also the query text is one one line ...

So I am in a dead end and cannot move forward. The most probable way is

• take param declaration from rpc_completed
• prepend it to a sql text from sql_statement_completed But if I make a mistake - one more space or newline, the query text will not exact match.

Answer 1

You could try

DECLARE @stmt nvarchar(max) = '(@P1 int)SELECT * FROM myTable WHERE item = @P1'

But plan guides are not famous for being simple to use. What version of SQL Server are you on? In SQL 2022 and Azure SQL Database, you can inject hints through Query Store, which is somewhat simpler, since you don't have to do this text-matching ballet.