MCQs on Advanced Performance Tuning | SQL Server

Advanced performance tuning in SQL Server is crucial for ensuring optimal database operations. This comprehensive set of 30 MCQs covers key areas like DMVs, Query Store, partitioning, and TempDB optimization for enhanced SQL performance.

1. Monitoring SQL Server with DMVs

1. Which of the following DMVs is used to monitor SQL Server session activity?
   • a) sys.dm_exec_sessions
   • b) sys.dm_exec_requests
   • c) sys.dm_exec_connections
   • d) sys.dm_exec_plan_stats
2. The DMV sys.dm_exec_query_stats provides statistics on:
   • a) SQL Server login activity
   • b) Query performance
   • c) Session locks
   • d) Resource utilization
3. Which of these DMVs is used to track memory usage for queries in SQL Server?
   • a) sys.dm_exec_query_memory_grants
   • b) sys.dm_exec_requests
   • c) sys.dm_exec_sessions
   • d) sys.dm_os_memory_nodes
4. To monitor the amount of time a query has been executing, which DMV would you query?
   • a) sys.dm_exec_requests
   • b) sys.dm_exec_sessions
   • c) sys.dm_exec_plan_stats
   • d) sys.dm_exec_connections
5. Which DMV helps identify missing indexes in SQL Server?
   • a) sys.dm_db_missing_index_details
   • b) sys.dm_exec_plan_stats
   • c) sys.dm_exec_sessions
   • d) sys.dm_exec_connections
6. To view active locks held by SQL Server sessions, which DMV should you use?
   • a) sys.dm_tran_locks
   • b) sys.dm_exec_sessions
   • c) sys.dm_exec_requests
   • d) sys.dm_exec_connections
7. Which DMV provides a snapshot of SQL Server instance-level configuration?
   • a) sys.dm_exec_configurations
   • b) sys.dm_exec_sessions
   • c) sys.dm_os_sys_info
   • d) sys.dm_exec_query_stats
8. The DMV sys.dm_exec_query_plan helps in:
   • a) Monitoring session locks
   • b) Viewing execution plans for cached queries
   • c) Tracking memory usage for queries
   • d) Identifying missing indexes
9. To see the current wait type for queries in SQL Server, which DMV would you query?
   • a) sys.dm_exec_requests
   • b) sys.dm_tran_locks
   • c) sys.dm_exec_sessions
   • d) sys.dm_exec_wait_stats
10. Which of the following DMVs would help identify CPU usage per query?

• a) sys.dm_exec_requests
• b) sys.dm_exec_sessions
• c) sys.dm_exec_query_stats
• d) sys.dm_exec_query_memory_grants

2. Query Store and Plan Forcing

11. What is the purpose of the Query Store in SQL Server?

• a) To store user data securely
• b) To track query execution plans
• c) To store database backups
• d) To manage SQL Server configuration settings

12. Which of the following can be done with Query Store in SQL Server?

• a) View execution plans and runtime statistics
• b) Automatically create indexes
• c) Manage user permissions
• d) Create database backups

13. Plan forcing in SQL Server allows:

• a) Forcing SQL Server to cache more data
• b) Enforcing a specific execution plan for a query
• c) Forcing queries to use less CPU
• d) Limiting the number of concurrent queries

14. Which of these is the correct statement for enabling Query Store in SQL Server?

• a) ALTER DATABASE <db_name> SET QUERY_STORE = ON
• b) ALTER DATABASE <db_name> SET QUERY_STORE = ENABLE
• c) EXEC sp_enable_query_store
• d) EXEC sp_configure_query_store

15. What does Query Store in SQL Server store by default?

• a) Execution plans, runtime statistics, and wait times
• b) Only execution plans
• c) Only user data
• d) Only query statistics

16. In SQL Server, what is the maximum retention period for Query Store data?

• a) 30 days
• b) 7 days
• c) 365 days
• d) No limit

17. Which system view contains the execution plans stored in Query Store?

• a) sys.query_store_plan
• b) sys.dm_exec_query_store
• c) sys.query_store_statistics
• d) sys.query_store_runtime_stats

18. To force a specific execution plan for a query in SQL Server, you would use:

• a) sp_plan_force
• b) ALTER PLAN FOR QUERY
• c) sp_query_store_force_plan
• d) ALTER QUERY STORE FORCE

19. Which of the following is a limitation of Query Store?

• a) Does not support SQL Server Azure Database
• b) Does not track plan changes automatically
• c) Cannot store execution plan history
• d) Can only track queries from one database

20. Plan forcing can help resolve performance degradation caused by:

• a) Missing indexes
• b) Execution plan changes
• c) Hardware failure
• d) Database corruption

3. Partitioning Tables and Indexes

21. Which of the following is the primary benefit of partitioning a table in SQL Server?

• a) Improved data security
• b) Better performance for large tables
• c) Simplified database design
• d) Enhanced query indexing

22. What does a partition scheme define in SQL Server?

• a) The data distribution method for partitions
• b) The type of indexes used for partitions
• c) The backup schedule for partitioned tables
• d) The encryption method for partitions

23. In SQL Server, what is used to define how the rows of a partitioned table are divided?

• a) Partition function
• b) Partition index
• c) Partition key
• d) Partition schema

24. Which of the following must be created first when partitioning a table in SQL Server?

• a) Partitioned index
• b) Partition function
• c) Partitioned table
• d) Partition key

25. Which type of partitioning allows you to divide a table into multiple partitions based on a specific column?

• a) Range partitioning
• b) Hash partitioning
• c) Composite partitioning
• d) List partitioning

26. In SQL Server, what is the maximum number of partitions supported for a single table?

• a) 64
• b) 100
• c) 1000
• d) 15,000

27. When partitioning a table in SQL Server, which of the following is true about indexes?

• a) Each partition must have its own index
• b) Indexes are not supported with partitioned tables
• c) A partitioned index can be created on the partitioned table
• d) Indexes must be global for partitioned tables

28. Which of the following is an advantage of partitioning indexes in SQL Server?

• a) Reduces fragmentation
• b) Enhances backup performance
• c) Decreases I/O performance
• d) Reduces storage overhead

29. What is the best practice for managing partitioned tables in SQL Server?

• a) Avoid using indexes on partitioned tables
• b) Align partitions with high-update columns
• c) Use the same partition function across all tables
• d) Regularly shrink partitioned tables

30. Which of the following is a disadvantage of partitioning a table in SQL Server?

• a) Decreased data security
• b) Increased complexity in queries and management
• c) Faster query performance on small tables
• d) Less efficient data retrieval

4. Optimizing TempDB Performance

1. Which of the following is recommended to optimize TempDB performance in SQL Server?
   • a) Disable automatic growth of TempDB
   • b) Use multiple data files for TempDB
   • c) Store TempDB on network-attached storage
   • d) Enable full recovery model for TempDB
2. Which of the following is a benefit of using multiple TempDB data files in SQL Server?
   • a) Improved I/O performance by reducing contention
   • b) Enhanced query optimization
   • c) Simplified database backup strategy
   • d) Increased security for TempDB
3. TempDB performance can be impacted by:
   • a) Lack of disk space
   • b) Low CPU usage
   • c) High network latency
   • d) Excessive use of user databases
4. In SQL Server, what is the maximum number of TempDB data files you should have on a system?
   • a) 4
   • b) 8
   • c) 16
   • d) 50
5. The primary purpose of TempDB is to:
   • a) Store system backup files
   • b) Serve as temporary storage for database operations
   • c) Store user-generated data
   • d) Store log files for the database

---

Answer Key

Qno	Answer (Option with text)
1	a) sys.dm_exec_sessions
2	b) Query performance
3	a) sys.dm_exec_query_memory_grants
4	a) sys.dm_exec_requests
5	a) sys.dm_db_missing_index_details
6	a) sys.dm_tran_locks
7	c) sys.dm_os_sys_info
8	b) Viewing execution plans for cached queries
9	a) sys.dm_exec_requests
10	c) sys.dm_exec_query_stats
11	b) To track query execution plans
12	a) View execution plans and runtime statistics
13	b) Enforcing a specific execution plan for a query
14	a) ALTER DATABASE <db_name> SET QUERY_STORE = ON
15	a) Execution plans, runtime statistics, and wait times
16	d) No limit
17	a) sys.query_store_plan
18	c) sp_query_store_force_plan
19	a) Does not support SQL Server Azure Database
20	b) Execution plan changes
21	b) Better performance for large tables
22	a) The data distribution method for partitions
23	a) Partition function
24	b) Partition function
25	a) Range partitioning
26	d) 15,000
27	c) A partitioned index can be created on the partitioned table
28	a) Reduces fragmentation
29	b) Align partitions with high-update columns
30	b) Increased complexity in queries and management
31	b) Use multiple data files for TempDB
32	a) Improved I/O performance by reducing contention
33	a) Lack of disk space
34	b) 8
35	b) Serve as temporary storage for database operations