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

Explore more Topics under SQL Server

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

a.

Use non-clustered indexes on high-usage tables

b.

Use clustered indexes on high-usage tables

c.

Use clustered indexes on less-usage tables

d.

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.

a.

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

b.

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

c.

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

d.

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)?

a.

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

b.

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

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

d.

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?

a.

Lock

b.

Block

c.

Deadlock

d.

Exclusive lock

Discuss
Answer: (b).Block
Q75.
Point out the wrong statement.

a.

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

b.

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

c.

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

d.

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.

a.

2

b.

3

c.

4

d.

5

Discuss
Answer: (a).2
Q77.
_____________ section lists all the resources that were involved in the deadlock.

a.

Processes

b.

Resources

c.

WAIT stats

d.

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.

a.

Reader

b.

Writer

c.

Reader-Writer

d.

All of the mentioned

Discuss
Answer: (c).Reader-Writer
Q79.
Which of the following query returns object names from partition IDs?

a.

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

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

c.

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

d.

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?

a.

Avoid using NOLOCK on SQL Server UPDATE and DELETE statements

b.

Capturing SQL Server Deadlock Information in XML Format

c.

Optimistic Locking in SQL Server using the ROWVERSION

d.

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

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!

Formal Languages and Automata Theory

Discover the mathematical models of computation with our Formal Languages and...

MS Office

Master the suite of productivity tools with our MS Office MCQs. From Word and Excel...

PHP

Venture into server-side scripting with our PHP MCQs. Cover everything from syntax...

HTML

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

Object Oriented Programming Using C++

Enhance your programming skills with our MCQs on Object Oriented Programming using...

MATLAB

Hone your computational mathematics skills with our MATLAB MCQs. Understand...

SQL Server

Master Microsoft's database server with our SQL Server MCQs. Topics cover database...

Digital Logic Design

Understand the foundation of digital computers with our Digital Logic Design MCQs....

R Programming

Unleash the power of statistical computing with our R Programming MCQs. Topics cover...