Memory leak when using time in NHibernate SQL query

Question

I use NHibernate in my ASP.NET application to connect to an MS SQL Server 2005 database. In some cases I need to write my own SQL queries. However, I noticed that the SQL server thread leaks about 50 KB of memory every time I execute the following piece of code:

NHibernate.ISession session = NHibernateSessionManager.Instance.GetSession();

ISQLQuery query = session.CreateSQLQuery(
  "select {a.*} from t_alarm a where a.deactivationtime > '" +
  DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss") +
  "'");

query.AddEntity("a", typeof(TAlarm));
System.Collections.IList aList = query.List();

When I look in Windows task manager I see that the process sqlservr.exe increases its memory usage by 50 KB each time I run this code.

Here is the really interesting part. If I, in the code above, replace "yyyy-MM-dd HH:mm:ss" with "yyyy-MM-dd HH:mm" (i.e. I remove the seconds) the memory leaks stop.

Update: Apparently the memory leaks doesn't actually stop. They just show once a minute instead. Presumably when the SQL query changes.

The returned results are the same in both cases.

Does anyone have a clue what is going on here?

Answer 1

You've added a query plan to the procedure cache, perhaps?

sqlservr.exe takes memory and does not release it unless it has to when other apps require it. Hence DB servers are standalone and not multi-purpose.

From BOL: Dynamic Memory Management

On a 32 bit PC with 2 GB RAM, SQL Server 2000 will max out consuming 1.7GB RAM. There is a KB article describing this.

In this case, by providing seconds you are implicitly stating "datetime" type, rather than "smalldatetime" type. This may affect your query plan. You may have an implicit conversion on "a.deactivationtime"

Answer 2

a) In the example you are not properly disposing NHibernate's Session, so the connection to DB is left opened.

Use using() {} statemenet or try{} cath{} finally{} to properly close connection.

b) SQL command you have written do not use SQL parameters, so SQL server will see this as a new command every time you execute it - more precisely every second (or if you remove :ss part, then every minute). Use SQL parameters (like NHibernate do when you use HQL, Criteria or QBE quering) and it will be cached properly with less memory consumption.

Hope this helps;)