You have a server named SQL1 that has SQL Server Integration Services (SSIS) installed. SQL1 has eight
CPUs, 8 GB of RAM, RAID-1 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?
A.
SQLServer:SSIS Pipeline 10.0:Buffers in use
B.
Processor\\% Idle Time
C.
SQLServer:SSIS Pipeline 10.0:Buffers spooled
D.
SQLServer:SSIS Pipeline 10.0:Private buffer memory
On exam there is no answer Processor\\% Idle Time
Insist on this there is SQLServer:SSIS Pipeline 10.0:Flat buffer memory
I think Buffers spooled (or Buffer memory but is not an option here.) if the Processor Idle time is not an option.
https://msdn.microsoft.com/en-us/library/ms137622.aspx
I agree, performance related remarks are written in the article against ‘Buffer Memory’ and ‘Buffers spooled’ only.
Even if processor Idle time is there an option, I would choose ‘Buffer memory’ or ‘Buffers Spooled’.
So with respect to this question and given options, I would choose ‘Buffers Spooled’
It think itshould be C: buffer spooled
From 70-463 training kit Monitoring SSIS Performance with Performance Counters section:
The Buffers Spooled counter shows whether your data flow has started swapping to disk storage. When this counter is greater than zero, you should first try to optimize your package; if that does not help, increase the server memory.
3GB of data shouldn’t be causing going to swap in a 8GB server.
Best option would be the Buffer Memory, but it doesn’t appear.
The Buffer Memory and Buffers In Use counters provide totals for the server, both memory use and total buffer count. The Buffer Memory counter is very useful because it shows the total memory being used by SSIS and can be compared with the total amount of available system memory, so that you can tell if SSIS processing is limited by the available physical memory.
Performance counters: https://docs.microsoft.com/en-us/sql/integration-services/performance/performance-counters
I think it should be A. SQLServer:SSIS Pipeline 10.0:Buffers in use because this is the only option in my exam yesterday.
I gave the exam today on 26 Sep 2017 and the answer options were:
1) Memory pages/sec
2) SQLServer:SSIS Pipeline 10.0:Buffers in use
3) SQLServer:SSIS Pipeline 10.0:Flat buffer memory
4) I forgot this option, but it was not related to SSIS Pipeline
I am not sure what the answer is. I selected 3) Flat buffer memory.
The answer isn’t listed here but I think its :
Memory\pages/sec
‘Use memory counters that report paging activity to identify memory shortages resulting in disk bottlenecks.’
Bottlenecks = slowness
https://technet.microsoft.com/en-us/library/cc958290.aspx