MCQs on Performance Tuning Basics | SQL Server

nhance your SQL Server expertise with these 30 MCQs on performance tuning basics. Covering query execution plans, statistics, index fragmentation, and optimization techniques, this guide ensures efficient query performance.

---

MCQs on Performance Tuning Basics | SQL Server

Query Execution Plans

1. What does an execution plan in SQL Server show?
   • A) Execution time of queries
   • B) Query optimization level
   • C) Steps SQL Server uses to execute a query
   • D) Index creation steps
2. Which command is used to view an execution plan in SQL Server?
   • A) SHOW PLAN;
   • B) EXECUTION PLAN;
   • C) EXPLAIN;
   • D) SET SHOWPLAN_XML ON;
3. What is a graphical execution plan in SQL Server?
   • A) A plan showing textual details of query execution
   • B) A visual representation of query execution steps
   • C) A list of database optimization issues
   • D) A performance report
4. What does a high “Cost” in an execution plan indicate?
   • A) Query is inexpensive
   • B) Query may need optimization
   • C) Query is running in parallel
   • D) Query uses no indexes
5. How do you save an execution plan for future analysis?
   • A) Use the SAVE EXECUTION command
   • B) Save it as .sqlplan
   • C) Export it as .txt
   • D) Save it in sys.execution_plans

---

Understanding Statistics

6. What is the primary purpose of statistics in SQL Server?
   • A) To track table sizes
   • B) To optimize query performance
   • C) To enforce constraints
   • D) To create indexes automatically
7. When are statistics automatically updated in SQL Server?
   • A) After every query execution
   • B) When the UPDATE STATISTICS command is run
   • C) When a significant portion of data changes
   • D) At database startup
8. Which system function shows detailed statistics for a specific table?
   • A) DBCC SHOWSTATISTICS
   • B) DBCC CHECKTABLE
   • C) DBCC CHECKSTATISTICS
   • D) DBCC SQLSTATS
9. How can outdated statistics affect query performance?
   • A) It improves performance by avoiding recalculations
   • B) It causes suboptimal query plans
   • C) It makes queries slower but more accurate
   • D) It has no impact
10. Which query updates all statistics for all tables in a database?
    • A) UPDATE ALL STATISTICS;
    • B) UPDATE STATISTICS ALL;
    • C) EXEC sp_updatestats;
    • D) DBCC UPDATESTATISTICS;

---

Index Fragmentation and Maintenance

11. What is index fragmentation?
    • A) Loss of index rows
    • B) Mismatch of index and table structures
    • C) Physical and logical disorganization of index pages
    • D) Duplicate rows in an index
12. How is index fragmentation identified in SQL Server?
    • A) Using DBCC SHOWSTATISTICS
    • B) Querying sys.dm_db_index_physical_stats
    • C) Running sp_helpindex
    • D) Analyzing the execution plan
13. What is the recommended approach for fixing a highly fragmented index?
    • A) Dropping and recreating the index
    • B) Using ALTER INDEX REBUILD
    • C) Using UPDATE STATISTICS
    • D) Exporting index data to a file
14. At what fragmentation level is an index reorganized rather than rebuilt?
    • A) Below 5% fragmentation
    • B) Between 5% and 30% fragmentation
    • C) Above 50% fragmentation
    • D) Any level of fragmentation
15. What happens when ALTER INDEX ... REBUILD is performed?
    • A) Index statistics are reset
    • B) The index is dropped and re-created
    • C) The index pages are reorganized
    • D) The index structure is updated incrementally

---

Query Hints and Optimizations

16. What is the purpose of a query hint in SQL Server?
    • A) To enforce specific query execution options
    • B) To show hints for better query writing
    • C) To debug stored procedures
    • D) To enable parallel execution
17. Which query hint forces the use of a specific index?
    • A) FORCE INDEX
    • B) USE INDEX
    • C) INDEX
    • D) WITH (INDEX)
18. What does the OPTION (RECOMPILE) query hint do?
    • A) Forces SQL Server to recompile the query plan for every execution
    • B) Optimizes the existing query plan
    • C) Disables query execution statistics
    • D) Forces the use of parallelism
19. What is the effect of the MAXDOP hint?
    • A) It limits the maximum degree of parallelism for a query
    • B) It disables the use of indexes
    • C) It forces serial execution of a query
    • D) It enables dynamic query optimization
20. When should you avoid using query hints?
    • A) When query performance is critical
    • B) When SQL Server can generate efficient plans without intervention
    • C) When dealing with complex queries
    • D) When working with indexed views

---

Performance Tuning Scenarios

21. Which operator in an execution plan is the most expensive?
    • A) Index Scan
    • B) Index Seek
    • C) Nested Loop
    • D) Table Scan
22. How can you improve performance for frequently used queries?
    • A) Drop all indexes
    • B) Use query hints for every execution
    • C) Cache the query results
    • D) Create appropriate indexes
23. Why is analyzing query execution plans critical for performance tuning?
    • A) It guarantees improved execution times
    • B) It helps identify bottlenecks in query execution
    • C) It enables query parallelism
    • D) It ensures all indexes are utilized
24. What is the recommended way to reduce blocking in queries?
    • A) Use NOLOCK hints
    • B) Disable transaction isolation
    • C) Use FORCE ORDER hints
    • D) Drop clustered indexes
25. What tool can analyze and suggest query performance improvements in SQL Server?
    • A) SQL Server Management Studio (SSMS)
    • B) Database Engine Tuning Advisor (DTA)
    • C) SQL Server Profiler
    • D) Activity Monitor

---

Answer Key

Qno	Answer
1	C) Steps SQL Server uses to execute a query
2	D) SET SHOWPLAN_XML ON;
3	B) A visual representation of query execution steps
4	B) Query may need optimization
5	B) Save it as .sqlplan
6	B) To optimize query performance
7	C) When a significant portion of data changes
8	A) DBCC SHOWSTATISTICS
9	B) It causes suboptimal query plans
10	C) EXEC sp_updatestats;
11	C) Physical and logical disorganization of index pages
12	B) Querying sys.dm_db_index_physical_stats
13	B) Using ALTER INDEX REBUILD
14	B) Between 5% and 30% fragmentation
15	B) The index is dropped and re-created
16	A) To enforce specific query execution options
17	D) WITH (INDEX)
18	A) Forces SQL Server to recompile the query plan for every execution
19	A) It limits the maximum degree of parallelism for a query
20	B) When SQL Server can generate efficient plans without intervention
21	D) Table Scan
22	D) Create appropriate indexes
23	B) It helps identify bottlenecks in query execution
24	A) Use NOLOCK hints
25	B) Database Engine Tuning Advisor (DTA)