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

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

2 / 20

How often are index statistics automatically updated in SQL Server?

After every query execution

Manually by the DBA

After each index rebuild

When data changes reach a certain threshold

3 / 20

What is a key lookup in an execution plan?

A method used to locate missing indexes

A step used to aggregate query results

A process where SQL Server retrieves columns from the clustered index

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

4 / 20

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

Increased disk space usage

Faster insert and update operations

Improved write performance

Reduced query performance

5 / 20

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

To create backups

To manage user permissions

To improve query performance

To store data

6 / 20

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

Temporary index

Full-text index

XML index

Filtered index

7 / 20

What is a covering index?

An index that includes all columns needed by a query

An index that covers all columns in a table

An index that covers only the primary key

An index that covers multiple tables

8 / 20

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

The query will perform a table scan.

SQL Server will automatically create an index.

The query will fail.

The query will use a clustered index by default.

9 / 20

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

Filtered index

Bitmap index

Clustered index

Non-clustered index

10 / 20

What is a good candidate for creating a unique index?

A column that stores timestamps

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

A column that is frequently updated

A column that allows duplicate values

11 / 20

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

Well-optimized queries

Overuse of indexes

Table scans due to missing indexes

Index rebuilds during peak hours

12 / 20

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

50%

100%

80%

13 / 20

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

A query that performs an aggregate function.

A query with a high number of join operations.

A query that selects only non-key columns.

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

14 / 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 is slower, non-clustered index is faster

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.

15 / 20

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

Avoid rebuilding indexes altogether

Use the ONLINE option for index rebuilds

Perform index rebuilds during peak hours

Increase the fill factor to 100%

16 / 20

What is a covering index?

A temporary index used during query execution

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

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

18 / 20

What does an index rebuild do?

Removes duplicate index entries

Updates the statistics of the index

Deletes the index and recreates it from scratch

Reorganizes the leaf-level pages of the index

19 / 20

How does SQL Server determine the selectivity of an index?

The number of rows in the table

The uniqueness of the indexed column

The number of joins in a query

The size of the indexed column

20 / 20

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

When data is frequently updated

When most queries return all columns

When the table has a large number of rows

When specific columns are queried

Your score is

The average score is 0%

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.