无法将计划指南与 sp_execute 一起使用

我有一个查询 1000 万行表的应用程序。

查询正在按索引的整数列进行过滤。此列仅包含NULLs 并且查询始终查找非NULL值，因此它应该进行得很快。

问题是应用程序发送查询，sp_prepare然后sp_execute它基本上是OPTIMIZE FOR (UNKNOWN)（有关此的更多信息，请参阅Erik 的回答）。

简而言之：

1. sp_execute从应用程序=>optimize for (unknown)
2. 用于估计的密度向量
3. 整行只有一个值 ( NULL)，因此估计 = 行数
4. 当查询返回 0 行时对表进行全扫描 = 性能不佳

由于我无法更改应用程序代码，因此我认为计划指南OPTIMIZE FOR (@P0 = 1)可以解决所有问题。

我从扩展事件会话中获取了查询文本（从 sql_text 列复制，但删除了该(@P0 bigint)部分）

然后我像这样创建了计划指南：

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

我还创建了一个在语句末尾没有空格的计划指南，只是为了确定，但应用程序没有使用这些空格。

奇怪的是，如果我自己运行查询，计划指南会被考虑并且它的执行就像我想的那样。

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; 

我还注意到，当我在 SSMS 中运行上面的查询时，它在扩展事件中显示为sql_batch而不是rpc. 也许这与问题有关？