Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Server 2008 R2 - Memory Allocation

Posted on 2012-09-21
7
Medium Priority
?
831 Views
Last Modified: 2012-10-18
Question,
After dropping the maximum of allocated memory for a SQL Server and restarting the services, the memory listing in the server details does not change. The server continues to pull upwards of 32GB when set at 28GB Max. Why?
0
Comment
Question by:paulrausch
  • 3
  • 3
7 Comments
 
LVL 38

Expert Comment

by:Jim P.
ID: 38424209
What are the results when you tun sp_configure?

sp_configure 'show advanced options', 1;
GO
RECONFIGURE with override;
GO
sp_configure
go

Open in new window

0
 
LVL 10

Accepted Solution

by:
Ramesh Babu Vavilla earned 1500 total points
ID: 38424293
if you are using SQL Server 64 Bit,then it is a bad consumer of RAM,
when you set max memory ,you are placing a limit for Buffer Manager not SQL Server,
run DBCC MEMORY STATUS for more information on memory consumption.

run this query
SELECT physical_memory_in_use_kb,locked_page_allocations_kb,
       page_fault_count, memory_utilization_percentage,
       available_commit_limit_kb, process_physical_memory_low,
       process_virtual_memory_low
FROM sys.dm_os_process_memory WITH (NOLOCK) OPTION (RECOMPILE);
-- You want to see 0 for process_physical_memory_low
-- You want to see 0 for process_virtual_memory_low
-- This indicates that you are not under internal memory pressure
0
 
LVL 2

Author Comment

by:paulrausch
ID: 38437733
When I run the SP_CONFIGURE I receive the following:

name      minimum      maximum      config_value      run_value
access check cache bucket count      0      65536      0      0
access check cache quota      0      2147483647      0      0
Ad Hoc Distributed Queries      0      1      0      0
affinity I/O mask      -2147483648      2147483647      0      0
affinity mask      -2147483648      2147483647      0      0
affinity64 I/O mask      -2147483648      2147483647      0      0
affinity64 mask      -2147483648      2147483647      0      0
Agent XPs      0      1      1      1
allow updates      0      1      0      0
awe enabled      0      1      1      0
backup compression default      0      1      0      0
blocked process threshold (s)      0      86400      0      0
c2 audit mode      0      1      0      0
clr enabled      0      1      0      0
cost threshold for parallelism      0      32767      5      5
cross db ownership chaining      0      1      0      0
cursor threshold      -1      2147483647      -1      -1
Database Mail XPs      0      1      0      0
default full-text language      0      2147483647      1033      1033
default language      0      9999      0      0
default trace enabled      0      1      1      1
disallow results from triggers      0      1      0      0
filestream access level      0      2      0      0
fill factor (%)      0      100      0      0
ft crawl bandwidth (max)      0      32767      100      100
ft crawl bandwidth (min)      0      32767      0      0
ft notify bandwidth (max)      0      32767      100      100
ft notify bandwidth (min)      0      32767      0      0
index create memory (KB)      704      2147483647      0      0
in-doubt xact resolution      0      2      0      0
lightweight pooling      0      1      0      0
locks      5000      2147483647      0      0
max degree of parallelism      0      1024      0      0
max full-text crawl range      0      256      4      4
max server memory (MB)      16      2147483647      28672      28672
max text repl size (B)      -1      2147483647      65536      65536
max worker threads      128      32767      0      0
media retention      0      365      0      0
min memory per query (KB)      512      2147483647      1024      1024
min server memory (MB)      0      2147483647      0      0
nested triggers      0      1      1      1
network packet size (B)      512      32767      4096      4096
Ole Automation Procedures      0      1      0      0
open objects      0      2147483647      0      0
optimize for ad hoc workloads      0      1      0      0
PH timeout (s)      1      3600      60      60
precompute rank      0      1      0      0
priority boost      0      1      0      0
query governor cost limit      0      2147483647      0      0
query wait (s)      -1      2147483647      -1      -1
recovery interval (min)      0      32767      0      0
remote access      0      1      1      1
remote admin connections      0      1      0      0
remote login timeout (s)      0      2147483647      20      20
remote proc trans      0      1      0      0
remote query timeout (s)      0      2147483647      600      600
Replication XPs      0      1      0      0
scan for startup procs      0      1      0      0
server trigger recursion      0      1      1      1
set working set size      0      1      0      0
show advanced options      0      1      1      1
SMO and DMO XPs      0      1      1      1
SQL Mail XPs      0      1      0      0
transform noise words      0      1      0      0
two digit year cutoff      1753      9999      2049      2049
user connections      0      32767      0      0
user options      0      32767      0      0
xp_cmdshell      0      1      0      0
0
Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

 
LVL 38

Expert Comment

by:Jim P.
ID: 38442262
Your configuration looks good. Have you run the query sqlservr suggested?

SQL 2008 usually runs a little over the configured memory, but it shouldn't be taking an extra 4GBs.

I'm going to suggest you get a DB Server monitoring tool to see what is eating the memory. I use Confio's Ignite Free, but use whatever you want to.
0
 
LVL 2

Author Comment

by:paulrausch
ID: 38444761
I did run the Query and I am infact getting 0 for both process_physical_memory_low and process_virtual_memory_low..

Memory Usage
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 38446652
There are ways to find what is using that extra memory.  Get a monitor system. And it really isn't that much slack.

KB             MB               GB
30965864       30240.10156      29.53134918

Open in new window


Once you do the math it comes out to 1.5 GB of extra memory. Just drop SQL to 26 or 27GB and go for it.
0
 
LVL 2

Author Comment

by:paulrausch
ID: 38454446
Ill give it a shot. Im no way a SQL expert; I just figured if you set the memory allocation to a maximum, thats as high as it will allow.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question