nerdexam
Microsoft

70-473 · Question #113

Your company identifies a stored procedure that runs slowly. You review the execution plan for the stored procedure, and you discover the properties of a Clustered Index Scan operator as shown in…

The correct answer is B. Add a NOLOCK query hint to the SELECT statement. To improve the performance of a slow stored procedure that includes a Clustered Index Scan, especially if blocking or contention is a suspected cause, add the NOLOCK query hint to the SELECT statement.

Monitor and manage an Azure data platform solution

Question

Your company identifies a stored procedure that runs slowly. You review the execution plan for the stored procedure, and you discover the properties of a Clustered Index Scan operator as shown in the exhibit. (Click the Exhibit button.) The stored procedure contains the following code. You need to improve the performance of the stored procedure. What should you do?

Options

  • AAdd a FORCESEEK query hint to the SELECT statement.
  • BAdd a NOLOCK query hint to the SELECT statement.
  • CConvert the table to an In-Memory OLTP optimized table.
  • DChange the FileName column from varchar(512) to nvarchar(512).

How the community answered

(25 responses)
  • A
    4% (1)
  • B
    68% (17)
  • C
    16% (4)
  • D
    12% (3)

Why each option

To improve the performance of a slow stored procedure that includes a Clustered Index Scan, especially if blocking or contention is a suspected cause, add the NOLOCK query hint to the SELECT statement.

AAdd a FORCESEEK query hint to the SELECT statement.

A FORCESEEK query hint forces the query optimizer to use an index seek operation; while a clustered index scan can be inefficient, FORCESEEK is used for incorrect index selection, not contention-based slowness.

BAdd a NOLOCK query hint to the SELECT statement.Correct

The NOLOCK query hint allows a SELECT statement to read data without acquiring shared locks and without waiting for exclusive locks held by other transactions. If the stored procedure is running slowly due to contention or blocking caused by other operations holding locks on the underlying table, NOLOCK can significantly improve performance by eliminating these waits, albeit at the risk of reading uncommitted data.

CConvert the table to an In-Memory OLTP optimized table.

Converting the table to an In-Memory OLTP optimized table requires significant schema and application changes and is a much more complex solution than a query hint for immediate slowness, especially if locking is the issue.

DChange the FileName column from varchar(512) to nvarchar(512).

Changing the FileName column from varchar(512) to nvarchar(512) primarily affects storage for Unicode characters and collation, not query execution plan efficiency or locking contention.

Concept tested: Improving SQL Server query performance with NOLOCK hint

Source: https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-ver16#readpast-nolock

Topics

#SQL query optimization#execution plan#clustered index scan#query hints

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 70-473 Practice