Fixing the stored procedure

You have a memory-optimized table named Customer. The table is accessed by a stored procedure named ManageCustomer. The database was created in Microsoft SQL Server 2014. A backup and restore operation was used to move the database to SQL Server 2016.

You have performance issues with the stored procedure. You need to resolve the performance issues and ensure the table statistics are updated automatically.

Which three Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact- SQL segments to the answer area and arrange them in the correct order.

---

Answer Given:

Step 1: ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL

Sets certain database behaviors to be compatible with the specified version of SQL Server. Syntax: ALTER DATABASE database_name - SET COMPATIBILITY_LEVEL = { 150 | 140 | 130 | 120 | 110 | 100 | 90 }

Step 2: UPDATE STATISTICS Customers UPDATE STATISTICS updates query optimization statistics on a table or indexed view.

Step 3: EXEC sp_recompile N' 'Manage_customer' sp_recompile causes stored procedures, triggers, and user-defined functions to be recompiled the next time that they are run. It does this by dropping the existing plan from the procedure cache forcing a new plan to be created the next time that the procedure or trigger is run

References: https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level https://docs.microsoft.com/en-us/sql/t-sql/statements/update-statistics-transact-sql