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

Explore more Topics under SQL Server

Q101.
SQL Server 2014 supports up to ________ partitions by default.

a.

20000

b.

2000

c.

1000

d.

15000

Discuss
Answer: (d).15000
Q102.
Point out the correct statement.

a.

On x86-based systems, creating a table or index with more than 1000 partitions is possible

b.

Compressing large tables or indexes can have manageability and performance benefit

c.

Partitioning slow tables or indexes can have the following manageability and performance benefit

d.

None of the mentioned

Discuss
Answer: (a).On x86-based systems, creating a table or index with more than 1000 partitions is possible
Q103.
__________ is a database object that defines how the rows of a table or index are mapped to a set of partitions based on the values of certain column.

a.

Partition function

b.

Partition scheme

c.

Partition column

d.

All of the mentioned

Discuss
Answer: (a).Partition function
Q104.
Designing an nonaligned partitioned index can be useful in the following case?

a.

The base table has not been partitioned

b.

The index key is unique and it does not contain the partitioning column of the table

c.

You want the base table to participate in collocated joins with more tables using different join columns

d.

All of the mentioned

Discuss
Answer: (d).All of the mentioned
Q105.
Point out the wrong statement.

a.

Computed columns that participate in a partition function must be explicitly marked PERSISTED

b.

The new, higher limit of 15,000 partitions affects memory, partitioned index operations, DBCC commands, and queries

c.

Partitioning column is the process by which the query optimizer accesses only the relevant partitions to satisfy the filter criteria of the query

d.

All of the mentioned

Discuss
Answer: (c).Partitioning column is the process by which the query optimizer accesses only the relevant partitions to satisfy the filter criteria of the query
Q106.
At least _______ of RAM is recommended if a large number of partitions are in use.

a.

16 GB

b.

4 GB

c.

8 GB

d.

2 GB

Discuss
Answer: (a).16 GB
Q107.
With a larger number of partitions, ______ commands could take longer to execute as the number of partitions increases.

a.

DML

b.

DDL

c.

DBCC

d.

TCL

Discuss
Answer: (c).DBCC
Q108.
SQL Server 2008 provides enhanced partitioning information for __________ execution plans.

a.

compile-time

b.

run time

c.

both compile-time and run-time

d.

all of the mentioned

Discuss
Answer: (c).both compile-time and run-time
Q109.
Which of the following partitioned attribute can appear in the following physical and logical operators?

a.

Index Seek

b.

Drop

c.

Alter

d.

None of the mentioned

Discuss
Answer: (a).Index Seek
Q110.
The query processor uses a __________ execution strategy for queries that select from partitioned objects.

a.

distributed

b.

parallel

c.

lock

d.

none of the mentioned

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

Cryptography and Network Security

Secure your knowledge of information security with our Cryptography and Network...

Discrete Mathematics

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

Compiler Design

Unravel the process of translating source code into executable programs with our...

Operating System

Dive deep into the core of computers with our Operating System MCQs. Learn about...

Computer Hardware

Explore the tangible components of computers with our Computer Hardware MCQs. Learn...

Object Oriented Programming

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

SQL Server

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

MS Office

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

Cloud Computing

Stay on top of the biggest trend in IT with our Cloud Computing MCQs. These questions...