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

Q21.

Which of the following query is used to enable a disabled index using CREATE INDEX?

CREATE INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee
(OrganizationLevel, OrganizationNode)
WITH (DROP_EXIST = ON);

CREATE INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee
(OrganizationLevel, OrganizationNode)
WITH (DROP_EXISTING = OFF);

CREATE INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee
(OrganizationLevel, OrganizationNode)
WITH (DROP_EXISTING = ON);

None of the mentioned

Discuss

Answer: (c).CREATE INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee
(OrganizationLevel, OrganizationNode)
WITH (DROP_EXISTING = ON);

Q22.

Point out the correct statement.

When SORT_IN_TEMPDB is set to OFF, the default, the sort runs are stored in the source filegroup

The Database Engine first scans the data pages of the base table to retrieve key values and builds an index leaf row for each data row

When SORT_IN_TEMPDB is set to ON, the default, the sort runs are stored in the destination filegroup

None of the mentioned

Discuss

Answer: (b).The Database Engine first scans the data pages of the base table to retrieve key values and builds an index leaf row for each data row

Q23.

Which of the following query enables the IX_Employee_OrganizationLevel_OrganizationNode index on the Employee table?

DBCC DBREINDEX (“Employee”, IX_Employee_OrganizationLevel_OrganizationNode);

DBCC REINDEX (“Employee”, IX_Employee_OrganizationLevel_OrganizationNode);

DBCC DBINDEX (“Employee”, IX_Employee_OrganizationLevel_OrganizationNode);

All of the mentioned

Discuss

Answer: (a).DBCC DBREINDEX (“Employee”, IX_Employee_OrganizationLevel_OrganizationNode);

Q24.

Which of the following query can enable all indexes on Employee table?

DBCC DBREINDEX (“Employee”, ” “);

DBCC DBREINDEX (“EMP”, ” “);

DBCC DBREINDEX (“HumanResources.Employee”, “ALL”);

None of the mentioned

Discuss

Answer: (a).DBCC DBREINDEX (“Employee”, ” “);

Q25.

Point out the wrong statement.

If SORT_IN_TEMPDB is set to OFF, the free space in the destination filegroup must be large enough to store the final index structure

When you create a clustered index on a table that has nonclustered indexes, you must have available as free space

When you set the SORT_IN_TEMPDB option to ON, you must have sufficient free disk space available in master database

All of the mentioned

Discuss

Answer: (a).If SORT_IN_TEMPDB is set to OFF, the free space in the destination filegroup must be large enough to store the final index structure

Q26.

Which of the following query disables all the indexes on Employee table?

ALTER INDEX MUL ON HumanResources.Employee
DISABLE;

ALTER INDEX NEST ON HumanResources.Employee
DISABLE;

ALTER INDEX ALL ON HumanResources.Employee
ENABLE;

ALTER INDEX ALL ON HumanResources.Employee
DISABLE;

Discuss

Answer: (d).ALTER INDEX ALL ON HumanResources.Employee
DISABLE;

Q27.

Which of the following index operations require no additional disk space?

ALTER INDEX

DROP INDEX

CREATE INDEX

All of the mentioned

Discuss

Answer: (d).All of the mentioned

Q28.

Which of the following index operation require additional disk space?

UPDATE INDEX

DROP INDEX MOVE TO

ALTER INDEX ADD CONSTRAINT

All of the mentioned

Discuss

Answer: (b).DROP INDEX MOVE TO

Q29.

The SORT_IN_TEMPDB option cannot be set for ________ statements.

ALTER INDEX

DROP INDEX

CREATE INDEX

All of the mentioned

Discuss

Answer: (b).DROP INDEX

Q30.

Which of the following query will disable the index on Employee table?

ALTER INDEX IX_Employee_OrganizationLevel_OrganizationNode Employee
DISABLE;

ALTER INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources
DISABLE;

CREATE INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee
DISABLE;

None of the mentioned

Discuss

Answer: (a).ALTER INDEX IX_Employee_OrganizationLevel_OrganizationNode Employee
DISABLE;

Page 3 of 12

Welcome to the Performance Tuning and Optimization MCQs Page

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

CSS

R Programming

DBMS

Software Engineering

Microprocessor

SQL Server

Digital Image Processing (DIP)

UNIX

Systems Analysis and Design Methods