I have some java code that is doing the following:
Statement stmt = GetMsSqlConnection().createStatement(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY);
ResultSet rs = stmt.executeQuery("SELECT * FROM Table WHERE ID=1");
//do stuff with recordset
rs.close();
stmt.close();
This is part of a loop where I'm going through different IDs on the table. The problem is that while this is looping, I'm seeing my SQL Server process's RAM is growing on each iteration. When I step through my code, I notice that the line causing the issue is on the executeQuery call. I'm not surprised that this is causing some increased RAM use, but I would think that rs.close and stmt.close would free up the resources on my SQL Server. Is there some better of handling this or something in my environment I need to look at to make sure that I'm freeing up all the resources before the next iteration?
The server is simply caching most recently accessed pages. MSSQL will use all the memory available in the system if allowed to. It is not a memory leak, and MSSQL will not run out of memory, it will simply start evicting least used pages from its cache.