This query definitely needs to be optimized since the "DISTINCT" part is forcing creation of temporary tables.
If you really don't want to touch the query (which is a mistake) - then your other option would be to create a RAM disk (tmpfs) for your tmp files. The query will still be creating temp tables, however it will avoid the disk I/O part.
Hope this helps.
You've got a couple of questions in here:
1) Any ideas on why the SQL Server process would not be following the limit set on the server?
Max server memory refers to the buffer pool, but not everything that the server uses memory for. For example, SSIS isn't part of the SQL Server engine - it's another application that just happens to come free in the box with SQL Server. (Same thing with SSAS and SSRS.) Max server memory has no impact on SSIS. This is one of the reasons you'll often hear recommendations that you should separate SSIS onto its own instance - especially if you're using SQL Server Enterprise in virtualization. At $7k USD per core, you're talking about $28k worth of licensing here, and you're working with something like $500 of RAM.
2) Does anyone know a good way of profiling memory usage within SQL server? We can see that page life expectancy is dropping and more memory is being allocated to the process, but is there any way to see what SQL is allocating the memory to (i.e. procedure cache, cached results, etc).
Yes, inside the SQL Server engine, you can use DMVs like sys.dm_exec_memory_clerks. If you're using the free Opserver tool from the StackExchange guys for SQL Server monitoring, look at the memory clerks listing. If not, you can start with the memory clerks query it uses.
However, outside of the SQL Server engine - like with your SSIS packages - SQL Server DMVs can't help because this is happening out of process. You'll need to use conventional systems administration tools to do process monitoring and watch which processes use RAM.
Best Answer
SSIS will use as much memory as it needs. If there is not enough physical DRAM on the box, it will take it from the page file (which generally results in a big slowdown)
The memory for running SSIS does not come out of the SQL Server memory space, it comes out of the memory that is left on the box after SQL Server has taken what it wants. In your case, this would be 2GB. Remember that out of those 2GB, you will also need space for the OS and another other applications running on the box.
In other words, think of SSIS just like any other application - not as part of SQL Server (even though it installs with it).