Exam 70-762 topic 1 question 150 discussion

Actual exam question from Microsoft's 70-762

Question #: 150
Topic #: 1

DRAG DROP -
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.
Select and Place:

Show Suggested Answer

Suggested Answer:

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

by gbrlz at Aug. 23, 2020, 10:26 p.m.

Comments

Submit Cancel

gbrlz

Highly Voted 4 years, 8 months ago

The answer is ok

upvoted 7 times

...

TrungMyLanTJ

Highly Voted 4 years, 5 months ago

Considerations for statistics on memory-optimized tables: Starting in SQL Server 2016 and in Azure SQL Database, automatic update of statistics is supported for memory-optimized tables, when using database compatibility level of at least 130. See ALTER DATABASE Compatibility Level (Transact-SQL). If a database has tables that were previously created using a lower compatibility level, the statistics need to be updated manually once, to enable automatic update of statistics going forward. For natively compiled stored procedures, execution plans for queries in the procedure are optimized when the procedure is compiled, which happens at create time. They are not automatically recompiled when statistics are updated. Therefore, the tables should contain a representative set of data before the procedures are created.

upvoted 6 times

...

lh2607

Most Recent 4 years, 5 months ago

I agree with the answer. Starting in SQL Server 2016 and in Azure SQL Database, automatic update of statistics is supported for memory-optimized tables, when using database compatibility level of at least 130. See ALTER DATABASE Compatibility Level (Transact-SQL). If a database has tables that were previously created using a lower compatibility level, the statistics need to be updated manually once, to enable automatic update of statistics going forward. For natively compiled stored procedures, execution plans for queries in the procedure are optimized when the procedure is compiled, which happens at create time. They are not automatically recompiled when statistics are updated. Therefore, the tables should contain a representative set of data before the procedures are created. Natively compiled stored procedures can be manually recompiled using sp_recompile (Transact-SQL), and they are automatically recompiled if the database is taken offline and brought back online, or if there is a database failover or server restart. Reference: https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/statistics-for-memory-optimized-tables?view=sql-server-ver15

upvoted 4 times

...

databasejamdown

4 years, 6 months ago

Statistics should be updated automatically not manually. I think it should be 1)Alter database ... 2) Drop procedure ... 3)Create procedure ...

upvoted 3 times

...

Exam 70-762 All Questions

View all questions & answers for the 70-762 exam