QUESTION: 197 Exam Topic: Optimize Query Performance questions

You have an Azure SQL managed instance.

You need to configure the SQL Server Agent service to email job notifications.

Which statement should you execute?

EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = ‘sysadmin_dbmail_profile’;
EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = ‘application_dbmail_profile’;
EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = ‘AzureManagedInstance_dbmail_profile’;
EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = ‘sys_dbmail_profile’;

Answer(s): C

Reveal Solution Next Question

QUESTION: 168 Exam Topic: Optimize Query Performance

View Related Case Study

You need to identify the cause of the performance issues on SalesSQLDb1.

Which two dynamic management views should you use? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.

sys.dm_pdw_nodes_tran_locks
sys.dm_exec_compute_node_errors
sys.dm_exec_requests
sys.dm_cdc_errors
sys.dm_pdw_nodes_os_wait_stats
sys.dm_tran_locks

Answer(s): C,F

Explanation:

SalesSQLDb1 experiences performance issues that are likely due to out-of-date statistics and frequent blocking queries.
Seeing a Count of All Active SQL Server Wait Types.
Sometimes we're trying to diagnose a problem and we want to know if we're seeing a large number of wait types occurring. We can do this using sys.dm_exec_requests because the current wait type being experienced is presented. Therefore, if we filter out any background or sleeping tasks, we can get a picture of what the waits are for active requests and we can also see if we have a problem. Here's the query:
SELECT COALESCE(wait_type, 'None') AS wait_type, COUNT(*) AS Total

FROM sys.dm_exec_requests -
WHERE NOT status IN ('Background', 'Sleeping')

GROUP BY wait_type -
ORDER BY Total DESC;
Here is an example of the query output:

We see that we have two LCK_M_S wait types. This is the wait type we get when we have requests waiting on obtaining a shared lock. We can then query along with sys.dm_tran_locks to determine what types of locks these active requests were trying to obtain:
SELECT L.request_session_id, L.resource_type,
L.resource_subtype, L.request_mode, L.request_type

FROM sys.dm_tran_locks AS L -
JOIN sys.dm_exec_requests AS DER
ON L.request_session_id = DER.session_id
WHERE DER.wait_type = 'LCK_M_S';
Incorrect:
Not A: Azure SQL database does not have sys.dm_pdw_nodes_tran_locks.
Not E: Azure SQL database does not have sys.dm_pdw_nodes_os_wait_stats.

Reference:

https://www.mssqltips.com/sqlservertip/5521/understanding-and-using-sql-server-sysdmexecrequests/

Reveal Solution Next Question

QUESTION: 277 Exam Topic: Perform Administration by Using T-SQL

View Related Case Study

You need to implement authentication for ResearchDB1. The solution must meet the security and compliance requirements.
What should you run as part of the implementation?

CREATE LOGIN and the FROM WINDOWS clause
CREATE USER and the FROM CERTIFICATE clause
CREATE USER and the FROM LOGIN clause
CREATE USER and the ASYMMETRIC KEY clause
CREATE USER and the FROM EXTERNAL PROVIDER clause

Answer(s): E

Explanation:

Scenario: Authenticate database users by using Active Directory credentials.
(Create a new Azure SQL database named ResearchDB1 on a logical server named ResearchSrv01.)

Authenticate the user in SQL Database or SQL Data Warehouse based on an Azure Active Directory user: CREATE USER [Fritz@contoso.com] FROM EXTERNAL PROVIDER;

Reference:

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-user-transact-sql

Reveal Solution Next Question

QUESTION: 118 Exam Topic: Monitor and Optimize Operational Resources Questions

You have an Azure SQL database named sqldb1.
You need to minimize the possibility of Query Store transitioning to a read-only state. What should you do?

Double the value of Data Flush interval
Decrease by half the value of Data Flush Interval
Double the value of Statistics Collection Interval
Decrease by half the value of Statistics Collection interval

Answer(s): B

Explanation:

The Max Size (MB) limit isn't strictly enforced. Storage size is checked only when Query Store writes data to disk. This interval is set by the Data Flush Interval (Minutes) option. If Query Store has breached the maximum size limit between storage size checks, it transitions to read-only mode.

Incorrect Answers:
C: Statistics Collection Interval: Defines the level of granularity for the collected runtime statistic, expressed in minutes. The default is 60 minutes. Consider using a lower value if you require finer granularity or less time to detect and mitigate issues. Keep in mind that the value directly affects the size of Query Store data.

Reference:

https://docs.microsoft.com/en-us/sql/relational-databases/performance/best-practice-with-the-query-store

Reveal Solution Next Question

Free DP-300 Exam Braindumps (page: 28)

QUESTION: 197 Exam Topic: Optimize Query Performance questions

QUESTION: 168 Exam Topic: Optimize Query Performance

Explanation:

Reference:

QUESTION: 277 Exam Topic: Perform Administration by Using T-SQL

Explanation:

Reference:

QUESTION: 118 Exam Topic: Monitor and Optimize Operational Resources Questions

Explanation:

Reference: