sp_optgoal 'show','goal_name' reports all individual criteria activated by the goal named goal_name. For example:
sp_optgoal 'goal_1571', 'show'
sp_optgoal @@optgoal, 'show' reports current goal settings:
sp_optgoal @@optgoal, 'show'
name ---------------------------------------------------------------------------- distinct_sorted distinct_sorting distinct_hashing group_sorted group_hashing nl_join merge_join append_union_all merge_union_all merge_union_distinct hash_union_distinct opportunistic_distinct_view parallel_query order_sorting store_index replicated_partition ndex_union streaming_sort nary_nl_join alternative_greedy_search cr562947: OPTLEVEL EXCEPTION SEE CR - allow cursor table scans data_page_prefetch_costing: clustered row bias added mru_buffer_costing: wash size buffer limit for MRU cr546125: implicitly updatable cursor non-unique index scan cr545771: improves multi-table outer-join and semi-join costing cr545653: avoid inner table buffer estimate starvation cr545585: covered iscan CPU costing too expensive cr545379: disallow reformatting on user forced index scan cr545180: avoid reformat with no sargs if useful index exists cr545059: reduce usage of buffer manager optimization sorts cr544485: mark subquery join predicates with distinct view as sargs cr534175: compute GROUP BY worktables in nested subqueries only once when possi ble cr531199: increases the number of useful nested loop join plans considered cr500736: supports nocase sortorder columns in mergejoin and hashjoin keys cr487450: improves DISTINCT costing of multi-table outer joins and/or semi-joins cr467566: allow abstract plans and statement cache to work together cr497066: infer the nullability of isnull() by looking at its parameters cr421607: support NULL=NULL merge and hash join keys cr552795: eliminate duplicate rows during reformatting when they're not needed imdb_costing: 0 PIO costing for scans for in-memory database allow_minmax: allow local session to consider MINMAX optimization cr646220: enable better store index key generation with correlated predicate
