Implementing a Data Warehouse with Microsoft SQL Server 2012/2014 — Question 19

You have a server named SQL1 that has SQL Server Integration Services (SSIS) installed. SQL1 has eight CPUs, 8 GB of RAM, RAID-10 storage, and a 10- gigabit Ethernet connection.
A package named Package1 runs on SQL1. Package1 contains 10 data tasks. Package1 queries 3 GB of data from the source system.
You review the performance statistics on SQL1 and discover that Package1 executes slower than expected.
You need to identify the problem that is causing Package1 to execute slowly.
Which performance monitor counter data should you review?

Answer options

• A. SQLServer:SSIS Pipeline 10.0: Buffers in use
• B. SQLServer: SSIS Pipeline 10.0:Private buffer memory
• C. Memory\Pages/sec
• D. SQLServer:SSIS Pipeline 10.0: Flat butter memory

Correct answer: B

Explanation

The correct answer is B, as reviewing the 'Private buffer memory' counter will help you assess how much memory is being dedicated to SSIS tasks, which is crucial for performance. Options A and D are related but focus on buffers rather than memory allocation, while option C pertains to overall memory pages rather than SSIS-specific memory usage.