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

 

Question 261

Which SQL Server feature can limit CPU, memory, or other resources available to specific workloads?

1. Resource Governor
2. Query Store
3. Database Mail
4. SQL Server Browser

Correct Answer: 1

Explanation

Resource Governor allows administrators to control and manage SQL Server resource consumption for different workloads. Administrators can classify incoming connections and place them into workload groups with specific resource controls. This can help prevent one workload from consuming excessive resources and affecting other applications. Resource Governor is especially useful on shared SQL Server instances where multiple applications or workloads compete for CPU and memory. Administrators should design classifications carefully to avoid unexpected workload behavior.

Question 262

Which SQL Server feature can capture detailed information about server and database events with low overhead compared with older tracing methods?

1. Extended Events
2. Database Mail
3. SQL Server Browser
4. Replication

Correct Answer: 1

Explanation

Extended Events is a lightweight event-handling system used to monitor and troubleshoot SQL Server activity. Administrators can create sessions to capture events such as deadlocks, long-running queries, errors, waits, and other useful information. Extended Events is generally preferred over SQL Server Profiler and SQL Trace for modern monitoring scenarios because it provides flexible event collection with relatively low overhead. Administrators should capture only the events and data required for the troubleshooting task.

Question 263

Which SQL Server feature is specifically useful for identifying deadlock events?

1. Database Mail
2. Extended Events
3. Data compression
4. Database snapshots

Correct Answer: 2

Explanation

Extended Events can capture deadlock events and provide detailed information about the sessions and resources involved. A deadlock occurs when two or more transactions wait for resources held by one another, preventing them from continuing. SQL Server normally detects the deadlock and chooses one transaction as the victim. Administrators can analyze the captured deadlock information to identify conflicting queries, indexes, or transaction patterns and then modify the workload to reduce future deadlocks.

Question 264

Which dynamic management view can help identify currently executing SQL requests?

1. sys.dm_exec_requests
2. sys.tables
3. sys.columns
4. sys.database_principals

Correct Answer: 1

Explanation

sys.dm_exec_requests provides information about requests that are currently executing on SQL Server. Administrators can use it to investigate active sessions, wait information, CPU usage, elapsed time, blocking, and other request details. It is particularly useful when troubleshooting a query that appears to be running slowly or when users report that the database is not responding normally. Administrators often combine this DMV with other dynamic management views for deeper investigation.

Question 265

Which dynamic management view can help identify currently active sessions and their connection information?

1. sys.dm_exec_sessions
2. sys.tables
3. sys.indexes
4. sys.database_files

Correct Answer: 1

Explanation

sys.dm_exec_sessions provides information about sessions connected to the SQL Server instance. Administrators can review session IDs, login names, host information, session status, and other connection-related details. This information can be useful when investigating active users, application connections, blocking, or unusual session activity. It can also be joined with other DMVs, such as sys.dm_exec_requests, to obtain a more complete picture of currently running database activity.

Question 266

Which dynamic management view can provide information about indexes and their usage statistics?

1. sys.dm_db_index_usage_stats
2. sys.dm_exec_sessions
3. sys.tables
4. sys.database_principals

Correct Answer: 3

Explanation

sys.dm_db_index_usage_stats provides information about how indexes are being used by database operations. Administrators can review statistics such as seeks, scans, lookups, and updates to identify indexes that are frequently used or may provide little benefit. This information can support index optimization decisions. However, administrators should not immediately delete an apparently unused index because usage statistics can reset after certain events, such as a SQL Server restart or database detachment.

Question 267

Which SQL Server command can be used to view configuration options and their current values?

1. sp_configure
2. sp_who2
3. sp_helptext
4. DBCC CHECKDB

Correct Answer: 1

Explanation

sp_configure displays and changes many SQL Server server-level configuration options. Administrators can use it to review settings such as maximum server memory and other advanced configuration options. Some configuration changes require additional steps before they take effect, depending on the option. Administrators should understand the purpose and impact of a configuration setting before changing it because inappropriate instance-level settings can affect performance, security, or application behavior.

Question 268

Which SQL Server configuration setting controls the maximum amount of memory that the SQL Server instance can use?

1. max server memory
2. min server memory
3. max degree of parallelism only
4. remote query timeout

Correct Answer: 4

Explanation

The max server memory setting controls the upper amount of memory SQL Server can use for its memory allocation. Setting an appropriate limit is important when SQL Server shares a server with other applications or operating system processes. If configured too high, SQL Server may leave insufficient memory for the operating system. If configured too low, SQL Server may not have enough memory for efficient database operations. Administrators should size this setting according to the server’s overall workload.

Question 269

Which SQL Server setting controls the maximum number of processors that a parallel query can use for a parallel execution plan?

1. MAXDOP
2. max server memory
3. remote access
4. backup compression default

Correct Answer: 1

Explanation

MAXDOP, or maximum degree of parallelism, controls the maximum number of processors that SQL Server can use for a parallel query execution plan, subject to other factors and configuration. Parallelism can improve the performance of large queries, but excessive parallelism can consume significant CPU resources and affect other workloads. Administrators should configure MAXDOP based on workload characteristics and Microsoft’s guidance for the SQL Server environment rather than choosing an arbitrary value.

Question 270

Which query hint can instruct SQL Server not to wait for locks and instead read uncommitted data?

1. NOLOCK
2. HOLDLOCK
3. UPDLOCK
4. TABLOCKX

Correct Answer: 1

Explanation

The NOLOCK table hint allows a query to read data without acquiring normal shared locks, effectively using READ UNCOMMITTED behavior for that table. This can reduce blocking but may result in dirty reads, missing rows, duplicate rows, or other inconsistent results. Therefore, NOLOCK should not be used simply as a general performance fix. Administrators should first understand the cause of blocking and consider safer concurrency approaches when data consistency is important.

Question 271

Which lock mode prevents other transactions from modifying a resource while the transaction holds the lock?

1. Shared lock
2. Exclusive lock
3. Intent shared lock
4. Schema stability lock

Correct Answer: 2

Explanation

An exclusive lock is used when a transaction modifies data. It prevents other transactions from acquiring conflicting locks that would allow them to modify the same resource simultaneously. Exclusive locks help maintain data consistency during INSERT, UPDATE, and DELETE operations. If held for a long time, they can contribute to blocking. Administrators should investigate long-running transactions and inefficient queries when excessive blocking caused by exclusive locks affects application performance.

Question 272

Which lock type is commonly acquired when a transaction reads data under locking-based isolation?

1. Exclusive lock
2. Shared lock
3. Bulk update lock
4. Key-range lock only

Correct Answer: 2

Explanation

A shared lock is commonly acquired when SQL Server reads data under locking-based isolation levels. Multiple transactions can generally hold compatible shared locks on the same resource, allowing concurrent reads. However, shared locks can conflict with exclusive locks required for modifications. The duration of shared locks depends on the isolation level and query behavior. Administrators should understand locking behavior when investigating blocking and concurrency problems in transactional workloads.

Question 273

What is blocking in SQL Server?

1. A query failing because a table does not exist
2. One session waiting because another session holds a conflicting lock
3. A database automatically backing up
4. An index being rebuilt

Correct Answer: 2

Explanation

Blocking occurs when one session must wait because another session holds a lock that conflicts with the requested operation. Blocking is normal in many transactional systems, but excessive or long-lasting blocking can reduce application performance. Administrators can investigate blocking by examining active requests, sessions, locks, and transaction duration. The best solution depends on the cause and may involve optimizing queries, shortening transactions, improving indexes, or changing application transaction behavior.

Question 274

Which SQL Server command can display information about active sessions and processes in a familiar text-based format?

1. sp_who2
2. sp_help
3. sp_configure
4. sp_helptext

Correct Answer: 2

Explanation

sp_who2 provides information about active SQL Server sessions and processes. Administrators can use it to identify session IDs, login names, database activity, status, and blocking relationships. It is a useful quick troubleshooting command when investigating active connections or blocked sessions. For more detailed and programmatic monitoring, administrators can use dynamic management views such as sys.dm_exec_sessions and sys.dm_exec_requests.

Question 275

Which command can display the definition of a stored procedure or other programmable object?

1. sp_helptext
2. sp_who2
3. sp_configure
4. DBCC CHECKDB

Correct Answer: 1

Explanation

sp_helptext can display the text definition of supported database objects such as stored procedures, views, and functions. It is useful when administrators need to inspect existing database code during troubleshooting or maintenance. The output can help identify the queries and logic contained within an object. Administrators should remember that object definitions may be encrypted or otherwise unavailable through this procedure in certain situations.

Question 276

Which SQL Server command checks the logical and physical integrity of a database?

1. DBCC CHECKDB
2. DBCC FREEPROCCACHE
3. sp_who2
4. sp_configure

Correct Answer: 4

Explanation

DBCC CHECKDB checks the logical and physical integrity of database objects and structures. It can detect various types of corruption and consistency problems. Administrators should run integrity checks according to an appropriate maintenance and recovery strategy, especially for critical databases. If corruption is detected, administrators should investigate the underlying cause and use appropriate recovery methods. A recent verified backup is essential because repair operations can involve data loss in some circumstances.

Question 277

Which SQL Server feature allows administrators to restore a database to a specific point in time when the required log backups are available?

1. Point-in-time recovery
2. Data compression
3. Partition elimination
4. Query Store

Correct Answer: 1

Explanation

Point-in-time recovery allows administrators to restore a database to a selected time when the required full, differential, and transaction log backups are available. This is useful when an unwanted change, accidental deletion, or application error occurs and the database must be recovered to a moment before the incident. The recovery strategy depends on the recovery model and available backup chain. Administrators should regularly test restore procedures to ensure recovery objectives can be met.

Question 278

Which recovery model supports transaction log backups and point-in-time recovery?

1. Simple
2. Full
3. Read-only
4. Bulk-only

Correct Answer: 2

Explanation

The Full recovery model supports transaction log backups and point-in-time recovery. Under this model, transaction log records are retained until they are backed up or otherwise managed according to SQL Server rules. Regular transaction log backups are therefore important to maintain the log backup chain and prevent unnecessary log growth. Administrators selecting Full recovery should have a reliable log-backup schedule and should regularly verify that backups can be restored successfully.

Question 279

Which recovery model automatically truncates the transaction log during normal checkpoint processing and does not support transaction log backups?

1. Full
2. Simple
3. Bulk-logged
4. Snapshot

Correct Answer: 2

Explanation

The Simple recovery model automatically reclaims transaction log space during normal checkpoint processing when the space is no longer required. It does not support transaction log backups and therefore does not provide traditional point-in-time recovery. Simple recovery can be appropriate for databases where detailed point-in-time recovery is not required. Administrators should choose the recovery model based on business recovery requirements rather than simply selecting the model that requires less backup management.

Question 280

Which recovery model can support minimally logged operations under specific conditions while still supporting transaction log backups?

1. Simple
2. Full
3. Bulk-logged
4. Read-only

Correct Answer: 3

Explanation

The Bulk-logged recovery model supports transaction log backups while allowing certain operations to be minimally logged under appropriate conditions. This can reduce the amount of log generated by qualifying bulk operations. However, there are limitations regarding point-in-time recovery during periods when minimally logged operations are involved. Administrators should use this recovery model only when its behavior matches the workload and recovery requirements, and they should understand its effect on restore operations.