SSIS Tips Memory Release Checklist

 

✅ SSIS Memory Release Checklist

---

STEP 1 — Make SSIS Run in 64-bit Mode

32-bit execution causes memory fragmentation and slower release.

✔ In Visual Studio (SSDT)

Project → Properties → Debugging → Run64BitRuntime = True

✔ In SQL Agent Job

Use 32-bit runtime → UNCHECK

---

STEP 2 — Avoid Large Lookup Caches

Full Cache lookups consume large memory.

Change Lookup Cache Mode To:

• Partial Cache

• OR No Cache (best for memory release)

---

STEP 3 — Reduce Data Flow Buffer Size

Large data flow buffers use more committed RAM.

In Data Flow Task properties:

Setting	Recommended
DefaultBufferSize	10–50 MB
DefaultBufferMaxRows	Lower value to reduce memory usage

---

STEP 4 — Monitor SSIS Runtime Memory Usage

Use Task Manager or PerfMon to check:

• DTSExec.exe (standalone executes)

• ISServerExec.exe (SSISDB executions)

Confirm memory drops after execution.

---

STEP 5 — Reduce SQL Server Max Memory (if SQL & SSIS share the server)

SQL Server will take as much RAM as possible unless limited.

Set a safe limit:

EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'max server memory (MB)', 40960;  -- example 40GB
RECONFIGURE;

This ensures SQL Server does not starve SSIS.

---

STEP 6 — After Package Finishes, Confirm Memory Release

Ensure:

• SSIS executable memory decreases

• OS free memory increases (or standby memory increases)

• No gradual memory buildup across executions

Read More »