Source:
Top 10 SQL Server Integration Services Best Practices
*********************************************************************************
"Does your system need to scale beyond 4.5 million sales transaction rows per second?"
SQL Server Integration Services can process at the scale of 4.5 million sales transaction rows per second.
SSIS is an in-memory pipeline, so ensure that all transformations occur in memory.
*
...robust pipeline that can efficiently perform row-by-row calculations and parse data all in memory.
...If transformations spill to disk (for example with large sort operations) ...
...extract and load phases of the pipeline will touch disk (read and write respectively), the transformation itself should process in memory.
:Construct your packages to partition and filter data so that all transformations fit in memory.
*
if your packages are staying within memory is to review the SSIS performance counter Buffers spooled, which has an initial value of 0; above 0 is an indication that the engine has started swapping to disk.
*
Plan for capacity by understanding resource utilization.
i.e., the CPU, memory, I/O, and network utilization of your packages.
CPU Bound
...how much CPU is being used by Integration Services and how much CPU is being used overall by SQL Server while Integration Services is running.
:important if you have SQL Server and SSIS on the same box
The perfmon counter that is of primary interest to you is Process / % Processor Time (Total).
Measure this counter for both sqlservr.exe and dtexec.exe.
:Application contention: SSIS ~ Ms Sql Server
:Hardware contention: suboptimal disk I/O or not enough memory to handle the amount of data being processed.
:Design limitation: SSIS package is not making use of parallelism
:Network Bound:
...important to understand your network topology and ensure that the path between your source and target have both low latency and high throughput.
The following Network perfmon counters can help you tune your topology:
Current Bandwidth:
Bytes Total / sec:
Transfers/sec:
...If it is approaching 40,000 IOPs, then get another NIC card and use teaming between the NIC cards.
...using gigabit network adapters, increasing the number of NIC cards per server, or creating separate network addresses specifically for ETL traffic.
I/O Bound:
...
But if your I/O is slow, reading and especially writing can create a bottleneck.
...
The following Network perfmon counters can help you tune your topology:
Current Bandwidth:
Bytes Total / sec:
Transfers/sec:
...If it is approaching 40,000 IOPs, then get another NIC card and use teaming between the NIC cards.
...using gigabit network adapters, increasing the number of NIC cards per server, or creating separate network addresses specifically for ETL traffic.
I/O Bound:
...
But if your I/O is slow, reading and especially writing can create a bottleneck.
...
Memory bound:
"How much memory does my package use?"
Baseline source system extract speed.
Measure the speed of the source system by creating a very simple package reading data from your source with the a destination of "Row Count":
Rows / sec = Row Count / TimeData Flow
:Improve drivers and driver configurations:
Note that for 64-bit systems, at design time you may be loading 32-bit drivers; ensure that at run time you are using 64-bit drivers.
:Start multiple connections:
...
if you start several extracts at once. If concurrency is causing locking or blocking issues, consider partitioning the source and having your packages read from different partitions to more evenly distribute the load.
...
:Use multiple NIC cards:
*
Optimize the SQL data source, lookup transformations, and destination.
When you execute SQL statements within Integration Services
Data access mode
SQL Command:
Select A,B,C,D From dbo.Staging WITH(NOLOCK)
:to read a source, to perform a look transformation, or to change tables, some standard optimizations significantly help performance:
- Use the NOLOCK or TABLOCK hints to remove locking overhead.
- ...SELECT only the columns you actually need.
- perform your datetime conversions at your source or target databases
- SQL Server 2008 Integration Services, there is a new feature of the shared lookup cache.
- ... Commit size 0 is fastest on heap bulk targets. For an indexed destination, I recommend testing between 100,000 and 1,000,000 as batch size.
- Use a commit size of <5000 to avoid lock escalation when inserting; ...SQL Server 2008 you can now enable/disable lock escalation at the object level
...
Tune your network.
A key network property is the packet size of your connection.
By default this value is set to 4,096 bytes.
This means a new network package must be assemble for every 4 KB of data.
SqlConnection.PacketSize Property in the .NET Framework Class Library:increasing the packet size will improve performance because fewer network read and write operations are required to transfer a large data set.
If your system is transactional in nature, with many small data size read/writes, lowering the value will improve performance.
Since Integration Services is all about moving large amounts of data, you want to minimize the network overhead. This means that the value 32K (32767) is the fastest option. While it is possible to configure the network packet size on a server level using sp_configure, you should not do this. The database administrator may have reasons to use a different server setting than 32K. Instead, override the server settings in the connection manager as illustrated below.
*********************************************************************************
*********************************************************************************
*********************************************************************************
SSIS Memory allocation
*********************************************************************************
*********************************************************************************
*********************************************************************************
*********************************************************************************
SSIS Interview Questions for Memory Management and Deployment
*********************************************************************************
*********************************************************************************
*********************************************************************************
No comments:
Post a Comment