MCQs on SQL Server Architecture | SQL Server
Dive into SQL Server Architecture and enhance your knowledge! These 30 Multiple Choice Questions (MCQs) are carefully designed to cover SQL Server Engine, Memory, Data Pages, Log Files, and TempDB intricacies.
SQL Server Engine and Memory Architecture
1. What is the primary responsibility of the SQL Server Database Engine?
A) Managing network communications
B) Query processing and data storage
C) Application development
D) File system operations
2. Which component handles query parsing in SQL Server?
A) Storage Engine
B) Query Optimizer
C) Relational Engine
D) SQL Agent
3. What type of memory is used by SQL Server to store execution plans?
A) Buffer Pool
B) Plan Cache
C) File Cache
D) CLR Memory
4. What is the default maximum memory allocation for SQL Server?
A) 2 GB
B) No Limit
C) 128 GB
D) 64 GB
5. Which system process manages background tasks such as checkpoint operations?
A) Lazy Writer
B) Checkpoint Process
C) SQL Agent
D) Background Writer
Understanding Data Pages and Extents
6. What is the size of a single SQL Server data page?
A) 2 KB
B) 4 KB
C) 8 KB
D) 16 KB
7. What is an extent in SQL Server?
A) A set of 8 contiguous data pages
B) A single page allocation
C) Memory block for queries
D) SQL Server backup file
8. Which type of extent is allocated to store objects with small sizes?
A) Mixed Extent
B) Uniform Extent
C) Filegroup Extent
D) Indexed Extent
9. How many records can typically fit into a single data page?
A) 1
B) Depends on record size
C) 10
D) Unlimited
10. What is the purpose of the IAM (Index Allocation Map) page?
A) Storing query plans
B) Managing extent allocation
C) Monitoring buffer cache
D) Allocating tempdb memory
Log File Architecture
11. What is the role of the transaction log file in SQL Server?
A) Query parsing
B) Recording all transactions
C) Storing execution plans
D) Temporary data processing
12. Which operation is NOT logged in the transaction log?
A) Bulk Insert
B) Index rebuilds
C) Data modifications
D) System checkpoint
13. What does the LSN (Log Sequence Number) signify in the log file?
A) Backup status
B) Recovery state
C) Order of transactions
D) SQL Agent jobs
14. What happens when the log file is full?
A) Query execution halts
B) Checkpoint truncates log
C) Log backup is required
D) Database shuts down
15. In simple recovery model, what happens to the transaction log during checkpoint?
A) Truncated automatically
B) Copied to TempDB
C) Compressed for storage
D) Archived for later recovery
TempDB Internals and Optimization
16. What type of data is stored in TempDB?
A) User data
B) System metadata
C) Temporary objects and internal objects
D) Execution plans
17. How many TempDB files are recommended for systems with heavy tempdb usage?
A) 1
B) 2
C) 1 per CPU core
D) No specific recommendation
18. What is the default recovery model for TempDB?
A) Full
B) Bulk-Logged
C) Simple
D) Mixed
19. How can you prevent contention on TempDB system tables?
A) Increase data file size
B) Configure multiple data files
C) Enable trace flags
D) Shrink the database
20. Which query can help identify TempDB contention?
A) sys.dm_exec_requests
B) sys.dm_exec_query_stats
C) sys.dm_os_wait_stats
D) sys.dm_db_file_space_usage
Mixed Topics
21. What is the role of the SQL Server optimizer?
A) Executes queries directly
B) Analyzes and creates query execution plans
C) Monitors query statistics
D) Manages memory allocation
22. Which memory architecture is critical for caching index pages?
A) Plan Cache
B) Buffer Pool
C) Log Cache
D) File Cache
23. What type of pages does the PFS (Page Free Space) page track?
A) Pages with free space
B) IAM pages
C) Log pages
D) Backup pages
24. How does TempDB handle user session temporary tables?
A) Stores in memory only
B) Allocates on shared extents
C) Assigns private extents
D) Backs up during shutdown
25. What is the purpose of the SQL Server Checkpoint process?
A) Writes dirty pages to disk
B) Clears transaction logs
C) Optimizes indexes
D) Deletes unused tables
26. What is the significance of the “ghost cleanup” task?
A) Deletes unused records
B) Optimizes stored procedures
C) Updates log files
D) Compresses backups
27. Which dynamic management view (DMV) provides memory usage details?
A) sys.dm_os_performance_counters
B) sys.dm_exec_sessions
C) sys.dm_os_memory_clerks
D) sys.dm_exec_requests
28. What should be monitored to optimize TempDB performance?
A) Wait statistics
B) Log size
C) Query parsing time
D) Filegroup allocations
29. Which SQL Server feature ensures minimal downtime during log backups?
A) Instant File Initialization
B) Active Transaction Log Truncation
C) Differential Backups
D) Log Mirroring
30. What type of workload is likely to cause TempDB contention?
A) OLTP queries
B) Bulk Inserts
C) Heavy usage of temporary tables
D) Full database backups
Answers
| QNo | Answer (Option with the text) |
|---|---|
| 1 | B) Query processing and data storage |
| 2 | C) Relational Engine |
| 3 | B) Plan Cache |
| 4 | B) No Limit |
| 5 | A) Lazy Writer |
| 6 | C) 8 KB |
| 7 | A) A set of 8 contiguous data pages |
| 8 | A) Mixed Extent |
| 9 | B) Depends on record size |
| 10 | B) Managing extent allocation |
| 11 | B) Recording all transactions |
| 12 | A) Bulk Insert |
| 13 | C) Order of transactions |
| 14 | C) Log backup is required |
| 15 | A) Truncated automatically |
| 16 | C) Temporary objects and internal objects |
| 17 | C) 1 per CPU core |
| 18 | C) Simple |
| 19 | B) Configure multiple data files |
| 20 | C) sys.dm_os_wait_stats |
| 21 | B) Analyzes and creates query execution plans |
| 22 | B) Buffer Pool |
| 23 | A) Pages with free space |
| 24 | C) Assigns private extents |
| 25 | A) Writes dirty pages to disk |
| 26 | A) Deletes unused records |
| 27 | C) sys.dm_os_memory_clerks |
| 28 | A) Wait statistics |
| 29 | B) Active Transaction Log Truncation |
| 30 | C) Heavy usage of temporary tables |