adplus-dvertising

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.

frame-decoration

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

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

a.

@tran_name_variable

b.

@tran_name

c.

transaction_name

d.

all of the mentioned

Discuss
Answer: (a).@tran_name_variable
Q52.
Point out the correct statement.

a.

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

b.

A ROLLBACK TRANSACTION statement does produce any messages to the user

c.

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

d.

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.

a.

SELECT

b.

PRINT

c.

RAISERROR

d.

All of the mentioned

Discuss
Answer: (c).RAISERROR
Q54.
Which of the following query deletes a job candidate?

a.

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

b.

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

c.

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

d.

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.

a.

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

b.

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

c.

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

d.

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 __________

a.

escalations

b.

containment

c.

contention

d.

none of the mentioned

Discuss
Answer: (a).escalations
Q57.
What is the fundamental difference between COMMIT and COMMIT WORK?

a.

COMMIT TRANSACTION accepts a user-defined transaction name

b.

COMMIT WORK accepts a user-defined transaction name

c.

COMMIT TRANSACTION accepts a user-defined function

d.

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 ___________

a.

SELECT

b.

DDL

c.

DML

d.

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.

a.

Simple

b.

Nested

c.

Distributed

d.

None of the mentioned

Discuss
Answer: (b).Nested
Q60.
Which of the following condition will ignore the request to enable delayed durability?

a.

DELAYED_DURABILITY = FORCED

b.

DELAYED_DURABILITY = 0

c.

DELAYED_DURABILITY = IGNORED

d.

DELAYED_DURABILITY = NULL

Discuss
Answer: (a).DELAYED_DURABILITY = FORCED
Page 6 of 12

Suggested Topics

Are you eager to expand your knowledge beyond SQL Server? We've curated a selection of related categories that you might find intriguing.

Click on the categories below to discover a wealth of MCQs and enrich your understanding of Computer Science. Happy exploring!

Software Architecture and Design

Get a strong hold on the blueprint of software systems with our Software Architecture...

Discrete Mathematics

Refine your mathematical skills essential for computer science with our Discrete...

Java Spring Framework

Enhance your Java development skills with our Spring Framework MCQs. Learn about...

Software Engineering

Learn about the systematic approach to developing software with our Software...

Data Warehousing and OLAP

Strengthen your knowledge of Data Warehousing and OLAP with our specialized MCQs....

Object Oriented Programming

Enhance your coding skills with our Object Oriented Programming MCQs. Understand...

HTML

Start your journey in web development with our HTML MCQs. Learn the building blocks...

Cyber Security

Understand the fundamentals of safeguarding digital assets with our Cyber Security...

C# programming

Hone your skills in the C# language with our MCQs. Understand data types, operators,...