Welcome to the Performance Tuning and Optimization MCQs Page

Dive deep into the fascinating world of Performance Tuning and Optimization with our comprehensive set of Multiple-Choice Questions (MCQs). This page is dedicated to exploring the fundamental concepts and intricacies of Performance Tuning and Optimization, a crucial aspect of SQL Server. In this section, you will encounter a diverse range of MCQs that cover various aspects of Performance Tuning and Optimization, from the basic principles to advanced topics. Each question is thoughtfully crafted to challenge your knowledge and deepen your understanding of this critical subcategory within SQL Server.

Check out the MCQs below to embark on an enriching journey through Performance Tuning and Optimization. Test your knowledge, expand your horizons, and solidify your grasp on this vital area of SQL Server.

Note: Each MCQ comes with multiple answer choices. Select the most appropriate option and test your understanding of Performance Tuning and Optimization. You can click on an option to test your knowledge before viewing the solution for a MCQ. Happy learning!

Performance Tuning and Optimization MCQs | Page 6 of 12

Explore more Topics under SQL Server

01

SQL Server Basics

02

Data Modification and Constraints

03

Beyond Relational

04

Data Definition Language

05

Development with SQL Server

06

Data Connectivity

07

Enterprise Data Management

08

Data Security

09

Monitoring and Auditing

10

Business Intelligence and Reporting Services

Q51.

Which of the following argument is ignored by database engine in COMMIT TRANSACTION?

@tran_name_variable

@tran_name

transaction_name

all of the mentioned

Discuss

Answer: (a).@tran_name_variable

Q52.

Point out the correct statement.

ROLLBACK TRANSACTION with a savepoint_name or transaction_name rolls back to the beginning of the transaction

A ROLLBACK TRANSACTION statement does produce any messages to the user

Issuing a COMMIT TRANSACTION when @@TRANCOUNT is 0 results in an error

All of the mentioned

Discuss

Answer: (c).Issuing a COMMIT TRANSACTION when @@TRANCOUNT is 0 results in an error

Q53.

____________ is the preferred statement for indicating errors in the transaction.

SELECT

RAISERROR

All of the mentioned

Discuss

Answer: (c).RAISERROR

Q54.

Which of the following query deletes a job candidate?

BEGIN TRANSACTION;
GO
DELETE FROM HumanResources.JobCandidate
WHERE JobCandidateID = 13;
GO
COMMIT TRANSACTION;

BEGIN TRANSACTION;
GO
DELETE FROM HumanResources.JobCandidate
WHERE JobCandidateID = 13;
GO
ROLLBACK TRANSACTION;

BEGIN TRANSACTION;
GO
DELETE FROM HumanResources.JobCandidate
WHERE JobCandidateID = 13;
GO
END;

All of the mentioned

Discuss

Answer: (a).BEGIN TRANSACTION;
GO
DELETE FROM HumanResources.JobCandidate
WHERE JobCandidateID = 13;
GO
COMMIT TRANSACTION;

Q55.

Point out the wrong statement.

It is the responsibility of the Transact-SQL programmer to issue COMMIT TRANSACTION only at a point when all data referenced by the transaction is logically correct

The Database Engine increments the transaction count within a statement only when the transaction count is 1 at the start of the statement

You cannot roll back a transaction after a COMMIT TRANSACTION statement is issued

COMMIT transaction requires membership in the public role

Discuss

Answer: (b).The Database Engine increments the transaction count within a statement only when the transaction count is 1 at the start of the statement

Q56.

A ROLLBACK TRANSACTION statement specifying a savepoint_name releases any locks that are acquired beyond the savepoint, with the exception of __________

escalations

containment

contention

none of the mentioned

Discuss

Answer: (a).escalations

Q57.

What is the fundamental difference between COMMIT and COMMIT WORK?

COMMIT TRANSACTION accepts a user-defined transaction name

COMMIT WORK accepts a user-defined transaction name

COMMIT TRANSACTION accepts a user-defined function

COMMIT WORK accepts a user-defined stored procedure

Discuss

Answer: (a).COMMIT TRANSACTION accepts a user-defined transaction name

Q58.

Implicit transactions are maintained by SQL Server for ___________

SELECT

DDL

DML

All of the mentioned

Discuss

Answer: (d).All of the mentioned

Q59.

ROLLBACK WORK always rolls back to the outermost BEGIN TRANSACTION statement for ___________ transactions.

Simple

Nested

Distributed

None of the mentioned

Discuss

Answer: (b).Nested

Q60.

Which of the following condition will ignore the request to enable delayed durability?

DELAYED_DURABILITY = FORCED

DELAYED_DURABILITY = 0

DELAYED_DURABILITY = IGNORED

DELAYED_DURABILITY = NULL

Discuss

Answer: (a).DELAYED_DURABILITY = FORCED

Page 6 of 12

Welcome to the Performance Tuning and Optimization MCQs Page

Performance Tuning and Optimization MCQs | Page 6 of 12

Explore more Topics under SQL Server

SQL Server Basics

Data Modification and Constraints

Beyond Relational

Data Definition Language

Development with SQL Server

Data Connectivity

Enterprise Data Management

Data Security

Monitoring and Auditing

Business Intelligence and Reporting Services

Suggested Topics

Internet of Things (IoT)

JavaScript

Computer Architecture

Digital Image Processing (DIP)

CSS

C Programming

CompTIA PenTest+ Certification Exam PT0 002

SQL Server

Systems Programming