NYC Certified IT Administrator
(Database) Exam Practice Questions And
Correct Answers (Verified Answers) Plus
Rationales 2026 Q&A | Instant Download
Pdf
Q1. A municipal agency database administrator notices that a SQL Server
production database experiences severe blocking during payroll processing every
Friday afternoon. Investigation reveals long-running transactions holding shared
locks on employee tables while reports are generated. Which administrative
solution would MOST effectively reduce blocking while preserving transactional
consistency?
Correct Answer: B. Implement snapshot isolation for reporting workloads
Rationale: Snapshot isolation provides row-versioning that allows reporting
queries to read a consistent snapshot of the data without acquiring shared locks,
thereby eliminating blocking on the payroll tables. Option A (READ
UNCOMMITTED) would allow dirty reads and break transactional consistency. C
disables logging, which is unsafe. D addresses file growth, not blocking.
Q2. Which operation would be the most effective initial step when a DBA needs to
diagnose a sudden, unexplained spike in CPU usage on a production SQL Server?
Correct Answer: Query
the sys.dm_exec_requests and sys.dm_os_wait_stats DMVs
Rationale: These dynamic management views provide real-time information about
currently executing requests, wait types, and CPU consumption, allowing the DBA
,to pinpoint the offending queries. Checking backups, logs, or rebuilding indexes
would be subsequent steps after identifying the root cause.
Q3. A city agency requires near-zero downtime and automatic failover for its
critical voter registration database across two data centers. Which high-availability
technology is most appropriate?
Correct Answer: Always On Availability Groups
Rationale: Always On Availability Groups provide enterprise-grade high
availability, automatic failover, and near real-time synchronization between
replicas. Log shipping has a delayed failover and possible data loss. Database
snapshots are read-only recovery points, not HA solutions. Export replication is
unsuitable for mission-critical scenarios.
Q4. During a performance review, a DBA notices
excessive PAGEIOLATCH_XX wait types on a heavily used reporting database.
What does this most likely indicate?
Correct Answer: Memory pressure caused by insufficient buffer cache
efficiency
Rationale: PAGEIOLATCH waits indicate that SQL Server is waiting for data
pages to be read from disk into memory, which is typically caused by insufficient
memory allocation or poor cache performance. CXPACKET waits would indicate
CPU parallelism issues, and network latency would produce different wait types.
Q5. A government auditing policy requires that database administrators cannot
directly read sensitive citizen financial records while still maintaining system
administration capabilities. Which principle should be implemented?
Correct Answer: Principle of least privilege with role separation
Rationale: Role separation ensures administrators have only the permissions
necessary to perform operational duties, without unrestricted access to confidential
, data. Transparent Data Encryption protects data at rest but does not prevent
authorized reads. Shared accounts violate accountability requirements.
Q6. A DBA plans to rebuild fragmented indexes nightly on a transactional
database. Which potential issue should be MOST carefully considered?
Correct Answer: Temporary locking and resource utilization during
rebuild operations
Rationale: Index rebuilds can consume CPU, memory, and I/O resources while
potentially causing locking depending on the SQL Server edition and
configuration. This can impact production workloads if not scheduled during
maintenance windows. The other options are unrelated to the rebuild process.
Q7. An enterprise database experiences deadlocks between inventory update
transactions and reporting queries. Which tool would BEST help identify the
deadlock participants and execution sequence?
Correct Answer: Extended Events deadlock graph
Rationale: Extended Events provide detailed deadlock graphs showing the
processes, resources, and query interactions involved. SQL Server Agent schedules
jobs but does not diagnose deadlocks. DBCC CHECKDB checks integrity, and
Event Viewer lacks granular deadlock analysis.
Q8. A database file continues growing rapidly despite frequent data deletion.
Which explanation is MOST accurate?
Correct Answer: Free space inside the database file is reusable but not
automatically returned to the operating system
Rationale: SQL Server reuses freed internal space but does not automatically
shrink physical files after deletions. AUTOSHRINK is generally disabled or
discouraged. Deleted data does not immediately reduce the physical file size, and
transaction logs do not permanently prevent file reduction.
(Database) Exam Practice Questions And
Correct Answers (Verified Answers) Plus
Rationales 2026 Q&A | Instant Download
Q1. A municipal agency database administrator notices that a SQL Server
production database experiences severe blocking during payroll processing every
Friday afternoon. Investigation reveals long-running transactions holding shared
locks on employee tables while reports are generated. Which administrative
solution would MOST effectively reduce blocking while preserving transactional
consistency?
Correct Answer: B. Implement snapshot isolation for reporting workloads
Rationale: Snapshot isolation provides row-versioning that allows reporting
queries to read a consistent snapshot of the data without acquiring shared locks,
thereby eliminating blocking on the payroll tables. Option A (READ
UNCOMMITTED) would allow dirty reads and break transactional consistency. C
disables logging, which is unsafe. D addresses file growth, not blocking.
Q2. Which operation would be the most effective initial step when a DBA needs to
diagnose a sudden, unexplained spike in CPU usage on a production SQL Server?
Correct Answer: Query
the sys.dm_exec_requests and sys.dm_os_wait_stats DMVs
Rationale: These dynamic management views provide real-time information about
currently executing requests, wait types, and CPU consumption, allowing the DBA
,to pinpoint the offending queries. Checking backups, logs, or rebuilding indexes
would be subsequent steps after identifying the root cause.
Q3. A city agency requires near-zero downtime and automatic failover for its
critical voter registration database across two data centers. Which high-availability
technology is most appropriate?
Correct Answer: Always On Availability Groups
Rationale: Always On Availability Groups provide enterprise-grade high
availability, automatic failover, and near real-time synchronization between
replicas. Log shipping has a delayed failover and possible data loss. Database
snapshots are read-only recovery points, not HA solutions. Export replication is
unsuitable for mission-critical scenarios.
Q4. During a performance review, a DBA notices
excessive PAGEIOLATCH_XX wait types on a heavily used reporting database.
What does this most likely indicate?
Correct Answer: Memory pressure caused by insufficient buffer cache
efficiency
Rationale: PAGEIOLATCH waits indicate that SQL Server is waiting for data
pages to be read from disk into memory, which is typically caused by insufficient
memory allocation or poor cache performance. CXPACKET waits would indicate
CPU parallelism issues, and network latency would produce different wait types.
Q5. A government auditing policy requires that database administrators cannot
directly read sensitive citizen financial records while still maintaining system
administration capabilities. Which principle should be implemented?
Correct Answer: Principle of least privilege with role separation
Rationale: Role separation ensures administrators have only the permissions
necessary to perform operational duties, without unrestricted access to confidential
, data. Transparent Data Encryption protects data at rest but does not prevent
authorized reads. Shared accounts violate accountability requirements.
Q6. A DBA plans to rebuild fragmented indexes nightly on a transactional
database. Which potential issue should be MOST carefully considered?
Correct Answer: Temporary locking and resource utilization during
rebuild operations
Rationale: Index rebuilds can consume CPU, memory, and I/O resources while
potentially causing locking depending on the SQL Server edition and
configuration. This can impact production workloads if not scheduled during
maintenance windows. The other options are unrelated to the rebuild process.
Q7. An enterprise database experiences deadlocks between inventory update
transactions and reporting queries. Which tool would BEST help identify the
deadlock participants and execution sequence?
Correct Answer: Extended Events deadlock graph
Rationale: Extended Events provide detailed deadlock graphs showing the
processes, resources, and query interactions involved. SQL Server Agent schedules
jobs but does not diagnose deadlocks. DBCC CHECKDB checks integrity, and
Event Viewer lacks granular deadlock analysis.
Q8. A database file continues growing rapidly despite frequent data deletion.
Which explanation is MOST accurate?
Correct Answer: Free space inside the database file is reusable but not
automatically returned to the operating system
Rationale: SQL Server reuses freed internal space but does not automatically
shrink physical files after deletions. AUTOSHRINK is generally disabled or
discouraged. Deleted data does not immediately reduce the physical file size, and
transaction logs do not permanently prevent file reduction.
