CODEWITHSUNDEEP
sql-serverssissql-server-data-toolssqlperformance
Jithin Shaji
Jithin Shaji

Reputation: 6073

SSIS performance

I am working on an intel core-i3, 64-bit machine with just 4GB RAM.OS is Windows 7 and SQL Server 2012, evaluation is installed.

I am trying to do some SSIS development in it. i need to load a flatfile with 0.5 million records (156 columns/ approximately Row length of 3500 in total). SQL engine and SSIS engine are running in same machine. As I am using a small Pc, I dont expect high performance in my machine. See the below print screens.

Once my package starts running, the memory usage reaches maximum in no time.

enter image description here

enter image description here

See processes tab enter image description here

The CPU usage is just 3%, and memory may be 96%,

1. Even after closing, SSDT and SQL server management studio, the memory still remains at 95%, until I restart MSSQL service. Why is it behaving So?

2. How can I KNOW the I/O efficiency?

thanks in advance.

Upvotes: 1

Views: 312

Answers (2)

Dance-Henry
Dance-Henry

Reputation: 953

As per your problem, you should consider decreasing the buffer size or decreasing the batch size. For example, decreasing the DefaultBufferSize and DefaultBufferMaxRows.

Instead of using fast-load in OLE DB destination or SQL destination, you can use ODBC with appropriate batch size.

Due to the limitation of your machine, try to use parallelism properly if there is any.

Upvotes: 2

PCSgtL
PCSgtL

Reputation: 262

I think you need to set your flat file input to Fast parse.

This is a better guide.

http://www.bidn.com/blogs/BrianKnight/ssis/780/loading-flat-files-faster-in-ssis-with-fastparse

Old guide http://henkvandervalk.com/speeding-up-ssis-bulk-inserts-into-sql-server

Upvotes: 2

Related Questions