2

I have a VB web application that reads from a CSV file which contains about 300,000++ lines. The application will read line-by-line and for each line, it will query a table in a sql server database which contains about 100,000++ records and based on the results of the query, the application will do something else. sqlservr.exe maxes out memory consumption in my development machine of 4GB.

So I created a simple application to test the performance of sql server in terms of loop query.

Dim Connection As New SqlConnection(ConfigurationManager.ConnectionStrings("SiteSqlServer").ConnectionString)
Dim Command As New SqlCommand("", Connection)
Connection.Open()
For i As Integer = 0 To 20000
    Command.CommandText = "SELECT CustomerID FROM Customer WHERE CustomerID = " & i
    Command.ExecuteScalar()
Next
Connection.Close()

Every time this code is executed, sqlservr.exe will take up an additional 100MB++ of memory and it's not releasing it back even though after the code has finished executing.

Is this normal? Is there any way to mitigate this effect?

Twisted Whisper
  • 1,166
  • 2
  • 15
  • 27

3 Answers3

2

Try this:

For i As Integer = 0 To 20000
    Command.CommandText = "SELECT CustomerID FROM Customer WHERE CustomerID = " & i
    Command.ExecuteScalar()
    Command.Dispose()
Next

The Dispose() call should clean up behind you and might mitigate the effect.

Femi
  • 64,273
  • 8
  • 118
  • 148
  • This has nothing to do with sqlservr.exe using memory. Dispose != Freeing memory. – driis Jun 13 '11 at 14:01
  • The assumption is that there are SOME server resources backing the executed command: `Dispose`ing of the `SqlCommand` should notify the server that those resources are no longer needed. As with all such details there is no way to tell if the resources will be immediately returned to the system or not, but if you are running a lot of these things it can't hurt to clean up behind yourself. – Femi Jun 13 '11 at 14:05
  • Agreed, you _should_ always` Dispose` your `IDisposable`s, I was just pointing out that it likely isn't the problem here. – driis Jun 13 '11 at 14:09
2

Sql Server does not immediately release memory back to the system, for several reasons (see: http://sqlnerd.blogspot.com/2006/07/memory-use-in-sql-server.html).

Anyway, you should use a Stored Procedure instead of a dynamic sql query: Sql Server can work more efficiently because your sql string does not have to be parsed each and every time.

It sure is, but double check - make sure that the table you're querying is properly indexed.

You might also consider loading your .csv file into sql server beforehand - if feasible. Having your .csv data in a sql server indexed table will make everything easier: no matter how well you optimize them, 100k round-trips to Sql Server are going to consume resources that you might be able to easily save by leveraging joins and server-side operations.

magma
  • 8,432
  • 1
  • 35
  • 33
0

Yes, it is normal. SQL Server in general doesn't release memory once it has acquired it. It's purpose is to deliver results to you as quickly as possible, and if it has data that is cached in RAM, it can be queried quicker. SQL Server might repurpose the memory blocks it has acquired internally, but it doesn't release them while it is running. This is by design. Try reading this.

This is also why, in general, you should run SQL Server on a separate box in production, so it doesn't contend with other applications for memory (such as IIS).

driis
  • 161,458
  • 45
  • 265
  • 341
  • so if this is normal, will the sql server crash if all of the memory is used up? or is it intelligent enough to recycle them and complete the task? – Twisted Whisper Jun 13 '11 at 14:59
  • No, SQL Server will not crash. It might use up all of your available memory, but it will recycle the memory it has claimed to cache other data. – driis Jun 13 '11 at 16:17