QUESTION: 169 Exam Topic: Optimize Query Performance questions

You have SQL Server on an Azure virtual machine that contains a database named DB1. You have an application that queries DB1 to generate a sales report.
You need to see the parameter values from the last time the query was executed.

Which two actions should you perform? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.

Enable Last_Query_Plan_Stats in the master database
Enable Lightweight_Query_Profiling in DB1
Enable Last_Query_Plan_Stats in DB1
Enable Lightweight_Query_Profiling in the master database
Enable PARAMETER_SNIFFING in DB1

Answer(s): B,C

Explanation:

Last_Query_Plan_Stats allows you to enable or disable collection of the last query plan statistics (equivalent to an actual execution plan) in sys.dm_exec_query_plan_stats.
Lightweight profiling can be disabled at the database level using the LIGHTWEIGHT_QUERY_PROFILING database scoped configuration: ALTER DATABASE
SCOPED CONFIGURATION SET LIGHTWEIGHT_QUERY_PROFILING = OFF;.
Incorrect Answers:
A: Enable it for DB1, not for the master database.
E: Parameter sensitivity, also known as "parameter sniffing", refers to a process whereby SQL Server "sniffs" the current parameter values during compilation or recompilation, and passes it along to the Query Optimizer so that they can be used to generate potentially more efficient query execution plans.
Parameter values are sniffed during compilation or recompilation for the following types of batches:
-Stored procedures
-Queries submitted via sp_executesql

Prepared queries

Reference:

https://docs.microsoft.com/en-us/sql/relational-databases/performance/query-profiling-infrastructure https://docs.microsoft.com/en-us/sql/relational-databases/performance/query-profiling-infrastructure

Reveal Solution Next Question

QUESTION: 170 Exam Topic: Optimize Query Performance questions

HOTSPOT (Drag and Drop is not supported)
You have SQL Server on an Azure virtual machine that contains a database named Db1. You need to enable automatic tuning for Db1.
How should you complete the statements? To answer, select the appropriate answer in the answer area.

NOTE: Each correct selection is worth one point.
Hot Area:

See Explanation section for answer.

Answer(s): A

Explanation:

Box 1: SET AUTOMATIC_TUNING = AUTO
To enable automatic tuning on a single database via T-SQL, connect to the database and execute the following query:

ALTER DATABASE current SET AUTOMATIC_TUNING = AUTO

Setting automatic tuning to AUTO will apply Azure Defaults.

Box 2: SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON)
To configure individual automatic tuning options via T-SQL, connect to the database and execute the query such as this one:

ALTER DATABASE current SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON)

Setting the individual tuning option to ON will override any setting that database inherited and enable the tuning option. Setting it to OFF will also override any setting that database inherited and disable the tuning option.

Reference:

https://docs.microsoft.com/en-us/azure/azure-sql/database/automatic-tuning-enable

Reveal Solution Next Question

QUESTION: 171 Exam Topic: Optimize Query Performance questions

You deploy a database to an Azure SQL Database managed instance.

You need to prevent read queries from blocking queries that are trying to write to the database.
Which database option should set?

PARAMETERIZATION to FORCED
PARAMETERIZATION to SIMPLE
Delayed Durability to Forced
READ_COMMITTED_SNAPSHOT to ON

Answer(s): D

Explanation:

In SQL Server, you can also minimize locking contention while protecting transactions from dirty reads of uncommitted data modifications using either:
-The READ COMMITTED isolation level with the READ_COMMITTED_SNAPSHOT database option set to ON.
-The SNAPSHOT isolation level.

If READ_COMMITTED_SNAPSHOT is set to ON (the default on SQL Azure Database), the Database Engine uses row versioning to present each statement with a transactionally consistent snapshot of the data as it existed at the start of the statement. Locks are not used to protect the data from updates by other transactions.

Incorrect Answers:
A: When the PARAMETERIZATION database option is set to SIMPLE, the SQL Server query optimizer may choose to parameterize the queries. This means that any literal values that are contained in a query are substituted with parameters. This process is referred to as simple parameterization. When SIMPLE parameterization is in effect, you cannot control which queries are parameterized and which queries are not.

B: You can specify that all queries in a database be parameterized by setting the PARAMETERIZATION database option to FORCED. This process is referred to as forced parameterization.

C: Delayed transaction durability is accomplished using asynchronous log writes to disk. Transaction log records are kept in a buffer and written to disk when the buffer fills or a buffer flushing event takes place. Delayed transaction durability reduces both latency and contention within the system.

Some of the cases in which you could benefit from using delayed transaction durability are:

-You can tolerate some data loss.
-You are experiencing a bottleneck on transaction log writes.
-Your workloads have a high contention rate.

Reference:

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql

Reveal Solution Next Question

QUESTION: 172 Exam Topic: Optimize Query Performance questions

You have an Azure SQL database.
You discover that the plan cache is full of compiled plans that were used only once.

You run the select * from sys.database_scoped_configurations Transact-SQL command and receive the results shown in the following table.

You need relieve the memory pressure. What should you configure?

LEGACY_CARDINALITY_ESTIMATION
QUERY_OPTIMIZER_HOTFIXES
OPTIMIZE_FOR_AD_HOC_WORKLOADS
ACCELERATED_PLAN_FORCING

Answer(s): C

Explanation:

OPTIMIZE_FOR_AD_HOC_WORKLOADS = { ON | OFF }
Enables or disables a compiled plan stub to be stored in cache when a batch is compiled for the first time. The default is OFF. Once the database scoped configuration OPTIMIZE_FOR_AD_HOC_WORKLOADS is enabled for a database, a compiled plan stub will be stored in cache when a batch is compiled for the first time. Plan stubs have a smaller memory footprint compared to the size of the full compiled plan.

Incorrect Answers:
A: LEGACY_CARDINALITY_ESTIMATION = { ON | OFF | PRIMARY }
Enables you to set the query optimizer cardinality estimation model to the SQL Server 2012 and earlier version independent of the compatibility level of the database. The default is OFF, which sets the query optimizer cardinality estimation model based on the compatibility level of the database.

B: QUERY_OPTIMIZER_HOTFIXES = { ON | OFF | PRIMARY }

Enables or disables query optimization hotfixes regardless of the compatibility level of the database. The default is OFF, which disables query optimization hotfixes that were released after the highest available compatibility level was introduced for a specific version (post-RTM).

Reference:

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql

Reveal Solution Next Question

Free DP-300 Exam Braindumps (page: 21)

QUESTION: 169 Exam Topic: Optimize Query Performance questions

Explanation:

Reference:

QUESTION: 170 Exam Topic: Optimize Query Performance questions

Explanation:

Reference:

QUESTION: 171 Exam Topic: Optimize Query Performance questions

Explanation:

Reference:

QUESTION: 172 Exam Topic: Optimize Query Performance questions

Explanation:

Reference: