-1

I am very worried about the SQL Server 2012 Express instance on which my database is running: the SQL Server process memory usage is growing steadily (1.5GB after only 2 days working). The database is made of seven tables, each having a bigint primary key (Identity) and at least one non-unique index with some included columns to serve the majority of incoming queries. An external application is calling via Microsoft OLE DB some stored procedures, each of which do some calculations using intermediate temporary tables and/or table variables and finally do an upsert (UPDATE....IF @@ROWCOUNT=0 INSERT.....) - I never DROP those temporary tables explicitly: the frequency of those calls is about 100 calls every 5 seconds (I saw that the DLL used by the external application open a connection to SQL Server, do the call and then close the connection for each and every call). The database files are organized in only one filgegroup, recovery type is set to simple. Some questions to diagnose the problem:

  1. is that steadily growing memory normal?
  2. did I do any mistake in database design which probably lead to this behaviour? (no explicit temp-table drop, filegroup organization, etc)
  3. can SQL Server manage such a stored procedure call rate (100 calls every 5 seconds, i.e. 100 upsert every 5 seconds, beyond intermediate calculations)?
  4. do the continuous "open connection/do sp call/close connection" pattern disturb SQL Server?
  5. is it possible to diagnose what is causing such a memory usage? Perhaps queues of wating requests? (I ran sp_who2, but I didn't see a big amount of orphan connections from the external application)
  6. if I restrict the amount of memory which SQL Server is allowed to use, may I sooner or later get into trouble?
Sven
  • 97,248
  • 13
  • 177
  • 225
pgmo
  • 1

1 Answers1

1
  1. Sort of/Depends. SQL Server will generally grab as much memory as it can (which is usually a lot more than it's actually using). Don't worry about it, unless you're actually experiencing problems as a result of that behavior. Is it possible that the working memory set is growing because the database is growing and being used more heavily?

  2. Probably not, see 1

  3. Yes. The question is going to be whether your hardware can handle it, and how complex your stored procs are. And there's no good one-size-fits-all answer to that. Do some benchmarking.

  4. No. That's what databases are built for, in fact (well, one of the things).

  5. Yes, it's possible, but you probably want to determine whether it's a problem before investing time in doing so.

  6. Yes, that will bite you down the line when SQL Server needs more memory than you've limited it to.

HopelessN00b
  • 53,385
  • 32
  • 133
  • 208