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 purpose of the SQL Server Database Engine Tuning Advisor?

To create indexes automatically

To analyze server memory usage

To identify missing indexes and optimize queries

To backup the database

2 / 20

Which of the following can cause high CPU usage during query execution?

Well-optimized queries

Index rebuilds during peak hours

Overuse of indexes

Table scans due to missing indexes

3 / 20

What does an index rebuild do?

Deletes the index and recreates it from scratch

Updates the statistics of the index

Reorganizes the leaf-level pages of the index

Removes duplicate index entries

4 / 20

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

The query will fail.

The query will use a clustered index by default.

SQL Server will automatically create an index.

The query will perform a table scan.

5 / 20

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

Non-clustered index

Filtered index

Bitmap index

Clustered index

6 / 20

What is a covering index?

An index that can be used for sorting data

An index that includes all columns of a table

An index that satisfies a query without accessing the base table

A temporary index used during query execution

7 / 20

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

Full-text index

Clustered index

Non-clustered index

Unique index

8 / 20

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

When data is frequently updated

When specific columns are queried

When the table has a large number of rows

When most queries return all columns

9 / 20

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

sp_who2

DBCC CHECKDB

sys.dm_exec_requests

sys.dm_db_missing_index_details

10 / 20

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

999

128

1024

11 / 20

What is a bookmark lookup in SQL Server?

A function used in dynamic SQL queries

A lookup operation in a clustered index

A process to optimize query execution time

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

12 / 20

What does a query optimizer do?

Increases the memory allocation for SQL Server

Analyzes queries and chooses the best execution plan

Rebuilds indexes automatically

Executes the SQL queries

13 / 20

How often are index statistics automatically updated in SQL Server?

When data changes reach a certain threshold

After every query execution

Manually by the DBA

After each index rebuild

14 / 20

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

Database Tuning Advisor

SQL Profiler

Execution Plan Viewer

Query Store

15 / 20

Which of the following is one of the primary differences between a clustered index and a non-clustered index?

Clustered index is slower, non-clustered index is faster

Non-clustered indexes are sorted by the key column, clustered indexes are not.

Clustered indexes allow duplicates, non-clustered do not.

Clustered index has the inherent ability to store data on the disk, non-clustered cannot.

16 / 20

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

To create backups

To store data

To manage user permissions

To improve query performance

17 / 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

18 / 20

What is a key lookup in an execution plan?

A process where SQL Server retrieves columns from the clustered index

A step used to aggregate query results

A search in a non-clustered index for the matching rows

A method used to locate missing indexes

19 / 20

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

100%

50%

80%

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.