Home » Microsoft » 70-461 v.2 » Which Transact-SQL statement should you run?
You administer a Microsoft SQL Server database named Orders. Orders is highly active OLTP system used for e-commerce.
Performance on the database has degraded over the past few months as the volume of data has increased, and now users report the application is unusable.
You need to identify the cause of the performance problem.
Which Transact-SQL statement should you run?
A. SET STATISTICS TIME ON
B. SET FORCEPLAN ON
C. SET STATISTICS IO ON
D. DBCC CHECKCONTRAINTS
Correct Answer: B
Explanation/Reference:
Explanation:
When FORCEPLAN is set to ON, the SQL Server query optimizer processes a join in the same order as the tables appear in the FROM clause of a query. In addition, setting FORCEPLAN to ON forces the use of a nested loop join unless other types of joins are required to construct a plan for the query, or they are requested with join hints or query hints.
Incorrect Answers:
A: When SET STATISTICS TIME is ON, the time statistics for a statement are displayed. When OFF, the time statistics are not displayed.
C: Causes SQL Server to display information regarding the amount of disk activity generated by Transact-SQL statements.
D: DBCC CHECKCONSTRAINTS constructs and executes a query for all FOREIGN KEY constraints and CHECK constraints on a table.
References: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkconstraints-transact-sql?view=sql-server-2017
