I have created a very basic SQL table as following
CREATE TABLE [dbo].[TickData]( [Date] [varchar](12) NULL, [Time] [varchar](12) NOT NULL, [Symbol] [varchar](12) NOT NULL, [Side] [varchar](2) NOT NULL, [Depth] [varchar](2) NOT NULL, [Quote] [varchar](12) NOT NULL, [Size] [varchar](18) NOT NULL ) ON [PRIMARY]
I then performed a 3 Gig Bulk Insert
BULK INSERT TickData FROM 'C:\SUMO.csv' GO
Then RAM usage for SQL server went Skyrocking, eating up ~30Go of RAM :
I prefer to think this is an abnormal behavior and that action can be taken to avoid this.
Ok, This seems to be the default behavior. Fair enough.
However, why isn't memory freed up long after the Bulk Insert is finished ?
A couple of extra-considerations :
As of the comments concerning SQL server freeing the memory when it is "told to" by the OS, my hands-on experience on a 24-Core 32 Gb Xeon Server proves this to be inexact : Once a Memory-Voracious BCP extract is over I have a pool of .Net Instances of my data processing application that need to process the extracted data, and they are left choking/fighting to share the remaining memory to try to perform their jobs, which take faaaaar longer that when SQL Server is turned off and memory is available for all applications to share. I have to stop the SQL Server Agent to make everything go smoothly and prevent Apps from crashing for Articiallt caused OutOfMemmroy Exception. As to artificial Brutal Memory Capping/Limitation, if Free Memory is available, why not use it ? Ideally it would rather be dinamically set to adapt to what is available rather than just being forcibly limited "randomly". But I guess this is by-design, so case closed on this last point.