I am trying to make a plan guide for a query that keeps being sent from a vendor app that basically pollutes my procedure cache with thousands and thousands of adhoc query plans. Unfortunately there is some odd spacing in the query and it would appear that my plan guide is not working because the difference in spacing.The original query looks like this:SELECT TOP 1000 p.PatientID FROM Db.dbo.tblPatient p WHERE PatientNumber like '12345678' AND COALESCE(NonPatient,0) = 0 AND (DeactivatedDateTime IS NULL)
my code for creating the plan guide looks like this:EXEC sp_get_query_template N'SELECT TOP 1000 p.PatientID FROM Db.dbo.tblPatient p WHERE PatientNumber like ''12345678'' AND COALESCE(NonPatient,0) = 0 AND (DeactivatedDateTime IS NULL) ', @stmt OUTPUT, @params OUTPUT;EXEC sp_create_plan_guide N'pg_tblPatient_PatientID', @stmt, N'TEMPLATE', NULL, @params, N'OPTION(PARAMETERIZATION FORCED)';
When I create this and then query sys.plan_guides SQL server returns the following for the query text and scope batch:select top 1000 p . PatientID from Db . dbo . tblPatient p where PatientNumber like '12345678' and coalesce ( NonPatient , @0 ) = @1 and ( DeactivatedDateTime is null )
Why is SQL not taking the query exactly as I have been giving it with all the spacing intact?
Problems with plan guides
Posted - 2009-05-26 : 16:00:52
