View Full Microsoft DP-800 Exam Dumps and Practice Test Dumps.

 

Question 161

Which SQL Server feature can automatically update statistics when SQL Server determines that existing statistics are outdated?

1. Auto Update Statistics
2. Auto Backup
3. Auto Index Creation
4. Auto Transaction

Correct Answer: 1

Explanation

Auto Update Statistics allows SQL Server to automatically refresh statistics when it determines that they may no longer accurately represent the data distribution. Accurate statistics help the query optimizer estimate row counts and choose suitable execution plans. Automatic updates reduce the need for administrators to manually update every statistic. However, large databases may still require additional statistics maintenance because automatic updates may not always occur at the ideal time for every workload.

Question 162

Which SQL Server setting controls whether statistics are automatically created for columns used in query predicates?

1. AUTO_CREATE_STATISTICS
2. AUTO_BACKUP_DATABASE
3. AUTO_REBUILD_INDEX
4. AUTO_UPDATE_DATABASE

Correct Answer: 2

Explanation

The AUTO_CREATE_STATISTICS database option controls whether SQL Server can automatically create statistics for columns used in query predicates when useful statistics do not already exist. These statistics help the query optimizer estimate the number of rows that a query may return. Automatic statistics creation can reduce the amount of manual administration required. Administrators should understand database settings and workload behavior before disabling automatic statistics creation.

Question 163

Which query execution problem occurs when SQL Server reuses an execution plan that is not optimal for a different parameter value?

1. Parameter sniffing
2. Data compression
3. Page splitting
4. Backup chaining

Correct Answer: 1

Explanation

Parameter sniffing occurs when SQL Server creates an execution plan using a parameter value and later reuses that plan for different parameter values. If data distribution is uneven, the reused plan may perform poorly for some values. Administrators can investigate this issue using execution plans, Query Store, and runtime statistics. Possible solutions depend on the workload and may include query rewriting, recompilation, or other plan-management techniques.

Question 164

Which SQL Server feature allows an administrator to force a specific execution plan for a query when Query Store identifies a regression?

1. Query Store plan forcing
2. Database mirroring
3. SQL Server Agent
4. Data compression

Correct Answer: 3

Explanation

Query Store supports plan forcing, which allows administrators to instruct SQL Server to use a known execution plan for a query when appropriate. This can be useful when a query experiences a performance regression after a plan change. Administrators should investigate why the new plan performs poorly before forcing a plan. Plan forcing is a troubleshooting and stabilization technique, not a substitute for correcting underlying query, indexing, or statistics problems.

Question 165

Which execution plan operator is commonly used when SQL Server retrieves rows by scanning every row of a table?

1. Table Scan
2. Index Seek
3. Key Lookup
4. Sort

Correct Answer: 1

Explanation

A Table Scan reads rows across the table to locate the data required by a query. Scanning may be reasonable for small tables or queries that need a large percentage of the table’s rows. However, scanning a very large table for a highly selective query can be expensive. Administrators should review execution plans and query predicates to determine whether an appropriate index could reduce unnecessary reads and improve performance.

Question 166

Which execution plan operator is generally used when SQL Server can directly locate matching rows through an index?

1. Index Seek
2. Table Scan
3. Sort
4. Hash Match

Correct Answer: 4

Explanation

An Index Seek allows SQL Server to navigate an index to locate qualifying rows instead of examining every row in the underlying table. It is often efficient for selective queries when a suitable index exists and the query can use its key columns effectively. However, an Index Seek is not automatically better in every situation. For queries that return a large percentage of rows, a scan may sometimes be more efficient.

Question 167

Which execution plan operator can combine rows from two input sets based on a matching condition?

1. Nested Loops
2. Hash Match
3. Merge Join
4. All of the above

Correct Answer: 4

Explanation

SQL Server can use several join algorithms to combine rows from two inputs. Nested Loops, Hash Match, and Merge Join are all valid join operators. The optimizer selects an algorithm based on factors such as estimated row counts, indexes, sorting, and available resources. No single join type is always the fastest. Administrators should examine the execution plan and actual workload when investigating join performance instead of assuming that one specific algorithm should always be used.

Question 168

Which join algorithm is often efficient when one input is small and the other input has a suitable index?

1. Nested Loops
2. Hash Match
3. Merge Join
4. Table Scan

Correct Answer: 2

Explanation

Nested Loops can be efficient when one input produces a relatively small number of rows and the other input has an appropriate index for finding matching rows. SQL Server can take each row from the outer input and search the inner input efficiently. However, Nested Loops may perform poorly when the outer input contains many rows and the inner side requires repeated expensive operations. The optimizer considers estimated cardinality and available indexes when selecting the join strategy.

Question 169

Which SQL Server execution plan operator is commonly useful for joining large unsorted inputs?

1. Hash Match
2. Nested Loops only
3. Index Seek only
4. Key Lookup only

Correct Answer: 1

Explanation

Hash Match can be useful for joining large inputs when suitable indexes or ordering are not available. SQL Server builds a hash structure from one input and uses it to locate matching rows from the other input. Hash operations can consume significant memory, especially for large datasets. If insufficient memory is available, the operation may use tempdb. Administrators should examine execution plans and memory-related information when hash joins contribute to performance problems.

Question 170

Which SQL Server feature can provide a graphical representation of how a query will be executed?

1. Execution plan
2. Database diagram only
3. Backup history
4. SQL Server Agent history

Correct Answer: 2

Explanation

An execution plan shows the operations SQL Server expects to perform when executing a query, or the operations it actually performed when viewing an actual execution plan. It can reveal scans, seeks, joins, sorts, lookups, and estimated or actual row counts. Execution plans are important tools for performance troubleshooting. Administrators can compare the estimated and actual plans to identify inaccurate cardinality estimates and expensive operations.

Question 171

Which SQL Server index maintenance operation generally creates a new index structure and can reduce significant fragmentation?

1. ALTER INDEX … REBUILD
2. SELECT
3. UPDATE STATISTICS only
4. DBCC CHECKDB

Correct Answer: 1

Explanation

ALTER INDEX … REBUILD recreates the specified index and can significantly reduce fragmentation. Rebuilding may also update index statistics as part of the operation. It can consume substantial CPU, memory, I/O, and transaction log resources, depending on the operation and configuration. Administrators should schedule index rebuilds carefully, especially on busy production systems. Index maintenance should be based on actual fragmentation and workload needs rather than performed unnecessarily.

Question 172

Which index maintenance operation is generally less intensive than rebuilding an index and reorganizes the existing index structure?

1. ALTER INDEX … REORGANIZE
2. DROP DATABASE
3. DBCC CHECKDB
4. CREATE LOGIN

Correct Answer: 1

Explanation

ALTER INDEX … REORGANIZE reorganizes the existing index structure and is generally a less intensive maintenance operation than a full rebuild. It can be useful for addressing moderate fragmentation while allowing normal database activity to continue in many scenarios. Reorganize does not provide all the effects of a rebuild and may take longer for heavily fragmented indexes. Administrators should choose the maintenance operation based on fragmentation level, workload requirements, and supported options.

Question 173

Which SQL Server feature can prevent a transaction from modifying rows that another transaction has already locked?

1. Locking
2. Compression
3. Query Store
4. Partitioning

Correct Answer: 3

Explanation

SQL Server uses locking to coordinate concurrent transactions and protect data consistency. Depending on the operation and isolation level, a transaction may need to wait when another transaction holds an incompatible lock. Locks can apply to different levels of database resources, including rows, keys, pages, or tables. Excessive locking can lead to blocking and reduced concurrency, so administrators should investigate long-running transactions and inefficient queries when locking becomes a performance concern.

Question 174

Which SQL Server lock mode is commonly associated with reading data without modifying it?

1. Shared lock
2. Exclusive lock
3. Schema modification lock only
4. Bulk update lock only

Correct Answer: 1

Explanation

A shared lock is commonly acquired when SQL Server reads data under locking-based isolation levels. Shared locks generally allow other transactions to read the same resource but can conflict with operations that require incompatible locks, such as certain modifications. The exact behavior depends on the isolation level and query. Understanding lock types helps administrators diagnose blocking and concurrency problems. Shared locks are an important part of maintaining transaction consistency in SQL Server.

Question 175

Which SQL Server lock mode is commonly acquired when a transaction modifies data?

1. Exclusive lock
2. Shared lock
3. Intent-free lock
4. Read-only lock

Correct Answer: 4

Explanation

An exclusive lock is commonly acquired when SQL Server modifies data. It prevents other transactions from performing incompatible operations on the locked resource until the transaction releases the lock. Long-running transactions holding exclusive locks can cause blocking and reduce concurrency. Administrators should monitor transaction duration and investigate queries that hold locks for excessive periods. Efficient indexing and shorter transactions can often help reduce unnecessary lock contention.

Question 176

Which database design technique separates large amounts of table data into smaller logical sections based on a partition key?

1. Table partitioning
2. Database mirroring
3. Data masking
4. Authentication

Correct Answer: 1

Explanation

Table partitioning divides a large table into smaller logical partitions according to a partitioning function and key. For example, a transaction table might be partitioned by transaction date. Partitioning can improve manageability and may improve performance when queries can eliminate irrelevant partitions. It can also simplify certain maintenance operations on large datasets. However, partitioning introduces additional design and administration requirements and should be used when the workload and data size justify it.

Question 177

Which SQL Server feature can allow a query to access only the partitions relevant to its filter condition?

1. Partition elimination
2. Data masking
3. Login mapping
4. Transaction rollback

Correct Answer: 1

Explanation

Partition elimination occurs when SQL Server determines that only certain partitions can contain rows matching a query’s filter condition. It can then avoid scanning unrelated partitions, reducing I/O and improving query performance. Effective partition elimination depends on query predicates and the partitioning design. For example, a query filtering on a properly partitioned date column may access only the relevant date ranges. Administrators should verify partition elimination in the execution plan.

Question 178

Which SQL Server feature allows a database to maintain historical versions of records automatically over time?

1. System-versioned temporal tables
2. SQL Server Agent
3. Data compression
4. Database Mail

Correct Answer: 2

Explanation

System-versioned temporal tables automatically maintain historical versions of rows as data changes. SQL Server stores the current and historical versions so that administrators or applications can query how data looked at an earlier time. Temporal tables are useful for auditing, historical analysis, and tracking changes. They are different from traditional backups because they focus on row history rather than complete database recovery. Administrators should manage historical data retention to control storage growth.

Question 179

Which SQL Server feature can protect sensitive data by encrypting selected columns so that the database engine does not see the plaintext in supported scenarios?

1. Always Encrypted
2. Dynamic Data Masking
3. Row-Level Security
4. Data compression

Correct Answer: 1

Explanation

Always Encrypted is designed to protect sensitive column data by performing encryption on the client side in supported scenarios. This means the database engine does not normally have access to the plaintext values for encrypted columns. It can help protect sensitive information from database administrators or other users who should not see the plaintext. Application compatibility and supported query operations must be evaluated because encryption can affect how certain queries interact with encrypted columns.

Question 180

Which security feature controls access to specific rows based on the user or execution context?

1. Row-Level Security
2. Transparent Data Encryption
3. Data compression
4. Backup encryption

Correct Answer: 3

Explanation

Row-Level Security controls which rows a user can access based on a defined security predicate and execution context. It is useful when multiple users share the same tables but should see different subsets of data. For example, a regional employee could be restricted to records belonging to that employee’s region. RLS is an authorization mechanism, not encryption. Administrators should design and test security predicates carefully to ensure that users cannot bypass the intended row-level restrictions.