Important System Stored Procedures

Question 1

Enables collection of natively compiled stored procedures statistics

Answer 1

sp_xtp_control_proc_exec_stats

@new_collection_value = 1 to enable collection of natively compiled stored procedures statistics

Question 2

Enable the collection of natively compiled query statistics

Answer 2

sp_xtp_control_query_exec_stats

Question 3

Create an application lock for a stored procedure to enable the system to only run the procedure if an application lock does not exist.

Answer 3

sp_getapplocks

Question 4

Ensures a query will use the most efficient query plan

Answer 4

sp_query_store_force_plan @queryId, @planId