ExamTopics Logo
Mail Us[email protected]
Menu
Microsoft Discussions
exam questions

Exam 70-762 All Questions

View all questions & answers for the 70-762 exam
Go to Exam

Exam 70-762 topic 1 question 122 discussion

Actual exam question from Microsoft's 70-762
Question #: 122
Topic #: 1
[All 70-762 Questions]

You have several real-time applications that constantly update data in a database. The applications run more than 400 transactions per second that insert and update new metrics from sensors.
A new web dashboard is released to present the data from the sensors. Engineers report that the applications take longer than expected to commit updates.
You need to change the dashboard queries to improve concurrency and to support reading uncommitted data.
What should you do?

  • A. Use the NOLOCK option.
  • B. Execute the DBCC UPDATEUSAGE statement.
  • C. Use the max worker threads option.
  • D. Use a table-valued parameter.
  • E. Set SET ALLOW_SNAPSHOT_ISOLATION to ON.
  • F. Set SET XACT_ABORT to ON.
  • G. Execute the ALTER TABLE T1 SET (LOCK_ESCALATION = AUTO); statement.
  • H. Use the OUTPUT parameters.
Show Suggested Answer Hide Answer
Suggested Answer: A 🗳️
The NOLOCK hint allows SQL to read data from tables by ignoring any locks and therefore not being blocked by other processes.
This can improve query performance, but also introduces the possibility of dirty reads.
Incorrect Answers:
F: When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.
G: DISABLE, not AUTO, would be better.
There are two more lock escalation modes: AUTO and DISABLE.
The AUTO mode enables lock escalation for partitioned tables only for the locked partition. For non-partitioned tables it works like TABLE.
The DISABLE mode removes the lock escalation capability for the table and that is important when concurrency issues are more important than memory needs for specific tables.
Note: SQL Server's locking mechanism uses memory resources to maintain locks. In situations where the number of row or page locks increases to a level that decreases the server's memory resources to a minimal level, SQL Server's locking strategy converts these locks to entire table locks, thus freeing memory held by the many single row or page locks to one table lock. This process is called lock escalation, which frees memory, but reduces table concurrency.
References:
https://www.mssqltips.com/sqlservertip/2470/understanding-the-sql-server-nolock-hint/
by lh2607 at Nov. 19, 2020, 10:34 a.m.

Comments

Chosen Answer:
This is a voting comment (?). It is better to Upvote an existing comment if you don't have anything to add.
Switch to a voting comment New
Submit Cancel
lh2607
Highly Voted 4 years, 5 months ago
Correct
upvoted 9 times
...
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.

Upvoting a comment with a selected answer will also increase the vote count towards that answer by one. So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.

SaveCancel
exam
Someone Bought Contributor Access for:
SY0-701
London, 1 minute ago