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:
is that steadily growing memory normal?
did I do any mistake in database design which probably lead to this behaviour? (no explicit temp-table drop, filegroup organization, etc)
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)?
do the continuous "open connection/do sp call/close connection" pattern disturb SQL Server?
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)
if I restrict the amount of memory which SQL Server is allowed to use, may I sooner or later get into trouble?
