- Email support@dumps4free.com
Topic 5: Misc. Questions
You have an Azure SQL Database server named sqlsrv1 that hosts 10 Azure SQL
databases.
The databases perform slower than expected.
You need to identify whether the performance issue relates to the use of tempdb on
sqlsrv1.
What should you do?
A.
Run Query Store-based queries
B.
Review information provided by SQL Server Profiler-based traces
C.
Review information provided by Query Performance Insight
D.
Run dynamic management view-based queries
Run dynamic management view-based queries
Explanation:
The diagnostics log outputs tempDB contention details. You can use the information as the
starting point for troubleshooting.
You can use the Intelligent Insights performance diagnostics log of Azure SQL Database to
troubleshoot performance issues.
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/database/intelligent-insights-troubleshootperformance#
tempdb-contention
https://docs.microsoft.com/en-us/azure/azure-sql/database/intelligent-insights-usediagnostics-
log
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.
| Page 8 out of 44 Pages |
| Previous |