MSQQL Service, Can we reduce memory (growth)?

[Stu]

Hi Folks,

Have been site:nettalkcentral.com'ing because I know I've read something about sql + memory growth + reducing due to file access etc, but can't find it.

1. I have a process that gets run which calcs totals from a number of different mssql tables (say ~25) and pushes the totals into a memory file. This process happens per user, and it gets run on about 10 users.

2. The MSSQL Service, if I watch it in Process Explorer, goes from about 905 meg to 3.5-4 gig in memory usage over the time this process runs (it takes a few minutes to run).

3. I've got code in the delete session embed, but as far as I know that's for reducing the memory size of the webserver file, not mssql.
  - And anyway, running it does not seem to affect the mssql service memory size at all.

What's the best way, or some good ways, to reduce the size of the mssql service memory usage?

I seem to remember that there was talk that the memory IS being reduced, but the task manager / process explorer won't show that. My concern is that the server IS starting to show load issues when it rises. So I guess I'm wanting to just understand and clear up what is happening.

[Stu]

** Should mention to:

The memory file in question is _not_ large. We're talking a couple of hundred, maybe a thousand sometimes, records at any time.

[kevin plummer]

You can't and you don't want to because it will only make your process slower. SQL likes to work in memory\cache because it's faster than disk i\o and it will suck up as much memory as it can\allowed. You should find the 2nd time your query run's it is faster because it is already in memory. Are you using prop:sql or clarion ABC code for your queries? Moving your code to stored procedures should also give you a performance gain.

cheers,

kev

[Stu]

Thanks Kev!

Code is PROP:SQL.

Stored procedures. Hmmm, need to investigate them, yeah.

[Larry Sand]

Stu,

One thing that happens when using dynamic SQL is that the server cannot reuse the execution plan and it consumes more memory.  If you use parameterized queries you get several benefits, reduced memory consumption, increased speed, resistance to sql injection attacks.  Here's an article that explains it well: https://www.simple-talk.com/sql/t-sql-programming/performance-implications-of-parameterized-queries/

Larry Sand

[debraballenger]

Hi Stu,
You can limit SQL Servers memory usage in general by running the below script, changing the 6000 to be whatever you want your memory limit for sql to be in.  It is a good thing to set this option if you have the server running other applications, so SQL does not crowd the other applications out. 

EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'max server memory (MB)', N'6000'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0'  RECONFIGURE WITH OVERRIDE
GO

[Stu]

Thanks Debra, very cool!