October 12, 2011 at 12:24 pm
Hello everyone,
I'm having a problem with my SQL Server not taking enough memory and suffering performance problems because of it. Here is my setup.
Sql Server 2008 R2 Standard SP 1 x64
Windows Server 2008 R2 Enterprise x64
8 GB RAM
2 Dual Core Xeon CPU
My Page life expectancy is low and almost all my buffer pages are stolen. SQL is only grabbing about 150 mb of memory.
Things I have tried
Set max memory to 6 gb
Set min memory to 4 gb
Turned on Lock pages in memory
Added Trace flag 845
Restarted the server
Banged my head against my desk repeatedly
Anyone have any ideas? Thank you.
Nathan
October 12, 2011 at 12:59 pm
anything grabbing the memory before SQL starts up?
---------------------------------------------------------------------
October 12, 2011 at 1:09 pm
The server memory is only at 20% utilization so I don't believe so. The only other thing on that server is SharePoint and it doesn't seem to be taking much.
October 12, 2011 at 9:37 pm
By default the 32 bit Operating System (the Kernel) can only use up to 2 GB of Virtual address space (VAS) leave 2 GB of Virtual address space for application.
On 64 Bit Opeartig System With IMAGE_FILE_LARGE_ADDRESS_AWARE set (default), Kernal mode have 8 TB VAS allocation and application have 8 TB VAS.
On SQL Server 2008 set the both Min Memory & Max memory option to use 3 GB for SQL Server Buffer pool.
Please note SQL Server is not guaranteed to allocate the amount of memory specified in min server memory. If the load on the server never requires allocating the amount of memory specified in min server memory, SQL Server will run with less memory.
Reference : Windows Memory Limits
Sagar Sonawane
** Every DBA has his day!!:cool:
October 13, 2011 at 1:37 am
Few Facts:
1) The amount of memory acquired by the Database Engine is entirely dependent on the workload placed on the instance. A SQL Server instance that is not processing many requests may never reach min server memory.
2) If the same value is specified for both min server memory and max server memory, then once the memory allocated to the Database Engine reaches that value, the Database Engine stops dynamically freeing and acquiring memory for the buffer pool.
3) If an instance of SQL Server is running on a computer where other applications are frequently stopped or started, the allocation and deallocation of memory by the instance of SQL Server may slow the startup times of other applications. Also, if SQL Server is one of several server applications running on a single computer, the system administrators may need to control the amount of memory allocated to SQL Server. In these cases, you can use the min server memory and max server memory options to control how much memory SQL Server can use.
4) Use min server memory to control memory usage. Establish minimum settings for each instance, so that the sum of these minimums is 1-2 GB less than the total physical memory on your machine. Again, you may establish these minimums proportionately to the expected load of that instance. This approach has the advantage that if not all instances are running at the same time, the ones that are running can use the remaining free memory. This approach is also useful when there is another memory-intensive process on the computer, since it would insure that SQL Server would at least get a reasonable amount of memory. The drawback is that when a new instance (or any other process) starts, it may take some time for the running instances to release memory, especially if they must write modified pages back to their databases to do so. You may also need to increase the size of your paging file significantly.
Now What to do:
1) Execute sp_configure and verify the difference between ‘config_value’ and ‘run_value’.
2) If there is any difference, reconfigure it.
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', <Some_No>;
GO
RECONFIGURE;
GO
sp_configure 'min server memory', <Some_No>;
GO
RECONFIGURE;
GO
October 13, 2011 at 8:13 am
How are you measuring the amount of memory used?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply