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

Q11.

Setting the SORT_IN_TEMPDB option to ON can direct the SQL Server Database Engine to use ________ to store the intermediate sort results.

tempdb

master

msdb

model

Discuss

Answer: (a).tempdb

Q12.

Point out the correct statement.

The Database Engine merges the sorted runs of index leaf rows into a single, sorted stream

SQL Server also supports full-text indexes and XML indexes, but those are relevant only for specific data types

The fill-factor value is a percentage from 1 to 50

All of the mentioned

Discuss

Answer: (a).The Database Engine merges the sorted runs of index leaf rows into a single, sorted stream

Q13.

Which of the following rebuilds the IX_Employee_OrganizationLevel_OrganizationNode index with a fill factor of 80 on the HumanResources.Employee table?

ALTER INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources
REBUILD WITH (FILLFACTOR = 80);

CREATE INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee
REBUILD WITH (FILLFACTOR = 80);

ALTER INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee
REBUILD WITH (FILLFACTOR = 80);

All of the mentioned

Discuss

Answer: (c).ALTER INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee
REBUILD WITH (FILLFACTOR = 80);

Q14.

Which of the following guideline is considered during column design?

Keep the length of the index key short for non clustered indexes

An xml data type can only be a key column only in an XML index

Examine column which have composite primary keys

None of the mentioned

Discuss

Answer: (b).An xml data type can only be a key column only in an XML index

Q15.

Point out the wrong statement.

SQL Server supports only two index types: clustered and non-clustered

Appropriate indexes can make a world of difference in performance

SET option requires ALTER permission on the table or view

None of the mentioned

Discuss

Answer: (a).SQL Server supports only two index types: clustered and non-clustered

Q16.

By default, indexes are stored in the ______ filegroup as the base table on which the index is created.

same

different

may be same or different

none of the mentioned

Discuss

Answer: (a).same

Q17.

Specifying the order in which key values are stored in an index is useful when queries referencing the table have _____________

ORDER BY clauses

SORT clauses

WHERE clauses

None of the mentioned

Discuss

Answer: (a).ORDER BY clauses

Q18.

Every table should have a ________ index defined on the column or columns.

CLUSTERED

NON CLUSTERED

FULL TEXT

All of the mentioned

Discuss

Answer: (a).CLUSTERED

Q19.

Which of the following option is immediately applied to the index by using the SET clause in the ALTER INDEX statement?

ALLOW_PAGE_LOCKS

ALLOW_COLUMN_LOCKS

STATISTICS_RECOMPUTE

None of the mentioned

Discuss

Answer: (a).ALLOW_PAGE_LOCKS

Q20.

Which of the following code snippet demonstrates ALTER statement with STATISTICS_NORECOMPUTE in ONLINE state?

ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 100, SORT_IN_TEMPDB = ON,
STATISTICS_RECOMPUTE = ON);

ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON);

ALTER INDEX ALL ON Production.Product
BUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON);

All of the mentioned

Discuss

Answer: (b).ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON);

Page 2 of 12

Welcome to the Performance Tuning and Optimization MCQs Page

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

Microprocessor

Computer Architecture

Systems Programming

Object Oriented Programming Using C++

Computer Graphics

MATLAB

Python

Visual Basic

C Programming