70-465 · Question #56
You need to recommend a solution that reduces the time it takes to import the supplier data. What should you include in the recommendation?
The correct answer is C. Disable Resource Governor. When importing large amounts of data into SQL Server, Resource Governor can throttle CPU and memory resources, slowing the import process. Disabling it removes those constraints and allows the import to use full available resources.
Question
Options
- AEnable instant file initialization.
- BReorganize the indexes.
- CDisable Resource Governor.
- DEnable Auto Update Statistics.
How the community answered
(22 responses)- A9% (2)
- B14% (3)
- C73% (16)
- D5% (1)
Why each option
When importing large amounts of data into SQL Server, Resource Governor can throttle CPU and memory resources, slowing the import process. Disabling it removes those constraints and allows the import to use full available resources.
Instant File Initialization speeds up data file growth events by skipping zero-initialization of pages, but it primarily helps with auto-growth events and initial file creation, not the throughput of bulk data import operations themselves.
Reorganizing indexes improves query read performance by reducing fragmentation, but during a bulk import it can actually increase overhead since indexes must be maintained as rows are inserted; it does not directly reduce import time.
Resource Governor is a SQL Server feature that limits CPU, memory, and I/O resources for specific workloads or sessions. During a large data import operation, if Resource Governor is configured with restrictive resource pools or workload groups, it can artificially throttle the import process. Disabling Resource Governor removes these resource limits, allowing the import job to consume the maximum available server resources and complete faster.
Auto Update Statistics causes SQL Server to automatically refresh statistics when data changes, which adds background overhead during large data loads rather than reducing import time, making it counterproductive for speeding up an import operation.
Concept tested: Resource Governor impact on bulk data import performance
Source: https://learn.microsoft.com/en-us/sql/relational-databases/resource-governor/resource-governor
Topics
Community Discussion
No community discussion yet for this question.