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 backup the database

To create indexes automatically

To identify missing indexes and optimize queries

To analyze server memory usage

2 / 20

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

Clustered indexes allow duplicates, non-clustered do not.

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

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

Clustered index is slower, non-clustered index is faster

3 / 20

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

999

1024

128

4 / 20

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

100%

80%

50%

5 / 20

Which of the following is not an index type in SQL Server?

Full-text index

XML index

Temporary index

Filtered index

6 / 20

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

When specific columns are queried

When data is frequently updated

When most queries return all columns

When the table has a large number of rows

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

8 / 20

What is a key lookup in an execution plan?

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

A step used to aggregate query results

A process where SQL Server retrieves columns from the clustered index

A method used to locate missing indexes

9 / 20

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

Query Store

Database Tuning Advisor

Execution Plan Viewer

SQL Profiler

10 / 20

What is a covering index?

An index that includes all columns of a table

An index that can be used for sorting data

An index that satisfies a query without accessing the base table

A temporary index used during query execution

11 / 20

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

Non-clustered index

Clustered index

Full-text index

Unique index

12 / 20

What is the effect of using an index on a column with many NULL values?

Forces a clustered index scan

Reduces the performance of queries

Increases query performance significantly

Has little to no impact on performance

13 / 20

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

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

A query with a high number of join operations.

A query that performs an aggregate function.

A query that selects only non-key columns.

14 / 20

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

Query Store

SQL Server Profiler

Index Tuning Wizard

Execution Plan

15 / 20

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

sp_who2

DBCC CHECKDB

sys.dm_db_missing_index_details

sys.dm_exec_requests

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

17 / 20

What is a good candidate for creating a unique index?

A column that allows duplicate values

A column that is frequently updated

A column that stores timestamps

A column that must contain unique values, like a social security number

18 / 20

How often are index statistics automatically updated in SQL Server?

Manually by the DBA

After every query execution

When data changes reach a certain threshold

After each index rebuild

19 / 20

How does SQL Server determine the selectivity of an index?

The uniqueness of the indexed column

The number of joins in a query

The size of the indexed column

The number of rows in the table

20 / 20

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

Overuse of indexes

Well-optimized queries

Table scans due to missing indexes

Index rebuilds during peak hours

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.