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

Explore more Topics under SQL Server

Q31.
The use of parameters, including parameter markers in _______ applications, can increase the reuse of execution plans.

a.

OLE DB

b.

ADO

c.

ODBC

d.

All of the mentioned

Discuss
Answer: (d).All of the mentioned
Q32.
Point out the correct statement.

a.

JDBC use parameter markers

b.

Parameter markers are question marks (?) that replace a constant in an SQL statement

c.

When forced parameterization is tried but fails, simple parameterization is still subsequently tried

d.

All of the mentioned

Discuss
Answer: (b).Parameter markers are question marks (?) that replace a constant in an SQL statement
Q33.
Use to bind an integer variable to the first parameter marker in an SQL statement.

a.

SQLParameter

b.

SQLBind

c.

SQLBindParameter

d.

All of the mentioned

Discuss
Answer: (c).SQLBindParameter
Q34.
Which of the following query cannot be parameterized according to the rules of forced parameterization?

a.

SELECT * FROM Person.Address
WHERE AddressID = 1 + 2;

b.

SELECT * FROM Person.Address
WHERE AddressID = 1;

c.

SELECT * FROM Person.Address
WHERE AddressID = 2;

d.

All of the mentioned

Discuss
Answer: (a).SELECT * FROM Person.Address
WHERE AddressID = 1 + 2;
Q35.
Point out the wrong statement.

a.

MisGuided Plan Executions/sec represents the number of plan executions per second in which the query plan has been generated by using a plan guide

b.

Execution plans remain in the procedure cache as long as there is enough memory to store them

c.

When a user process inserts an execution plan into the cache, the user process sets the current cost equal to the original query compile cost

d.

All of the mentioned

Discuss
Answer: (a).MisGuided Plan Executions/sec represents the number of plan executions per second in which the query plan has been generated by using a plan guide
Q36.
Which of the following SQL Server SQL Statistics counter represents number of auto-parameterization attempts per second?

a.

Batch Requests/sec

b.

Failed Auto-Params/sec

c.

Forced parameterizations/sec

d.

Auto-Param Attempts/sec

Discuss
Answer: (d).Auto-Param Attempts/sec
Q37.
The maximum size for all caches is a function of the ________ size.

a.

RAM

b.

Buffer pool

c.

Hard disk

d.

None of the mentioned

Discuss
Answer: (b).Buffer pool
Q38.
The conditions that invalidate a plan include which of the following?

a.

An explicit call to sp_compile

b.

Executing a stored procedure using the WITH COMPILE option

c.

Changes made to a table or view referenced by the query

d.

All of the mentioned

Discuss
Answer: (c).Changes made to a table or view referenced by the query
Q39.
What is the syntax for DBCC FREEPROCCACHE with all the optional parameters in T-SQL?

a.

DBCC FREEPROCCACHE [ ( { plan_handle | sql_handle | pool_name } ) ]

b.

DBCC FREEPROCCACHE [ ( { plan_handle | sql_handle | pool_name } ) ] [ WITH NO_INFOMSGS ]

c.

DBCC FREEPROCCACHE [ ( { sql_handle | pool_name } ) ] [ WITH NO_INFOMSGS ]

d.

None of the mentioned

Discuss
Answer: (b).DBCC FREEPROCCACHE [ ( { plan_handle | sql_handle | pool_name } ) ] [ WITH NO_INFOMSGS ]
Q40.
_________ uniquely identifies a query plan for a batch that has executed and whose plan resides in the plan cache.

a.

pool_name

b.

plan_handle

c.

sql_handle

d.

none of the mentioned

Discuss
Answer: (b).plan_handle
Page 4 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!

DBMS

Get a firm grasp on database systems with our DBMS MCQs. Explore relational...

Cyber Security

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

Internet of Things (IoT)

Discover how devices communicate with each other in the smart world with our IoT...

C Programming

Master one of the most widely used programming languages with our C Programming MCQs....

Data Science

Discover the fascinating world of extracting insights from data with our Data Science...

Systems Analysis and Design Methods

Get familiar with the process of designing and improving business systems with our...

Visual Basic

Enhance your knowledge of Microsoft's event-driven programming language with our...

Java Programming

Level up your coding skills with our Java Programming MCQs. From object-oriented...

Embedded Systems

Dive into the world of specialized computing systems with our Embedded Systems MCQs....