SQL Server Indexes and Performance Tuning: Intermediate-Level Quiz

SQL Server Indexes and Performance Tuning: Intermediate Level Quiz

This quiz tests your knowledge of SQL Server indexes and performance tuning. It covers key concepts such as index creation, optimization techniques, and performance monitoring. Good luck!

1 / 20

What is the maximum number of non-clustered indexes that can be created on a SQL Server table?

1024

999

128

2 / 20

Which of the following is a potential downside of over-indexing a table?

Improved write performance

Reduced query performance

Increased disk space usage

Faster insert and update operations

3 / 20

What is the primary function of a composite index?

To improve query performance on large text columns

To index XML data types

To create an index on a computed column

To index multiple columns within a table

4 / 20

Which of the following is the primary purpose of an index in SQL Server?

To store data

To improve query performance

To manage user permissions

To create backups

5 / 20

How can you reduce the impact of index maintenance on a production environment?

Avoid rebuilding indexes altogether

Increase the fill factor to 100%

Use the ONLINE option for index rebuilds

Perform index rebuilds during peak hours

6 / 20

What is the recommended fill factor setting for a table with frequent inserts?

50%

80%

100%

7 / 20

What does an index rebuild do?

Reorganizes the leaf-level pages of the index

Deletes the index and recreates it from scratch

Updates the statistics of the index

Removes duplicate index entries

8 / 20

What command would you use to view the missing index recommendations in SQL Server?

sp_who2

sys.dm_exec_requests

sys.dm_db_missing_index_details

DBCC CHECKDB

9 / 20

In which situation would a non-clustered index be more efficient than a clustered index?

When data is frequently updated

When the table has a large number of rows

When most queries return all columns

When specific columns are queried

10 / 20

What is a bookmark lookup in SQL Server?

A lookup operation in a clustered index

A process to optimize query execution time

A function used in dynamic SQL queries

A method to retrieve rows using a non-clustered index and the row identifier

11 / 20

What kind of index is best for columns with a small range of values, such as status flags?

Clustered index

Filtered index

Bitmap index

Non-clustered index

12 / 20

What is a covering index?

An index that covers only the primary key

An index that covers all columns in a table

An index that includes all columns needed by a query

An index that covers multiple tables

13 / 20

How does SQL Server determine the selectivity of an index?

The size of the indexed column

The number of rows in the table

The number of joins in a query

The uniqueness of the indexed column

14 / 20

What is the purpose of the SQL Server Database Engine Tuning Advisor?

To backup the database

To create indexes automatically

To identify missing indexes and optimize queries

To analyze server memory usage

15 / 20

What does a query optimizer do?

Increases the memory allocation for SQL Server

Executes the SQL queries

Analyzes queries and chooses the best execution plan

Rebuilds indexes automatically

16 / 20

What index type is created by default when a primary key is defined on a table?

Clustered index

Unique index

Non-clustered index

Full-text index

17 / 20

What happens if a query does not have a suitable index?

SQL Server will automatically create an index.

The query will use a clustered index by default.

The query will fail.

The query will perform a table scan.

18 / 20

Which SQL Server feature helps in tracking the performance of queries over time?

SQL Server Profiler

Execution Plan

Query Store

Index Tuning Wizard

19 / 20

Which tool in SQL Server can is used to view the execution plan of a query?

Execution Plan Viewer

Query Store

Database Tuning Advisor

SQL Profiler

20 / 20

Which query is most likely to benefit from a covering index?

A query that performs an aggregate function.

A query that selects only non-key columns.

A query that retrieves both key and non-key columns.

A query with a high number of join operations.

Your score is

The average score is 40%

Do you feel you are past the beginner level in your SQL Server proficiency? This intermediate-level quiz will test you on SQL Server indexes and performance tuning.

Efficient database management starts with mastering SQL Server indexes and performance-tuning techniques. Also, indexes like clustered and non-clustered can drastically improve query speed, while proper maintenance and optimization strategies help prevent slowdowns.

In this quiz, you will answer questions covering essential indexing methods and common pitfalls. The quiz will also test your knowledge of the best practices to enhance your SQL Server’s performance, ensuring faster data retrieval and smoother operations.

All the best.