Prepare and test your skills

Prepare and test your skills

Free sample question with answers and detailed explanation below. The Professional Cloud Database Engineer practice bank has an additional 769 practice questions.Try 10 free realistic questions: instant score, no credit card, and no account required.
Your team manages a multi-database workload using Cloud SQL for SQL Server and Spanner. After heavy updates and bulk deletions, query latency increases on both systems. Diagnostics show:
sys.dm_db_index_physical_stats): PK_Stock is at 95.5% avg_fragmentation_in_percent, IX_Warehouse is at 0.8%, and IX_OrderLine is at 0.01%. Disk utilization is 70%.Which maintenance strategy should you implement to optimize query execution and storage across both databases?
DBCC CHECKDB is a SQL Server integrity check command that validates the logical and physical consistency of all objects in a database. A NULL_FILTERED secondary index in Spanner is a sparse index that excludes rows where all indexed key columns contain NULL values.
DBCC CHECKDB detects database corruption rather than defragmenting indexes. Because it is highly resource-intensive, running it on a production Cloud SQL instance degrades live query performance; best practices recommend running DBCC CHECKDB on a cloned database or a restored backup.NULL_FILTERED indexes: While a NULL_FILTERED index saves storage when indexing sparse columns, it does not bypass deleted row versions generated by bulk deletions on existing tables or indexes. Adding another index also increases write overhead and storage consumption without compacting historical row versions.DBCC CHECKDB validates page structures and physical integrity but never reorganizes fragmented index leaf pages.You suspected data corruption on a non-production clone of SQL Server and needed to reduce storage costs for a sparse subset of non-null rows in Spanner rather than removing deleted row versions.
This strategy pairs targeted B-tree index defragmentation in Cloud SQL for SQL Server with manual data compaction in Spanner. In SQL Server, heavy updates cause logical and physical page splits that degrade index scan performance. In Spanner, deleted or overwritten row versions remain in storage until the Point-in-Time Recovery (PITR) retention window passes and background compaction removes them, which can take up to seven days.
PK_Stock has 95.5% fragmentation, rebuilding (or reorganizing) it restores contiguous page ordering and boosts I/O efficiency. Current 70% disk utilization leaves 30% free space, exceeding the 20% minimum available space recommended for critical Cloud SQL maintenance.IX_Warehouse (0.8%) and IX_OrderLine (0.01%) prevents wasted CPU, I/O, and transaction log growth on healthy indexes.This approach aligns with the underlying storage mechanics of both platforms: SQL Server requires index defragmentation and fill factor tuning for mutable B-trees, whereas Spanner relies on compaction rather than index rebuilds to reclaim space from deleted rows.
This option combines PostgreSQL maintenance commands (VACUUM ANALYZE and REINDEX) with Spanner optimizer statistics management. In PostgreSQL, VACUUM reclaims dead tuples created by Multi-Version Concurrency Control (MVCC), and REINDEX rebuilds bloated indexes. In Spanner, manually constructing a statistics package updates cardinality and distribution metadata used by the query optimizer.
VACUUM ANALYZE and REINDEX are invalid commands in Cloud SQL for SQL Server, which instead uses ALTER INDEX ... REBUILD or REORGANIZE and UPDATE STATISTICS to resolve fragmentation and refresh query plans.VACUUM and REINDEX apply exclusively to PostgreSQL and AlloyDB for PostgreSQL storage engines.The relational database were Cloud SQL for PostgreSQL suffering from MVCC dead-tuple bloat, and the Spanner slowdown were caused by outdated optimizer statistics selecting a suboptimal index rather than scanning uncompacted deleted rows.
This approach applies ALTER INDEX ... REORGANIZE across every table index in Cloud SQL for SQL Server, regardless of fragmentation percentage, and attempts to run a SQL Server-style ALTER INDEX ... REBUILD statement against a Spanner secondary index.
REBUILD or REORGANIZE commands on indexes because its distributed storage architecture uses splits and immutable blocks rather than mutable on-disk B-tree pages. Running ALTER INDEX in Spanner is limited to schema operations such as adding or dropping STORING columns.IX_Warehouse (0.8%) and IX_OrderLine (0.01%) provides virtually no query speedup while consuming unnecessary IOPS, CPU, and transaction log capacity.PK_Stock, a lightweight online reorganize is typically slower and less effective at restoring contiguous page order than a full index rebuild paired with an appropriate fill factor.ALTER INDEX ... REORGANIZE in SQL Server only defragments the leaf level of an index in place and does not update statistics automatically.You were managing only a SQL Server instance where all listed indexes showed moderate fragmentation (such as between 5% and 30%) and Spanner was not experiencing post-deletion compaction lag.
Answer them yourself and get your score the moment you finish. No account needed.
Full access includes
Keep the momentum going with these hand-picked practice scenarios
Last updated Validated