Why is my SQL server using ~4 GBs of memory? I notice that pretty often my SQL server will eat up 50% of my 8GB of memory. This usually happens after I finish running some SQL queries, not while they're running. The most common query is one that is generating millions of rows of random data. In the query I use 

BEGIN TRAN
  ....
COMMIT TRAN


I'm guessing this might be the cause, but I don't know nearly enough about SQL to figure out the issue.
SQL Server uses that memory because it needs it, and typically won't release it unless there is pressure from the operating system to do so (since it assumes that you'll need that much memory again). So it is actually a good thing that it is using 4 GB of RAM, and if you don't need that memory for other things, why do you care?

You have basically two options:


Limit SQL Server to something lower (e.g. 2 GB). This will make your queries slower, but at least you'll still have all that precious RAM to sit idle.
Restart SQL Server when you're done with any high-memory operations that push it up over 4 GB. This will allow your queries to run with the memory they need, but still return that memory when finished. Not my idea of fun times, but hey...