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:

can you explain how you've come to the conclusion that SQL Server is leaking?
–
Nick KavadiasMar 9 '09 at 13:47

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. I added that to the question as well.
–
Henrik RipaMar 9 '09 at 15:10

2 Answers
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.

I agree that I should probably close the session. I will look into it. However, it didn't make any difference in this case. But I rewrote the query using HQL, and that fixed the problem. Many thanks.
–
Henrik RipaMar 10 '09 at 14:06

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"