Practice Free DP-300 Exam Online Questions
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
C
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/monitoring-performance-by-using-the-query-store
You have several Azure SQL databases on the same Azure SQL Database server in a resource group named ResourceGroup1.
You must be alerted when CPU usage exceeds 80 percent for any database. The solution must apply to any additional databases that are created on the Azure SQL server.
Which resource type should you use to create the alert?
- A . Resource Groups
- B . SQL Servers
- C . SQL Databases
- D . SQL Virtual Machines
C
Explanation:
There are resource types related to application code, compute infrastructure, networking, storage + databases.
You can deploy up to 800 instances of a resource type in each resource group.
Some resources can exist outside of a resource group. These resources are deployed to the subscription, management group, or tenant. Only specific resource types are supported at these scopes.
Reference: https://docs.microsoft.com/en-us/azure/azure-resource-manager/management/resource-providers-and-types
HOTSPOT
You have a SQL Server on Azure Virtual Machines instance named VM1.
You run the following query.
For each of the following statements, select Yes if the statement is true. Otherwise, select No.
DRAG DROP
You have SQL Server on an Azure virtual machine.
You need to use Policy-Based Management in Microsoft SQL Server to identify stored procedures that do not comply with your naming conventions.
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.
Explanation:
Reference: https://www.mssqltips.com/sqlservertip/2298/enforce-sql-server-database-naming-conventions-using-policy-based-management/
HOTSPOT
You have an Azure subscription that contains a logical SQL server. The server hosts two databases named db1 and db2 and an Azure AD sen/ice principal named appl.
You need to ensure that appl can access db1. The solution must use the principle of least privilege.
How should you complete the Transact-SQL statement? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.
DRAG DROP
You have an Azure subscription that contains an Azure SQL managed instance named Server1.
You need to enable auditing for Server 1. The solution must minimize administrative effort.
Which four actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them m the correct order.
HOTSPOT
You have an instance of SQL Server on Azure Virtual Machines named VM1.
You need to use an Azure Automation runbook to initiate a SQL Server database backup on VM1.
How should you complete the command? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.
HOTSPOT
You have an instance of SQL Server on Azure Virtual Machines named VM1.
You need to use an Azure Automation runbook to initiate a SQL Server database backup on VM1.
How should you complete the command? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.
You have an Azure subscription that contains 20 Azure SQL databases.
You create a Transact-SQL statement to perform index maintenance on a database.
You need to schedule the statement to run once daily against each database by using Transact-SQL commands.
What should you use to schedule the statement?
- A . Azure Automation
- B . an elastic job
- C . a SQL Server Agent job
- D . an Azure function
SIMULATION
Task 6
You need to ensure that any enhancements made to the Query Optimizer through patches are available to dbl and db2 on sql37006895.
Here are the steps to enable the query optimizer hotfixes option for dbl and db2 on sql37006895:
Connect to sql37006895 using SQL Server Management Studio, Azure Data Studio, or any other tool that supports Transact-SQL statements.
Open a new query window and run the following commands for each database:
— Switch to the database context USE dbl;
GO
— Enable the query optimizer hotfixes option
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON; GO
Repeat the same commands for db2, replacing dbl with db2 in the USE statement.
To verify that the query optimizer hotfixes option is enabled for each database, you can query the sys.database_scoped_configurations catalog view. The value of the query_optimizer_hotfixes column
should be 1 for both databases.
These are the steps to enable the query optimizer hotfixes option for dbl and db2 on sql37006895.