Topic 5: Misc. Questions
Note: This question is part of a series of questions that present the same scenario.
Each question in the series contains a unique solution that might meet the stated
goals. Some question sets might have more than one correct solution, while others
might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a
result, these questions will not appear in the review screen.
You have two Azure SQL Database servers named Server1 and Server2. Each server
contains an Azure SQL database named Database1.
You need to restore Database1 from Server1 to Server2. The solution must replace the
existing Database1 on Server2.
Solution: From the Azure portal, you delete Database1 from Server2, and then you create a
new database on Server2 by using the backup of Database1 from Server1.
Does this meet the goal?
A.
Yes
B.
No
No
Explanation:
Instead restore Database1 from Server1 to the Server2 by using the RESTORE Transact-
SQL command and the REPLACE option.
Note: REPLACE should be used rarely and only after careful consideration. Restore
normally prevents accidentally overwriting a database with a different database. If the
database specified in a RESTORE statement already exists on the current server and the
specified database family GUID differs from the database family GUID recorded in the
backup set, the database is not restored. This is an important safeguard.
Reference:
https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql
You have an Azure SQL database.
Users report that the executions of a stored procedure are slower than usual. You suspect
that a regressed query is causing the performance issue.
You need to view the query execution plan to verify whether a regressed query is causing
the issue. The solution must minimize effort.
What should you use?
A.
Performance Recommendations in the Azure portal
B.
Extended Events in Microsoft SQL Server Management Studio (SSMS)
C.
Query Store in Microsoft SQL Server Management Studio (SSMS)
D.
Query Performance Insight in the Azure portal
Query Store in Microsoft SQL Server Management Studio (SSMS)
Explanation:
Use the Query Store Page in SQL Server Management Studio.
Query performance regressions caused by execution plan changes can be non-trivial and
time consuming to resolve.
Since the Query Store retains multiple execution plans per query, it can enforce policies to
direct the Query Processor to use a specific execution plan for a query. This is referred to
as plan forcing. Plan forcing in Query Store is provided by using a mechanism similar to the
USE PLAN query hint, but it does not require any change in user applications. Plan forcing
can resolve a query performance regression caused by a plan change in a very short
period of time.
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoringperformance-
by-using-the-query-store
You have SQL Server 2019 on an Azure virtual machine that contains an SSISDB
database.
A recent failure causes the master database to be lost.
You discover that all Microsoft SQL Server integration Services (SSIS) packages fail to run
on the virtual machine.
Which four actions should you perform in sequence to resolve the issue? To answer, move
the appropriate actions from the list of actions to the answer area and arrange them in the
correct.
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.
You have the following Transact-SQL query
Which column returned by the query represents the free space in each file?
A.
ColumnA
B.
ColumnB
C.
ColumnC
D.
ColumnD
ColumnC
Explanation:
Example:
Free space for the file in the below query result set will be returned by the FreeSpaceMB
column.
SELECT DB_NAME() AS DbName,
name AS FileName,
type_desc,
size/128.0 AS CurrentSizeMB,
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files
WHERE type IN (0,1);
Reference:
https://www.sqlshack.com/how-to-determine-free-space-and-file-size-for-sql-serverdatabases/
You have an Azure SQL database.
You identify a long running query.
You need to identify which operation in the query is causing the performance issue.
What should
You have an Azure SQL database.
You identify a long running query.
You need to identify which operation in the query is causing the performance issue.
What should you use to display the query execution plan in Microsoft SQL Server
Management Studio (SSMS)?
A.
Live Query Statistics
B.
an estimated execution plan
C.
an actual execution plan
D.
Client Statistics
an actual execution plan
Explanation:
To include an execution plan for a query during execution
1. On the SQL Server Management Studio toolbar, click Database Engine Query. You can
also open an existing query and display the estimated execution plan by clicking the Open
File toolbar button and locating the existing query.
2. Enter the query for which you would like to display the actual execution plan.
3. On the Query menu, click Include Actual Execution Plan or click the Include Actual
Execution Plan toolbar button.
Note: Actual execution plans are generated after the Transact-SQL queries or batches
execute. Because of this, an actual execution plan contains runtime information, such as
actual resource usage metrics and runtime warnings (if any). The execution plan that is
generated displays the actual query execution plan that the SQL Server Database Engine
used to execute the queries.
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/performance/display-an-actualexecution-
plan
You plan to move two 100-GB databases to Azure.You need to dynamically scale resources consumption based on workloads. The solution
must minimize downtime during scaling operations.
What should you use?
A.
two Azure SQL Databases in an elastic pool
B.
two databases hosted in SQL Server on an Azure virtual machine
C.
two databases in an Azure SQL Managed instance
D.
two single Azure SQL databases
two Azure SQL Databases in an elastic pool
Explanation:
Azure SQL Database elastic pools are a simple, cost-effective solution for managing and
scaling multiple databases that have varying and unpredictable usage demands. The
databases in an elastic pool are on a single server and share a set number of resources at
a set price.
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/database/elastic-pool-overview
You have an Azure virtual machine named VM1 on a virtual network named VNet1.
Outbound traffic from VM1 to the internet is blocked.
You have an Azure SQL database named SqlDb1 on a logical server named SqlSrv1.
You need to implement connectivity between VM1 and SqlDb1 to meet the following
requirements:
Ensure that all traffic to the public endpoint of SqlSrv1 is blocked.
Minimize the possibility of VM1 exfiltrating data stored in SqlDb1.
D18912E1457D5D1DDCBD40AB3BF70D5D
What should you create on VNet1?
A.
a VPN gateway
B.
a service endpoint
C.
a private link
D.
an ExpressRoute gateway
a private link
Explanation:
Azure Private Link enables you to access Azure PaaS Services (for example, Azure
Storage and SQL Database) and Azure hosted customer-owned/partner services over a
private endpoint in your virtual network.
Traffic between your virtual network and the service travels the Microsoft backbone
network. Exposing your service to the public internet is no longer necessary.
Reference:
https://docs.microsoft.com/en-us/azure/private-link/private-link-overview
You have an Azure SQL database that contains a table named Employees. Employees
contains a column named Salary.
You need to encrypt the Salary column. The solution must prevent database administrators
from reading the data in the Salary column and must provide the most secure encryption.
Which three actions should you perform in sequence? To answer, move the appropriate
actions from the list of actions to the answer area and arrange them in the correct order.
You have an Azure SQL database named DB1 that contains two tables named Table1 and
Table2. Both tables contain a column named a Column1. Column1 is used for joins by an
application named App1.
You need to protect the contents of Column1 at rest, in transit, and in use.
How should you protect the contents of Column1? To answer, select the appropriate
options in the answer area.
NOTE: Each correct selection is worth one point
You have 20 Azure SQL databases provisioned by using the vCore purchasing model.
You plan to create an Azure SQL Database elastic pool and add the 20 databases.
Which three metrics should you use to size the elastic pool to meet the demands of your
workload? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.
A.
total size of all the databases
B.
geo-replication support
C.
number of concurrently peaking databases * peak CPU utilization per database
D.
maximum number of concurrent sessions for all the databases
E.
total number of databases * average CPU utilization per database
total size of all the databases
number of concurrently peaking databases * peak CPU utilization per database
total number of databases * average CPU utilization per database
Explanation:
CE: Estimate the vCores needed for the pool as follows:
For vCore-based purchasing model: MAX(utilization per DB>, A: Estimate the storage space needed for the pool by adding the number of bytes needed
for all the databases in the pool.
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/database/elastic-pool-overview
You have an Azure SQL database. The database contains a table that uses a columnstore
index and is accessed infrequently.
You enable columnstore archival compression.
What are two possible results of the configuration? Each correct answer presents acomplete solution.
NOTE: Each correct selection is worth one point.
A.
Queries that use the index will consume more disk I/O.
B.
Queries that use the index will retrieve fewer data pages.
C.
The index will consume more disk space.
D.
The index will consume more memory.
E.
Queries that use the index will consume more CPU resources.
Queries that use the index will retrieve fewer data pages.
Queries that use the index will consume more CPU resources.
Explanation:
For rowstore tables and indexes, use the data compression feature to help reduce the size
of the database. In addition to saving space, data compression can help improve
performance of I/O intensive workloads because the data is stored in fewer pages and
queries need to read fewer pages from disk.
Use columnstore archival compression to further reduce the data size for situations when
you can afford extra time and CPU resources to store and retrieve the data.
| Page 4 out of 18 Pages |
| Previous |