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 8 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

Q71.

Which of the following design strategy can reduce the occurrence of blocking locks?

Use non-clustered indexes on high-usage tables

Use clustered indexes on high-usage tables

Use clustered indexes on less-usage tables

Make sure that CREATE and DROP statements use an existing index

Discuss

Answer: (b).Use clustered indexes on high-usage tables

Q72.

Point out the correct statement.

Avoid less row count SQL statements that can cause a table lock

A blocking lock occurs when one lock causes another process to wait in a holding queue

Lock is a done by database when any connection access a different piece of data concurrently

None of the mentioned

Discuss

Answer: (b).A blocking lock occurs when one lock causes another process to wait in a holding queue

Q73.

Which of the following scenario give rise to deadlock (There are 2 processes)?

P1 & P2 trying TO GET TO 2 resources R1 & R2.
P1 gets a LOCK ON R2
AND
P2 gets a LOCK ON R2
THEN
P1 tries TO GET a LOCK ON R2 but can’t because it IS locked BY P2
AND
P2 tries TO GET a LOCK ON R1 but can’t because it IS locked BY P1

P1 & P2 trying TO GET TO 2 resources R1 & R2.
P1 gets a LOCK ON R1
AND
P2 gets a LOCK ON R1
THEN
P1 tries TO GET a LOCK ON R2 but can’t because it IS locked BY P2
AND
P2 tries TO GET a LOCK ON R1 but can’t because it IS locked BY P1

P1 & P2 trying TO GET TO 2 resources R1 & R2.
P1 gets a LOCK ON R1
AND
P2 gets a LOCK ON R2
THEN
P1 tries TO GET a LOCK ON R2 but can’t because it IS locked BY P2
AND
P2 tries TO GET a LOCK ON R1 but can’t because it IS locked BY P1

All of the mentioned

Discuss

Answer: (c). P1 & P2 trying TO GET TO 2 resources R1 & R2.
P1 gets a LOCK ON R1
AND
P2 gets a LOCK ON R2
THEN
P1 tries TO GET a LOCK ON R2 but can’t because it IS locked BY P2
AND
P2 tries TO GET a LOCK ON R1 but can’t because it IS locked BY P1

Q74.

Which of the following occurs when two connections need access to same piece of data concurrently and the meanwhile another is blocked because at a particular time, only one connection can have access?

Lock

Block

Deadlock

Exclusive lock

Discuss

Answer: (b).Block

Q75.

Point out the wrong statement.

SQL Server lock monitor has a performance monitor that periodically checks the locks

A deadlock graph shows us the sessions and resources that were involved in a deadlock

Prior to SQL Server 2008, if a deadlock occurred in SQL Server, we’d have to enable trace flag

None of the mentioned

Discuss

Answer: (a).SQL Server lock monitor has a performance monitor that periodically checks the locks

Q76.

An XML deadlock graph has ______ main sections.

Discuss

Answer: (a).2

Q77.

_____________ section lists all the resources that were involved in the deadlock.

Processes

Resources

WAIT stats

All of the mentioned

Discuss

Answer: (b).Resources

Q78.

___________ is a deadlock between a statement that is reading and a statement that is performing some form of data modification.

Reader

Writer

Reader-Writer

All of the mentioned

Discuss

Answer: (c).Reader-Writer

Q79.

Which of the following query returns object names from partition IDs?

SELECT OBJECT_NAME(p.object_id) AS TableName ,
i.name AS IndexName
FROM sys.partition AS p
INNER JOIN sys.indexes AS i ON p.object_id = i.object_id
AND p.index_id = i.index_id
WHERE partition_id = 72057594038845440

SELECT OBJECT_NAME(p.object_id) AS TableName ,
i.name AS IndexName
FROM sys.partitions AS p
INNER JOIN sys.indexes AS i ON p.object_id = i.object_id
AND p.index_id = i.index_id
WHERE partition_id = 72057594038845440

SELECT OBJECT_NAME(p.object_id) AS TableName ,
i.name AS IndexName
FROM sys.partitions AS p
INNER JOIN sys.index AS i ON p.object_id = i.object_id
AND p.index_id = i.index_id
WHERE partition_id = 72057594038845440

None of the mentioned

Discuss

Answer: (b).SELECT OBJECT_NAME(p.object_id) AS TableName ,
i.name AS IndexName
FROM sys.partitions AS p
INNER JOIN sys.indexes AS i ON p.object_id = i.object_id
AND p.index_id = i.index_id
WHERE partition_id = 72057594038845440

Q80.

Which of the following best practice should be carried out concerning data types?

Avoid using NOLOCK on SQL Server UPDATE and DELETE statements

Capturing SQL Server Deadlock Information in XML Format

Optimistic Locking in SQL Server using the ROWVERSION

Processing Data Queues in SQL Server with READPAST and UPDLOCK

Discuss

Answer: (c).Optimistic Locking in SQL Server using the ROWVERSION

Page 8 of 12

Welcome to the Performance Tuning and Optimization MCQs Page

Performance Tuning and Optimization MCQs | Page 8 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

Cyber Security

Reverse Engineering

Embedded Systems

MongoDB

PHP

CompTIA PenTest+ Certification Exam PT0 002

Data Structures and Algorithms

Computer Hardware

Digital Image Processing (DIP)