MS SQL Server - 74 - Query Optimizing and Plans MCQ

74 – Query Optimizing and Plans MCQ

1 / 25

1. Which tool in SQL Server allows you to edit the optimizer strategy for query optimization?

SQL Server Data Tools

SQL Server Configuration Manager

SQL Server Management Studio

SQL Server Profiler

2 / 25

2. What is the primary purpose of the SET statement in SQL Server?

To declare variables

To configure query execution settings

To create new tables

To define indexes

3 / 25

3. In SQL Server, which tool provides a graphical interface for query execution plans?

SQL Server Profiler

SQL Server Configuration Manager

SQL Server Management Studio

SQL Server Data Tools

4 / 25

4. What is the main advantage of using graphical execution plans in SQL Server Management Studio?

They display query execution steps visually.

They provide detailed error messages.

They automatically optimize queries.

They require less memory for execution.

5 / 25

5. In SQL Server, what does an execution plan show?

The original SQL query

The query execution time

The query results

The estimated and actual query execution steps

6 / 25

6. Which SQL Server tool allows you to analyze execution plans, view statistics, and identify performance bottlenecks?

SQL Server Configuration Manager

SQL Server Data Tools

SQL Server Profiler

SQL Server Management Studio

7 / 25

7. In SQL Server, what is an estimated execution plan?

A plan that is generated without considering query parameters

The plan provided by the user before query execution

A plan that is automatically optimized

The actual plan generated during query execution

8 / 25

8. What is the purpose of a “Table Scan” execution plan in SQL Server?

To perform an index seek

To execute a subquery

To scan the entire table for rows that meet the query criteria

To display query results

9 / 25

9. In SQL Server Management Studio, what is the significance of a “Clustered Index Scan” execution plan?

It indicates an efficient query execution plan.

It shows that the query uses a non-clustered index.

It suggests a need for index optimization.

It scans the entire database for query results.

10 / 25

10. What does a “Hash Match” execution plan in SQL Server typically indicate?

A need for subquery optimization

Efficient query performance

The use of an index

A high degree of parallelism

11 / 25

11. Which dynamic management views (DMVs) in SQL Server provide information about query execution plans?

sys.dm_exec_query_stats and sys.dm_exec_query_plan

sys.dm_db_index_physical_stats and sys.dm_db_partition_stats

sys.dm_exec_requests and sys.dm_exec_sessions

sys.dm_os_memory_objects and sys.dm_os_buffer_descriptors

12 / 25

12. In SQL Server, what is the primary role of the Query Optimizer?

To design tables

To generate execution plans

To optimize stored procedures

To create indexes

13 / 25

13. What is the purpose of the “Missing Index” warning in SQL Server execution plans?

It suggests deleting existing indexes.

It suggests creating a new index for query optimization.

It indicates that an index is missing in the query result set.

It warns about unsupported SQL statements.

14 / 25

14. In SQL Server Management Studio, what tool is used to identify and analyze query performance issues?

SQL Server Configuration Manager

SQL Server Performance Dashboard

SQL Server Profiler

SQL Server Data Tools

15 / 25

15. Which SQL Server tool is commonly used for monitoring and analyzing query execution plans in real-time?

SQL Server Profiler

SQL Server Management Studio

SQL Server Configuration Manager

SQL Server Data Tools

16 / 25

16. In SQL Server, what is the main function of the SET STATISTICS IO statement?

To configure query execution settings

To show the I/O statistics for a query

To declare variables

To display query execution plans

17 / 25

17. Which SQL Server tool provides performance tuning recommendations based on query execution plans?

SQL Server Profiler

SQL Server Configuration Manager

SQL Server Database Engine Tuning Advisor

SQL Server Data Tools

18 / 25

18. In SQL Server, what is the purpose of SET STATISTICS TIME?

To display query execution plans

To show the execution time for a query

To configure query execution settings

To declare variables

19 / 25

19. Which dynamic management view (DMV) in SQL Server provides information about query statistics?

sys.dm_db_index_physical_stats

sys.dm_os_buffer_descriptors

sys.dm_exec_query_stats

sys.dm_os_memory_objects

20 / 25

20. What is the primary function of the Query Optimizer in SQL Server?

To execute queries

To analyze query execution plans

To generate execution plans

To display query results

21 / 25

21. In SQL Server, what is the main purpose of the SET NOCOUNT statement?

To configure query execution settings

To display query execution plans

To control whether row count is returned in query results

To declare variables

22 / 25

22. What is the purpose of the “Nested Loops” execution plan in SQL Server?

It suggests using an index.

It indicates an efficient query execution plan.

It performs row-by-row matching of data.

It shows a high degree of parallelism.

23 / 25

23. In SQL Server, what does a “Clustered Index Seek” execution plan indicate?

It suggests a need for index optimization.

It indicates efficient query performance.

It shows the use of non-clustered indexes.

It performs a full table scan.

24 / 25

24. What is the significance of a “Sort” operation in SQL Server execution plans?

It scans the entire table for query results.

It indicates that data is being sorted.

It performs a hash match.

It suggests a need for subquery optimization.

25 / 25

25. In SQL Server Management Studio, which tool allows you to view execution plans and analyze query performance?

SQL Server Data Tools

SQL Server Query Store

SQL Server Performance Dashboard

SQL Server Profiler

Your score is

MS SQL Server – 74 – Query Optimizing and Plans MCQ