Microsoft DP-800 Questions Answers

The best explanation is an open explicit transaction . During the incident, session 72 was sleeping but still had open_transaction_count = 1 , and sys.dm_exec_input_buffer(72, NULL) showed only BEGIN TRANSACTION UPDATE Sales.Orders. That pattern indicates the session executed an update inside an explicit transaction and then remained idle without committing or rolling back , while still holding locks. Other sessions showing blocking_session_id = 72 is the expected symptom of that situation. Microsoft explains that blocking occurs when one session holds a lock on a resource and another session requests a conflicting lock, and sleeping sessions can continue to block if they retain locks through an open transaction.

This also fits the observed behavior that the timeouts stopped only after session 72 was terminated . Killing the session would roll back the active transaction and release the locks, allowing waiting updates to continue. That is much more consistent with an uncommitted transaction than with a deadlock, because deadlocks are normally detected and one session is chosen as the victim automatically rather than persisting until manual termination.

The correct answer is C because the email must be normalized by removing only the subaddressing portion between the plus sign and the @, while preserving the domain. JSON_VALUE is the correct function to extract the scalar email value from the JSON document. Microsoft states that JSON_VALUE is used to extract a scalar value from JSON text.

Then REGEXP_REPLACE should remove the pattern \+.*@ and replace it with a single @. For example:

user1+promo@contoso.com → user1@contoso.com

Microsoft documents that REGEXP_REPLACE returns the source string with text matching the regular expression replaced by the replacement string, and that an empty or custom replacement string can be used to reshape the result.

Why the other options are wrong:

A removes everything from + to the end of the string, which would leave user1 and lose @contoso.com.

B tries to extract a string that already excludes +..., but it does not reliably reconstruct the normalized address in this pattern.

D also removes everything after +, including the domain, which is incorrect.

So the normalized-email expression is:

REGEXP_REPLACE(JSON_VALUE(Payload, ' $.customer_email ' ), ' \+.*@ ' , ' @ ' )

CTE inner source → FROM sys.dm_exec_requests

Join after sys.dm_exec_sessions AS s → LEFT OUTER JOIN sys.dm_exec_requests

Text retrieval → OUTER APPLY sys.dm_exec_sql_text(r.sql_handle)

Input buffer retrieval → OUTER APPLY sys.dm_exec_input_buffer(r.session_id, r.request_id)

The correct drag-and-drop choices are based on how blocking-chain details are normally assembled in Azure SQL Database.

The CTE must read from sys.dm_exec_requests because the alias er is used with er.session_id and er.blocking_session_id, and those columns come from sys.dm_exec_requests. Microsoft documents that sys.dm_exec_requests returns information about executing requests and includes the blocking_session_id column used to identify blockers.

After FROM sys.dm_exec_sessions AS s, the correct join is LEFT OUTER JOIN sys.dm_exec_requests so the query can still return sessions from sys.dm_exec_sessions even when a current request row is missing. This is useful when showing sessions that are blocked or blocking, while still attempting to attach current request details when available.

For batch text, use OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) because Microsoft documents sys.dm_exec_sql_text(sql_handle) as the function that returns the SQL batch text for the specified sql_handle.

For the input buffer, use OUTER APPLY sys.dm_exec_input_buffer(r.session_id, r.request_id) because Microsoft documents that sys.dm_exec_input_buffer takes session_id and request_id and returns event_info, which is commonly used when sys.dm_exec_sql_text is null or when you want the last command text.

So the completed code uses:

FROM sys.dm_exec_requests

LEFT OUTER JOIN sys.dm_exec_requests

OUTER APPLY sys.dm_exec_sql_text(r.sql_handle)

OUTER APPLY sys.dm_exec_input_buffer(r.session_id, r.request_id)

The correct mapping is:

FOR JSON PATH

JSON_VALUE

JSON_QUERY

To serialize the retrieved rows from knowledge_base , the correct command is FOR JSON PATH . Microsoft documents that FOR JSON formats query results as JSON, and PATH mode is the standard way to shape relational rows into JSON for downstream application or AI use.

To extract the answer field from the response , the correct command is JSON_VALUE because answer is a single scalar field . Microsoft states that JSON_VALUE is used to extract a scalar value from JSON text.

To extract the embeddings to store in query_cache , the correct command is JSON_QUERY because embeddings are returned as a JSON array , not a scalar. Microsoft states that JSON_QUERY extracts an object or array from JSON text, which is exactly the right behavior for an embeddings payload.

The unused options are not the best fit here:

OPENJSON is mainly for shredding JSON into rows and columns.

AI_GENERATE_CHUNKS is for chunking text, not extracting fields from a response payload.

VECTOR_DISTANCE computes similarity between vectors and is unrelated to JSON extraction.

FOR XML PATH produces XML, not JSON.