SQL Server – Unable to Use Plan Guide with sp_execute

I have an application that's querying a 10 million rows table.

The query is filtering by an integer column which is indexed. This column contains only NULLs and the query always looks for non-NULL values, so it should go fast.

The thing is that the application sends the query with sp_prepare and then sp_execute and it's basically OPTIMIZE FOR (UNKNOWN) (see Erik's answer for more information about this).

In short:

1. sp_execute from the application => optimize for (unknown)
2. density vector used to get estimates
3. only one value (NULL) for the entire row, so estimate = row count
4. full scan of the table when the query returns 0 rows = bad performance

Since I can't change the application code, I thought that a plan guide with OPTIMIZE FOR (@P0 = 1) would fix everything.

I got the query text from an extended event session (copied from the sql_text column, but removed the (@P0 bigint) part)

And then I created the plan guide like so:

EXEC sp_create_plan_guide 
    @name = N'Test_1',
    @stmt = N'select subsidiary0_.primary_event_gkey as primary40_1_, subsidiary0_.gkey as gkey1_, subsidiary0_.gkey as gkey1667_0_, subsidiary0_.operator_gkey as operator2_1667_0_, subsidiary0_.complex_gkey as complex3_1667_0_, subsidiary0_.facility_gkey as facility4_1667_0_, subsidiary0_.yard_gkey as yard5_1667_0_, subsidiary0_.placed_by as placed6_1667_0_, subsidiary0_.placed_time as placed7_1667_0_, subsidiary0_.event_type_gkey as event8_1667_0_, subsidiary0_.applied_to_class as applied9_1667_0_, subsidiary0_.applied_to_gkey as applied10_1667_0_, subsidiary0_.applied_to_natural_key as applied11_1667_0_, subsidiary0_.note as note1667_0_, subsidiary0_.billing_extract_batch_id as billing13_1667_0_, subsidiary0_.quantity as quantity1667_0_, subsidiary0_.quantity_unit as quantity15_1667_0_, subsidiary0_.responsible_party as respons16_1667_0_, subsidiary0_.related_entity_gkey as related17_1667_0_, subsidiary0_.related_entity_id as related18_1667_0_, subsidiary0_.related_entity_class as related19_1667_0_, subsidiary0_.related_batch_id as related20_1667_0_, subsidiary0_.acknowledged as acknowl21_1667_0_, subsidiary0_.acknowledged_by as acknowl22_1667_0_, subsidiary0_.flex_string01 as flex23_1667_0_, subsidiary0_.flex_string02 as flex24_1667_0_, subsidiary0_.flex_string03 as flex25_1667_0_, subsidiary0_.flex_string04 as flex26_1667_0_, subsidiary0_.flex_string05 as flex27_1667_0_, subsidiary0_.flex_date01 as flex28_1667_0_, subsidiary0_.flex_date02 as flex29_1667_0_, subsidiary0_.flex_date03 as flex30_1667_0_, subsidiary0_.flex_double01 as flex31_1667_0_, subsidiary0_.flex_double02 as flex32_1667_0_, subsidiary0_.flex_double03 as flex33_1667_0_, subsidiary0_.flex_double04 as flex34_1667_0_, subsidiary0_.flex_double05 as flex35_1667_0_, subsidiary0_.created as created1667_0_, subsidiary0_.creator as creator1667_0_, subsidiary0_.changed as changed1667_0_, subsidiary0_.changer as changer1667_0_, subsidiary0_.primary_event_gkey as primary40_1667_0_, subsidiary0_1_.instrument_value as instrument2_1671_0_, subsidiary0_1_.check_time as check3_1671_0_, subsidiary0_1_.node_name as node4_1671_0_, subsidiary0_1_.instrument_gkey as instrument5_1671_0_, subsidiary0_2_.move_kind as move2_1836_0_, subsidiary0_2_.ufv_gkey as ufv3_1836_0_, subsidiary0_2_.line_op as line4_1836_0_, subsidiary0_2_.carrier_gkey as carrier5_1836_0_, subsidiary0_2_.exclude as exclude1836_0_, subsidiary0_2_.fm_pos_loctype as fm7_1836_0_, subsidiary0_2_.fm_pos_locid as fm8_1836_0_, subsidiary0_2_.fm_pos_loc_gkey as fm9_1836_0_, subsidiary0_2_.fm_pos_slot as fm10_1836_0_, subsidiary0_2_.fm_pos_orientation as fm11_1836_0_, subsidiary0_2_.fm_pos_name as fm12_1836_0_, subsidiary0_2_.fm_pos_bin as fm13_1836_0_, subsidiary0_2_.fm_pos_tier as fm14_1836_0_, subsidiary0_2_.fm_pos_anchor as fm15_1836_0_, subsidiary0_2_.fm_pos_orientation_degrees as fm16_1836_0_, subsidiary0_2_.to_pos_loctype as to17_1836_0_, subsidiary0_2_.to_pos_locid as to18_1836_0_, subsidiary0_2_.to_pos_loc_gkey as to19_1836_0_, subsidiary0_2_.to_pos_slot as to20_1836_0_, subsidiary0_2_.to_pos_orientation as to21_1836_0_, subsidiary0_2_.to_pos_name as to22_1836_0_, subsidiary0_2_.to_pos_bin as to23_1836_0_, subsidiary0_2_.to_pos_tier as to24_1836_0_, subsidiary0_2_.to_pos_anchor as to25_1836_0_, subsidiary0_2_.to_pos_orientation_degrees as to26_1836_0_, subsidiary0_2_.che_fetch as che27_1836_0_, subsidiary0_2_.che_carry as che28_1836_0_, subsidiary0_2_.che_put as che29_1836_0_, subsidiary0_2_.che_qc as che30_1836_0_, subsidiary0_2_.dist_start as dist31_1836_0_, subsidiary0_2_.dist_carry as dist32_1836_0_, subsidiary0_2_.t_carry_complete as t33_1836_0_, subsidiary0_2_.t_dispatch as t34_1836_0_, subsidiary0_2_.t_fetch as t35_1836_0_, subsidiary0_2_.t_discharge as t36_1836_0_, subsidiary0_2_.t_put as t37_1836_0_, subsidiary0_2_.t_carry_fetch_ready as t38_1836_0_, subsidiary0_2_.t_carry_put_ready as t39_1836_0_, subsidiary0_2_.t_carry_dispatch as t40_1836_0_, subsidiary0_2_.t_tz_arrival as t41_1836_0_, subsidiary0_2_.rehandle_count as rehandle42_1836_0_, subsidiary0_2_.twin_fetch as twin43_1836_0_, subsidiary0_2_.twin_carry as twin44_1836_0_, subsidiary0_2_.twin_put as twin45_1836_0_, subsidiary0_2_.restow_account as restow46_1836_0_, subsidiary0_2_.service_order as service47_1836_0_, subsidiary0_2_.restow_reason as restow48_1836_0_, subsidiary0_2_.processed as processed1836_0_, subsidiary0_2_.pow as pow1836_0_, subsidiary0_2_.che_carry_login_name as che51_1836_0_, subsidiary0_2_.che_put_login_name as che52_1836_0_, subsidiary0_2_.che_fetch_login_name as che53_1836_0_, subsidiary0_2_.berth as berth1836_0_, subsidiary0_2_.category as category1836_0_, subsidiary0_2_.freight_kind as freight56_1836_0_, subsidiary0_3_.cv_id as cv2_1838_0_, subsidiary0_3_.transaction_count as transact3_1838_0_, subsidiary0_3_.run_time_ms as run4_1838_0_, case when subsidiary0_1_.ahe_gkey is not null then 1 when subsidiary0_2_.mve_gkey is not null then 2 when subsidiary0_3_.edievent_gkey is not null then 3 when subsidiary0_.gkey is not null then 0 end as clazz_0_ from srv_event subsidiary0_ left outer join srv_app_health_event subsidiary0_1_ on subsidiary0_.gkey=subsidiary0_1_.ahe_gkey left outer join inv_move_event subsidiary0_2_ on subsidiary0_.gkey=subsidiary0_2_.mve_gkey left outer join edi_event subsidiary0_3_ on subsidiary0_.gkey=subsidiary0_3_.edievent_gkey where subsidiary0_.primary_event_gkey= @P0  ',        
    @type = N'SQL',
    @params = N'@P0 bigint',
    @hints = N'OPTION (OPTIMIZE FOR (@P0 = 1))'
GO

I also created a plan guide without the spaces at the end of the statement, just to be sure, but none of these are being used by the application.

The weird thing is that if i run the query by myself, the plan guide is considered and it performs like I thought.

declare @p1 int;  

exec sp_prepare 
    @p1 output,   
    N'@P0 bigint',  
    N'select subsidiary0_.primary_event_gkey as primary40_1_, subsidiary0_.gkey as gkey1_, subsidiary0_.gkey as gkey1667_0_, subsidiary0_.operator_gkey as operator2_1667_0_, subsidiary0_.complex_gkey as complex3_1667_0_, subsidiary0_.facility_gkey as facility4_1667_0_, subsidiary0_.yard_gkey as yard5_1667_0_, subsidiary0_.placed_by as placed6_1667_0_, subsidiary0_.placed_time as placed7_1667_0_, subsidiary0_.event_type_gkey as event8_1667_0_, subsidiary0_.applied_to_class as applied9_1667_0_, subsidiary0_.applied_to_gkey as applied10_1667_0_, subsidiary0_.applied_to_natural_key as applied11_1667_0_, subsidiary0_.note as note1667_0_, subsidiary0_.billing_extract_batch_id as billing13_1667_0_, subsidiary0_.quantity as quantity1667_0_, subsidiary0_.quantity_unit as quantity15_1667_0_, subsidiary0_.responsible_party as respons16_1667_0_, subsidiary0_.related_entity_gkey as related17_1667_0_, subsidiary0_.related_entity_id as related18_1667_0_, subsidiary0_.related_entity_class as related19_1667_0_, subsidiary0_.related_batch_id as related20_1667_0_, subsidiary0_.acknowledged as acknowl21_1667_0_, subsidiary0_.acknowledged_by as acknowl22_1667_0_, subsidiary0_.flex_string01 as flex23_1667_0_, subsidiary0_.flex_string02 as flex24_1667_0_, subsidiary0_.flex_string03 as flex25_1667_0_, subsidiary0_.flex_string04 as flex26_1667_0_, subsidiary0_.flex_string05 as flex27_1667_0_, subsidiary0_.flex_date01 as flex28_1667_0_, subsidiary0_.flex_date02 as flex29_1667_0_, subsidiary0_.flex_date03 as flex30_1667_0_, subsidiary0_.flex_double01 as flex31_1667_0_, subsidiary0_.flex_double02 as flex32_1667_0_, subsidiary0_.flex_double03 as flex33_1667_0_, subsidiary0_.flex_double04 as flex34_1667_0_, subsidiary0_.flex_double05 as flex35_1667_0_, subsidiary0_.created as created1667_0_, subsidiary0_.creator as creator1667_0_, subsidiary0_.changed as changed1667_0_, subsidiary0_.changer as changer1667_0_, subsidiary0_.primary_event_gkey as primary40_1667_0_, subsidiary0_1_.instrument_value as instrument2_1671_0_, subsidiary0_1_.check_time as check3_1671_0_, subsidiary0_1_.node_name as node4_1671_0_, subsidiary0_1_.instrument_gkey as instrument5_1671_0_, subsidiary0_2_.move_kind as move2_1836_0_, subsidiary0_2_.ufv_gkey as ufv3_1836_0_, subsidiary0_2_.line_op as line4_1836_0_, subsidiary0_2_.carrier_gkey as carrier5_1836_0_, subsidiary0_2_.exclude as exclude1836_0_, subsidiary0_2_.fm_pos_loctype as fm7_1836_0_, subsidiary0_2_.fm_pos_locid as fm8_1836_0_, subsidiary0_2_.fm_pos_loc_gkey as fm9_1836_0_, subsidiary0_2_.fm_pos_slot as fm10_1836_0_, subsidiary0_2_.fm_pos_orientation as fm11_1836_0_, subsidiary0_2_.fm_pos_name as fm12_1836_0_, subsidiary0_2_.fm_pos_bin as fm13_1836_0_, subsidiary0_2_.fm_pos_tier as fm14_1836_0_, subsidiary0_2_.fm_pos_anchor as fm15_1836_0_, subsidiary0_2_.fm_pos_orientation_degrees as fm16_1836_0_, subsidiary0_2_.to_pos_loctype as to17_1836_0_, subsidiary0_2_.to_pos_locid as to18_1836_0_, subsidiary0_2_.to_pos_loc_gkey as to19_1836_0_, subsidiary0_2_.to_pos_slot as to20_1836_0_, subsidiary0_2_.to_pos_orientation as to21_1836_0_, subsidiary0_2_.to_pos_name as to22_1836_0_, subsidiary0_2_.to_pos_bin as to23_1836_0_, subsidiary0_2_.to_pos_tier as to24_1836_0_, subsidiary0_2_.to_pos_anchor as to25_1836_0_, subsidiary0_2_.to_pos_orientation_degrees as to26_1836_0_, subsidiary0_2_.che_fetch as che27_1836_0_, subsidiary0_2_.che_carry as che28_1836_0_, subsidiary0_2_.che_put as che29_1836_0_, subsidiary0_2_.che_qc as che30_1836_0_, subsidiary0_2_.dist_start as dist31_1836_0_, subsidiary0_2_.dist_carry as dist32_1836_0_, subsidiary0_2_.t_carry_complete as t33_1836_0_, subsidiary0_2_.t_dispatch as t34_1836_0_, subsidiary0_2_.t_fetch as t35_1836_0_, subsidiary0_2_.t_discharge as t36_1836_0_, subsidiary0_2_.t_put as t37_1836_0_, subsidiary0_2_.t_carry_fetch_ready as t38_1836_0_, subsidiary0_2_.t_carry_put_ready as t39_1836_0_, subsidiary0_2_.t_carry_dispatch as t40_1836_0_, subsidiary0_2_.t_tz_arrival as t41_1836_0_, subsidiary0_2_.rehandle_count as rehandle42_1836_0_, subsidiary0_2_.twin_fetch as twin43_1836_0_, subsidiary0_2_.twin_carry as twin44_1836_0_, subsidiary0_2_.twin_put as twin45_1836_0_, subsidiary0_2_.restow_account as restow46_1836_0_, subsidiary0_2_.service_order as service47_1836_0_, subsidiary0_2_.restow_reason as restow48_1836_0_, subsidiary0_2_.processed as processed1836_0_, subsidiary0_2_.pow as pow1836_0_, subsidiary0_2_.che_carry_login_name as che51_1836_0_, subsidiary0_2_.che_put_login_name as che52_1836_0_, subsidiary0_2_.che_fetch_login_name as che53_1836_0_, subsidiary0_2_.berth as berth1836_0_, subsidiary0_2_.category as category1836_0_, subsidiary0_2_.freight_kind as freight56_1836_0_, subsidiary0_3_.cv_id as cv2_1838_0_, subsidiary0_3_.transaction_count as transact3_1838_0_, subsidiary0_3_.run_time_ms as run4_1838_0_, case when subsidiary0_1_.ahe_gkey is not null then 1 when subsidiary0_2_.mve_gkey is not null then 2 when subsidiary0_3_.edievent_gkey is not null then 3 when subsidiary0_.gkey is not null then 0 end as clazz_0_ from srv_event subsidiary0_ left outer join srv_app_health_event subsidiary0_1_ on subsidiary0_.gkey=subsidiary0_1_.ahe_gkey left outer join inv_move_event subsidiary0_2_ on subsidiary0_.gkey=subsidiary0_2_.mve_gkey left outer join edi_event subsidiary0_3_ on subsidiary0_.gkey=subsidiary0_3_.edievent_gkey where subsidiary0_.primary_event_gkey= @P0  ';  
exec sp_execute @p1, 123123; 

exec sp_unprepare @p1; 

I also noted that, when I run the query above in SSMS, it appears in the extended events as sql_batch and not as rpc. Maybe that's something to do with the problem?