CCNA Data Systems Management Questions

Collation governs how character data is sorted, compared and stored at the column and database level. Changing it alters those stored comparison rules, so existing data must be rewritten. SQL Server therefore rebuilds the database or recreates the affected tables to apply the new collation.

Separating data, log, and tempdb files onto distinct physical volumes reduces I/O contention and improves performance. The log volume should be dedicated because log writes are sequential and critical for durability. Placing tempdb on its own volume prevents heavy tempdb usage from competing with user database I/O. This matches the stated requirement to isolate data, log, and tempdb.

`pg_stat_activity` exposes one row per server process, including `state`, `query_start`, and the currently executing `query` text, so it reveals which sessions are actively running long queries. This directly satisfies the requirement to inspect execution statistics across all active sessions, unlike cumulative `pg_stat_statements` aggregates.

Read-only replicas copy data to additional nodes that serve SELECT traffic, spreading read load away from the primary. This parallelises read capacity and reduces contention on the primary, directly addressing the read-heavy workload constraint in the scenario.

A copy-only backup captures the database without resetting the differential base, so it does not disturb the existing full and differential backup chain. This allows an ad-hoc backup in a 24/7 environment while preserving the scheduled sequence's integrity.

Low page density means index pages contain excessive free space from splits and deletes, forcing more pages to be read. This wasted space is a direct structural symptom of fragmentation, satisfying the question's requirement for an indicator of significant index fragmentation.

sys.dm_exec_requests provides information about currently executing requests, including blocking_session_id and the SQL handle. Joining with sys.dm_exec_sql_text retrieves the actual SQL text for each request, allowing the DBA to identify the blocking session and its query. This is the standard method to diagnose blocking chains in SQL Server.

Striped backups split the backup set across multiple files, letting SQL Server read them in parallel during restore. This directly addresses the stem's constraint: restoration of a large database takes too long. Parallel I/O streams reduce elapsed restore time proportionally to the number of stripes, unlike single-file backups, which remain bound by one device's throughput.

OPTION (RECOMPILE) forces a new plan to be generated for each execution, allowing the optimizer to use the actual parameter values and choose an optimal plan per execution. This mitigates parameter sniffing by avoiding reuse of a plan optimized for a different parameter value, while still allowing seeks or scans as appropriate.

RPO defines the maximum tolerable data loss measured in time. Transaction log backups every 10 minutes capture changes more frequently than the 15-minute RPO, so at most 10 minutes of data is lost, meeting the requirement.

When the transaction log cannot grow to accommodate incoming transactions, SQL Server raises error 9002, the transaction log full error, and may suspend further writes. This symptom directly reflects the undersized log constraint in the stem, distinguishing it from data file growth issues.

Extended Events captures live query execution plans with minimal overhead, satisfying the need to inspect currently running queries on the CPU-bound server. Unlike deprecated SQL Trace, its lightweight, event-based architecture lets you target plan-related events such as query_post_execution_showplan, revealing full table scans without materially worsening the existing CPU pressure.

In FULL recovery model, restoring a differential backup requires the base full backup, then the differential, then all transaction log backups taken after the differential to reach the point of failure. This sequence preserves the log chain and allows point-in-time recovery to 10:07 AM.

When the parameter's data type differs from the column's, the optimiser applies an implicit conversion to the column side, making the predicate non-sargable and forcing a scan instead of an index seek. This satisfies the execution plan's implicit conversion warning.

An index maintains a separate ordered structure pointing to table rows, letting the database locate matching records without scanning the whole table. This directly reduces the I/O needed for retrieval, which is its primary function.

A clustered columnstore index stores data in a columnar format, which is highly efficient for aggregation queries that scan large amounts of data. It provides high compression and batch-mode execution, significantly speeding up queries that group by product_category and sum sales. It eliminates the need for full table scans by reading only relevant columns.

ALTER TABLE ... SHRINK SPACE reclaims fragmented space in-place, moving rows downward and resetting the high-water mark without dropping the table. It satisfies the stem's requirement to defragment while preserving the table structure, unlike MOVE (which relocates segments) or export/import. Row movement must be enabled first.

SHOW ENGINE INNODB STATUS returns the LATEST DETECTED DEADLOCK section, listing each transaction's held and waiting InnoDB row and table locks plus the SQL involved. This gives the internal lock detail needed to identify the conflicting access pattern.

With FULL recovery, transaction log backups every 15 minutes limit data loss to at most 15 minutes, satisfying the RPO. Weekly full backups plus daily differentials keep restore time manageable, and the log chain provides point-in-time recovery. The RTO of 1 hour is realistic because restoring one full, one differential, and a handful of log backups is far faster than replaying a week of logs from a single full backup.

Updating statistics is a standard maintenance task that refreshes the query optimiser's cardinality estimates, satisfying the stem's requirement. It runs alongside index rebuilds and backups, preventing poor execution plans caused by stale distribution data after significant data modification.

Mirroring requires the log chain.

Transaction log backups capture every committed transaction since the last log backup, so combining them with periodic full backups lets you replay logs to a precise point in time. Full backups alone restore only to the last backup, and differential backups restore only to the differential's creation, so neither meets the point-in-time requirement.

An AFTER trigger that fires on INSERT, UPDATE, and DELETE can capture all changes and write the user, timestamp, and old and new values to an audit table. AFTER triggers run after the row is modified, so they can access both OLD and NEW values. This requires no application code changes and provides a complete audit trail as required by compliance.

Off-site or cloud backup storage places copies geographically apart from the primary database, so a site-level disaster cannot destroy both production data and its backups. This separation satisfies the stem's requirement for an essential component of a robust disaster recovery plan.

Moving the transaction log to a dedicated high-performance drive directly addresses the I/O wait bottleneck by isolating sequential log writes from competing data-file reads. This separation reduces disk contention and lowers write latency, satisfying the stem's requirement to alleviate log-drive I/O pressure most effectively.

This command is used to shrink a specific file, such as a transaction log.

Disk latency directly exposes storage-bound bottlenecks: high I/O wait means queries stall waiting on physical reads rather than CPU or network. Monitoring it satisfies the stem's requirement to identify performance bottlenecks, since slow disk throughput commonly throttles database operations before other resources saturate.

Including the actual execution plan captures the plan after execution, showing the operators actually used, including scans, seeks, and their properties. This directly confirms whether the index is being scanned or seeked for the report query, matching the need to verify the operator used.

Trace flag 1118 forces uniform extent allocations, reducing contention on SGAM pages for tempdb. In SQL Server 2016 and later, this behavior is enabled by default for tempdb, but on older versions or when explicitly disabled, enabling it improves performance. It complements multiple data files by further reducing allocation bottlenecks. This is a recognized tempdb optimization.

A foreign key column references the primary key of a related table, so the database engine rejects inserts or updates that would leave an orphaned row. This directly enforces referential integrity between the two tables, unlike a check constraint or unique index.

A full backup copies the entire database, providing a complete standalone restore point and resetting the differential and transaction log chain. It satisfies the stem's requirement as one of the three standard backup types, alongside differential and transaction log backups.

Replication and failover between nodes depend on timely heartbeat and synchronisation traffic, so high network latency delays detection of failure and risks stale reads. This directly satisfies the high-availability requirement by ensuring rapid, consistent failover.

Row-level security enforces predicates inside the database engine, filtering rows by the executing user's identity regardless of the query submitted. This satisfies the requirement to prevent unauthorised access to sensitive tables, because Microsoft Entra ID-backed users only ever see rows matching their granted predicate, even through ad-hoc queries or reporting tools.

An expression index on the converted timestamp allows the database to use an index when queries filter on local time. Without it, the conversion would prevent index usage, causing full scans. This approach maintains the UTC storage while enabling efficient queries for each region by creating indexes tailored to their time zone.

TDE encrypts data and log files at rest, and using an EKM provider allows the asymmetric key that protects the Database Encryption Key (DEK) to be stored in an HSM. This satisfies both encryption at rest and HSM key management. TDE with EKM is designed for regulatory compliance scenarios requiring hardware-based key protection. The administrator must configure the EKM provider and create a credential for the HSM.

Range partitioning on order_date allows you to define partitions for specific date ranges, such as monthly or yearly. This enables efficient archiving by switching out old partitions, which is a metadata operation. It also improves query performance through partition elimination when filtering on order_date, directly addressing the slow queries.

The full recovery model allows transaction log backups, enabling point-in-time recovery and minimizing data loss. With log backups every 10 minutes, the maximum data loss is 10 minutes, which satisfies the 15-minute requirement. This model is standard for OLTP databases where data loss must be minimized. It supports all restore options, including restoring to a specific point in time.

SQL Server Management Studio delivers the graphical console the stem requires, exposing Object Explorer for instance configuration, login and role security management, and the Maintenance Plan Wizard for scheduled backups and index rebuilds. It satisfies the demand for a single GUI tool covering both security configuration and maintenance plan creation.

Transparent data encryption protects the database encryption key with a certificate stored in the master database. Restoring the backup elsewhere requires that certificate and its private key, otherwise the database encryption key cannot be decrypted and the restore fails.

A full backup provides the baseline for recovery and is required to start the log chain in the full recovery model. It captures the entire database and is essential for restoring the database to a known point. Without a full backup, transaction log backups cannot be applied. It also serves as the foundation for differential backups.

Range partitioning on the timestamp aligns partition boundaries with the narrow time-window predicates, so the PostgreSQL planner can exclude nonmatching partitions during execution. Monthly partitions keep each relation small enough for efficient indexing and vacuuming, and the default partition catches rows that fall outside declared ranges, preventing insert failures when new periods are not yet created.

SQL Server Agent Maintenance Plans provide built-in tasks, including Update Statistics, that run across every user database without custom scripting. This satisfies the requirement to automate statistics maintenance centrally, unlike ad hoc T-SQL jobs or manual commands, and is the documented standard mechanism for scheduled administrative upkeep in SQL Server.

Dropping a large table is logged row by row, so the transaction log must record every deleted row before commit. This unbounded log growth can exhaust disk space or stall the operation, directly matching the risk the stem asks about.

This strategy meets the RPO because transaction log backups every 15 minutes limit data loss to at most 15 minutes. The full and differential backups reduce restore time, helping meet the 1-hour RTO. The full recovery model is required for transaction log backups, so changing from simple to full is essential for point-in-time recovery.

Columnstore indexes store data in a columnar format, which provides high compression and improves performance for large scans and analytical queries. They are ideal for read-only or infrequently updated historical data. This feature reduces storage space and speeds up queries that scan large volumes of data, meeting the administrator's goals.

Configuring Readable Secondary to "Read-intent only" or "Yes" lets the secondary replica accept read-only connections, satisfying the reporting requirement. Without it, the secondary remains non-readable regardless of the availability mode, so the DBA must set this replica-level option explicitly.

TRUNCATE TABLE is a minimally logged bulk operation: it deallocates the data pages instead of deleting rows one at a time, so per-row DELETE triggers do not fire and the log does not contain individual row images. In a production table with millions of rows, this makes the operation fast but also means you cannot undo it selectively; recovery depends on the surrounding transaction or backup strategy. This is the key behavioral fact a junior DBA must grasp.

The OPTION (RECOMPILE) hint forces the query optimizer to generate a new plan for each execution, using the current parameter values. This eliminates the parameter sniffing issue for that query without affecting other queries. It is a targeted solution that addresses the specific problematic query while leaving the rest of the workload unaffected. The trade-off is a slight increase in CPU for compilation.

A server-level audit specification can capture login events, including successful and failed logins, and write them to a target such as the Windows Security log or a file. This is the standard method for auditing authentication events. It is designed to be lightweight and can be configured to capture only the necessary actions, minimizing overhead.

This PowerShell command or the Failover Cluster Manager GUI triggers the manual transition.

Indirect checkpoints allow you to specify a target recovery time at the database level. By enabling indirect checkpoints and setting a low target recovery interval, SQL Server flushes dirty pages more aggressively in the background, limiting the number of pages that must be recovered after a crash. This directly reduces the 45-minute recovery window without sacrificing the FULL recovery model or point-in-time restore capability.

The --no-data flag suppresses row retrieval, dumping only the CREATE TABLE statements that define the schema. This satisfies the schema-only export constraint directly, unlike --no-create-info, which does the reverse, or a full dump that includes all table data.

max server memory caps the buffer pool and other instance allocations, preventing SQL Server from consuming all available OS memory. Setting it bounds instance consumption so the operating system retains memory for its own processes.