This session has great tips and tricks for you in SSIS and is in English (just in case you're reading this blog and don't understand Hebrew :))
Checking for problems when test loading data:
Select * from sys.dm_os_wait_stats
Number 1 wait: pageiolatch_up
Solution: add more spindles
So - just add more hardware?
Number 2 pagelatch_up
Solution: add more database files
Configure 1 to 4 files per filegroup to get 200+ KB writes IOs.
Use Soft Numa - assign a specific port for it
Use money data type instead of integer
Use Fast Parse
New to SQL Server 2008 R2:
Support for 64+ cores
Enterprise class SSD (Solid State Disks)
Unisys SQL Server SSD Solution codename SQL PowerRack
Bulk Insert file in SSIS - how can you speed it up?
Use a conditional split + modulo to double the speed (increasing the throughput)
Reading from a table as fast as possible
Read the data from 3 sources (all are the same table) using the union all to connect the data inside a new table. Use maxdop 1 for each OLEDB source.
Read more: Ella Maschiach's BI Blog
Posts
