asked
Physical memory usage is high.
Hello, I'm going threw high physical memory usage in SQL Server. It always around 90%.When i reboot the server, it will initially good but end of the day it will around 90to 95% physical memory usage. Configurataion : Windows SQL server 2008r2 Processor Intel Xeon CPU E5450 @3.00GHZ Installed memory 32.GB System type: 64bit operating system. I've installed Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64) Sep 16 2010 19:43:16 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7600: ). Allocate memory to instance is 26214MB. Can some one help me on this please.?
Is the server dedicated to SQL Server? By the fact that you are allocating 26214MB to SQL from a physical limit of 32Gb, it sounds like it. In that case I wouldn't worry that SQL Server is using a high portion of the memory - what else do you want to 'save' the memory for? The limit you have set is approx 80% of the physical memory, but this limit is only used for the buffer pool, and not all of SQL's memory requirements. Are you saying the RAM is 90-95% utilised in total, or that the sqlservr process is consuming 90-95%?
This is the normal behaviour of SQL Server, SQLOS manages the memory automatically.When SQL Server starts, it starts with minimal memory specified in the sp_configure min server memory, when it finds the memory is not enough for the workload, sql server will expand its memory gradually to the value specified in Max server memory in sp_configure. likewise, SQL Server will release the memory back to os when OS reports memory pressure(External memory pressure). Never reboot the SQL Server Service, this clears the buffer pool, proc cache and other memory related objects causing performance problems. SQL Server always read\write data in memory only(later they are written to disk), so when the data is not found in the memory, it needs to bring them from disk to memory(physical read), this degrades performance. Similarly when you execute a query, SQL Server generate checks for an existing plan to execute your query, if it cannot find any it will create a new execution plan, and SQL Server will save the plan in plan cache(another memory object) for later use, the plans are not saved for simple queries. Generating plan is CPU intensive process, so after a restart of service, SQL Server needs to rebuild all the execution plans.
Exactly. Unless you specifically configure it to do otherwise, SQL will gradually consume all available memory for caching purposes. On a dedicated SQL machine, this is not only normal, it is good. Now, on a multi-purpose machine you may need to tweak the settings to limit memory usage, but even there SQL will normally "play nice" and I would only put caps if you actually have performance issues with the other applications.
Seriously, don't worry about it. SQL Server uses as much memory as it can to prevent it having to read lots of data from the disks whenever you run a SELECT statement - it's helping you out, by caching this data and making queries faster. The only time you should worry about memory is if you don't have enough to keep SQL Server and the operating system running side by side, or if the server isn't dedicated to SQL Server.
Even then SQLOS is pretty good at freeing up the resources when the O/S demands it.
If the server is dedicated to SQL, then leaving SQL Server set to max emory of 26GB out of 32GB should be fine. I would leave it where it is.
If, on the other hand, it's also doing other stuff (eg SSRS, multiple instances of SQL Server, SSAS, IIS, whatever), then you would want to reduce it so that the other applications have a chance.
13 People are following this question.
